Subform parameter needs refreshing

[jonefer]

I have a subform that is nested 2 deep.

The main form is DocEntry
1st Subform is Offices
2nd is OfficesSpecsSub

I'm trying to limit the selection of 'Specialties' a doctor has in his/her
office, depending on what TITLE he/she has. The doctor can have multiple
titles.

So, on the OfficesSpecSub form, I'm using an embedded SQL that requires the
DocID from the main form to limit the selection.

I have tested the SQL statement with manually input values and it works fine.

But when the form is activated, the limitations of the Specialty box, do not
occur unless I hit ctrl+F9.

the rowsource for the 'Specialty combo box' is:

SELECT Specialties.SpecialtyID, Specialties.Specialty
FROM (Specialties INNER JOIN SpecTitleGroup ON Specialties.SpecialtyID =
SpecTitleGroup.SpecialtyID) INNER JOIN Doc_Titles ON SpecTitleGroup.Title =
Doc_Titles.Title
WHERE (((Doc_Titles.DocID)=[Forms]![DocEntry]![OfficesSub]![DocID]))
ORDER BY Specialties.Specialty;

What can I do to assure that the parameter is immediately read in the
current event?

 

[Marshall Barton]

I have a subform that is nested 2 deep.

The main form is DocEntry
1st Subform is Offices
2nd is OfficesSpecsSub

I'm trying to limit the selection of 'Specialties' a doctor has in his/her
office, depending on what TITLE he/she has. The doctor can have multiple
titles.

So, on the OfficesSpecSub form, I'm using an embedded SQL that requires the
DocID from the main form to limit the selection.

I have tested the SQL statement with manually input values and it works fine.

But when the form is activated, the limitations of the Specialty box, do not
occur unless I hit ctrl+F9.

the rowsource for the 'Specialty combo box' is:

SELECT Specialties.SpecialtyID, Specialties.Specialty
FROM (Specialties INNER JOIN SpecTitleGroup ON Specialties.SpecialtyID =
SpecTitleGroup.SpecialtyID) INNER JOIN Doc_Titles ON SpecTitleGroup.Title =
Doc_Titles.Title
WHERE (((Doc_Titles.DocID)=[Forms]![DocEntry]![OfficesSub]![DocID]))
ORDER BY Specialties.Specialty;

What can I do to assure that the parameter is immediately read in the
current event?

Not sure I followed all that, but it looks like maybe the
OfficesSub subform's Current event might need this line of
code:

Me.OfficesSub!Specialty.Requery

Whatever, the right line of code turns out to be, don't
forget that you will probably need the same line of code in
the DocID control's AfterUpdate event.

 

[jonefer]

That was the exact answer.

In the current event of the OfficesSubForm
I needed to requery the KEY field.

Thanks a bunch.

I have a subform that is nested 2 deep.

The main form is DocEntry
1st Subform is Offices
2nd is OfficesSpecsSub

I'm trying to limit the selection of 'Specialties' a doctor has in his/her
office, depending on what TITLE he/she has. The doctor can have multiple
titles.

So, on the OfficesSpecSub form, I'm using an embedded SQL that requires the
DocID from the main form to limit the selection.

I have tested the SQL statement with manually input values and it works fine.

But when the form is activated, the limitations of the Specialty box, do not
occur unless I hit ctrl+F9.

the rowsource for the 'Specialty combo box' is:

SELECT Specialties.SpecialtyID, Specialties.Specialty
FROM (Specialties INNER JOIN SpecTitleGroup ON Specialties.SpecialtyID =
SpecTitleGroup.SpecialtyID) INNER JOIN Doc_Titles ON SpecTitleGroup.Title =
Doc_Titles.Title
WHERE (((Doc_Titles.DocID)=[Forms]![DocEntry]![OfficesSub]![DocID]))
ORDER BY Specialties.Specialty;

What can I do to assure that the parameter is immediately read in the
current event?

Not sure I followed all that, but it looks like maybe the
OfficesSub subform's Current event might need this line of
code:

Me.OfficesSub!Specialty.Requery

Whatever, the right line of code turns out to be, don't
forget that you will probably need the same line of code in
the DocID control's AfterUpdate event.