Drop Down Menu's

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

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----------------

"bmistry" wrote in 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

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" wrote
in message news 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----------------

"bmistry" wrote in 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

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-
wrote
in message newsn8tc.23534
...
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----------------

"bmistry" wrote in
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





.

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----------------

"bmistry" wrote in[/vbcol]
message[vbcol=seagreen]
...
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]



--
doctor
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message713508.html

Ok In order to maked dynamic drop down menus it seems like i need a
foreign key linking the two fields (makes and the models in my case).
Which is unfortunate because as of now I have a table with
[make][model] relationship columns. So as per your advice, I made two
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 that
it scans table 3, looks at the Model field, looks at the Manufacturer
field, and then inserts the appropriate ManufID into table 1. There are
over 2k+ Models associated with their respective makes in table 3 and
manually entering ManufID into table 1 it would be painful!Sorry if
this is confusing, if somthing is not clear ask me to elaborate and I
gladly will!




bmistry wrote:
*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]
wrote[vbcol=seagreen]
in message newsn8tc.23534[/vbcol]
...[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----------------

"bmistry" wrote in[/vbcol]
message[vbcol=seagreen]
...
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] *



--
doctor
------------------------------------------------------------------------
Posted via http://www.mcse.ms
------------------------------------------------------------------------
View this thread: http://www.mcse.ms/message713508.html