Unbound Combo Box Based on a Query (1 Viewer)

[wilderfan]

On one of my forms, I have an unbound combo box based on a query.

The query contains 6 columns. The 1st column is the "bound" column, but it is not seen by the user when the user clicks on the combo box. The user sees only the contents of Columns 2 through 6.

After making a selection and moving on to the next control on the form, the combo box shows only the contents of Column 2. What was previously visible in Columns 3 through 6 disappears.

I thought about putting an expression into the Query (instead of showing Columns 2 through 6 separately), but this option is NOT pleasing to the eye when you click on the dropdown arrow of the combo box.

QUESTION: Without resorting to an expression in the query, is there a way to "see" the contents of Columns 2 through 6 AFTER the user makes a selection?

 

[Ranman256]

put text boxes on the form, fill the boxes after user selects.
Note: in vb, columns start with zero:

sub cboBox_afterupdate()
txtFirstN = cboBox.column(1)
txtLastN = cboBox.column(2)
etc
end sub

 

[wilderfan]

Thanks, Ranman256. Works great.

There is one other minor change I would like to make to the combo box. One of the visible columns contains $ figures from the query. In the query and in the original source table, the $ amounts are formatted to show zero decimal places.

But in the combo box, the column containing the $ amounts shows 2 decimal places for some reason.

I'd like to get rid of the 2 decimal places, if that is possible. Perhaps I should temporarily change the format of the $ amounts to text ?

 

[Ranman256]

in the query , try setting the format,
click in the cell with the money,
format property,
fixed, 0

 

[wilderfan]

in the query , try setting the format,
click in the cell with the money,
format property,
fixed, 0

Unfortunately, that didn't work. After making the changes, the dropdown contents of the combo box still show the amount field with 2 decimal places. Oddly enough, when I go back to look at the field properties in the query builder, the changes I made have disappeared - even though I had tried to save the changes when exiting the query builder. Very strange.

 

[onur_can]

Take advantage of the round function. Add a field to the query design window and use the expression below.

Test: Round([field])

 

[wilderfan]

Test: Round([field])

I tried that and still had problems. However ...

that prompted me to try FormatCurrency with the option of 0 decimal places. And that worked.

Merci !

 

[Pat Hartman]

When I want my closed combo to show multiple columns I concatenate them in the RowSource query.

Select EmpID, FirstName & " " & LastName As FullName From tblEmployee

So EmpID is still the bound column but the combo shows Bob Jones instead of Bob.

 

[zeroaccess]

You may run into issues if you ever export this data to a spreadsheet and plan to work with it there. In my experience, the Format function results in numbers formatted as text. Therefore, you won't be able to perform any calculations on the data until you use the =VALUE function to convert them.

Just a heads up.