Record already exists message box?

[Ezyflo]

I have a table with the primary key set to EmployeeID.

Users will be inputting information via a form. I would like a message to pop up after the user enters the EmployeeID on the form to confirm that it is correct before they proceed. I also need to have a message pop up if the EmployeeID already exists (they wouldn’t be able to save the record anyways) and to verify their EmployeeID or contact administration for support.

I’m thinking that I would do this in VB code and set it in the lost focus event of the textbox. I’m not sure how to code it.

Any help would greatly be appreciated.

Thanks.

 

[SBendBuckeye]

I would suggest you use the Before Update event instead of the Lost Focus event because Before Update includes a Cancel parm which if set to True automatically returns focus to the control regardless of what the user clicked or what key they pressed.

You can use the DCOUNT function to verify that the current value does not already exist. It takes the following parms:

FN - Some field name enclosed in double quotes ". If the field name has embedded spaces you must use square brackets around the field name. Example "FirstName" or "[First Name]"

TN - Some table name with same rules as above for Field Name

WC - SQL Where Clause without the WHERE with the same rules as above for field name and table name. If the value is a string it must be enclosed in single quotes ', if it is a date or time value it must be enclosed pound signs #.
Example "[Last Name] = '" & txtControl & "'"
"HireDate > #" & datControl & "#"
&quot;TotalSales < &quot; & numControl

In all cases, you can always use square brackets []

Using the above, try something like the following where ctlEmployeeID is the name of your form control:

Private Sub SomeCommandName_BeforeUpdate(Cancel As Integer)

If DCount(&quot;FN&quot;, &quot;TN&quot;, &quot;ctlEmployeeID = &quot; & EmployeeID Then
Msgbox &quot;EmployeeID &quot; & ctlEmployeeID & &quot; already &quot; _
& &quot;exists&quot;, vbOKOnly + vbInformation, &quot;Data Error&quot;
ctlEmployeeID.Undo
Cancel = True
Exit Sub
ElseIf vbNo = Msgbox(&quot;Please confirm addition of &quot; _
&quot;EmployeeID &quot; & ctlEmployeeID, vbYesNo + vbQuestion, _
&quot;Data Verification&quot; Then
Cancel = True
Exit Sub
End If

End Sub

Good Luck!

 

[Ezyflo]

Thanks! I'll give it a go.