Combo Box in Access help

[btrede]

Hello:

I am almost done with this terrible project but,
I am hoping someone can point me to a direction to help me achieve what I am trying to do.

I have a form (shown below)

I have 2 combo boxes, type and state

What I to do is when some one chooses the type, it automatically populates the state that the type coresponds to.

For example if I choose Type 1, it will populate, IL. WI, MI, NY into the State combox box and then the user can select the state they want.

If they choose Type 2, it will populate NV, CA, CO, TX into the State combo
box.

I know how to populate a single value, but to populate all relating to a type, I am not sure.

Here are the screens:

Here's the table that the data has:

I appreciate any help anyone can provide.

~~Bradley

 

[Andrew Fergus]

Hi Bradley

Open the form in design view mode, right click the first combo box and make sure the bound column is the 'Type' field - if you have hidden the autonumber field in the first combo box then you need to bind column 2 (ie Set the 'Bound Column' property to 2 if you have hidden the autonumber field, otherwise set the 'Bound Column' property to 1). If you aren't sure about this, post the 'Row Source' property for the first combo box.

Go to the 'Event' tab for the first combo box, click the 'After Update' property and open the VB editor screen by clicking the 3 dots to the right. This will take you into the VB editor screen and enter the following code :

Private Sub MyFirstComboBox_AfterUpdate()

On Error Resume Next

Me.Combo4.Value = ""

Me.Combo4.RowSource = "SELECT MyStateTable.MiscRecID, MyStateTable.State " & _
    "FROM MyStateTable " & _
    "WHERE MyStateTable.Type = '" & Me.MyFirstComboBox.Value & "'" & _
    "ORDER BY MyStateTable.State;"

End Sub

You will find the first line is already there with a different name (ie the name of your combo box) - leave that as it is.

Within the code where I have used 'MyFirstComboBox', 'Combo4' & 'MyStateTable' - change these to your actual combo box names and the correct name of your table. If you change the name of your 2nd combo box on your form to something more meaningful, then you will need to change that in the code too - I'm referring to the 'Name' property of the 2nd combo box, not the label.

Lastly, in your second combo box, open the properties dialogue box and set the 'Column Count' property to 2, set the 'Column Widths' to something like '0cm; 3cm' (without the quotes) and depending on whether you are binding on the autonumber or the state field, set the 'Bound Column' property to either 1 (for the Autonumber) or 2 (for the State),

HTH, Andrew

 

[btrede]

Hi Bradley

Open the form in design view mode, right click the first combo box and make sure the bound column is the 'Type' field - if you have hidden the autonumber field in the first combo box then you need to bind column 2 (ie Set the 'Bound Column' property to 2 if you have hidden the autonumber field, otherwise set the 'Bound Column' property to 1). If you aren't sure about this, post the 'Row Source' property for the first combo box.

Go to the 'Event' tab for the first combo box, click the 'After Update' property and open the VB editor screen by clicking the 3 dots to the right. This will take you into the VB editor screen and enter the following code :

Private Sub MyFirstComboBox_AfterUpdate()

On Error Resume Next

Me.Combo4.Value = ""

Me.Combo4.RowSource = "SELECT MyStateTable.MiscRecID, MyStateTable.State " & _
    "FROM MyStateTable " & _
    "WHERE MyStateTable.Type = '" & Me.MyFirstComboBox.Value & "'" & _
    "ORDER BY MyStateTable.State;"

End Sub

You will find the first line is already there with a different name (ie the name of your combo box) - leave that as it is.

Within the code where I have used 'MyFirstComboBox', 'Combo4' & 'MyStateTable' - change these to your actual combo box names and the correct name of your table. If you change the name of your 2nd combo box on your form to something more meaningful, then you will need to change that in the code too - I'm referring to the 'Name' property of the 2nd combo box, not the label.

Lastly, in your second combo box, open the properties dialogue box and set the 'Column Count' property to 2, set the 'Column Widths' to something like '0cm; 3cm' (without the quotes) and depending on whether you are binding on the autonumber or the state field, set the 'Bound Column' property to either 1 (for the Autonumber) or 2 (for the State),

HTH, Andrew

Andrew

Thanks for your help!

One more question:

Now in the first combo box it lists the type multiple times. What can I do to only list it once so it would look like this

Combo Box One: One
Two

Meaning, there are no duplicated rows. is this possible.

Thanks,

Bradley

 

[Andrew Fergus]

Hi Bradley
Change the control source for the first combo box to this :

SELECT MyStateTable.Type FROM MyStateTable GROUP BY [Type] ORDER BY [Type];

but use your actual table name where I have used 'MyStateTable'.

Also, change the column count property to 1, set the column width accordingly (for one column) and set the 'Bound Column' property also to 1.

HTH, Andrew

 

[SydneyGeek]

Hi, here's a quick summary of what I would do:

Type
Rowsource; SELECT DISTINCT Type FROM MyState ORDER BY Type

Combo4
Rowsource; SELECT * FROM MyState WHERE MyState.Type = Forms!Form1!Type

Also, in the Type combo, add this code to the AfterUpdate event --
Combo4.Requery

This is close to what Andrew suggested but DISTINCT lets you choose unique occurrences. It's a good idea to only include fields that you will use in the rowsource of a combo; that way you don't have excess baggage, and if the tables get large, performance is improved.

Denis