Findfirst Question

[Barth Wilsey]

I am not able to edit a specific record in my table tblAnalgesia with the
code below. Instead of going to the record requested, the code below always
changes the field in the first record. The key field in the table is a
number (long integer) and I wonder if some sort of conversion has to be
done. But because the findfirst method requires a string, I am not sure how
to do this

thanks in advance, Barth

Private Function AddWorst(str As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAnalgesia", dbOpenDynaset)

With rs
.FindFirst "Encounter=" & Me.txtWorstEncounter.Value
.Edit
.Fields("WorstPainPastWeek") = str
.Update
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

 

[Marshall Barton]

I am not able to edit a specific record in my table tblAnalgesia with the
code below. Instead of going to the record requested, the code below always
changes the field in the first record. The key field in the table is a
number (long integer) and I wonder if some sort of conversion has to be
done. But because the findfirst method requires a string, I am not sure how
to do this

thanks in advance, Barth

Private Function AddWorst(str As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAnalgesia", dbOpenDynaset)

With rs
.FindFirst "Encounter=" & Me.txtWorstEncounter.Value
.Edit
.Fields("WorstPainPastWeek") = str
.Update
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

The first thing I see is that you never check if the
FindFirst found anything.

.FindFirst "Encounter=" & . . .
If Not NoMatch Then
.Edit
.Fields("WorstPainPastWeek") = str
.Update
Else
MagBox "Not Found: " & Me.txtWorstEncounter
End If



Note a couple of other things that probably don't really
mean anything. Str is the name of a built-in function, it's
unlikely, but Access may be confused and refer to that
instead of your variable.

The other is that you should not try to close the current
db. Access is supposed to ignore that situation, but even
if it amounts to nothing, why add a useless and potentially
confusing line of code.

 

[Dirk Goldgar]

I am not able to edit a specific record in my table tblAnalgesia with
the code below. Instead of going to the record requested, the code
below always changes the field in the first record. The key field in
the table is a number (long integer) and I wonder if some sort of
conversion has to be done. But because the findfirst method requires
a string, I am not sure how to do this

thanks in advance, Barth

Private Function AddWorst(str As String)
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("tblAnalgesia", dbOpenDynaset)

With rs
.FindFirst "Encounter=" & Me.txtWorstEncounter.Value
.Edit
.Fields("WorstPainPastWeek") = str
.Update
End With

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Function

There's nothing obvious wrong with your code, but you don't check to see
if the record you searched for was actually found. My guess is that,
for some as yet unknown reason, you aren't finding it. Revise your code
like this:

With rs
.FindFirst "Encounter=" & Me.txtWorstEncounter.Value
If .NoMatch Then
MsgBox "Searched for Encounter=" & _
Me.txtWorstEncounter.Value & _
"; didn't find it."
Else
.Edit
.Fields("WorstPainPastWeek") = str
.Update
End If
End With

At least then, if it fails, you'll know.