Updating one field based on text string found in another

[jdashm]

I am trying to create VBA for the purpose of updating a category field based on whether a specific text string is found in another field. For example, if the Product Name field contains the word "seed" then update the Category field to "Seeds" else update Category field to "Misc". I also want it to loop through the current recordset. The code I have created runs through the recordset but doesn't seem to update the Category field correctly. It changes all records to "Seed" or all to "Misc" depending on how I have tweaked the code.

the code I have created so far looks like this...
Private Sub cmdAssignCat_Click()


Dim curdb As Database
Dim rsCat As Recordset
Dim SearchString As String
Set curdb = CurrentDb
Set rsCat = CurrentDb.OpenRecordset("tblProduct")
SearchString = Me.strProductName
Do Until rsCat.EOF
If InStr(1, SearchString, "seed") > 0 Then
' MsgBox "yes"
rsCat.Edit
rsCat!strProdCat.Value = "Seeds"
rsCat.Update
Else
' MsgBox "no"
rsCat.Edit
rsCat!strProdCat.Value = "Misc"
rsCat.Update
End If
rsCat.MoveNext
'
Loop

End Sub

[jwhite]

BACKUP your database, then run this in a new query, correcting field names as needed:

UPDATE tblProduct SET IIf(InStr([ProductName]),"seed")>0,"Seeds","Misc") As [Category]

It should do what you need...

[jdashm]

Thanks for the reply but I was curious as to why my code did not work. Do you have any insights that?

[jwhite]

In your code you are searching the same string variable for each record, not the ProductName in each record. Updated:

Private Sub cmdAssignCat_Click()
    Dim curdb As Database
    Dim rsCat As Recordset
   
    Set curdb = CurrentDb
    Set rsCat = curdb.OpenRecordset("tblProduct")
    Do Until rsCat.EOF
        'JHW: Changed search target from SearchString to rsCat!ProductName
        If InStr(1, rsCat!ProductName, "seed") > 0 Then
            ' MsgBox "yes"
            rsCat.Edit
            rsCat!strProdCat.Value = "Seeds"
            rsCat.Update
        Else
            ' MsgBox "no"
            rsCat.Edit
            rsCat!strProdCat.Value = "Misc"
            rsCat.Update
        End If
        rsCat.MoveNext
    Loop
    rsCat.Close
    curdb.Close
    Set rsCat = Nothing
    Set curdb = Nothing
End Sub

A SQL statement is better than using all the above code.

[ssanfu]

The line that is causing the problem is:

If InStr(1, SearchString, "seed") > 0 Then

The syntax for the InStr() function is InStr([start, ]string1, string2)
where
string1 - Required. String expression being searched.
string2 - Required. String expression sought.

If you enter "seed" in "SearchString" (string1) and you are searching for "seed" (string2), what do you thing the result will be?? Yep, the result will always be TRUE.
If you enter "stem" in "SearchString"(string1) and you are searching for "seed" (string2), what do you thing the result will be?? Yep, the result will always be TRUE.

So "SearchString" is what you are searching for (ie string2) and "rsCat!strProdCat" is what string is being searched (ie string1).
I rewrote your function

Private Sub cmdAssignCat_Click()
    Dim curdb As DAO.Database
    Dim rsCat As DAO.Recordset
    Dim SearchString As String
    Dim NewValue As String

    Set curdb = CurrentDb
    Set rsCat = curdb.OpenRecordset("tblProduct")

    SearchString = Me.strProductName

    Do Until rsCat.EOF
        If InStr(1, rsCat!strProdCat, SearchString) > 0 Then
            NewValue = "Seeds"
        Else
            NewValue = "Misc"
        End If

        rsCat.Edit
        rsCat!strProdCat = NewValue
        rsCat.Update

        rsCat.MoveNext
    Loop

    'clean up
    rsCat.Close
    Set rsCat = Nothing
    Set curdb = Nothing

End Sub

The BLUE is what I added/changed......

[jdashm]

Thanks that did it with one exception (sure it was a typo)... instead of referencing the field I want to update (strProdCat) as the second argument, I changed it to strProductName (the field I wanted to search in).
I also made my variable "Searchstring" = to "seed" (the value I wanted to look for.

Again many thanks for this response.