By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,819 Members | 1,459 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,819 IT Pros & Developers. It's quick & easy.

prevent duplicate entry in unbound form

P: 12
I have used an UNBOUND form to add new record and used the vb code as follows:

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click
Dim rstAllotment As DAO.Recordset
Set rstAllotment = CurrentDb.OpenRecordset("SELECT * FROM [Allotment]")
rstAllotment.AddNew
rstAllotment![FinacialYear] = Me.cmbFY.Value
rstAllotment![PlanType] = Me.cmbPNP.Value
rstAllotment![Demand No] = Me.cmbDmnd.Value
rstAllotment![Major Head] = Me.cmbMj.Value
rstAllotment![Sub-Major Head] = Me.cmbSbMj.Value
rstAllotment![Minor Head] = Me.cmbMn.Value
rstAllotment![Plan Status] = Me.cmbPlStat.Value
rstAllotment![Sub-Head] = Me.cmbSb.Value
rstAllotment![detailed Head] = Me.cmbDet.Value
rstAllotment![Sub-detailed Head] = Me.cmbSbDet.Value
rstAllotment![ChargedOrVoted] = Me.cmbCV.Value
rstAllotment![StateContribution] = Me.txtStCon.Value
rstAllotment![Budget Earmark] = Me.cmbBrmk.Value
rstAllotment![BE 2016-17 Rs# In lakh_] = Me.txtBE.Value
rstAllotment![RE 2016-17 Rs# In lakh_] = Me.txtRBE.Value
rstAllotment.Update
MsgBox "Records added!"

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub
……
It works fine. But when I try to prevent duplicate entry with vb code as follows:
Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.newRecord then
Dim lngCount as Long
lngCount=DCount("*","[Allotment]", _
“[FinacialYear] =‘“ & Me.cmbFY.Value & “‘ AND “ & _
“[PlanType] =‘“ & Me.cmbPNP.Value & “‘ AND “ & _
“[Demand No] =‘“ & Me.cmbDmnd.Value & “‘ AND “ & _
“[Major Head] =‘“ & Me.cmbMj.Value & “‘ AND “ & _
“[Sub-Major Head] =‘“ & Me.cmbSbMj.Value & “‘ AND “ & _
“[Minor Head] =‘“ & Me.cmbMn.Value & “‘ AND “ & _
“[Plan Status] =‘“ & Me.cmbPlStat.Value & “‘ AND “ & _
“[Sub-Head] =‘“ & Me.cmbSb.Value & “‘ AND “ & _
“[detailed Head] =‘“ & Me.cmbDet.Value & “‘ AND “ & _
“[Sub-detailed Head] =‘“ & Me.cmbSbDet.Value & “‘ AND “ & _
“[ChargedOrVoted] =‘“ & Me.cmbCV.Value & “‘ AND “ & _
“[StateContribution] =‘“ & Me.txtStCon.Value & “‘ AND “ & _
“[Budget Earmark] =‘“ & Me.cmbBrmk.Value & “‘ AND “ & _
“[BE 2016-17 Rs# In lakh_] =‘“ & Me.txtBE.Value & “‘ AND “ & _
“[RE 2016-17 Rs# In lakh_] =‘“ & Me.txtRBE.Value & "'")

If lngCount>0 then
Dim intReply as VbMsgBoxResult
intReply=Msgbox("Warning: Possible Duplicate Entry detected!" & vbnewline & _
"Press Yes to continue Checking the entry," & vbnewline & _
"Press No to discard all changes",vbYesNo+vbExclamation)

Select Case intReply
Case vbYes
Cancel=True
Case vbNo
Cancel=True
Me.Undo
End Select
End If
End If
End Sub

.......
It does not work. No error code. But the duplicate entry added as many times I click the “add new record” command button.
Any help is eagerly solicited from the kind hearted fellows.
Dec 18 '16 #1
Share this Question
Share on Google+
2 Replies


Expert 100+
P: 634
Hi

Unless I am mistaken (and that is always possible), I don't think an UNBOUND form will not have an Update event (and hence no BeforeUpdate). Therefore you cannot Cancel this event!

To achieve the result you are trying to produce, you should put the current "BedforeUdate" code in the cmdNew_Click even sub before the existing code in that Sub.

HTH

BWT do you have a primary key (but not an Autonumber) in the Allotment table?
This could be a composite Key.
If you do then the DCount() could be much simpler (i.e. the where clause would just need to look for the PK not all the fields!!

I also generally use an Append query rather than a recordset in these circumstance.


MTB

ps Clearly the form will have an Update event, but what I mean is it will never have reason fire in an unbound form!!
Dec 19 '16 #2

P: 12
I did it with your help:
Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click

Dim lngCount As Long
lngCount = DCount("*", "[Allotment]", _
"[FinacialYear] =" & Me.cmbFY & " AND " & _
"[PlanType] =" & Me.cmbPNP & " AND " & _
"[Demand No] =" & Me.cmbDmnd & " AND " & _
"[Major Head] =" & Me.cmbMj & " AND " & _
"[Sub-Major Head] =" & Me.cmbSbMj & " AND " & _
"[Minor Head] =" & Me.cmbMn & " AND " & _
"[Plan Status] =" & Me.cmbPlStat & " AND " & _
"[Sub-Head] =" & Me.cmbSb & " AND " & _
"[detailed Head] =" & Me.cmbDet & " AND " & _
"[Sub-detailed Head] =" & Me.cmbSbDet & " AND " & _
"[ChargedOrVoted] =" & Me.cmbCV & " AND " & _
"[StateContribution] =" & Me.txtStCon & " AND " & _
"[Budget Earmark] =" & Me.cmbBrmk & " AND " & _
"[BE 2016-17 Rs# In lakh_] =" & Me.txtBE & " AND " & _
"[RE 2016-17 Rs# In lakh_] =" & Me.txtRBE & "")

If lngCount = 0 Then
Dim rstAllotment As DAO.Recordset
Set rstAllotment = CurrentDb.OpenRecordset("SELECT * FROM [Allotment]")
rstAllotment.AddNew
rstAllotment![FinacialYear] = Me.cmbFY.Value
rstAllotment![PlanType] = Me.cmbPNP.Value
rstAllotment![Demand No] = Me.cmbDmnd.Value
rstAllotment![Major Head] = Me.cmbMj.Value
rstAllotment![Sub-Major Head] = Me.cmbSbMj.Value
rstAllotment![Minor Head] = Me.cmbMn.Value
rstAllotment![Plan Status] = Me.cmbPlStat.Value
rstAllotment![Sub-Head] = Me.cmbSb.Value
rstAllotment![detailed Head] = Me.cmbDet.Value
rstAllotment![Sub-detailed Head] = Me.cmbSbDet.Value
rstAllotment![ChargedOrVoted] = Me.cmbCV.Value
rstAllotment![StateContribution] = Me.txtStCon.Value
rstAllotment![Budget Earmark] = Me.cmbBrmk.Value
rstAllotment![BE 2016-17 Rs# In lakh_] = Me.txtBE.Value
rstAllotment![RE 2016-17 Rs# In lakh_] = Me.txtRBE.Value
rstAllotment.Update
MsgBox "Records added!"
Else: MsgBox "Warning: Possible Duplicate Entry detected! Please check again", vbOKOnly, "Required Data"
Cancel = True
End If
Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub
....
The table has a primary key(with auto number),.. I don't know how to use PK,.. Thanks again for your kind response.
Dec 19 '16 #3

Post your reply

Sign in to post your reply or Sign up for a free account.