Problems with FindFirst function

[AdamJ]

I have created a find first function, that will ask the user to input a series of criteria to search by, use this criteria to find the specific record and then use the final user input to update part of the record.

I have written thusly:

Private Sub cmdFrame_Click()
Dim fra, occ, car, cat, dat, ms1, ms2, ms3, ms4, ms5, title, default As String
Dim db As DATABASE
Dim rst As Recordset

On Error GoTo err_frame

ms1 = "Please enter the Occurrence Number."
ms2 = "Please enter the Card Number."
ms3 = "Please enter the Merchant Cat Code."
ms4 = "Please enter the date of use."
ms5 = "Enter the Frame Ref Number to input."

title = "Frame Reference Number Update"

default = "1"

occ = InputBox(ms1, title, default)
car = InputBox(ms2, title, default)
cat = InputBox(ms3, title, default)
dat = InputBox(ms4, title, default)
fra = InputBox(ms5, title, default)

Set db = CurrentDb
Set rst = db.OpenRecordset("tblCardUse", dbOpenDynaset)
With rst
.FindFirst (!Occurrence = occ And !CardNo = car And !MerCat = cat And !Date = dat)
If .NoMatch Then
MsgBox "No Existing Record, please check what you have entered."
Else
.edit
!FrameRef = fra
.Update
MsgBox "The Frame Reference Number has been updated."
End If
.Close
End With

Exit_frame:
Exit Sub
err_frame:
MsgBox Err.Number
MsgBox Err.Description
Resume Exit_frame
Me.Refresh

The response i get is that there is no matching records, although i no there is and am entering the information correctly.

Can some see where i've gone wrong.

Cheers,

Adam

 

[tlbroadbent]

Modify your findFirst statement as follows. Let me know if it works. I haven't time to test right now.

.FindFirst "Occurrence=" & occ & " And CardNo=" & car & " And MerCat=" & cat & " And Date=" & dat
Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[AdamJ]

Terry,

When i changed the format to what you stated i got the following err msg.

3464 Data type Mismatch in criteria expression.

Incidently when i use the method detailed in my first post if there is only one record in the table it will return a matching record, though it will not just match it anyway becuase i have tested with incorrect data and it didnt return a match. Does this suggest that it is only applying the search the first record then stopping?

In case you wish to recreate this the data im using as criteria to search tblCardUse is as follows:

occ = 1
car = 906843
mer = 72
dat = 14/07/2001
fra is what gets inputted obviously.

cheers again,

Adam

 

[AdamJ]

Further to above, the findfirst is only looking at the first record then stopping, I have tried adding .movelast before the .findfirst command, but this has not made any difference. So the criteria is working but the search is not covering more than the first record, any suggestions?

 

[GBall]

Adam,
did you resolve this, 'cos I'm getting the same problem.
Findfirst does not return nomatch, but it doesn't move to the found record either.

Regards,
Graham