Data entry form...

[Nightowl4933]

I have a form for users to enter data and, on clicking a Command Button, adding that record to the dataset.

I've added some basic error checking to ensure the necessary fields have data before the record can be added, but I'd like to prevent duplicate records being added.

Only the [Reference] field cannot be a duplicate (everything else can be), but I was considering using a Lookup of that field in the data table, and using a MsgBox() to identify to users a record with that [Reference] already exists.

Could someone give me a starting point, please?

Thanks.

 

[Ryker]

What do you have set as the primary key? that is usually the unique ID that can't be duplicated.

If this isn't what you want as a PK, then I would use a validation rule. You can go into design view on your table and at the bottom under general there is a validation rule area. Kind of like doing expression builder.

 

[arnelgp]

on before update of the textbox on the form that holds Reference:

Private Sub textbox_BeforeUpdate(cancel as integer)
If Not IsNull(DLookup("Reference", "table", "Reference = " & Chr(34) & Me.textbox & Chr(34))
'does not exists
Else
'reference exists
'do not move to another control
Cancel = True
End If
End Sub