Look up, subform or other

[Guest]

I have two tables, Resident(many) and Doctor(one) and I want to design a form
which is based on a resident and looks up the doctor name with a combo box
and auto complete for the details such as address and phone etc. Is this
possible and how do I do it?

 

[Guest]

Hi Moni,

Yes, it is possible. The easiest way, in my opinion, is to include the
information that you want to add to the textboxes in the row source for the
Doctor's combo box. For example:

SELECT pkDoctorID,
DocLastName & ", " & DocFirstName AS Doctor,
DocAddress, DocPhone
FROM tblDoctors
ORDER BY DocLastName, DocFirstName

This query includes (4) fields: pkDoctorID, Doctor, DocAddress and DocPhone,
where Doctor is a calculated field based on concatenating the Doc's first &
last names together. You will likely want the bound column to be column 1 in
the properties sheet for the combo box. The Control Source will be the
foreign key field that should be present Resident table.

The textboxes that you want to autopopulate are calculated controls that
refer to a zero-based column count. For example, the Control Source for
txtDocAddress would be: =[cboDoctors].[column](2) since pkDoctorID would
be column zero, and Doctor would be column 1.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________