Help with a form

[Guest]

How would I do the following;

I have a simple trouble ticket database (table and form created off the
table). Since it's a small group of people, I have a drop down list for the
customer's name. I would like the form to show the customers phone number
and email address automatically when I select the costumers name from the
drop down list.

Right now I have created another table with the customer_information that
contains their phone and email but the only way I can see to include it
automatically in the form is to do a sub-form which works but doesn't give me
the look I'm going for (i.e. it creates a sub-form with the arrows and create
new record etc), I just want two more fields to auto populate the info when a
name is selected from the drop down list.

Hope that's not too confusing but any help would be greatly appreciated.

Cheryl

 

[Guest]

Cheryl:

You can use unbound text boxes on the form which look up the columns from
the Customer_Information table. There are a number of ways this can be done,
but one way would be to use the DLookup function. The expressions for the
ControlSource properties of the text boxes would depend on whether your combo
box's value is a hidden numeric CustomerID value or the actual name. Names
don't make good keys as they can be duplicated, so its better to have a
unique ID but if the combo box's value is the name the expressions would be
along these lines:

=DLookup("PhoneNumber", "Customer_Information", "CustomerName = """ &
cboCustomers & """")

=DLookup("EmailAddress", "Customer_Information", "CustomerName = """ &
cboCustomers & """")

where cboCustomers is the name of the combo box on your form. If the value
of the combo box is a hidden numericCustomerID there is no need to delimit
the value with quotes characters, so the expressions would be:

=DLookup("PhoneNumber", "Customer_Information", "CustomerID = " &
cboCustomers)

=DLookup("EmailAddress", "Customer_Information", "CustomerID = " &
cboCustomers)

You might find the above expressions have wrapped over two lines when you
read this. They should be entered as a single line as the ControlSource
properties in the text boxes' properties sheets of course.

Another approach would be to base the form on a query which joins the two
tables. You can then bind text boxes to the columns from the
Customer_Information table.

Ken Sheridan
Stafford, England