Lookup field

[RipperT]

Hey, all,

I want to make a lookup field in a form that limits the
data entered to values from another field in another
table. I want the user to be able to type in the data,
not pick it from a drop down list. How can I do this, and
can it be done with an input mask?

Rip

 

[Sandra Daigle]

The combo or listbox is the normal way to do this - it is relatively easy to
do - here is a KB article that describes how:

How to Synchronize Two Combo Boxes on a Form
http://support.microsoft.com/default.aspx?scid=kb;EN-US;289670

If you don't use a combo or listbox then you will need to validate the
user's entry using the BeforeUpdate event of the control. You can use the
Dlookup function to query the other table:

me.field2=dlookup("Field2","tblMyTable","Field1=" & me.field1)

Note that if no matching value is found, Dlookup will return a Null which
may cause problems so you may need to use the NZ function to convert the
null to something else or, test for the null using the isNull function and
warn the user that no match was found.

 

[Sandra Daigle]

One other note, preferably field2 (Bad choice of names on my part) is an
unbound control on the form that merely displays the data from field2 that
is related to the value in field1. The information should not be stored in
two tables.

Actually, all of my names were rotten since they don't help you
differentiate between names of controls versus names of fields in the table.
So here is the revised Dlookup:

me.txtField2=dlookup("Field2","tblMyTable","Field1=" & me.txtfield1)

The controls are prefixed with 'txt'. Hope that's more understandable.