Solved Search and Change values in a table

[Garcimat]

Hi guys
I want to search the table and when some conditions are achieved I want to change the value in one of the fields, I am trying the code below but it is not working...
Thanks in advance.

Private Sub ModifyStatus()
Dim i As Integer
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Data")


For i = 0 To rs.RecordCount - 1
If rs.Fields("SWO_Number") = gvar_SWONumber Then
If rs.Fields("PTF_Number") = gvar_PtfNumber Then
If rs.Fields("Record_Status") = "ACTIVE" Then
rs.Edit
rs.Fields("Record_Status") = "MODIFIED"
rs.Update
End If
End If
End If
Next i

Set rs = Nothing
Set db = Nothing
rs.Close
db.Close

End Sub

 

[jdraw]

Have you considered using a query?
You might try using a Select query to identify the records where
SWO_Number = gvar_SWONumber and
PTF_Number = gvar_PtfNumber and
Record_Status = "ACTIVE"

Then, if you are satisfied with the records selected,
convert the query from SELECT to UPDATE as appropriate.

UPDATE queries can be quite unforgiving, I suggest double check things and perhaps do a Backup before anything.

 

[Garcimat]

Have you considered using a query?
You might try using a Select query to identify the records where
SWO_Number = gvar_SWONumber and
PTF_Number = gvar_PtfNumber and
Record_Status = "ACTIVE"

Then, if you are satisfied with the records selected,
convert the query from SELECT to UPDATE as appropriate.

UPDATE queries can be quite unforgiving, I suggest double check things and perhaps do a Backup before anything.

I am really green with SQL, I can never get it to work, I will give it a try

 

[theDBguy]

Hi. When you say the code is not working, what does it mean? What is happening? If you're getting error messages, what do they say?

 

[Garcimat]

It does not changing the fields….

 

[Garcimat]

I got it

Private Sub Command877_Click()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Data")

Do Until rs.EOF
    If rs![SWO_Number] = gvar_SWONumber Then ' gvar_SWONumber Global Variable
            If rs![PTF_Number] = gvar_PtfNumber Then 'gvar_PtfNumber Global Variable
                        If rs![Record_Status] = "ACTIVE" Then
                            rs.Edit
                            rs![Record_Status] = "MODIFIED"
                            rs.Update
                        End If
                End If
    End If
rs.MoveNext
Loop
End Sub

 

[The_Doc_Man]

OK, just for your information, this is what the SQL might have looked like:

strSQL = "UPDATE tbl_Data SET Record_Status = 'Modified'  " & _
"WHERE SWO_Number = " & CStr( gvar_SWONumber ) & " AND " & _
    "PTF_Number = " & CStr( gvar_PtfNumber ) & " AND " & _
    "Record_Status = 'ACTIVE' ;"
currentDB.Execute strSQL, dbFailOnError

I broke it up into parts so you could see how it related to your loop. This would have done the same thing as the VBA loop you wrote but a LOT faster. I'm not saying for you to change anything, but you mentioned you were not confident with your SQL. Note also that this SQL presumes that the SWO_Number and PTF_Number are actually numbers and not strings.

 

[arnelgp]

since we don't know your Variable/Field Type:

Private Sub Command877_Click()
    Dim SQL As String
    SQL = "Update tbl_Data Set Status = [p1] Where [Status] = [p2] And [SWO_Number] = [p3] And [PTF_Number] = [p4];"
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters("p1") = "MODIFIED"
        .Parameters("p2") = "ACTIVE"
        .Parameters("p3") = gvar_SWONumber
        .Parameters("p4") = gvar_PtfNumber
        .Execute
    End With
End Sub