Using a List box to goto a record

[rmacleod]

I have a form that I am using a list box to goto a record. in the List box I am selecting the Serial number of an item to go to a repair record for that item. the Problem I have is if that Serial number is in the table more then once. It only goes to the first record. The Primary key is a combonation of Serial number and In Date.
here is the code I am using:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FldSN] = '" & Me![LstSearch] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Is there a way I can add the [FldIndate] to find the correct entry?

I am new to Access and VB so I dont really understand the
rs.FindFirst "[FldSN] = '" & Me![LstSearch] & "'"
line.

 

[WayneRyan]

r,

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FldSN] = '" & Me![LstSearch] & "' And " & _
             "[InDate] = #" & Me.InDate & "#"
If Not rs.EOF Then 
   Me.Bookmark = rs.Bookmark
Else
   MsgBox("There are no records.")
End If

In a typical SQL statement you must "tell" sql what the
datatypes are. You must think ahead to what the JET
engine requires.

Field = 'SomeString'
Field = SomeNumber
Field = #SomeDate#

Wayne

 

[rmacleod]

Ok...I understand it now...but it is not working for me

If I do this:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[fldInDate] = #" & Me.LstSearch.Column(1) & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

it has no error

If I do this:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FldSN] = '" & Me.LstSearch.Column(0) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It has no error

But if I do this:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FldSN] = '" & Me.LstSearch.Column(0) & "'" And "[fldInDate] = #" & Me.LstSearch.Column(1) & "#"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

It has a Type Mismatch error.

any Ideas?