Bound Combo Box - Not In List

[gavin31]

After struggling with some code I am looking for the reasoning behind why the following occurs.

I have a bound combo box (employeeId) with the limit to list property set to Yes, and the bound column = 1.

Entering a erroneus value prompts the "not in list" error message as expected.

But by leaving employeeId blank and leaving the combo box (e.g. tab to next box), the limit to list error message is not displayed.

To catch this error I have to put code into the exit event:

If (IsNull(Me.employeeID.Value)) Then
MsgBox "Please seect an employee from list"
Cancel = True
Me.employeeId.SetFocus
End If

This all works fine and does what I want it to.

But I have 2 questions.

1. Why does leaving employeeId blank or as a null string not trip the Limit to List property, since a blank value is not part of the list?

2. The code I put into the exit event does not work in the after update event, why is this?

Any thoughts would be most interesting

Cheers

Gavin

 

[mhartman1]

Hello:

Try the code below for your "Find Record" combo box and see what happens.

'Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[YourIDField] = " & Str(Nz(Me![YourComboName], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Regards
Mark

 

[gwinn7]

Honestly, there is a much easier way than to write code for that.

If you want a custom message for the user, then use some code in the Before_Update event. Access will take care of this validation for you if you set the "Required" property for that field to TRUE in the table design.

Then, apply a default value, usually 0. If you don't have a reference record to 0, then make one. You can do a simple INSERT on your Autoincrement to make a 0 record for a default value.

When someone tries to enter NULL, it won't take because Access, at that point won't allow it and tell the user.

Gary
gwinn7

 

[gwinn7]

Here is the SQL equivalent (something like)....

myfield int4 NOT NULL DEFAULT 0


Gary
gwinn7

 

[gavin31]

Gary, Mark

Thank you both for your suggestions, they are both better than the code I currently have.

I had completely forgotten about the Required property, I can now think of more places in my DB where I can use this.

However I am still curious to gain answers to the 2 questions I posed in m first post.

Any suggestions...blah blah blah

Cheers

Gavin