return a value in combo box based on another combo box's selection

[banderson]

Hello amazing vba writers,
I am trying to make a combo box return a value based on a combo box selection. I have tried a number of the codes posted here and am still having problems. I think it is because I am trying to return a value from a table that only links with the form through an intermediate table. I am hoping to avoid a subform for this one box if at all possible, because of space limitations. Any guidance would be greatly appreciated!

I have a form called frmBldgs based on a table called tblBldgs with the following fields:
Field1: BldgID – autonumber primary key
Field2: BldgNm – name of building
Field3: Addr1IDFK – foreign key that links to a table called tblAddr1
Field4: DistrictIDFK – foreign key that links to a table called tblDistricts
etc….

The table called tblDistricts has the following fields:
Field1: DistrictID – autonumber primary key
Field2: DistrictNm – name of sanitation district
Field3: SanBoroIDFK – foreign key that links to a table called tblSanBoros

(Each SanBoro has a number of districts nested beneath it.)

The table called tblSanBoros has the following fields:
Field1: SanBoroID – autonumber primary key
Field2: SanBoroNm
Field3: BoroIDFK – foreign key that links to a table called tblBoroughs

(Each Borough has one or two SanBoros nested beneath it.)

I also have the following simple query, called qrySanDistBoro:
SELECT tblBldgs.DistrictIDFK, tblSanBoros.SanBoroID, tblDistricts.DistrictNm, tblSanBoros.SanBoroNm
FROM tblSanBoros INNER JOIN (tblDistricts INNER JOIN tblBldgs ON tblDistricts.DistrictID = tblBldgs.DistrictIDFK) ON tblSanBoros.SanBoroID = tblDistricts.SanBoroIDFK;

The first combo box I am working with in frmBldgs is called cboSanDistrict.
Its ControlSource is DistrictIDFK.
I made the Row Source the following so that it returns the DistrictNm in the form from tblDistricts:

SELECT tblDistricts.DistrictID, tblDistricts.DistrictNm
FROM tblDistricts
ORDER BY tblDistricts.DistrictID;
 

I have added a combo box called cboSanBoro that I hope to fill with SanBoroNm from the tblSanBoros that matches the DistrictNm chosen in cboSanDistrict.
The Control Source for cboSanBoro is DistrictIDFK.

For the Row Source, I have tried using the following query
SELECT qrySanDistBoro.SanBoroNm
FROM qrySanDistBoro;

This did not work. The combo box just returns the DistrictIDFK associated with the DistrictNm in cboSanDistrict.

I’m guessing the simple query was too simple because tblSanBoros is not directly linkable to tblBldgs except through tblDistricts. Maybe I need to change the control source?? (but to what?)

Thanks in advance, any help would be greatly appreciated!
Bridget

[Scott Price]

Hi Bridget,

In your query:

SELECT tblBldgs.DistrictIDFK, tblSanBoros.SanBoroID, tblDistricts.DistrictNm, tblSanBoros.SanBoroNm
FROM tblSanBoros INNER JOIN (tblDistricts INNER JOIN tblBldgs ON tblDistricts.DistrictID = tblBldgs.DistrictIDFK) ON tblSanBoros.SanBoroID = tblDistricts.SanBoroIDFK;

You need to add a WHERE criteria pointing to the DistrictID chosen in the cboSanDistrict. This should look something like: WHERE tblDistricts.DistrictID = Forms![FormName]!cboSanDistrict

Regards,
Scott

[banderson]

Hello Scott,
Thank you for this suggestion!
I have tried to add a WHERE statement without luck. :(
It makes logical sense that a WHERE statement would be needed, but when I tried to add what you suggested in the row source for cboSanBoro, it first prompts me with a dialogue box to enter a parameter value for cboSanDistrict (even when a value already exists in the cboSanDistrict combo box already!). Once I enter a value, nothing is returned in cboSanBoro. When I’ve made a choice in the dialogue box once, I can then choose straight from the cboSanDistrict box, but still no values are returned in cboSanBoro.

Here is what I have tried…
1. With the original code I sent, the DistrictIDFK is returned in the cboSanBoro box. This is the right idea, except the wrong value is returned (rather than DistrictIDFK, I'd like SanBoroNm returned.)

2. When I put the following in the row source for cboSanBoro, it first prompts me for a parameter value for cboSanDistrict (even if a value is already chosen in the cboSanDistrict box), and when I choose a cboSanDistrict value, nothing is returned in cboSanBoro.

SELECT tblDistricts.DistrictID, qrySanDistBoro.SanBoroNm
FROM qrySanDistBoro INNER JOIN tblDistricts ON qrySanDistBoro.DistrictIDFK = tblDistricts.DistrictID
WHERE (((tblDistricts.DistrictID)=[Form]![frmBldgsAdd]![cboSanDistrict]));
 

(The qrySanDistBoro is the same as the original one I posted)

3. Perhaps the closest I have gotten so far is the following code in which I get the opposite effect of what I want: when I make a selection in cboSanBoro, the correct SanDistrict is returned in the cboSanDistrict box. If I make a selection in cboSanDistrict, nothing is returned in cboSanBoro.

Code in row source of SanBoro:

SELECT tblDistricts.DistrictID, qrySanDistBoro.SanBoroNm
FROM qrySanDistBoro INNER JOIN tblDistricts ON qrySanDistBoro.DistrictIDFK = tblDistricts.DistrictID;
 

This code does not limit the list in cboSanBoro (show’s all possible values), but when I choose one, it does return the correct value for cboSanDistrict. If I could combine the outcome of 1 and 3, I'd be golden...

Any insight is greatly appreciated!
Bridget

[banderson]

Hello Scott and anyone else following this string,
I found a work around for my problem. Its not as slick what I originally was trying to do, but works fine.

I used a query that looks up the entire list of SanBoros and nested Districts:

SELECT tblDistricts.DistrictID, tblSanBoros.SanBoroID, tblDistricts.DistrictNm, tblSanBoros.SanBoroNm
FROM tblSanBoros INNER JOIN tblDistricts ON tblSanBoros.SanBoroID=tblDistricts.SanBoroIDFK;
 

I made the row source for cboSanBoro the following:

SELECT tblDistricts.DistrictID, qrySanDistBoro2.SanBoroNm, tblDistricts.DistrictNm
FROM tblDistricts INNER JOIN qrySanDistBoro2 ON tblDistricts.DistrictID = qrySanDistBoro2.DistrictID
ORDER BY tblDistricts.DistrictID;
 

Then I set up the properties of cboSanBoro so that both SasnBoroNm and DistrictNm show up on the drop down:
ColumnCount: 3
ColumnWidth: 0";1";1"
BoundColumn: 1

The row source for cboSanDistrict is the same as my original posting:

SELECT tblDistricts.DistrictID, tblDistricts.DistrictNm
FROM tblDistricts
ORDER BY tblDistricts.DistrictID;
 

Now, when I click on the drop down of cboSanBoro, I can see both the SanBoro and District name and choose the correct district. This returns the correct SanBoro in cboSanBoro and the correct SanDistrict in cboSanDistrict. If I choose another DistrictNm from cboSanDistrict, the correct SanBoro is returned in cboSanBoro.

I'd still love to know how to properly filter, but it'll have to wait until I have a bit more time to play around with things!

Bridget

[Scott Price]

Bridgit,

Glad you found a work-around, and thanks for posting back with what you've got working.

Regards,
Scott