Solved

Simple Combo Drill Down Query

Posted on 2013-06-13
14
269 Views
Last Modified: 2013-06-15
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
0
Comment
Question by:Patrick O'Dea
  • 8
  • 6
14 Comments
 
LVL 28

Expert Comment

by:omgang
ID: 39245136
Two things.

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)=[forms]![frmCity]![ContinentC]));

OM Gang
0
 

Author Comment

by:Patrick O'Dea
ID: 39245192
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.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39245387
Modified db attached
OM Gang
Database1.accdb
0
 
LVL 28

Expert Comment

by:omgang
ID: 39245425
I went back to double-check and see that it's not working as expected.  Hold please.
OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 39245440
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_AfterUpdate:
    Exit Sub

Err_ContinentC_AfterUpdate:
    MsgBox Err.Number & " (" & Err.Description & ") in procedure ContinentC_AfterUpdate of VBA Document Form_frmCity"
    Resume Exit_ContinentC_AfterUpdate

End Sub
0
 

Author Comment

by:Patrick O'Dea
ID: 39245537
Sorry OM,

It's still not working for me.
I attach my amended database.

Perhaps I am missing something?
Database1.accdb
0
 
LVL 28

Expert Comment

by:omgang
ID: 39245968
For ContinentC combo box, change the BoundColumn property back to 1.  I gave you bad advice previously.

OM Gang
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Patrick O'Dea
ID: 39245988
OM,

No joy with the change of the BoundColumn.  I still get nothing when I dropdown on the yellow country combo.
0
 
LVL 28

Expert Comment

by:omgang
ID: 39246026
I think that's the only change I made.  I've attached the db.
OM Gang
Database2.accdb
0
 

Author Comment

by:Patrick O'Dea
ID: 39246056
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!
0
 
LVL 28

Expert Comment

by:omgang
ID: 39246092
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
0
 

Author Comment

by:Patrick O'Dea
ID: 39246115
Thanks again for your time and patience.
0
 
LVL 28

Accepted Solution

by:
omgang earned 500 total points
ID: 39247973
Take a look at frmCity in the attached db.  I believe this functions as you desire.  I changed the CountryC combo box to be unbound and added two new controls to the form (you could make one or both invisible).  The CountryID textbox is bound to CountryID.  The other textbox is unbound and is used to display the CountryName value based upont the CountryID assigned.
OM Gang
Database2.accdb
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 39250014
Great thanks,
I finally got it working.

Thanks again for all your effort.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now