How to insert text into a new field based on the positioning of a string?

[shawnnnnnnn]

Hi all,

I am required to write a code to check the positioning of a text in a string.

E.g.

"~[Admin]~xxxxxxxxx~ACK~" = Acknowledged
"~xxxxxxxxx~[Admin]~ACK~" = Not Acknowledged
"~xxxxxxxxx~ACK~[Admin]~" = Not Acknowledged

As you can see, as long as the string starts with "~[Admin]~", it is counted as acknowledged. If the "~[Admin]~" appears anywhere else except the start, it is counted as not acknowledged.

I want to make it such that when the code runs, the second column of the table states whether the event was acknowledged or not. I've attached screenshots of what it should look like.

Before:

After:

My current code is like super buggy and does not even come close to getting the job done. It skips the first 3 records and only starts inserting at the fourth.

Private Sub Command2_Click()
    Dim IsOK As Boolean
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstInsert As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Table5")
    Set rstInsert = dbs.OpenRecordset("Table5")
 
    If Not rst.EOF Then
        Do
            rst.AddNew
            If InStr(rst![Field1], "~[Admin]~") = 1 Then
                IsOK = True
                rstInsert![Field2] = "yes"
            Else
                IsOK = False
                rstInsert![Field2] = "no"
                rstInsert.Update
            End If
            rst.MoveNext
        Loop Until rst.EOF
    End If
 
End Sub

[shawnnnnnnn]

Ok, new plan. I'm thinking of extracting both the string and the "yes" or "no" into another table.

Private Sub Command8_Click()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim rstInsert As DAO.Recordset
 
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Table2", dbOpenDynaset)
    Set rstInsert = dbs.OpenRecordset("Acknowledgement", dbOpenDynaset)
 
    If Not rst.EOF Then
        Do
            rstInsert.AddNew
            If InStr(rst![Field1], "~[ admin]~") = 1 Then
                rstInsert![Field1] = rst![Field3]
                rstInsert![Field2] = "Yes"
            Else
                rstInsert![Field1] = rst![Field3]
                rstInsert![Field2] = "No"
            End If
            rstInsert.Update
            rst.MoveNext
        Loop Until rst.EOF
    End If
 
    rst.Close
    Set rst = Nothing
    rstInsert.Close
    Set rstInsert = Nothing
 
End Sub

This code managed to do it, but all the acknowledgements are marked as "no" even though some are supposed to be "yes".

PS, "~[ admin]~" is for my actual data. "~[Admin]~" is for example purposes

[shawnnnnnnn]

Ok, never mind! I found my problem! I forgot to change the name of the field in the If statement

[Luuk]

Would an update query not be simpler?

UPDATE Table5 
SET Table5.[Field2] = IIF(InStr(rst![Field1], "~[ admin]~") = 1, "yes", "no")