Cascading Combo Box (1 Viewer)

brsawvel · Sep 25, 2007

Hello World,

I am trying to create a cascading combo box on one of my forms, but am lost on how to do it.

I have two tables with the following fields

Table 1 -
Countries - A, B, C, D, E, F

Table 2 -
Countries - Selected from "Countries" field of Table 1
Sites - 1, 2, 3, 4, 5, 6, 7, 8, 9

How can I create a cascading combo box where when a certain country is selected in box 1, only those sites in that country are available for selection in box 2?

missinglinq · Sep 25, 2007

Here's a tutorial on the subject from TheScripts:

Cascading Combo/List boxes

brsawvel · Sep 25, 2007

Figured it out - Partly

Ok, here is the code I used to get the form to do what I wanted:

****To Put in Row Source****
SELECT DISTINCT Country.Country FROM Country ORDER BY Country.Country;

****To Put in After Update****
On Error Resume Next
Site.RowSource = "Select Country.Site " & _
"FROM Country " & _
"WHERE Country.Country = '" & Country.Value & "' " & _
"ORDER BY Country.Site;"

(All Red is variable based on your db fields).

But now my problem is attempting to do the same on a Data Access Page. Anyone know how?

brsawvel · Sep 26, 2007

Problem 2

Ok, I figured it out.

Now, does anyone know how to make it work for more than 2 (3 specifically) fields on one table?

ajetrumpet · Sep 26, 2007

It works the same as when you write two, you have to add a few more lines of code that filter records based on another field added to the mix.

There should be a FAQ on this issue, it is really quite simple, but yet is discussed day in and day out on internet forums...

Sawvel, would you mind posting what you have so far (as for two fields)??

brsawvel · Sep 26, 2007

I wasn't able to find anything in FAQ. I've seen a lot of people post about the cascades drop down, but I couldn't find one similar to mine. Please help...

ajetrumpet · Sep 26, 2007

post your file...or an example like it. There is more than one way to go about doing this, although I think some would disagree...

brsawvel · Sep 26, 2007

Example

Here is what I used to create a cascade for 2 fields on one table:

****To Put in Row Source****
SELECT DISTINCT tbl.fld1 FROM tbl ORDER BY tbl.fld1;

****To Put in After Update****
On Error Resume Next
fld2.RowSource = "Select tbl.fld2 " & _
"FROM tbl " & _
"WHERE tbl.fld1 = '" & fld1.Value & "' " & _
"ORDER BY tbl.fld2;"

Here is the table and 3 fields I am attempting to use to create a cascade drop down. (Field entries are shown as if they were side by side in the table).

Table = AssetType

Field 1 = Manufacturer
Dell
Cisco
Hewlett Packard
Dell
Dell
Sun Microsystems
Sun Microsystems

Field 2 = Category ID
Monitor
Router
Printer
Desktop
Desktop
Server
Server

Field 3 = Model Number
1908FP
2811
3800DN
GX270
GX520
V120
V240

ajetrumpet · Sep 26, 2007

This is what I'm talking about...now this might be a very simple version of how to do this, but I am really thinking that doing it this way can solve a lot of problems that people think are difficult, but are really not. Does this do the trick for you??

brsawvel · Sep 26, 2007

Thanks!

I appreciate the help. I'm working on getting it to work on mine. I could copy and paste, but I'd learn nothing. As soon as I get it working, I'll repost.

**Update**

I couldn't get it to work on my own, although I saw and understood the vb code you wrote. So I deleted the table, relationships and boxes I had and copy/pasted yours. It works really good! Thanks for the help, hopefully some day I'll figure out where I went wrong with mine.

ajetrumpet · Sep 27, 2007

brsawvel said:
It works really good! Thanks for the help, hopefully some day I'll figure out where I went wrong with mine.

Hopefully I'll figure out someday why there seems to be a million different successful answers to this question...

Rabbie · Sep 27, 2007

ajetrumpet said:
Hopefully I'll figure out someday why there seems to be a million different successful answers to this question...

As Missinglinq says in their signature. There's more than one way to skin a cat

Cascading Combo Box (1 Viewer)

brsawvel

Registered User.

missinglinq

AWF VIP

brsawvel

Registered User.

brsawvel

Registered User.

ajetrumpet

Banned

brsawvel

Registered User.

ajetrumpet

Banned

brsawvel

Registered User.

ajetrumpet

Banned

Attachments

brsawvel

Registered User.

ajetrumpet

Banned

Rabbie

Super Moderator

Similar threads

Users who are viewing this thread