Lookup in subform

Using Access 2003 I have a table of medical practices and a table of
physicians and the corresponding forms. The form for the physicians is a
subform in the form for practices as one practice can have multiple
physicians. A lookup was created to find the practices in the form. Now a
lookup is needed for physicians. What is the best way to create a lookup for
a physician (whose information is in the subform) and have it sychronize to
the correct the practice form?

Hi,

In the RowSource for your combobox, you could have something like:

SELECT ID, FirstName, LastName
FROM tblPhysicians
WHERE PracticeID = Forms!MainFormName.PracticeID

Please modify to suit your object and field names.

Hope that helps..


"Basenji" wrote:

Using Access 2003 I have a table of medical practices and a table of
physicians and the corresponding forms. The form for the physicians is a
subform in the form for practices as one practice can have multiple
physicians. A lookup was created to find the practices in the form. Now a
lookup is needed for physicians. What is the best way to create a lookup for
a physician (whose information is in the subform) and have it sychronize to
the correct the practice form?

Okay, I have created an unbound combo box in the header of my form. The row
source is SELECT ID AS Expr 1,
tblPhysiciansNursePractioners.LastName,tblPhysicia nsNursePractioners.FirstName FROM tblPhysiciansNursePractioners
WHERE
(((tblPhysiciansNursePractioners.PracticeID)=[Forms]![frmPractices].[PracticesID]));

Now I am getting an Enter Parameter Value. What needs to change in order to
have a lookup for a physician's name so that his practice can be found, when
the practice is not known?

"theDBguy" wrote:

Hi,

In the RowSource for your combobox, you could have something like:

SELECT ID, FirstName, LastName
FROM tblPhysicians
WHERE PracticeID = Forms!MainFormName.PracticeID

Please modify to suit your object and field names.

Hope that helps..


"Basenji" wrote:

Using Access 2003 I have a table of medical practices and a table of
physicians and the corresponding forms. The form for the physicians is a
subform in the form for practices as one practice can have multiple
physicians. A lookup was created to find the practices in the form. Now a
lookup is needed for physicians. What is the best way to create a lookup for
a physician (whose information is in the subform) and have it sychronize to
the correct the practice form?