Unbound combo box does not display assigned value

[Freeflow]

I have an unbound combo box on a form.

I assign a query programatically to the combo box row source

The pull down of the combobox shows the query has behaved correctly but the combobox is blank.

The combo box query has 5 columns

The bound column is column 4

All columns in the query are populated with a value (no nulls or empty strings)

The value assigned to the unbound combo box is taken from the underlying recordset.

eg me.cmbMyUnbound = me.recordset!MyUnbound

If I trace the above step then hovering the cursor shows each side of the statement to be different before execution and the same after execution (the value of me.recordset!Myunbound is copied to me.cmbMyunbound)

So why is the combo box showing blank?

I understand about the bound column and displayed column not needing to be the same.

I'm completely confused.

Can anyone point me to the utterly obvious mistake I'm making?

 

[cyanidem]

Maybe you need to Requery combo box after you assign new rowsource?
Me.cmbMyUnbound.Requery
Just a thought.

 

[Freeflow]

Maybe you need to Requery combo box after you assign new rowsource?
Me.cmbMyUnbound.Requery

Requery happens automatically whenever you assign a value to rowsource.

As I said in my post above. Pulling down the combobox shows that the query has run correctly.

 

[arnelgp]

what is the value of this, me.recordset!MyUnbound?
does the datatype match with your unbound combo?

 

[Freeflow]

what is the value of this, me.recordset!MyUnbound?
does the datatype match with your unbound combo?

They are both text.

When single stepping through

me.cmbMyUnbound = me.recordset!MyUnbound

With the yellow highlight on the statement then hovering the cursor over each part of the statement shows

me.cmbMyUnbound ->"Hello World"

Me.recordset!MyUnbound ->"A nice reply"

Afterwards, with the yellow highlight on the next statement we have

me.cmbMyUnbound ->"A nice reply"

Me.recordset!MyUnbound ->"A nice reply"

So the assignment appears to have worked but nothing appears in the combo box.

 

[arnelgp]

bound column is 4, right. does that column has the word "A nice reply"? if not, then you are looking at the wrong column.

is column 4 number if so :

me.cmbMyUnbound = the number (row) where "A nice reply" can be found.

 

[Freeflow]

No, column 4 is a number (foreign key)

From

https://msdn.microsoft.com/en-us/library/office/ff822489.aspx

In Visual Basic, set the BoundColumn property by using a number or a numeric expression equal to a value from 0 to the setting of the ColumnCount property.

The leftmost visible column in a combo box (the leftmost column whose setting in the combo box's ColumnWidths property is not 0) contains the data that appears in the text box part of the combo box in Form view or in a report. The BoundColumn property determines which column's value in the text box or combo box list will be stored when you make a selection. This allows you to display different data than you store as the value of the control.

But I'm not sure if the above applies to an unbound combobox.

If it helps I can post some of the source code I'm using (I'm sure everyone could do with a good laugh)

 

[arnelgp]

have you really tried it?

me.cmbMyUnbound = number corresponding to "A nice reply"

 

[Freeflow]

I have now.

Your suggestion got me most of the way. All is now working as it should.

I think my problem came from how I named things so I was trying to line up the combo box 'cmbMyUnbound' with the field 'myUnbound' in the construction of the query.

I changed that so that I was assigning 'myUnboundID' and two other similar changes.

So a big thanks. I'll mow go stand in the corner and sulk for a while that my limited grasp of Access is even more limited than I thought.

It *was* misleading though about how inspecting the value of variables gave me no clue that I was doing something wrong.

 

[Freeflow]

Final observation

The combobox is unbound therefore the form load event sets the initial value to avoid the appearance of a blank combobox but data for fields that come from the underlying dataset.

I have tried assigning both number and text during the load event and either is accepted.

The form and its associated event procedures for managing changes to the current form and changes to the combobox only work correctly if a number is assigned on the first occasion during the load event.

So my next adventure is how to constrain the input of a combo box to a number only.