Solved Search and Change values in a table (1 Viewer)

[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

 

[Pat Hartman]

@Garcimat I edited your code in #6 to be formatted so it is easier to read.

It is time to learn how to use queries. Action queries are always faster than VBA loops. If you have a few hundred records, it won't matter much but once you get to thousands, you will notice the sluggishness. I'm not saying to change your code but you might want to copy Doc's example and paste it as comments in your current code so you have a reference when you need to do this again.