Is it possible to limit Combo Box choices based on the value of a field in the same record?

[avalentini]

I'm wondering if the following is possible, and if so how to implement it.

I have three tables: tblMunicipalities, tblCensusTracts, and tblInvestments.

tblInvestments has three fields: investment_ID, investment_Municipality, and investment_CensusTract.



The display control for investment_Municipality is a Combo Box, and the row source is the following query: SELECT tblMunicipalities.municipality_Name FROM tblMunicipalities.

I would like to do something similar for investment_CensusTract, but also limit the Combo Box choices by the value of investment_Municipality.

In some cases a single census tract corresponds to multiple municipalities and in others a single municipality corresponds to multiple census tracts, but I am not using multi-value fields in any of the tables.

My current solution is to have the row source for investment_CensusTract to be the following query: SELECT tblCensusTracts.censusTracts_Tract FROM tblCensusTracts WHERE tblCensusTracts.censusTracts_Muni = tblInvestments.investment_Muni;

This only partially works as the query is only run according to the value of investment_Municipality for the first record in tblInvestments. For instance, if the value of the investment_Municipality for the first record in tblInvestments is "MuniA", then the corresponding census tracts should be "TractA1" and TractA2" and if the value of investment_Municipality for the second record in tblInvestments is "MuniB", then the corresponding census tracts should be "TractB1" and "TractB2". However, my current solution makes is such that investment_CensusTract returns "TractA1" and "TractA2" for every record in tblInvestments, regardless if investment_Municipality is equal to "A" or not.

Is there a way to ensure that for each record in tblInvestments the Combo Box which is used to set investment_CensusTract is filtered by the value of investment_Municipality in the same record?

Thanks

[ArviLaanemets]

In Current event of form, set the RowSource property of combo (edit the querystring or value list)[, and requery the combo control].

[CJ_London]

sounds like what you are referring to is 'cascading combo's'. See this link for an example

https://www.access-programmers.co.uk...d.php?t=275155

[avalentini]

In Current event of form, set the RowSource property of combo (edit the querystring or value list)[, and requery the combo control].

I was intended on using a form later in the database, but is it possible to achieve what I said just using tables?

[ArviLaanemets]

I was intended on using a form later in the database, but is it possible to achieve what I said just using tables?

1. It isn't!
2. Using combos for table fields is not good idea!

[avalentini]

2. Using combos for table fields is not good idea!

How does one limit what may be entered into a field/present re-existing options for convenience, then?

[Bob Fitz]

How does one limit what may be entered into a field/present re-existing options for convenience, then?

I don't really understand what you're asking but one dosen't enter data directly into tables, rather, via forms which are bound to the tables. The "events" that forms and the controls that are put on them have are then used to control what data the user enters.