Prevent duplicates during data entry 1

[nerdgnome]

Hello,

I need to check that duplicate values are not entered into the field, batch, batch is a txt field.

The table mtblSample already exists and HAS duplicate values in batch, I want to keep this data, but prevent further duplicates from being entered.


The form frmSample on which the data is added has numerous fields so I would like the check to be done when the batch field is completed rather than when the record is saved.

Not used much VB, so not sure what event to add the code to or the format of the syntax, would include something like the following

If
Select Count (*) from mtblSample where batch like frmSample.batch

Msg = “Batch Number already entered”

End If

Bee

Bee
temp, thrown in at the deep-end....again!

 

[PHV]

In the BeforeUpdate event procedure of the batch control:

If Not IsNull(DLookUp("batch", "mtblSample", "batch='"& Me!batch & "'")) Then
  Msg = "Batch Number already entered"
  Cancel = True
  Exit Sub
End If

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nerdgnome]

Thanks.

Seams to work, i'll need to mod it slightly to allow N/A duplicates!! (theres always always something you didn't think of!!)

Bee
temp, thrown in at the deep-end....again!