Combo Box won't select a second record w/ the same info...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maura - 29 Jan 2007 00:22 GMT

I have a combo box that does a search by the last 8 of a VIN from an
automobile. Sometimes we will transport an automobile 2 or 3 times. When you
put the last 8 in the combo box it shows that there is a second record in the
list, but if you try to select that second record, the form will only remain
on the first record it finds.

I have another database for repossessing vehicles, I have used the same code
and I don't have this problem as I do for the transporting database. Any
ideas? Thanks, maura

Here is the code:

Private Sub Combo52_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "right([transVin],8)= '" & Me![Combo52] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Reply to this Message

Marshall Barton - 29 Jan 2007 01:35 GMT

>I have a combo box that does a search by the last 8 of a VIN from an
>automobile. Sometimes we will transport an automobile 2 or 3 times. When you
[quoted text clipped - 16 lines]
> If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>End Sub

That code finds the first record in the form's record source
table/query. If you want it to find a different record, you
need some identifying information for the second (or third)
record.

Maybe the combo box could have the entire VIN in another
column in its row source. if that's possible, then the find
first could be:

rs.FindFirst "transVin = '" & Me!Combo52.Column(1) & "'"

Be sure to adjust the combo box's ColumnCount, BoundColumn
and ColumnWidths properties if you change the row source
query.

Signature

Marsh
MVP [MS Access]

Reply to this Message