Multicolumn combobox

Johnny C

Well-known Member
Joined
Nov 7, 2006
Messages
1,003
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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,003
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,151
Messages
5,509,461
Members
408,736
Latest member
BillMuffler

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top