before update of form

[maxmangion]

i have a table with 2 fields (StampID and ThemeID). Now i created a query with 3 fields (StampID, ThemeID and the third field as follows

validate:[StampID] & "," & [ThemeID]

now i created a form with 2 fields (StampID and ThemeID). i created an unbound box (Text7) and i wrote in it =[StampID] & "," & [ThemeID]

Now in the before update of the form i did the following code:

Dim counter As Long
counter = DCount("*", "qryStampsThemes", "validate = forms!frmStampsThemes!Text7")
If counter > 0 Then
If MsgBox("Record already exist" & vbCrLf & "Discard Record (Yes) or Amend Record (No)", vbYesNo, "Duplication Error") = vbYes Then
Cancel = True
Me.Undo
MsgBox "Recorded Discard Successfully"
Else
Me.ThemeID = ""
Me.ThemeID.SetFocus
End If
End If

Is this a correct approach to avoid duplicate records in this table ?

 

[MikeAngelastro]

The best way to avoid duplicate records in a table is to designate some of the fields as keys.

 

[maxmangion]

yes actually, in the table design view, i selected both stampid and themeid as keys, but i used the above method, so that the duplication message will be more user friendly. ideally, i just wanted to know, if the above method is correct!

 

[MikeAngelastro]

You could just let the error happen and then trap it. Find out what the error number is and, if that is what causes an error in the future, inform the user with a very user-friendly message.

ExitSub:
Exit Sub

HandleError:
If err = 1234 then
msgbox "Blah blah blah"
resume ExitSub
end if

It just looks like to me that the approach you want to use may be more code intensive than needed. With the error trapping method, processing occurs only if there is an error.

 

[Pat Hartman]

I concur with Mike. Given that the majority of entries will be error free, you should code based on that. ie, trap an error that did happen rather than trying to prevent one that isn't going to happen.

 

[maxmangion]

ok thank you for your guideline ! However by the error trapping which you suggested i.e. if err = 1234 .... , shall i still do that in the before update of the form ?

Thank You!