Address Field

[Guest]

I am new to Access so please excuse any stupid questions. I have a table
with doctors names and addresses, example, Last Name, First Name, Middle
Name, Street Address, Suite #, City, State and Zip, all separate fields. I
want to use a combo box so that when I click on a Doctors name it will
automatically insert all these fields into my form in different places. I
would like to use expression builder and not visual basic Visual Basic. Thank
you for any help you can give me.

Lj

 

[Guest]

Hopefully you also have a unique DoctorID column or similar in the table of
Doctors as names can be duplicated (I worked with two Maggie Taylors once!).
The combo box from which you pick the doctor should have as its bound column
the DoctorID, but hidden so you just se the names (the combo box wizard can
set this up but post back if you need help).

For the other controls on the form use the DLookup function to get the other
values, e.g. for Strret you'd have a ControlSource of:

=DLookup("Street", "Doctors", "DoctorID = " & [cboDoctors])

where Street is the field name, Doctors the table name and cboDoctors the
name of the combo box from which you select a doctor.

If this is a form bound to another table it is very important to store only
the DoctorID as a foreign key field in that table. You can look up the other
fields from the Doctors table in computed controls as above or by joining the
tables in a query as the RecordSource for a report or form.

Ken Sheridan
Stafford, England