Validation based on Lookup to 'nother table 1

[juliethedit]

Table A has list of 400+ valid values.

User types entry into Table B. I want to do a lookup in Table A to check that it is a valid value. Then generate a clear message if the entry is not found in Table A.

I don't want to use Relationship-Referential Integrity, because the error messages are hard to understand.

I don't want to make them do a pull-down because there are 400+ values, and each entry is only three digits.

Thanks much

 

[Maquis]

Well, If I were coding it, I would do the pull down combo box. The user doesn't have to actually pull down the list and choose an entry; they can start typing their entry in the combo box and the box will go to the entry that matches their typing.

However, if you really don't want to go that route, you could instead put a DLookup function in the 'on change' event of the text box they are entering.

Good Luck B-)

 

[juliethedit]

Combo box seems not to work, because the user can enter values that are not on the list.

Any pointers to DLookup code examples?

many thanks

 

[Maquis]

Hmmm, I thought you wanted to limit the users to be able to type only values that are in Table A. If so, the combo box would be perfect. If not, you can still use the combo box and just set the "limit to list" property to no.

If you want to use the Dlookup to validate what they type the syntax would be similar to the following:

Private Sub textbox1_change() '(or lost focus event)
Dim Criteria as string

Criteria = "[yourfieldname] = " Me!textbox1
If IsNull(DLookup("yourfieldname", Table A, Criteria)) then
msgbox "error"
end if
End Sub

What did you want to do if they type in a value which isn't in Table A? Put it in Table A or force the user to type something else?

 

[juliethedit]

"limit to list"
was exactly what I needed!

Thank you very much!

julie the dit