DLookup in VB Conditional Statements.

[InQuery2004]

Greetings,

I am working with a data-entry form for my database. On this form, I would like to search a table in my DB with the current number entered in a textbox named txtEmpID. If the number is not found in the table, then the event will display a message telling the user to enter a valid employeeID or something to that effect. Although I thought that this would be a simple subroutine using DLookup I have been stuck for a few hours with bugs in this event. Here's my event procedure (copied and pasted):

Private Sub txtEMP_ID_LostFocus()
If (DLookup("[EMP_ID#]", "AssociateFixedInformation", "EMP_ID =" & Forms![AddNewProfile]!txtEMP_ID) = Null) Then
MsgBox "The EmployeeID that you have entered is for a non-existant Associate" _
+ " Please enter an EmployeeID for a current employee already in databas."
End If

End Sub

When I try to run the event, it generates some error saying that I cancelled the previous operation. What does that mean and more importantly, how can I get this operation to run on my form???

Thanks very much for the help

 

[InQuery2004]

Thanks for the swift response Pat. Acutally, I also realized this error with the field name. Unfortunatley for me, the field name in the table is EMP_ID# and not EMP_ID which is the name of the texbox control. What can I say? this is my first database project (and I'm actually doing this for work oddly enough)


Your point is well taken with the combo box. I will apply that approach, but there is a complication. The record source for this data entry form only has a field for an EmployeeID number, which isn't very intuitive for a data-entry operator. I have another field listing the Employee's names but it is in another table called AssociateFixedInformation. Is there a way I can somehow incorporate that field from the external (but related) table into this form as a combo box. When I tried to do this before, I got errors.

Thanks in advance once again.

 

[InQuery2004]

One thing I completely overlooked that makes a combo box impractical: this database will be holding thousands of records of employees, making a combo box cumbersome.

Given this information, what can I do to the EMP_ID textbox event procedure to make it run correctly. Here's my most recent sub:

If IsNull(DLookup("EMP_ID#", "AssociateFixedInformation", _
"[EMP_ID#] = " & Me.txtEMP_ID)) Then
MsgBox "The EmployeeID that you have entered is for a non-existant Associate"
End If

 

[InQuery2004]

Just to let you know, I took your suggestion and implemented the data entry form with a combo box. It works quite well actually. Thanks for the help Pat.