Using value from one control as filter on another with a twist

[Aikistan]

All, Thanks in advance for this. My new DB project has a lookup table I created to limit the selection of the State a project is in to a list.

On form "subfrmDocumentEntry" I use a combobox that displays the StateAbbr (e.g., "GA") but the table I'm holding this in keeps the R4StateID (e.g., "3"). The DB also has a lookup table I import (don't own it) with a list of Sponsors/Grantees. It only contains values that are StateAbbr ("GA") and not R4StateID ("3").

I created this query (luqryGranteeStateAbbrLink) to relate the two lookup tables.

SELECT lutblR4States.R4StateID, lutblR4States.StateAbbr, lutblR4States.StateName, lutblTEAMID.Grantee
FROM lutblR4States INNER JOIN lutblTEAMID ON lutblR4States.StateAbbr = lutblTEAMID.State;

...and I set the combobox I use to select Grantee (cmbSponsorGranteeName) with the following row source:

SELECT luqryGranteeStateAbbrLink.StateAbbr, luqryGranteeStateAbbrLink.StateName, luqryGranteeStateAbbrLink.Grantee
FROM luqryGranteeStateAbbrLink
WHERE (((luqryGranteeStateAbbrLink.R4StateID)=[Forms]![subfrmDocumentEntry]![cmbDocumentState]))
ORDER BY luqryGranteeStateAbbrLink.StateAbbr;

cmbDocumentState is the control for "What state is this project in?" I'm attempting to filter the list in cmbSponsorGranteeName with the value in cmbDocumentState. So, if someone selects "GA" in the former with R4TEAMID=3, only values where the StateAbbr="GA" appear in cmbSponsorGranteeName.

I think I'm on the right track but with the above SQL, cmbSponsorGranteeName shows nothing in the drop down and I'm stumped.

 

[CJ_London]

you need to requery your second combo in the after update event of the first combo.

Also, your form is called subfrmDocumentEntry so is this a different form from where your cmbSponsorGranteeName is located? If both on the same form, they are with the same object so you can just use

WHERE (((luqryGranteeStateAbbrLink.R4StateID=cmbDocumentState]))

 

[Aikistan]

They're on the same form (it's a sub form). Where to add that WHERE, though? In the first combo's After Update?

 

[Aikistan]

I'm still hoping for North Dakota to become just Dakota...

 

[CJ_London]

Where to add that WHERE, though? In the first combo's After Update?

No, replace the where statement you have in your second combo rowsource

 

[Aikistan]

Okay, this now works perfectly to display the filtered list; however, when I choose a value from the list, the first column is chosen (lutblTEAMID.State), not the value of the Grantee's name (lutblTEAMID.Grantee).

My revised entry query is (I took Mr. Hartman's implied advice and changed lutblR4States key field to the StateAbbr, doing away with the query):

SELECT lutblTEAMID.State, lutblTEAMID.TEAMID, lutblTEAMID.Grantee FROM lutblTEAMID WHERE (((lutblTEAMID.State)=[Forms]![subfrmDocumentEntry]![cmbDocumentState])) ORDER BY lutblTEAMID.State;

The subfrmDocumentEntry control source is tblDocuments and the cmbSponsorGranteeName control source is SponsorGranteeName. The bound column is 3. I would like the SELECT to display in that order, but lutblTEAMID.Grantee be the entered value.