Combo Box Advice

[ribena1980]

Hi Folks,



Hoping someone can help me out with this. Its a very small datebase for my own records and I dont want to over complicate it. I am using Access 2010 64 Bit Version

Basically I have a Locations Table as follows

Country	City
UK	London
UK	Manchester
UK	Birmingham
UK	Newcastle
USA	New York
USA	California
USA	Arizona
Spain	Madrid
Germany	Berlin
Italy	Rome
France	Paris
Canada	Toronto

Each City is unique and the Primary Key, where as each Country can have more than one City.

One the Transactions table I am looking to have the following set up.

Transaction_ID

Transaction_Date

Transaction_Details

Country

City

Transaction ID is the primary key, Transaction Date And Details are fine.

When the user enters a Country, I want it to pick a value from the Locations Table Field via a Combo Box.

Following on from that, when a Country is Selected, I want a Combo Box to select a City that is only valid for that country. So if the user selects Canada, I dont want any other cities, apart from Canadian ones appearing there.

Any help greatly appreciated.

[ranman256]

cboCountry has the query qsCountries
cboCity has the query qsCity1Country , this query uses the paramater that looks at the cboCountry on the form...

select City from table where [country] = forms!frmForm!cboCountry

also in the cboCountry_AFTERUPDATE event put
cboCity.requery

[orange]

There are a number of free videos regarding Combo boxes on this site
https://www.accessforums.net/tutoria...ers-52741.html
These were prepared by forum member ItsMe. The instructions for using these videos are in the referenced (link) thread.

You may also be interested in these 2 Cascading Combo videos from Datapig.
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html

Good luck.

[ribena1980]

Hi Folks,

Should have said, I was hoping to do this using Tables as oppose to forms. I know this is widely frowned upon but it is a small database for personal use.

Any advice?

Thanks,
Mark

[orange]

I would still use a form.
Biggest issue with any database is to get your tables and relationships designed to match your requirements.
One of the best tutorials is this one from RogersAccessLibrary --but you have to work through it to appreciate the concepts. It does have a solution included.

If you are looking for Access tutorials -- Steve Bishop has a great playlist here. Steve is a member of this forum.

Good luck with your database.

[June7]

Cannot have code behind tables (except for Data Macros). Cascading combo/list boxes must be on form.

[ribena1980]

Hi Folks,

Thanks for all your help. Ive Con(Form)ed and used a form as oppose to a table :-)

Ive also used a 3rd field (Continent) and have a 3 tier cascading combo box, which thanks to the advice on here I have working :-)

There is one little issue I was hoping someone could help me kick out.

When I select the Continent, lets say Europe, Country Spain and City Madrid, If I go back and select the Continent as Asia, it updates the other 2 combo boxes with regional values, which is what I want but if I dont change the values that are already there I can enter something along the lines of Asia, Spain, Madrid even though it violates Referential Integrity.

Is there an error I can put in to prevent the user doing this before it gets committed to the table?

Thanks Again,
Mark

[June7]

When value is selected in first 2 boxes, set the following box(es) to Null.