Using value from a combo box in query of another combo box

[dschmer]

My goals is that I have a "Status" and a "Substatus" field in combo boxes on my form. I want the substatus query to limit the results based on the value in the Status field.

I found this syntax on this forum for referencing the combo box
[Forms]![frmDoSearch]![Combo0]

I have this as my query. This "almost" works - however it seems that it is not getting the value of the cmbStatus field from the current record. It appears like it is looking only at the first record in my original form query. So if my first record had a status of "Open" - then my substatus lists only the Open substatus. I reversed the order of my query (out of curiosity) so the first record is Closed - then the substatus drop down list contains only closed values. What am I doing wrong?

SELECT MIRFCodes.CodeId, MIRFCodes.Description, MIRFCodes.[Code Type], MIRFCodes.SubTypeOf
FROM MIRFCodes
WHERE (((MIRFCodes.[Code Type])=3) AND ((MIRFCodes.SubTypeOf)=[Forms]![FormMIRFMain]![cmbStatus]))
ORDER BY MIRFCodes.Description;


Thanks a million!

[June7]

Fact of Life: dependent (cascading) combobox won't work nice in datasheet or continuous view if the combobox has lookup alias. This is because the property setting is reflected in all instances of the combobox as there is only one combobox and when the combobox is filtered it is filtered for all records and not all alias values are available.

The only 'fix' I know is to include the lookup table in the form RecordSource with join type "Include ALL records from {primary table} and only those from {lookup table} that are equal."

Bind textboxes to the lookup table fields and set them as Locked Yes, TabStop No.

Size the combobox so only the dropdown arrow shows and sit it next to the locked textbox bound to the alias descriptor field.

[dschmer]

Thanks for this reply. I'm a newbie/very occasional Access user so your fix is a little greek to me, but I'll try to walk through it. Thanks.

[June7]

Ooops! Just tested and the reduced size of the combobox won't work.

I've never had this issue so never fully tested. I was going by what I thought I've read. Unfortunately, now I can't remember what I read.