I would like to know if a value is duplicate as soon as entered.

[punter]

Hi,

The attached database has a form called BOL_information_entry. The first entry box on that form is called BOL #. I do not want to be able to enter duplicates into that field. Currently it will tell me if there is a dupe but only when I try to update the record. The subform may contain up to 15 lines of data so I do not want to enter all of them just to find that the the BOL # is duplicated. How can I get the form to tell me that I have entered a duplicated entry as soon as I do it?

Thanks

Eddie.

 

[KernelK]

Add this to the code for form BOL_information_entry:

Private Sub BOL___BeforeUpdate(Cancel As Integer)
    If DCount("BOL Number", "BOL information", "[BOL Number] = '" & Me.BOL__ & "'") > 0 Then
        MsgBox "BOL Number Exists", vbOKOnly, "Error"
        Cancel = True
    End If
End Sub

That should do it for you.

 

[RuralGuy]

This should get you started but you would not have as many errors to deal with if the field was not your Primary Key field.

 

[punter]

Thank you so much for your quick replies. It works great now.

Rural Guy, why would I have less problems if I didn't have that field set as my primary field? I'm relatively new to access and always trying to figure out how to make it work better. Any thoughts on why that makes a differnce are most welcome.

Thanks

Eddie.

 

[RuralGuy]

After posting, I realized you would have probably set up the same constraints on a non-primary key field. Sorry.

 

[punter]

Don't be sorry at all. You helped me. I am more than happy to listen to any advice that anyone has about Access. Thank you so much for your time.

Eddie.