Patrick O'Dea
asked on
Simple Combo Drill Down Query
Hi,
(This is one I should be able to sort myself .... but I'm struggling).
One Continent has many Countries. One Country has many cities.
See frmCountry. The allows me add new "Countries" to pre-setup "Continents".
frmCity is the problem!!
When I choose a continent in frmCity I want the "Country" (in yellow) to show ONLY countries in the chosen continent.
HOW do I restrict the "country" combo?
(By the way, I have tried using Forms!frmCity!Continent etc but failed).
Database1.accdb
(This is one I should be able to sort myself .... but I'm struggling).
One Continent has many Countries. One Country has many cities.
See frmCountry. The allows me add new "Countries" to pre-setup "Continents".
frmCity is the problem!!
When I choose a continent in frmCity I want the "Country" (in yellow) to show ONLY countries in the chosen continent.
HOW do I restrict the "country" combo?
(By the way, I have tried using Forms!frmCity!Continent etc but failed).
Database1.accdb
ASKER
OM,
I have tried your suggestion but I am getting nothing displayed in the "yellow" Combo.
Any chance you could email me back a working database with the changes your suggested.
I appreciate your help.
I have tried your suggestion but I am getting nothing displayed in the "yellow" Combo.
Any chance you could email me back a working database with the changes your suggested.
I appreciate your help.
I went back to double-check and see that it's not working as expected. Hold please.
OM Gang
OM Gang
We need to requery the CountryC combo box after each change to the ContinentC combo box. Add the following code to the form (it's an AfterUpdate event procedure for the ContinentC combo box).
OM Gang
Option Compare Database
Option Explicit
Private Sub ContinentC_AfterUpdate()
On Error GoTo Err_ContinentC_AfterUpdate
Me.CountryC.Requery
Exit_ContinentC_AfterUpdat e:
Exit Sub
Err_ContinentC_AfterUpdate :
MsgBox Err.Number & " (" & Err.Description & ") in procedure ContinentC_AfterUpdate of VBA Document Form_frmCity"
Resume Exit_ContinentC_AfterUpdat e
End Sub
OM Gang
Option Compare Database
Option Explicit
Private Sub ContinentC_AfterUpdate()
On Error GoTo Err_ContinentC_AfterUpdate
Me.CountryC.Requery
Exit_ContinentC_AfterUpdat
Exit Sub
Err_ContinentC_AfterUpdate
MsgBox Err.Number & " (" & Err.Description & ") in procedure ContinentC_AfterUpdate of VBA Document Form_frmCity"
Resume Exit_ContinentC_AfterUpdat
End Sub
ASKER
Sorry OM,
It's still not working for me.
I attach my amended database.
Perhaps I am missing something?
Database1.accdb
It's still not working for me.
I attach my amended database.
Perhaps I am missing something?
Database1.accdb
For ContinentC combo box, change the BoundColumn property back to 1. I gave you bad advice previously.
OM Gang
OM Gang
ASKER
OM,
No joy with the change of the BoundColumn. I still get nothing when I dropdown on the yellow country combo.
No joy with the change of the BoundColumn. I still get nothing when I dropdown on the yellow country combo.
ASKER
OM,
....not there yet , I'm afraid.
The combos now seem to work and indeed the data seems to get written to the table.
However, if I close the form and then re-open and then click thru the records .... the data does NOT show. aaaagh!
....not there yet , I'm afraid.
The combos now seem to work and indeed the data seems to get written to the table.
However, if I close the form and then re-open and then click thru the records .... the data does NOT show. aaaagh!
When the form opens it is on the first record in the datasource (which is tblCity). If I select a Continent and then a Country and then enter a value for City and then move to the next record (new record) the city value is saved to the tblCity table with the appropriate Country ID. If I then re-open the the form it opens to the same first record, now populated with the city I entered previously. Based upon your design this is working correctly but it's not what you want.
The Control Source for CountryC is CountryID from tblCity but the Row Source says to only display values for the ContinentID displayed in ContinentC. When we open the form there is nothing in ContinentC so the CountryC combo box displays nothing.
I think I understand how you want the form to function. I'll make some changes and repost. Probably won't be until tomorrow.
OM Gang
The Control Source for CountryC is CountryID from tblCity but the Row Source says to only display values for the ContinentID displayed in ContinentC. When we open the form there is nothing in ContinentC so the CountryC combo box displays nothing.
I think I understand how you want the form to function. I'll make some changes and repost. Probably won't be until tomorrow.
OM Gang
ASKER
Thanks again for your time and patience.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great thanks,
I finally got it working.
Thanks again for all your effort.
I finally got it working.
Thanks again for all your effort.
1) You've got the wrong column specified as Bound Column for the ContinentC combo box. Change the BoundColumn property to 0
2) Set your SQL statement for the RowSource of CountryC combo box to
SELECT tblCountry.CountryID, tblCountry.CountryName, tblCountry.Continent FROM tblCountry WHERE (((tblCountry.Continent)=[
OM Gang