Drop-down 1 leading to drop-down 2

[KMEscherich]

Microsoft Access '97
Using VBA code

Have been asked to include a drop-down list which will retrieve the options from lookup table 1, then according to what the end-user selects from this drop-down, I need it to go to lookup table 2 with those specific options and record both selections in a table. Right now, I have a table for each option and corresponding catetory in order to ensure there are no mistakes for each category they want. But, if I can simplify the database with this, then it will be a LOT cleaner for the end-user. I can always create new tables and delete existing ones if needed.

Is there a way to accomplish this???

Thank you in advance for your time and assistance.

K Escherich

[missinglinq]

The concept is called Cascading Comboboxes.You should take a look at Rabbit's tutorial on the subject:

Cascading Combo/List Boxes

Good Luck!

Linq

[KMEscherich]

The concept is called Cascading Comboboxes.You should take a look at Rabbit's tutorial on the subject:

Cascading Combo/List Boxes

Good Luck!

Linq

Hi there, tried the information and it seems it does not work. Have any other ideas?

Thank you

[ADezii]

Microsoft Access '97
Using VBA code

Have been asked to include a drop-down list which will retrieve the options from lookup table 1, then according to what the end-user selects from this drop-down, I need it to go to lookup table 2 with those specific options and record both selections in a table. Right now, I have a table for each option and corresponding category in order to ensure there are no mistakes for each category they want. But, if I can simplify the database with this, then it will be a LOT cleaner for the end-user. I can always create new tables and delete existing ones if needed.

Is there a way to accomplish this???

Thank you in advance for your time and assistance.

K Escherich

You must be much more specific than this. Post the Names of the Combo Boxes, the RowSources for the Combos, Bound Column for each Combo, what 2 Fields are you writing to the Table, what is the Table Name, Data Types for the Fields in the Combos, etc. I would also take missingling's advice again and read the Reference on Cascading Combo Boxes.

[KMEscherich]

You must be much more specific than this. Post the Names of the Combo Boxes, the RowSources for the Combos, Bound Column for each Combo, what 2 Fields are you writing to the Table, what is the Table Name, Data Types for the Fields in the Combos, etc. I would also take missingling's advice again and read the Reference on Cascading Combo Boxes.

Hi there, I created 2 tables (T_STATE and T_CITY)

State = STATE_ID, STATE
City = CITY_ID, CITY, STATE_ID

Data in tables
STATE = California, Texas, Nevada
CITY = Los Angeles, San Francisco, San Diego
Austin, Houston, Dallas
Las Vegas, Reno
STATE_ID = CA, TX, NV

CONTROL 1 = State and bound column = STATE_ID
CONTROL 2 = City and no bound column

I linked the two table using STATE_ID in the relationships view.
I created a form with 2 unbound controls (combo & list boxes) and it seems that it did not work with either type of list.



Private Sub Form_Current()
Me.LST_STATE.RowSource = "SELECT T_STATE.STATE_ID, T_STATE.STATE FROM T_STATE"
End Sub

This works FINE. When you open the form, you see both columns of data like you should.

On the AfterUpdate option for CONTROL1, I placed the following code and all I get is a Parameter window

Private Sub LST_STATE_AfterUpdate()
With Me.LST_CITY
If IsNull(Me.LST_STATE) Then
.RowSource = ""
Else
.RowSource = "Select City from T_CITY where State_ID = " & Me.LST_STATE
End If
Call .Requery
End With
End Sub

I am testing this code in Access 2003, could this be the cause??? I am testing this so I can take it to work tomorrow.

Thank you again for your assistance.

[ADezii]

Hi there, I created 2 tables (T_STATE and T_CITY)

State = STATE_ID, STATE
City = CITY_ID, CITY, STATE_ID

Data in tables
STATE = California, Texas, Nevada
CITY = Los Angeles, San Francisco, San Diego
Austin, Houston, Dallas
Las Vegas, Reno
STATE_ID = CA, TX, NV

CONTROL 1 = State and bound column = STATE_ID
CONTROL 2 = City and no bound column

I linked the two table using STATE_ID in the relationships view.
I created a form with 2 unbound controls (combo & list boxes) and it seems that it did not work with either type of list.



Private Sub Form_Current()
Me.LST_STATE.RowSource = "SELECT T_STATE.STATE_ID, T_STATE.STATE FROM T_STATE"
End Sub

This works FINE. When you open the form, you see both columns of data like you should.

On the AfterUpdate option for CONTROL1, I placed the following code and all I get is a Parameter window

Private Sub LST_STATE_AfterUpdate()
With Me.LST_CITY
If IsNull(Me.LST_STATE) Then
.RowSource = ""
Else
.RowSource = "Select City from T_CITY where State_ID = " & Me.LST_STATE
End If
Call .Requery
End With
End Sub

I am testing this code in Access 2003, could this be the cause??? I am testing this so I can take it to work tomorrow.

Thank you again for your assistance.

1. It would be more efficient to set the Row Source Property for [LST_STATE} in the Properties Sheet as opposed to the Form's Current Event where it would have to be re-evaluated for each Record.
2. The Bound Column for [LST_STATE] is [STATE_ID], a String (CA, TX, NV), so you need to modify the syntax for the creation of the Row Source to:
   

   .RowSource = "Select City from T_CITY where State_ID = '" & Me.LST_STATE & "'"

[KMEscherich]

1. It would be more efficient to set the Row Source Property for [LST_STATE} in the Properties Sheet as opposed to the Form's Current Event where it would have to be re-evaluated for each Record.
2. The Bound Column for [LST_STATE] is [STATE_ID], a String (CA, TX, NV), so you need to modify the syntax for the creation of the Row Source to:
   

   .RowSource = "Select City from T_CITY where State_ID = '" & Me.LST_STATE & "'"

Hi there, I just tested it out and it now works!! THANK YOU VERY MUCH for your assistance.

[KMEscherich]

Hi there, I just tested it out and it now works!! THANK YOU VERY MUCH for your assistance.

Hi there, I forgot to ask, once you make the first selection and the 2nd list appears in the 2nd window, is there a way to capture the selction from the first and a selction from the second window in a table??

Thank you.

[ADezii]

Hi there, I just tested it out and it now works!! THANK YOU VERY MUCH for your assistance.

You're quite welcome.

[ADezii]

Hi there, I forgot to ask, once you make the first selection and the 2nd list appears in the 2nd window, is there a way to capture the selction from the first and a selction from the second window in a table??

Thank you.

Are you asking to capture both values from the Combo Boxes, namely [State_ID] and [City] then write them to a Table? If the answer is yes, this would have to be done from the AfterUpDate() Event of the 2nd Combo Box. If you don't know how to accomplish this, let me know.

[KMEscherich]

Are you asking to capture both values from the Combo Boxes, namely [State_ID] and [City] then write them to a Table? If the answer is yes, this would have to be done from the AfterUpDate() Event of the 2nd Combo Box. If you don't know how to accomplish this, let me know.

YES, I would like to capture the 2 selected values. Let's say someone selects CALIFORNIA as the state and SAN FRANCISCO for the city. I would like to see those 2 values appear in the two tables accordingly. Unfortunately, I don't know how to accomplish this. Can you show me???


State = STATE_ID, STATE
City = CITY_ID, CITY, STATE_ID

Data in STATE tables
STATE_ID = CA, TX, NV
STATE = California, Texas, Nevada

Data in CITY tables
CITY_ID = LAX, SFO, SAN, AUS, HOU, DAL, LAS, RNO
CITY = Los Angeles, San Francisco, San Diego,Austin, Houston, Dallas
Las Vegas, Reno
STATE_ID = CA, TX, NV


CONTROL 1 = State and bound column = STATE_ID
CONTROL 2 = City and no bound column

I hope this helps.

Thank you once again for your assitance.

[ADezii]

YES, I would like to capture the 2 selected values. Let's say someone selects CALIFORNIA as the state and SAN FRANCISCO for the city. I would like to see those 2 values appear in the two tables accordingly. Unfortunately, I don't know how to accomplish this. Can you show me???


State = STATE_ID, STATE
City = CITY_ID, CITY, STATE_ID

Data in STATE tables
STATE_ID = CA, TX, NV
STATE = California, Texas, Nevada

Data in CITY tables
CITY_ID = LAX, SFO, SAN, AUS, HOU, DAL, LAS, RNO
CITY = Los Angeles, San Francisco, San Diego,Austin, Houston, Dallas
Las Vegas, Reno
STATE_ID = CA, TX, NV


CONTROL 1 = State and bound column = STATE_ID
CONTROL 2 = City and no bound column

I hope this helps.

Thank you once again for your assitance.

You're welcome for the assistance. Here is the remainder of your question - any further questions, feel free to ask.

I'm not sure of the mechanism by which you want to add the data to a Table,
but here it is demonstrated in the AfterUpdate() Event of the City Combo Box.
Remember, the Bound Field in cboState is [STATE_ID], so you cannot use Me![cboState],
because you want to write the State, not the State_ID to the Table.

Private Sub cboCity_AfterUpdate()
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRet As Integer
 
If Not IsNull(Me![cboState].Column(1)) And Not IsNull(Me![cboCity]) Then
  'You can name your Table/Fields anything you like. I just named then tblStateCity with
  '[State] and [City] as Fields in the Table.
  intRet = MsgBox("Add State and City to tblStateCity", vbQuestion + vbYesNo + vbDefaultButton1, "Add Record")
    If intRet = vbYes Then
      Set MyDB = CurrentDb()
      Set MyRS = MyDB.OpenRecordset("tblStateCity", dbOpenTable)
        MyRS.AddNew
          MyRS![State] = Me![cboState].Column(1)
          MyRS![City] = Me![cboCity]
        MyRS.Update
          MyRS.Close
    End If
End If
End Sub

[KMEscherich]

You're welcome for the assistance. Here is the remainder of your question - any further questions, feel free to ask.

I'm not sure of the mechanism by which you want to add the data to a Table,
but here it is demonstrated in the AfterUpdate() Event of the City Combo Box.
Remember, the Bound Field in cboState is [STATE_ID], so you cannot use Me![cboState],
because you want to write the State, not the State_ID to the Table.

Private Sub cboCity_AfterUpdate()
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, intRet As Integer
 
If Not IsNull(Me![cboState].Column(1)) And Not IsNull(Me![cboCity]) Then
  'You can name your Table/Fields anything you like. I just named then tblStateCity with
  '[State] and [City] as Fields in the Table.
  intRet = MsgBox("Add State and City to tblStateCity", vbQuestion + vbYesNo + vbDefaultButton1, "Add Record")
    If intRet = vbYes Then
      Set MyDB = CurrentDb()
      Set MyRS = MyDB.OpenRecordset("tblStateCity", dbOpenTable)
        MyRS.AddNew
          MyRS![State] = Me![cboState].Column(1)
          MyRS![City] = Me![cboCity]
        MyRS.Update
          MyRS.Close
    End If
End If
End Sub

Hi there, THANK YOU VERY MUCH for your assistance. As I am reviewing the code, am wondering if there should be a button on the form. And if so, should all of this code be associated with a button?

[KMEscherich]

Hi there, THANK YOU VERY MUCH for your assistance. As I am reviewing the code, am wondering if there should be a button on the form. And if so, should all of this code be associated with a button?

I finally got it to work. I want to THANK YOU VERY, VERY MUCH for your assistance.

[ADezii]

I finally got it to work. I want to THANK YOU VERY, VERY MUCH for your assistance.

The other Moderators/Experts and myself are also glad to be of assistance.

[KMEscherich]

The other Moderators/Experts and myself are also glad to be of assistance.

By the way, where are you folks located??? Are you overseas or here in the US?

[KMEscherich]

By the way, where are you folks located??? Are you overseas or here in the US?

Hi there, am wondering if you can help me once again?

Am attempting the following

I have a main table that has a primary key (SRI). I developed two tables
LT_KEY_FACTORS TABLE
FACTOR_ID
FACTOR

LT_SUB_FACTORS TABLE
SUB_FACTOR_ID
SUB_FACTOR
FACTOR_ID

T_FACTORS TABLE (This is the table that will capture the data when someone clicks on a line item in the second panel.
FACTOR_ID
KEY_FACTOR
SUB_FACTOR
SRI

I need to pull in the SRI from the main table so I can see how many key factors and sub factors are tied to a specific SRI. I have a MAIN table where the SRI is the PRIMARY KEY.

When I run this like we did using the STATE and CITY tables it works fine. However, when I attempt to pull in a third table that contains the SRI (MAIN TABLE). It does not work. Can you please tell me where I am going wrong??

Thank you

[MMcCarthy]

By the way, where are you folks located??? Are you overseas or here in the US?

US is home but the site is international. Admins, moderators and experts come from all over the world.