Update (edit) field from VBA based on a condition

[Isis]

I have a simple table called Table1

The table has 5 fields I want to loop through it and change the value of
one of the fields based on the value of another field - so for instance if
Table1.Num1 = 2
Table1.Num2 = 222

something like that - what do I use VBA wise to do that.

Thanks

 

[Wayne-I-M]

UPDATE Table1 SET Table1.Num2 = 222
WHERE (((Table1.Num1)=2));

 

[Isis]

UPDATE Table1 SET Table1.Num2 = 222
WHERE (((Table1.Num1)=2));

Wayne,

Thanks for the reply - I must be doing something wrong ! I have got the
code you gave me on a command button like this;

Private Sub Command11_Click()

UPDATE Table1 SET Table1.Num2 = 222 WHERE (((Table1.Num1) = 2));

End Sub

I get a Compile Error - expected end of statement - I was not sure of the
';' so I tried it with and without - I get the same error.

What am I likely doing wrong ?

Thanks

 

[John Spencer]

What Wayne sent you was not VBA code. It was a query.

Queries are much better for changing data. In query design view

Open a new query
Select your table
Add the Num2 to the grid
SELECT Query: UPdate from the menu
enter 222 in the UPDATE To box
Add Num1 to the grid
Leave Update To blank
Set the criteria to =2 under Num1

The code to do this using VBA would be much more complex.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Isis]

What Wayne sent you was not VBA code. It was a query.

Queries are much better for changing data. In query design view

Open a new query
Select your table
Add the Num2 to the grid
SELECT Query: UPdate from the menu
enter 222 in the UPDATE To box
Add Num1 to the grid
Leave Update To blank
Set the criteria to =2 under Num1

The code to do this using VBA would be much more complex.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the reply John - I actually want to do it from code - any
pointers for that please ?

Thanks

 

[John Spencer]

One way to do this would be to use something like the following code. All the
following code is UNTESTED and may contain errors.

Public Sub fUpdateTable()
Dim dbAny as DAO.Database
Dim strSQL as String

On Error GoTo Proc_Error
strSQL = "UPDATE Table1 " & _
" SET Table1.Num2 = 222" & _
" WHERE Table1.Num1=2"

Set dbAny = Currentdb()
dbAny.Execute strSQL,dbFailOnError
'Optional lines follow
MsgBox "Updated " & dbAny.RecordsAffected & " records"

Exit Sub

Proc_Error:
MsgBox Err.Number & " : " & Err.Description,,"fUpdateTable Error"
End Sub

If you wanted to step through the records one at a time and edit and save each
record, you would need to build a recordset and do that. That code would look
something like the following.

Public Sub fUpdateTable()
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Table1.Num1, Table1.Num2" & _
" FROM Table1 " & _
" WHERE Table1.Num1=2"

Set dbAny = CurrentDb()
Set rs = dbAny.OpenRecordset(strSQL)

If rs.RecordCount > 0 Then
With rs
While Not .EOF
.Edit
.Fields("Num1") = 22
.Update
.MoveNext
Wend
End With
End If
End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Douglas J. Steele]

Private Sub Command11_Click()

Dim strSQL As String

strSQL = "UPDATE Table1 SET Table1.Num2 = 222 WHERE Table1.Num1 = 2"

CurrentDb.Execute strSQL, dbFailOnError

End Sub