Referencing a Main Form Field in a sub-form Combo Box Lookup Query

[rjgriffin46]

In an unbound combo box query to look up and select students in a subform, I want to limit the query lookup list based on the value in a field (the Class field) in the main form so that the lookup only shows students in the Class of the main form record. I can create a Criteria expression in the query referencing the class field in the main form ([Forms]![Tests and Grades]![Test Parameters]![Class Number]) but when using the combo box an "Enter Parameter Value" window appears for the expression. I can enter a literal (eg., "2015-1") in the query criteria for that field and that works fine.

I suspect that since this is a query, any fields on the form are not available, hence Access treats it as a query parameter. If so, what is the preferred technique for referencing another field on a form as criteria in a combo box lookup? If it is a VBA module, can you point me to a similar sample? This is needed on two forms, one for recording test scores and one for recording student evaluations. Both of these have the same form, sub-form and combo box structure and both need the same student look up criteria that the student's class be the same as the main form record's class.

In searching the internet I found the following which might be relevant. In my case, the Class field on the main form is also a combo box, although it does not have to be. Also, the user will not update the class field in the main form, so there would not be a triggering Event for that field. Could it instead be a Form level event such as On Load or On Open?

Here is the link:
http://msdn.microsoft.com/en-us/library/office/ff837224%28v=office.15%29.aspx

Here is the sample code from this link:

Private Sub cboCategories_AfterUpdate()

' Update the row source of the cboProducts combo box
' when the user makes a selection in the cboCategories
' combo box.
Me.cboProducts.RowSource = "SELECT ProductName FROM" & _

" tblProducts WHERE CategoryID = " & Me.cboCategories & _

" ORDER BY ProductName"

Me.cboProducts = Me.cboProducts.ItemData(0)

End Sub

In the sample abovec I would change the following (please correct):

From	To
cboProducts	StudentNumber*
ProductName	[Students Extended].[Student Name]
tblProducts	[Students Extended]**
CategoryID	[Students Extended].Students.[Class Number]
cboCategories	Class Number***

* StudentNumber is the sub-form combo box field name and control source.
** Students Extended is the query upon which the lookup query is based.
*** This is the main form combo box name. The Expression builder yields "[Forms]![Tests and Grades]![Test Parameters]![Class Number]" when you choose from the field list of the loaded form. I am not sure which is the correct reference...?

As always, your guidance and assistance will be greatly appreciated.


Thanks

[amrut]

If I understand, correctly you should not require any code to set up.
Create the main form which has the table Class as its recordsource.
Create another form having student details and Class they belong.
Use the second form as sub-form linked (Master-Detail arrangement) to the main form based on the Class field which is common to both the forms.
So the subform will always show only students belonging to the class on main form.

[rjgriffin46]

While preparing a response to your questions, I believe that I figured out the problem and solution as follows:

While in the Expression Builder, attempting to select the field for the criteria, I first drill down in the left panel (Expression Elements) to Forms, Loaded Forms, Test Grade Results (the form I am working on). This then loads the middle (Expression Categories) and right (Expression Values) panels. I'd always clicked on "<Field List>" in the middle panel (Expression Categories) then selected the field I wanted from the resulting list in the right panel. In looking closer at the middle panel, I saw the field (Class) was listed there and so I selected that and amazingly the lookup query then worked.

As I look at it more closely at Expression Builder yesterday and again today, I see that after selecting the form, the 1st option in Expression Categories is "<Form>" and the right panel is then loaded with Event and other (all?) properties, I guess of the overall form.

But in the middle panel (Expression Categories) is listed all the things (labels, controls, subforms, header, detail and footers) on the form, which includes the Class Number!

So it seems that when in the Expression Builder and you've selected a form in the left Expressions Elements panel, the middle Expression Categories panel lists the Form Controls and when you select <Field List> then what appears in the right Expression Values panel are the underlying table fields. I don't recall reading that in any of the 2000+ pages of my 2 reference books. I even looked at them again yesterday and all they cover is expressions in queries that don't involve referring to forms.

What further misled me was that when I incorrectly selected from the field list, the from name was included:

"Forms![Tests and Grades]![Test Parameters]![Class Number]" so it looked like the right thing.

But the really right thing is: "Forms![Tests and Grades]![Class Number] ", the somewhat subtle difference being that the 1st one includes the table name "[Test Parameters]" and thus must be pointing to the underlying table while the correct one points to the control on the form. I guess I don't quite understand that with [Test Parameters].[Class Number] as the control source the query isn't able to see the value in it. What then is the point of the Expression builder showing the field list?

Its annoying that it took me so long to figure this out and that none of the reference and help on Expression Builder seem to talk about how to reference controls on a form. But better late than never.

Let me know if you think this is correct or other thoughts you may have.

I also submitted this reply to the other response I received from June7 - I'd unwittingly submitted the post twice and June7 replied to the other one.

I've yet to resume the copying forms to the blank database and submitting a redacted copy to you for assessment.

Thanks

[John_G]

I don't see where you update the contents of the combo box. Just changing the RowSource does not change the contents - you have to requery the combo box after changing the RowSource - me!studentnumber.requery.

[pbaldy]

I'd unwittingly submitted the post twice

I noticed that and since there appeared to be no substantive discussion on the other thread I deleted it.

[rjgriffin46]

Paul: There was a post from June7.

John_G: You seem to be referring the the sample VBA code I found and included in my original post as a possible solution. But if you look at Post #3 you see that I seem to have found the correct way to reference the control on the form in the combo box lookup query criteria expression.

Do you agree?

[June7]

I don't use the expression builder just as I don't use other design wizards.

If the dependent controls are on the same form, the cboProducts RowSource property can be simplified to:

SELECT ProductName FROM tblProducts WHERE CategoryID = [cboCategories] ORDER BY ProductName;

Then cboCategories AfterUpdate event code would requery cboProducts and set the ItemData value. Actually, cboProducts GotFocus event might be better for the Requery.

[rjgriffin46]

Can you comment on my post # 3 of 1/6/2015, 3:30 pm as to whether the solution I think I found is correct or not or if you have no opinion? Depending on your response I can close the thread.

thanks

[June7]

The tablename prefix would be needed only if the datasource is a query that includes several tables and there are fields pulled from the various tables that have the same name. For instance, if each table had a field named ID, then the tablename prefix would be used as part of the field reference so as to differentiate the various fields.

[rjgriffin46]

The tablename prefix would be needed only if the datasource is a query that includes several tables and there are fields pulled from the various tables that have the same name. For instance, if each table had a field named ID, then the tablename prefix would be used as part of the field reference so as to differentiate the various fields.

OK, I will close this thread.