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

 

[Pat Hartman]

Several things are wrong starting with the field names. In one place you have [EMP_ID#] and in another you have EMP_ID. Hopefully the real column name is the latter because it is poor practice to have special characters or spaces embedded in any object name.

The Lost_Focus event is the wrong event to use since it will be executed whenever the user tabs into and then out of it. A more appropriate event would be the BeforeUpdate event. That event is only executed if the field is changed and it may be cancelled to prevent bad data from being stored.

The easiest solution is to use a combo. That way the user can choose from a list and not have to type in the value. You won't need any validation code as long as the table is defined properly and referential integrity is enforced.

 

[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

 

[Pat Hartman]

Typically, comboboxes show a text value but store a numeric value. There are lots of examples here and in Northwind of how combos work. They can hold up to 64,000 rows. If you have more people than that, you can break the list down alphabetically.

 

[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.