How to filter combo box based on multiple fields

[Welshgasman]

No that is a statement, but phrased as to ask you, are you aware of this?

[Micron]

No, subforms load first. If you have a field on the subform that references the main form, it may prompt you for the reference that it can't resolve when the subform opens or loads.
You would have to take care of the reference (or calculation, or whatever is involved) in the main form code.

EDIT - if you get a prompt when a main form/subform opens but you don't when you open the subform directly (from the nav pane) then usually that is because your reference syntax is wrong.
e.g. to reference a subform control:
[Forms]![Main form name]![subform control name].[Form]![control name on subform]

where subform control name is the name of the control that contains the subform. It may or may not be the same as the subform itself.
Referring to subform properties (e.g. recordsource) would simply be a matter of replacing the control reference part with the property part.

[ArviLaanemets]

I would think that the main form is loaded first, but not sure how that is relevant here.

Let's assume you have a main form (fMainForm), with subform control (sfSubform) on it. The source of suform control is another form (fSubform).

In case the loading would be started from fMainForm, Access doesn't find the source of sfSubform, as fSubform is not loaded jet - and as result an error would be returned!

So really all source forms of any subform controls of fMainForm are loaded at first, and are keeped in some kind of limbo. And after that the main form is loaded, and source forms for all subform controls on it are linked into proper subforms.

Btw. In my experience whenever I create a subform inserting a form into main form, Access gives the subform control the same name as inserted form did have. As 1st step after that, I always rename the subform control (all my forms are named like fFormName, and when the form is used as source of subform control, then this subform is named as sfFormName).

[Chief_Up_Yours]

SampleDB.zip
I've attached a sample DB that demonstrates my problem with cascading combo boxes.
This is acting slightly different than my actual application.

Problem #1 - If I open the subform directly, the first record displays correctly.
When I position to the second record, the State_ID combo box is blank.
It should display the value from the second record which is Alberta.

Problem #2 - If I open the fDept form which embeds the sfUsers subform, I get a pop-up prompting for whatever control is in the WHERE clause of the qStates query. I have tried every permutation I can think of to specify the field control in the WHERE clause.
I've tried the following WHERE clauses:

qStates:     WHERE (((STATES.Country_ID)=[forms]![sfUsers]![Country_ID]))
qStates1:    WHERE (((STATES.Country_ID)=[Me].[Country_ID]))
qStates2:    WHERE (((STATES.Country_ID)=[Me]![sfUsers].[Form]![Country_ID]))
qStates3:    WHERE (((STATES.Country_ID)=[forms]![fDept]![sfUsers].[form]![Country_ID]))

The qStates3 query works from the fDept form for the first record, but not for subsequent records.

At this point, I am getting very frustrated.

[Welshgasman]

I got rid of the table lookups.
Put some controls in to verify values, you can remove or hide these later.
Renamed suborm control to be different to source object.
Made the combo unbound as well, else you will be changing data without realising it.

You also need to google cascading combos, as I was able to add myself as Great Britain, but not the just the states (counties) for that country, and was shown everything.

[Chief_Up_Yours]

@Welshgasman - Thanks for taking a look at the DB I provided and for uploading your changes.
However, it does not fix the problem and has several new problems.
1) Your fDept form does not work at all. Attempting to display any record in the table, only displays blanks in the fDept form and the sfUser subform
2) You removed the WHERE clause completely from qStates3, thereby causing ALL states to be displayed in the States combo box.
Cascading combo boxes rely on the WHERE clause in the record source to restrict the data in the combo box to match the combo box they depend on.

Believe me, I have googled cascading combo boxes multiple times.
I understand how they are supposed to work, I just can not get the syntax in the WHERE clause right in the record source query for the dependent combo box when the subform is embedded in an outer form.
See message #19 for the list of WHERE clauses I have tried.

[Welshgasman]

Yes, the form is empty when you first open it.
You need to select a dept. If you want to see data imemdiately, then set a dept in the form load.
I have added the cascade for the state for you. I am using qStates.

Use the builder in the QBE if you are not savvy with the syntax.
You cannot use Me. in queries. Me refers to the object code is running in.

This is what works

SELECT STATES.State_ID, STATES.State_Name
FROM STATES
WHERE (((STATES.Country_ID)=[Forms]![fDept]![ctrlsfUsers].[Form]![Country_ID]))
ORDER BY STATES.State_Name;

[Welshgasman]

Unable to edit previous post.

Something weird going on with the State combo. It is populating the records correctly, but not me records?
The values are in the table and I confirmed they are correct with another textbox.


I also added this note as an edit and the site just ignored it.

[Chief_Up_Yours]

@welshgasman - Thanks. It's working now.
I'm going to mark this thread as solved.

[Welshgasman]

@welshgasman - Thanks. It's working now.
I'm going to mark this thread as solved.

Do you have an issue with the state combo not being populated when moving records, for some records at least?

[Chief_Up_Yours]

@Welshgasman - Yeah, I agree there is something weird going on with the STATE combo box.
If I change the state on record A, record A's city list changes as expected.
If I then move to record B which has a different state value, Record B's CITY combo box has the city list for record A.

[Chief_Up_Yours]

I changed this thread to UNSOLVED as I am still having the problem in the previous post.

[ano]

did you open the form and then run separated the sql? without open form u get popup for parameter , with open form on the right record the sql should give normal answer else there is a mismatch with the form.

[forms]![subform1]![field]

should be : forms!mainform!subform!field the "[]" are for space in the name like forms![main form]![sub form]!field

[ano]

refresh?
or recreate form to make sure all is ok again , it helped me

[ano]

here in the new created form after changing users lookup and requery in country from state seems all working fine