Multicolumn combobox

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
I've got a database that looks after problem logs. The users want to be able to pick all queries that relate to 3 fields, Account#, Workstream and update_status. They've asked for a combobox that lists those 3 and the number of logs that relate to each combination.

So I've created a query that generates those and uses that as the source for a 4 column combobox.

I figured to set BoundColumn to 0 to give me the (n-1)th item in the list (i.e. first item - 0).

How can I now pull the (n-1)th row from the query to identify the 3 fields in the Combobox selection, so I can select the rows from the database to populate a subform that satisfy the 3 fields in the Combobox selection?

Cheers
Johnny
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

tinkythomas

Active Member
Joined
Dec 13, 2006
Messages
432
Hi,

You cant refer directly to the combo column in a query but with a simple function we can. Just copy and paste the following function into a standard vba module in your project...
Code:
Public Function GetComboValue(lIndex As Long) As Variant
    GetComboValue = Forms![Form1]![Combo0].Column(lIndex)
End Function
Remember to replace [Form1]![Combo0] with your form/combo name. Now you can call the function in your query criteria by simply using GetComboValue(0).

Repeat this for each field, remembering to change the combo index number, e.g GetComboValue(1) this would return the second columns value from the combo.

HTH
 

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,008
Hi TT

I had reckoned that columns(index) was what should work, but it didn't in the expression builder on the subform.

So I tried making label values = the columns(index) and that worked and you can refer to the labels in the expression builder so I got around it that way.

However it's enormously useful to know that I can do it via functions for the future. VBA is more my comfort zone, if I can call VBA functions from the expression builder then my scope increases enormously.

Thanks a million!
JC
 

Watch MrExcel Video

Forum statistics

Threads
1,133,714
Messages
5,660,431
Members
418,580
Latest member
JP82

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