Updating record

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

I am reading a single record in my table and then want to update this
record. How would I do this?

vSql = "select * from tbImport"

Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)

Do While Not rs.EOF

If rs![MLength] < vLLen Then
xLen = "L"
End If

(Then update this record)

loop
 
Mark said:
Hello all,

I am reading a single record in my table and then want to update this
record. How would I do this?
Click to expand...

vSql = "select * from tbImport"

Set db = CurrentDb()
Set rs = db.OpenRecordset(vSql)

Do While Not rs.EOF
rs.Edit
If rs![MLength] < vLLen Then xLen = "L"
rs.Update
Loop

However; an Update query would do this in one operation and would be a lot more
efficient. Looping through a Recordset to do updates is (almost) never the best
way to do it.
 
Do While Not rs.EOF
rs.Edit
If rs![MLength] < vLLen Then xLen = "L"
rs.Update
Loop
Click to expand...

I think you need a rs.MoveNext in there too....
Oh, and I would put the If...End If outside the Edit...Update

But I agree the whole thing would be better with


jetSQL = "UPDATE tblImport " & _
"SET xLen = ""L"" " & _
"WHERE MLength > " & vLLen

db.Execute jetSQL, dbFailOnError


B Wishes


Tim F
 
1 - The SQL statement will return all of the records in tbImport. So if
you're just needing to update a single record then you'll need to either
add a WHERE statement to select the specific record OR implement on
If...Then statement in the loop to determine when you've found the
record you need to modify

2 - Change the loop to

While Not rs.eof
If [Condition] Then
rs.edit
rs("fieldName") = Somevalue
rs.update
end if
rs.movenext
Wend
 
Back
Top