Combo Box in Access help

btrede

Board Regular
Joined
Oct 4, 2005
Messages
87
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:

244052119_404aaf8c26_o.jpg


244052121_52859f3f9f_o.jpg


244052123_b274f51b97_o.jpg


Here's the table that the data has:
244052125_0f5ca3ec82_o.jpg


I appreciate any help anyone can provide.

~~Bradley
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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 :

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

Board Regular
Joined
Oct 4, 2005
Messages
87
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 :

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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

Forum statistics

Threads
1,141,011
Messages
5,703,722
Members
421,311
Latest member
tanujath

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top