lookup on a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi and thanks for the help. I am using Access 2003.

I have two tables. Here are the important fields for this question:

Table One: Client Number (primary key field), Client Name
Table Two: Client Number (used to relate the 2 tables), Order Date, Order
Amount

I would like to create a form that would work as a lookup. Nobody knows the
client numbers but we all know the client names. I would like to be able to
create a drop down box where I can select the Client Name from Table One and
populate the form with all of the associated fields and data from Table Two.

So when I select ABC Company from the dropdown I can see the client number,
order dates and order amounts.

Thanks for the help!
 
Sheri,

I would create a query with both tables. Connect the tables by Client
Number. In the Criteria box for the Client Name put this:

Like "*" & [What is the desired Client Name (or part of it)?] & "*"

Save your query. Now, use this query as the RecordSource for your
form.

Now, when you run the form, you will get an input box asking, "What is
the desired Client Name (or part of it)?" For ABC Company, the user
can enter any of the following and still get ABC Company:

ABC
BC Com
C Compan
Company

Using LIKE "*" & [] & "*" allows the user to enter any part of the
name.

The other thing you can do with this is to use this in the Criteria
line:

Like [What is the desired Client Name (or first part of it)?] & "*"

This will require the user to enter the FIRST part of the Client Name -
not just any part of it. Another thought.

Hope it helps.

Tammy
 
One of the options of the Control Wizard for Combo Boxes is to select the
record to be displayed on a form... a technique which used to be known as
Query By Form. That seems to be what you want to do -- Client Number would
be the hidden column in your Row source and Client Name would be displayed.

Larry Linson
Microsoft Access MVP
 
When you scroll through the records in the main form bound toTable 1, do you
see the related records from Table 2? I expect you do, since the tables are
related through Client Number. In that case you should see the related
Table 2 records whenever and however you select a record from Table 1. As
Larry suggested, the combo box wizard can perform this task. You don't need
to look at the client number unless you want to. You will probably want to
use an unbound combo box for selecting the client. With the main form bound
to Table 1 (Table 1 is the Record Source), you can bind controls such as
text boxes to any of the fields in Table 1.
 
Back
Top