Update Table Issue - help please

[FordTX]

Hi,

For some reason the criteria does not seem to be hit. When this update
it will update records which should not match the findfirst and findnex
criteria. The fields in the criteria are text fields.

Thanks,


Dim db As DAO.Database
Dim rs As DAO.Recordset

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

rs.MoveFirst
rs.FindFirst "[REQNumber] = '" & Me!ReqNumberREQ.Value & "'"
rs.Edit
rs!PONumber = Me!PONumberREQ
rs.Update
Do While Not rs.EOF
rs.FindNext "[REQNumber] = '" & Me!ReqNumberREQ.Value & "'"
rs.Edit
rs!PONumber = Me!PONumberREQ
rs.Update
rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothin

 

[Van T. Dinh]

You should check in case the FindFirst doesn't find the required value.

Personally, I would create an SQL String so that all rows returned match
your criteria and then use the Do loop to update all rows like:

****Untested****
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

strSQL = "SELECT REQNumber, PONumber " & _
" FROM BudgetTracking " & _
" WHERE REQNumber = '" & Me.ReqNumberREQ.Value & "'"

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordsetstrSQL, dbOpenDynaset)

With rs
If Not .EOF Then
.MoveFirst
Do
.Edit
.Fields("PONumber") = Me!PONumberREQ
.Update
.MoveNext
Loop Until .EOF
End If
End With

rs.Close
Set rs = Nothing
********

 

[FordTX]

Thank for the help this makes sense now