If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Drop Down Menu's
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 |
#3
|
|||
|
|||
Drop Down Menu's
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 |
#5
|
|||
|
|||
Drop Down Menu's
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 |
#6
|
|||
|
|||
Drop Down Menu's
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 |
Thread Tools | |
Display Modes | |
|
|