rst.findfirst problem

[jm112]

Hey guys,

Relatively new in access and programming and having a small problem. I'm trying to open a table and find a record where the ID value on the form matches the PK of the table and grabs that record so I can edit 2 fields.

The problem I'm running into is that rst.nomatch is always false and it always pulls the first record in the table. I've tried playing around with this for a few hours but always the same result.

Here's my code:

Dim db As DAO.Database
Dim rst As DAO.Recordset
dim i1 as integer

Set rst = db.OpenRecordset("tbl_hours", dbOpenSnapshot)
        rst.MoveFirst
        rst.FindFirst "[Hours_ID] = '" & Me!txtID & "'"   'Hours_ID is a number, long integer and the PK of tbl_hours.  txtID is the field on the form which is a primary key, long integer (not sure if type matters)
        i1 = rst!Hours_ID 'for debugging this always returns 1
        rst.Edit  'everything below here works fine!
        rst("Hours") = Me!fraHours
        rst("Weekend") = Me!fraWeekends
        rst.Update
        rst.Close
        Set rst = Nothing

When debugging this, Me!txtID always returns the correct value, it's just that FindFirst always pulls the first record ([Hours_ID]=1) in the table. Everything I've tried, even using not rst.nomatch always returns that there's a match and it's always returning the first record as that match.

Thanks in advance!

 

[gemma-the-husky]

not sure

but you are doing

rst.movefirst

THEN doing rst.findfirst

so when the findfirst fails, i imaging you are still on the first record

------------
you probably need

rst.findfirst expression
if not rst.nomatch then etc

 

[jm112]

Thanks for the response.

That makes sense why it always says "1" for the value because of movefirst, but that still doesn't explain why not rst.nomatch is always true, yet it doesn't move to that record when editing.

 

[jm112]

After another hour slamming my head into the keyboard I figured it out.

Needed to change to:

Set rst = db.OpenRecordset("tbl_hours", dbOpenDynaset)

rst.FindFirst "[Hours_ID] = " & Me!txtID

 

[boblarson]

1. Get rid of the MoveFirst. You don't need it. You are already at the first record when you open the recordset. You only need that if you have iterated through and want to go back.

2. We just went through this with SMIG in another thread and I see you figured out that you needed dbOpenDynaset.