Linking (1 Viewer)

[Peggy]

Does anybody know how to write a code in a "FORM" (MS Access 97) to error when duplicates are input? I cannot do it through the table because it is a linked database. I'm trying to find a way to do it in the form. Can this be done in VBA, macro or validation rule. I've had know luck yet. H.E.L.P.!

 

[jimbrooking]

Peggy,

Say the form's input field is tbxText, and the underlying table is called tblTable, and has a field in it called ID. Say the field you do not want duplicate values in is called Unique.

In the form's BeforeUpdate event put some code like

If DCount("ID", "tblTable", "Unique = """ & me.tbxText & """") > 0 Then
Msgbox "Can't have duplicate value", ,"Duplicate Value"
Cancel = -1
DoCmd.GoToControl "tbxText"
Exit Sub
End If
' Code ends on line above.

I'm assuming the value not-to-duplicate is text, not numeric or date. If it's numeric you need to remove the double-double-quotes (""). If it's date you need to replace the double-double-quotes by single pound symbols (#). See the online help for more about DCount.

HTH,
Jim