Multiple Combo Boxes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like the outcome of one combo box to affect the contents of the next
combo box. eg. In the first box I have regions (North West, North East,
Midlands etc.) and when I click on North West, I want the second combo box to
have Lancashire, Cumbria, Cheshire etc. When I click on Midlands, I want
Worcesterchire, Shropshire etc. to come up and so on...

Any ideas? I'm only just getting the hang of Access and I have seen
"Events"... is this the right place to look?
 
Check on "cascading combo boxes" -- this is the term used to describe when
combo box1 is used to limit what's shown in combo box 2, and cb2 limits
cb3...
 
The most efective way that I used

On the RowSource of combo 2 build a criteria that refer to
the field of combo 1, and from combo 3 a criteria to combo
2, etc
 
I am very new to doing anything more sophisticated than a simple database
with Access! I have seen the RowSource option inthe properties but I'm
affraid I haven't a clue where to start.

If you wouldn't mind spending some time, could someone please walk me
through it? I'm still waitning for my Access book to arrive and I need to
make a start!

I currently have one combo box to select the region. Will I have to make a
second combo box and pul ALL of the counties in it and then specify ones
belong to each region?

As I said, I'm very new at this and would appreciate any help.
 
Combo1 select the region:
The RowSource of the combo Should have: "Select regionId,
regionName From regionTable"

Combo2 select the counties:
The RowSource of the combo Should have: "Select
countiesId, countiesName From countiesTable Where regionId
=" & Forms![FormName]![Combo1]

(don't copy the query, write it with the names you have)
 
Does that mean that I need to have separate tables for regions and counties?
At the moment I have a from built from the table containing all of the
fields.

Ofer said:
Combo1 select the region:
The RowSource of the combo Should have: "Select regionId,
regionName From regionTable"

Combo2 select the counties:
The RowSource of the combo Should have: "Select
countiesId, countiesName From countiesTable Where regionId
=" & Forms![FormName]![Combo1]

(don't copy the query, write it with the names you have)


-----Original Message-----
I am very new to doing anything more sophisticated than a simple database
with Access! I have seen the RowSource option inthe properties but I'm
affraid I haven't a clue where to start.

If you wouldn't mind spending some time, could someone please walk me
through it? I'm still waitning for my Access book to arrive and I need to
make a start!

I currently have one combo box to select the region. Will I have to make a
second combo box and pul ALL of the counties in it and then specify ones
belong to each region?

As I said, I'm very new at this and would appreciate any help.


.
Click to expand...
Click to expand...
 
If you have any problem, send me your DB and I'll send it
back to you fixed (but don't tell anyone else)

Compact your DB, I have a limit there.
 
You don't have to, but it's the right thing to do.
look at my offer from a minute ago.

-----Original Message-----
Does that mean that I need to have separate tables for regions and counties?
At the moment I have a from built from the table containing all of the
fields.

Ofer said:
Combo1 select the region:
The RowSource of the combo Should have: "Select regionId,
regionName From regionTable"

Combo2 select the counties:
The RowSource of the combo Should have: "Select
countiesId, countiesName From countiesTable Where regionId
=" & Forms![FormName]![Combo1]

(don't copy the query, write it with the names you have)


-----Original Message-----
I am very new to doing anything more sophisticated
Click to expand...
than a
simple database
with Access! I have seen the RowSource option inthe properties but I'm
affraid I haven't a clue where to start.

If you wouldn't mind spending some time, could someone please walk me
through it? I'm still waitning for my Access book to arrive and I need to
make a start!

I currently have one combo box to select the region.
Click to expand...
Will
I have to make a
second combo box and pul ALL of the counties in it and then specify ones
belong to each region?

As I said, I'm very new at this and would appreciate
Click to expand...
any
help.
:

The most efective way that I used

On the RowSource of combo 2 build a criteria that
Click to expand...
refer
to
the field of combo 1, and from combo 3 a criteria to combo
2, etc



-----Original Message-----
I would like the outcome of one combo box to affect the
contents of the next
combo box. eg. In the first box I have regions (North
West, North East,
Midlands etc.) and when I click on North West, I
Click to expand...
want
the
second combo box to
have Lancashire, Cumbria, Cheshire etc. When I
Click to expand...
click
on
Midlands, I want
Worcesterchire, Shropshire etc. to come up and so on...

Any ideas? I'm only just getting the hang of Access and I
have seen
"Events"... is this the right place to look?
.


.
Click to expand...
Click to expand...
.
Click to expand...
 
I would like the outcome of one combo box to affect the contents of the next
combo box. eg. In the first box I have regions (North West, North East,
Midlands etc.) and when I click on North West, I want the second combo box to
have Lancashire, Cumbria, Cheshire etc. When I click on Midlands, I want
Worcesterchire, Shropshire etc. to come up and so on...

Any ideas? I'm only just getting the hang of Access and I have seen
"Events"... is this the right place to look?
Click to expand...

You'll need just a little VBA code to do this.

I gather downthread that you have a table of Regions and Counties.
Base the first combo, cboRegion say, on a query selecting just
Regions, using the "Unique Values" property of the query. The SQL
would be something like

SELECT DISTINCT Region FROM AreaTable ORDER BY Region;

Create a second query to select the counties, *using the first combo
box as a criterion*. It will be something like

SELECT County FROM AreaTable
WHERE AreaTable.Region = Forms!YourFormName!cboRegion
ORDER BY County;

Base a new combo box, cboCounty, on this query. Then Requery the
county combo in the AfterUpdate event of the Regions combo. View the
combo's properties, and on the Events tab click the ... icon by the
AfterUpdate event; choose the Code Builder; and enter

Private Sub cboRegion_AfterUpdate() ' Access gives you this free
Me!cboCounty.Requery
End Sub ' and this as well


John W. Vinson[MVP]
 
Back
Top