Drop Down Menu's

[bmistry]

Hi,

I would like to create a 2 drop down menu on my form.
Primary Type & Secondary Type. I would like to limit the
secondary type to options that are related to the primary
type.

For example I'll use countries and city's

Primary
Canada
UK
America

Secondary
Toronto
Vancouver
London
Manchester
LA
New York

In this example, if i choose Canada, I would only like the
Candadian cities to show up and the same for the other
countries.

Is this possible in Access? I have seen and used it in
other systems.

Thanks

B

 

[Brian Kastel]

Your secondary table's records need to have a foreign key field to your
primary table. If you have that, it's a simple matter of using an unbound
list for your secondary control and binding it at runtime in code using the
value selected in your primary list as the criteria.

If your two tables are similar to below:

tblCountries
========
CountryID (Autonumber)
CountryName (Text)

tblStatesProvinces
============
StateProvinceID (Autonumber)
CountryID (Long Integer) *FK to tblCountries
StateProvinceName (Text)

then something similar to below should work:

=== START OF CODE ===============

Private Sub cmbCountry_Click()

Dim sql As String

sql = "SELECT " & _
"tblStatesProvinces.StateProvinceID, " & _
"tblStatesProvinces.CountryID, " & _
"tblStatesProvinces.StateProvinceName " & _
"FROM tblStatesProvinces " & _
"WHERE (((tblStatesProvinces.CountryID) = " & _
Trim$(Nz(cmbCountry.Column(0), 0)) & ")) " & _
"ORDER BY tblStatesProvinces.StateProvinceName"

cmbStateProvince.RowSource = sql

End Sub

=== END OF CODE ===============

--

Brian Kastel


--Original Message----------------

Hi,

I would like to create a 2 drop down menu on my form.
Primary Type & Secondary Type. I would like to limit the
secondary type to options that are related to the primary
type.

For example I'll use countries and city's

Primary
Canada
UK
America

Secondary
Toronto
Vancouver
London
Manchester
LA
New York

In this example, if i choose Canada, I would only like the
Candadian cities to show up and the same for the other
countries.

Is this possible in Access? I have seen and used it in
other systems.

Thanks

B

 

[Brian Kastel]

I forgot to note something very important in the previous text, and that is
the properties for your combo boxes.

For your Countries combo, you should set the columns property to 2, the
ColumnWidths property to 0", and set the RowSource property to a SELECT
query that contains the PK field and the text field, with the text field
sorted, if you like.

Set the Columns property of the StateProvince combo to 3, and ColumnWidths
to 0";0"

BTW, that code is tested code using the above configuration.

--

Brian Kastel


--Original Message----------------

in message Your secondary table's records need to have a foreign key field to your
primary table. If you have that, it's a simple matter of using an unbound
list for your secondary control and binding it at runtime in code using the
value selected in your primary list as the criteria.

If your two tables are similar to below:

tblCountries
========
CountryID (Autonumber)
CountryName (Text)

tblStatesProvinces
============
StateProvinceID (Autonumber)
CountryID (Long Integer) *FK to tblCountries
StateProvinceName (Text)

then something similar to below should work:

=== START OF CODE ===============

Private Sub cmbCountry_Click()

Dim sql As String

sql = "SELECT " & _
"tblStatesProvinces.StateProvinceID, " & _
"tblStatesProvinces.CountryID, " & _
"tblStatesProvinces.StateProvinceName " & _
"FROM tblStatesProvinces " & _
"WHERE (((tblStatesProvinces.CountryID) = " & _
Trim$(Nz(cmbCountry.Column(0), 0)) & ")) " & _
"ORDER BY tblStatesProvinces.StateProvinceName"

cmbStateProvince.RowSource = sql

End Sub

=== END OF CODE ===============

--

Brian Kastel


--Original Message----------------

Hi,

I would like to create a 2 drop down menu on my form.
Primary Type & Secondary Type. I would like to limit the
secondary type to options that are related to the primary
type.

For example I'll use countries and city's

Primary
Canada
UK
America

Secondary
Toronto
Vancouver
London
Manchester
LA
New York

In this example, if i choose Canada, I would only like the
Candadian cities to show up and the same for the other
countries.

Is this possible in Access? I have seen and used it in
other systems.

Thanks

B

 

[bmistry]

Thank you so much - that's great!

-----Original Message-----
I forgot to note something very important in the previous text, and that is
the properties for your combo boxes.

For your Countries combo, you should set the columns property to 2, the
ColumnWidths property to 0", and set the RowSource property to a SELECT
query that contains the PK field and the text field, with the text field
sorted, if you like.

Set the Columns property of the StateProvince combo to 3, and ColumnWidths
to 0";0"

BTW, that code is tested code using the above configuration.

--

Brian Kastel


--Original Message----------------

"Brian Kastel" <be-ar-eye-ay-en-kay-ay-ess-tee-ee-

(e-mail address removed)> wrote

 

[doctor]

Quick question, Why cant the countryname be the FK?

tblCountries
========
CountryID (Autonumber)
CountryName (Text)

tblStatesProvinces
============
StateProvinceID (Autonumber)
CountryID (Long Integer) *FK to tblCountries
StateProvinceName (Text)

then something similar to below should work:

=== START OF CODE ===============

Private Sub cmbCountry_Click()

Dim sql As String

sql = "SELECT " & _
"tblStatesProvinces.StateProvinceID, " & _
"tblStatesProvinces.CountryID, " & _
"tblStatesProvinces.StateProvinceName " & _
"FROM tblStatesProvinces " & _
"WHERE (((tblStatesProvinces.CountryID) = " & _
Trim$(Nz(cmbCountry.Column(0), 0)) & ")) " & _
"ORDER BY tblStatesProvinces.StateProvinceName"

cmbStateProvince.RowSource = sql

End Sub

=== END OF CODE ===============

--

Brian Kastel


--Original Message----------------

Hi,

I would like to create a 2 drop down menu on my form.
Primary Type & Secondary Type. I would like to limit the
secondary type to options that are related to the primary
type.

For example I'll use countries and city's

Primary
Canada
UK
America

Secondary
Toronto
Vancouver
London
Manchester
LA
New York

In this example, if i choose Canada, I would only like the
Candadian cities to show up and the same for the other
countries.

Is this possible in Access? I have seen and used it in
other systems.

Thanks

B





.
[/vbcol] [/B

-
docto

 

[doctor]

Ok In order to maked dynamic drop down menus it seems like i need
foreign key linking the two fields (makes and the models in my case)
Which is unfortunate because as of now I have a table wit
[make][model] relationship columns. So as per your advice, I made tw
more tables.. Here is what i have so far.

table1
====
ModID(Auto Number)
Model(Text)
ManufID(foreign key to table 2, but EMPTY at this point)

table2
====
ManufID(Auto Number)
Manufaturer(Text)


table3
====
Manufacturer(Text)
Model(Text)

OK So heres the question, Is there any way i can run a script so tha
it scans table 3, looks at the Model field, looks at the Manufacture
field, and then inserts the appropriate ManufID into table 1. There ar
over 2k+ Models associated with their respective makes in table 3 an
manually entering ManufID into table 1 it would be painful!Sorry i
this is confusing, if somthing is not clear ask me to elaborate and
gladly will!

*Thank you so much - that's great![vbcol=seagreen]

-----Original Message-----
I forgot to note something very important in the previous[/vbcol]

text, and that is[vbcol=seagreen]

the properties for your combo boxes.

For your Countries combo, you should set the columns[/vbcol]

property to 2, the[vbcol=seagreen]

ColumnWidths property to 0", and set the RowSource[/vbcol]

property to a SELECT[vbcol=seagreen]

query that contains the PK field and the text field, with[/vbcol] the text field[vbcol=seagreen]
sorted, if you like.

Set the Columns property of the StateProvince combo to 3,[/vbcol]

and ColumnWidths[vbcol=seagreen]

to 0";0"

BTW, that code is tested code using the above[/vbcol] configuration.[vbcol=seagreen]

--

Brian Kastel


--Original Message----------------

"Brian Kastel" <be-ar-eye-ay-en-kay-ay-ess-tee-ee-[/vbcol]

(e-mail address removed)> wrote[vbcol=seagreen]

in message newsn8tc.23534[/vbcol] [email protected]...[vbcol=seagreen]
Your secondary table's records need to have a foreign key[/vbcol] field to your[vbcol=seagreen]
primary table. If you have that, it's a simple matter of[/vbcol]

using an unbound[vbcol=seagreen]

list for your secondary control and binding it at runtime[/vbcol]

in code using the[vbcol=seagreen]

value selected in your primary list as the criteria.

If your two tables are similar to below:

tblCountries
========
CountryID (Autonumber)
CountryName (Text)

tblStatesProvinces
============
StateProvinceID (Autonumber)
CountryID (Long Integer) *FK to tblCountries
StateProvinceName (Text)

then something similar to below should work:

=== START OF CODE ===============

Private Sub cmbCountry_Click()

Dim sql As String

sql = "SELECT " & _
"tblStatesProvinces.StateProvinceID, " & _
"tblStatesProvinces.CountryID, " & _
"tblStatesProvinces.StateProvinceName " & _
"FROM tblStatesProvinces " & _
"WHERE (((tblStatesProvinces.CountryID) = " & _
Trim$(Nz(cmbCountry.Column(0), 0)) & ")) " & _
"ORDER BY tblStatesProvinces.StateProvinceName"

cmbStateProvince.RowSource = sql

End Sub

=== END OF CODE ===============

--

Brian Kastel


--Original Message----------------

Hi,

I would like to create a 2 drop down menu on my form.
Primary Type & Secondary Type. I would like to limit the
secondary type to options that are related to the primary
type.

For example I'll use countries and city's

Primary
Canada
UK
America

Secondary
Toronto
Vancouver
London
Manchester
LA
New York

In this example, if i choose Canada, I would only like the
Candadian cities to show up and the same for the other
countries.

Is this possible in Access? I have seen and used it in
other systems.

Thanks

B





.
[/vbcol] *