Creating a combo box that interacts with a list box

[qwerty]

The goal is following,

1. Pick 1 of 2 regions in the combo box.
2. The list box then displays the cities in those regions, and you can only select one city.
3. Then submit takes you to a new page or form based on the selected criterias.

[CJ_London]

this is called cascading combo's. Plenty about them on this and other forums. In principle

rowsource to combo (called say cboAreas)

SELECT RegionPK, RegionName FROM tblRegions

rowsource to listbox (called say lstCities)

SELECT CityPK, CityName FROM tblCities


in the lstCities Enter Event put

lstCities.Rowsource="SELECT CityPK, CityName FROM tblCities WHERE RegionFK=[cboAreas]"


in the lstCities Exit Event put

lstCities.Rowsource="SELECT CityPK, CityName FROM tblCities"

[ranman256]

the combo box has 2 columns: caption, Query
the combo is bound to col 2, but col 2 has width =0 so user only sees the caption

the afterupdate event:
lstCities.rowsource = cboBox

[orange]

Here is a link to detailed info and examples of MS Access combo boxes--good reference material.

[qwerty]

Thank you all for your continued support. I got some of the feature8s setup, except for the button.

1. I have 2 combobox and 1 listbox.
2. Combo1 has west and east. (Region tbl)
3. Combo2 displays the location either for west or east. (States tbl)
4. Listbox displays the sublocations. (Cities tbl)

How do I write the code for the button after selecting the sublocation to move on to the next page, so the data stays consistent with the selected city?

In my relationship after the cities table there are two species table as well. Not sure if this should be part of the button as well.

[CJ_London]

you need to provide more detail and be clearer about what you are asking for

best guess at the moment is to use

docmd.OpenForm "formname",,,"CityID=" & CityID

No idea what the relevance of species is. But the normal rule is one form, one table, so sounds like these tables, if required, should be in subforms on the form you are opening.

[qwerty]

you need to provide more detail and be clearer about what you are asking for

best guess at the moment is to use

docmd.OpenForm "formname",,,"CityID=" & CityID

No idea what the relevance of species is. But the normal rule is one form, one table, so sounds like these tables, if required, should be in subforms on the form you are opening.

asking for help with creating a button that takes you to a new page.

[orange]

asking for help with creating a button that takes you to a new page

Does this mean go to a new record?

Ajax has provided
docmd.OpenForm "formname",,,"CityID=" & CityID

which would open "formname" with criteria to restrict records to a specific CityID.

Please provide a sample to put your question in to context. What does new page mean to you in terms of a Form?

[qwerty]

asking for help with creating a button that takes you to a new page

Does this mean go to a new record?

Ajax has provided
docmd.OpenForm "formname",,,"CityID=" & CityID

which would open "formname" with criteria to restrict records to a specific CityID.

Please provide a sample to put your question in to context. What does new page mean to you in terms of a Form?

For example, signing up for an email account. On the first form you enter certain information, and click next to go to the next page. The button remembers the information in first page to continue with the new page. The new page will have additional information and options you need to select. The last third page be about the client. Depending were they live the selected data in page 1 will correspond with that city. For information about that species in Iowa if that is what you selected on page 1.

[orange]

Further to Ajax's comment in post #6

No idea what the relevance of species is. But the normal rule is one form, one table, so sounds like these tables, if required, should be in subforms on the form you are opening

, can you overview the scope of your project so readers understand your posts/questions/responses in context.

When you are dealing with form(s) and subforms, they are usually called "form". We/I do not see how regions, cities, species and signing up for an email account "fit together". All we are asking for is the 30,000 ft overview that relates these pieces.

[qwerty]

Further to Ajax's comment in post #6
, can you overview the scope of your project so readers understand your posts/questions/responses in context.

When you are dealing with form(s) and subforms, they are usually called "form". We/I do not see how regions, cities, species and signing up for an email account "fit together". All we are asking for is the 30,000 ft overview that relates these pieces.

Maybe like how a car website operates, when building a new car.

You select make, model, and trim. Then it you click next and gives you other options to choose from on the new page. Based on the criteria you selected on the first page.

Page 1, you select the region, location, and sublocation. This filters the data out to only that location. There more information that needs to be filled or selected on page 2. Page 2 will be related to filling out information about the species based on the location selected from 1.

It's a input form and selection option, for example you select the iowa from the first page. Then you select blue bird in page 2, it's only going to provide data about the blue birds in Iowa.

I have created the combo and list box. They work perfectly together, how do I create the button so that it is dependent based on the selections made in the list box?

[orange]

???I am using email as an example.???
How about plain English, real life example.

Consider a database of bird sightings in various areas across North America. What set up would allow a user to access specific species by region (country,area,state/prov,city)?
It is important to work with your expected outputs (responses to anticipated user requests).

For example:

What is the most popular species sighted in Utah? in Mexico? In Canadian Prairies?
What are the top X in Iowa? In Alaska? West of the Mississippi?
Are Cardinals ever sighted in New Brunswick? In Hawaii?
What raptors are found in Canada, but not in Mexico?

So how should you define/design region in order to answer the above?

Loading/inputting data may require several forms/subforms.
Accessing/reading data may only require 1 form with several combo boxes.

Good luck with your project.