Combo Box can't look up 2nd record w/ same info

[Guest]

I have a field in my database for vehicle VIN number that has 17
letters/numbers. I need my combo box to lookup/search the last 6 of the VIN
and I have done this successfully:

Private Sub cboVinDate_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

The problem is when I have 2 records with the same last six of the VIN. The
combo box will NOT pull up the second record. It was suggested to me that I
try another column in its row source:

rs.FindFirst "right([transVIN],6) = '" & Me!cboVinDate.Column(1) & "'"

I tried this but it didn’t work. I have even added more fields to the
cboVinDate to include: right([transVIN],6); transId; transDateIn;
transDropDate; CustomerID… just so the user can identify a record and then
type the transID into another combo box and pull that record up. I adjusted
the column count, and column widths properties. Am I not doing something like
adjusting the bound column so the code will recognize that there is a
secondary record or does the code have enough with the "right([transVIN],6) "
that it can’t look up an additional piece of information?

Please let me know if you have any ideas. I have been playing around with
this for months and can’t seem to remedy this problem.

 

[Mark A. Sam]

The problem is that you need do the find on a unique item. Whenever there
are records with identical information, Access will choose the first item on
the list. I would add an autonumber and use that as the first column for
the combo or search against the VIN number and not the date.

God Bless,

Mark A. Sam

 

[Guest]

Hi Mark...

I have three different ways (three different combo boxes) for a user to
lookup a record: transId (and this is an autonumber), Last 6 of the VIN, and
Last name.

The combo box to look up last name has three fields in its row source:
TransID (which is hidden), Last Name and First Name. So when a user looks up
a last name like Smith (and there maybe several Smiths), the user has the
ability to choose from the list any of the Smiths, including ones with the
same first name. Here's the code:

Private Sub cboFindByDebtor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TransID] = " & Me![cboFindByDebtor].Value & ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'cboFindByDebtor.Text = Me![DbLastName]

End Sub

And this works for the last name. How can I write the code by Last 6 of the
Vin, so it will do the same as the search for last name???

Thanks for looking at this, maura

The problem is that you need do the find on a unique item. Whenever there
are records with identical information, Access will choose the first item on
the list. I would add an autonumber and use that as the first column for
the combo or search against the VIN number and not the date.

God Bless,

Mark A. Sam

I have a field in my database for vehicle VIN number that has 17
letters/numbers. I need my combo box to lookup/search the last 6 of the
VIN
and I have done this successfully:

Private Sub cboVinDate_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

The problem is when I have 2 records with the same last six of the VIN.
The
combo box will NOT pull up the second record. It was suggested to me that
I
try another column in its row source:

rs.FindFirst "right([transVIN],6) = '" & Me!cboVinDate.Column(1) & "'"

I tried this but it didn't work. I have even added more fields to the
cboVinDate to include: right([transVIN],6); transId; transDateIn;
transDropDate; CustomerID. just so the user can identify a record and then
type the transID into another combo box and pull that record up. I
adjusted
the column count, and column widths properties. Am I not doing something
like
adjusting the bound column so the code will recognize that there is a
secondary record or does the code have enough with the
"right([transVIN],6) "
that it can't look up an additional piece of information?

Please let me know if you have any ideas. I have been playing around with
this for months and can't seem to remedy this problem.

 

[Mark A. Sam]

Hello Maura,

In your first example you seem to be looking at the date:

rs.FindFirst "right([transVIN],6) = '" & Me![cboVinDate] & "'"

If you use [TransID], [Last Name] [First Name] , [Date] or whatever the
fields are.

This should solve it. It seems you want to sort and select by [Date]. Is
that correct?

Set you ColumnWidths property to something like this: 0";2";2";1"

God Bless,

Mark

Hi Mark...

I have three different ways (three different combo boxes) for a user to
lookup a record: transId (and this is an autonumber), Last 6 of the VIN,
and
Last name.

The combo box to look up last name has three fields in its row source:
TransID (which is hidden), Last Name and First Name. So when a user looks
up
a last name like Smith (and there maybe several Smiths), the user has the
ability to choose from the list any of the Smiths, including ones with the
same first name. Here's the code:

Private Sub cboFindByDebtor_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[TransID] = " & Me![cboFindByDebtor].Value & ""
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

'cboFindByDebtor.Text = Me![DbLastName]

End Sub

And this works for the last name. How can I write the code by Last 6 of
the
Vin, so it will do the same as the search for last name???

Thanks for looking at this, maura

The problem is that you need do the find on a unique item. Whenever
there
are records with identical information, Access will choose the first item
on
the list. I would add an autonumber and use that as the first column for
the combo or search against the VIN number and not the date.

God Bless,

Mark A. Sam

I have a field in my database for vehicle VIN number that has 17
letters/numbers. I need my combo box to lookup/search the last 6 of the
VIN
and I have done this successfully:

Private Sub cboVinDate_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

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

The problem is when I have 2 records with the same last six of the VIN.
The
combo box will NOT pull up the second record. It was suggested to me
that
I
try another column in its row source:

rs.FindFirst "right([transVIN],6) = '" & Me!cboVinDate.Column(1) & "'"

I tried this but it didn't work. I have even added more fields to the
cboVinDate to include: right([transVIN],6); transId; transDateIn;
transDropDate; CustomerID. just so the user can identify a record and
then
type the transID into another combo box and pull that record up. I
adjusted
the column count, and column widths properties. Am I not doing
something
like
adjusting the bound column so the code will recognize that there is a
secondary record or does the code have enough with the
"right([transVIN],6) "
that it can't look up an additional piece of information?

Please let me know if you have any ideas. I have been playing around
with
this for months and can't seem to remedy this problem.