need help with some VBA

Factual Questions
1

I’m not very well versed in VBA, but I think it can do this and I just can’t figure out how: I’ve got a form in Access, and while the database is allowed to have duplicates in a particular field, I would like a message box to display when the user enters a duplicate value. It’s not really worth getting into why I need to do it this way, but suffice it to say that supervisors sometimes want what they want and it’s not worth arguing about.

So when someone keys a value into the ACCOUNT_NUMBER field in this form, I need a way to warn the user that they’ve entered a duplicate. Anyone know how to do this?

2

You could use the DLookup or DCount functions. Something like this would work:
Private Sub ACCOUNT_NUMBER_AfterUpdate()
If DCount("[ACCOUNT_NUMBER]", “[MYTABLE]”, "[ACCOUNT_NUMBER]= Forms![MYForm]![ACCOUNT_NUMBER] ") > 0 Then
MsgBox “This ACCOUNT NUMBER has already been entered”, vbInformation
End If
End Sub

3

thanks, I’ll give it a try now and let you know how it works out.

4

that did the trick, thanks for the help!

5

Make sure that field is indexed, allowing duplicates. Otherwise, DLookup/DCount will take a looonnnnggg time once you get some serious data in the table.