Msg when a record doesn't exist in table

[gguy]

Is there a way to popup a message in a form when a record does not exist in a table that is not bound to the form.

I am trying to automate some error checking in an application.

I have a form that is bound to a table called Test. The user enters an agency ID and a serial number into this form.

I want an error to popup when the agency ID has been entered and it does not match a record in the Agency table.

I also want an error to popup when the agency ID does match a record in the Agency table but the serial number is not in the Serial Number table.

Also, another error should display when the serial number is in the Serial Number table but the record shows a different agency ID.

Any ideas? Thanks, GGuy

 

[Rich]

Why not use a combo box?

 

[gguy]

I have stayed away from a combo box in this instance because there are a zillion agencies and 10 times as many serial numbers. When the user enters this info they have the product (serial number is stamped on the side) in their hand and a sheet of paper indicating the agency number. Therefore, I was thinking, it makes more sense to just allow direct entry and do a Dlookup on lostfocus.

Will a combo box allow direct entry and do the error checking that I need without dropping down a stinking list?

 

[gguy]

Rich, I added the combo box but it causes some problems.

First, when I enter and agency number, say 5, it immediatley shows 5027. For this app the users will not want to look at a list, so, why does it jump to 5027 when 5 is a legit agency number.

Second, if I enter Y5, which is not a legit agency number (in the agency table), it will accept the entry and write it to the test table. I need it to realize that Y5 is not legit and spit out a message then kick you back to reenter the record.

 

[ghudson]

You need to change the "Limit to List" property of the combo box to YES to prevent invalid values keyed in the combo box.

OR

You need to change the "Auto Expand" property of the combo box NO to prevent the auto searching of your keyed values in the combo box.

BUT

When the LimitToList property is set to Yes and the combo box list is dropped down, Microsoft Access selects matching values in the list as the user enters characters in the text box portion of the combo box, even if the AutoExpand property is set to No.

HTH

 

[gguy]

Thanks, That worked well for the Agency ID but I for the serial number I really didn't want the drop down list so I added the following code in the On Lost Focus;

If (DCount("[serial_no]", "standard", "[serial_no] = [serial]") = 0) Then
MsgBox "This serial number is not associated with any standard in the Standards table."
Else
If (DCount("[serial_no]", "standard", "[serial_no] = [serial] AND [Agency_ID] = [AG#]") = 0) Then MsgBox "This serial number is in the Standards table but is not associated with the current Agency."
End If

This seems to work fine except that after the error message the cursor returns to the next field. I would like it to return to the serial number field and clear it. Any ideas? GGuy

 

[ghudson]

You need to set the focus to the object you want for each IF where needed.

Me.TextBox1.SetFocus

HTH