Synchronize combobox row with external value

Question

I have an unbound combobox that I fill dynamically from a SQL Server in-memory recordset. The combobox is bound to the first column (PO #), which is NOT the unique ID. The Unique ID is ReceiptID, which is in Column 7. 

What I want to do is reposition the combobox to the row that matches a ReceiptID from another source.

For example, ReceiptID 1234 is passed in. How do I select the CBox row that contains 1234 in column 7?

Thanx!

---

Darrell H Burns

Answer 1

Just open the Combobox Row Source and move (drag) column 7 over to column 1 (assuming you want column 7 to be the new column 1)

Answer 2

Or perhaps just bind the combo to column(7) if that doesn't cause other issues elsewhere

Answer 3

No, I don't want to change the column order or change the column binding because I don't want it to sort by ReceiptID. I know it would be a trivial matter if the CBox was bound to an Access query where I could manage the sort order, but since it's being filled from a SQL recordset I have to choose PO# as column 1 so it'll sort by PO#.

Again, is there a way to position the CBox on a row based on an unbound column value (short of looping over every row to find a match)?

---

Darrell H Burns

Answer 4

Please see my response to Lawrence, above.

---

Darrell H Burns

Answer 5

I have no idea what an "in-memory" recordset might be. So I guess you have no access to the CBox properties on your form so you can't change the Row Source? I have never heard of that.

Answer 6

Sorry but this makes no sense to me at all.

You can sort by whatever column you like whilst binding to another column.

Neither Lawrence's suggestion, nor mine require you to sort by ReceiptID

In any case, as I'm sure you know, the column numbering is zero based so the first column is column(0)

BTW - I believe the OP is referring to what I call a disconnected ADO recordset

See https://www.databasejournal.com/features/msaccess/article.php/3846361/Create-In-Memory-ADO-Recordsets.htm

Answer 7

For example, ReceiptID 1234 is passed in. How do I select the CBox row that contains 1234 in column 7?

Hi Darrell,

You could make a separate form, loaded with the same recordset. With a RecordsetClone and Bookmark, or FindFirst, you can make any record the current record. Some kind of button action returns you the wanted value.

Imb.

Answer 8

I appreciate the response, but I'm not trying to synchronize the record on the form, I'm trying to synchronize the combobox row TO the record on the form. I use the OnCurrent event to capture the current record's ReceiptID, then I want to move the combobox row position to that same record. Again, the thing I can't figure out is how to do that when the ReceiptID is not the CBox's bound column.

---

Darrell H Burns

Answer 9

Open the CBox Row Source and in the ReceiptID column Criteria Box enter:

Forms![NAME OF THE FORM]![ReceiptID]

so you restrict the Row Source ReceiptID field to the forms current record ReceiptID

Answer 10

You need to search through the columns like this (aircode):

Dim i As Long

For i = 0 To Me!cboReceipt.ListCount - 1
    If Me!cboReceipt.Column(6, i) = ActualReceiptID Then Exit For
Next
Me!cboReceipt = Me!cboReceipt.Column(0, i)

Keep in mind that column numbering in VBA starts from 0. Thus if ReceiptID is preceded by 6 other columns, then you would look at Column(6, i), not Column(7, i).

Matthias Kläy, Kläy Computing AG

Answer 11

Open the CBox Row Source and in the ReceiptID column Criteria Box enter:

Forms![NAME OF THE FORM]![ReceiptID]

so you restrict the Row Source ReceiptID field to the forms current record ReceiptID

But I don't want to restrict the row source. I just want to select that row.

---

Darrell H Burns

Answer 12

You need to search through the columns like this (aircode):

Dim i As Long

For i = 0 To Me!cboReceipt.ListCount - 1
    If Me!cboReceipt.Column(6, i) = ActualReceiptID Then Exit For
Next
Me!cboReceipt = Me!cboReceipt.Column(0, i)

Keep in mind that column numbering in VBA starts from 0. Thus if ReceiptID is preceded by 6 other columns, then you would look at Column(6, i), not Column(7, i).

Matthias Kläy, Kläy Computing AG

OK, I guess that's the only solution. I tried a similar approach using a loop to locate the rownum and then setting cboReceipt.selected(rownum) = true, but that didn't seem to work. Thank you, Matthias. I appreciate everybody's contribution!

---

Darrell H Burns