prevent a specific value from being duplicated in MS access using vba

Question

in my form there is "Position" combo box which is bounded to "Position" field in table1. To make it simple,let's say that each person will get the position "employee" and only one person will get the position "MANAGER",i want to get a warning message(in VBA) if i assigned the "MANAGER" title to another person and preventing the process unless i remove the position from the first person . i googled around and i found that i have to use "DLOOKUP" function ,tried that but still no luck.any help would be a appreciated ,thank you

Davy C Davy C · Accepted Answer · 2016-05-27T19:26:53

Try creating an "AfterUpdate" event on the Combobox on the Form and putting this code in there:

    If Nz(DLookup("Person", "Table1", "Position = 'MANAGER'", "NOTFOUND") <> "NOTFOUND") Then

      MsgBox ("Someone is already assigned to role MANAGER")

    Else

    'OK, no manager currently exists

    End If

I have assumed that "Person" is the field in "Table1" that will hold the people's names.

Hope this helps.

prevent a specific value from being duplicated in MS access using vba

1 Answers