Update Records (Access 2003)

Topic

New Reply

I have a form that has a command button; when I click on the button I want the table to be field in the table to be updated. Currently when I click the button only one record get updated on the table. How do I get the whole table to be looped?

Reply | Quote

Replies

Sound like you need to get the button to run an update wuery to me.

BUT., I reckon you need to explain this in a bit more detail.

Do you want a field to be updated to the same value throughout the entire table.
Where does the new value come from.

Is the table the recordsource of the form?

etc

Reply | Quote

The following code is on the ‘On Click’ Event

If [ReportingUnit] > “009” And [ReportingUnit] “060” And [ReportingUnit] “080” And [ReportingUnit] “083” And [ReportingUnit] “090” And [ReportingUnit] “500” And [ReportingUnit] “629” And [ReportingUnit] < "712" Then
[Org] = "NORT"
[Office] = "NORTHERN"
Else
[Org] = "UNDF"
[Office] = "OTHERS"
End If

Reply | Quote

Not sure if this is what you are after, but something like this ought to work assuming you want to run this
against EVERY record in the forms recordset

Dim rst as DAO.recordset

set rst=Me.RecordSetClone
If not rst.RecordCount=0 then
rst.Movefirst
Do Until rst.EOF
If rst(“ReportingUnit”) > “009” And rst(“ReportingUnit”) “060” And rst(“ReportingUnit”) “080” And rst(“ReportingUnit”) “083” And rst(“ReportingUnit”) “090” And rst(“ReportingUnit”) “500” And rst(“ReportingUnit”) “629” And rst(“ReportingUnit”) < "712" Then
rst("Org") = "NORT"
rst("Office") = "NORTHERN"
Else
rst("Org") = "UNDF"
rst("Office") = "OTHERS"
End If
rst..MoveNext
Loop
Endif
rst.close
set rst=Nothing

Obvously you will need to check it out against you data

Reply | Quote

Got a error;
Update or CancelUpdate without AddNew or Edit. (Error 3020)
On a Microsoft Jet database, you called the Update or CancelUpdate method but did not use the AddNew or Edit method before writing data to a record.
On an ODBCDirect database, this error occurs when you attempt to write data to a record without first calling AddNew or Edit.

Reply | Quote

OOPS Sorry, I forgot a couple of lines

Just After the Start of the Loop you need

Do Until rst.EOF

rst.Edit

If rst(“ReportingUnit”) > “009” And rst(“ReportingUnit”) < "045" Then

Then at the other end of the loop

Just before the rst.MoveNext line you need

End If

rst.Update

rst..MoveNext
Loop

Apologies, I just forgot to put them in.

Reply | Quote