Multicolumn combobox

[Johnny C]

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

 

[tinkythomas]

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...

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]

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