Look Up Feature & Queries

  • Thread starter Thread starter Kate
  • Start date Start date
K

Kate

I am trying to create a querie in my database from
tables...I have used the "Look Up Wizard" feature in
designing the table...
**example**
TABLE1
ID - autonumber
First Name - text
Last Name - text
Location - Look Up Wizard
....and location looked up values in Table2...
TABLE2
ID - autonumber
Location - text
I encounter a problem as I try to querie the Location
field - any recomendations on how to eliminate use of the
Look Up Wizard? I am trying to eliminate misspellings of
Location on the form the user will input the data on...

Please & Thanks!
 
If you go to the design of the table "TABLE1" and click on
field "Location". Under the bottom, there should be two
tabs "General" and "Lookup". Click on "Lookup" and change
the Display to "Text Box". It will disable the Lookup.
 
JL...
Thank you for the suggestion, I have already tried that
but then the value of "Location" in "TABLE1" is changed to
a numerical value (1, 2, 3, etc)...instead of the cities
as the look up requests...
 
Sorry, I miss read your question. Actually, it is more a
Form question than a Query question.

To eliminate mis spelling on the Form. You have to create
a combo box and use either a table or query as record
source that only list the location that are valid.

Of course, that does not prevent the user to type anyting
that they want. So, in the "After Update" or "On Change"
or "Not on List" event of the combo box, put the
following code and it will prevent anything that is not in
the list. The code will blank out anything that is not
valid (not on the list).

Me![LocationCombo] = Me![LocationCombo].Column(0)
 
That is fine. Then you use a query to get and display your data. And a
combobox on your entry form to enter the data.

The query would look something like:

SELECT T1.FirstName, T1.LastName, T2.Location
FROM Table1 as T1LEFT JOIN Table2 as T2
ON T1.Location = T2.ID

Your combobox on your form would have a record source of Table1.Location and a
Row source of

SELECT Table2.ID, Table2.Location
FROM Table2
ORDER BY Table2.Location
 
Back
Top