Records not updating correctly when looping through recordset

[dickohead]

Hi guys,

I am trying to do this:

If the ResultLetter field is No, then append the Memo field and change ResultLetter to Yes, otherwise go to the next record in the table.

But the code below is doing this:
If the ResultLetter field is No, then append the Memo field and don't worry about changing ResultLetter to Yes, then ignore all following records, because I'm lazy, useless and had a massive weekend - I'll work when I want to.

#Note - Access has no sense of humour, the added comedy was to stop me from tearing my hair out.

Private Sub TickAllButton_Click()
On Error GoTo Err_ClearAllButton_Click

Dim dbs As Database
Dim rst As Recordset
Dim Counter As Integer
Dim objNetwork

Set objNetwork = CreateObject("WScript.Network")
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * FROM Practitioner", dbOpenDynaset)

Counter = rst.RecordCount

While Counter > 0
    
    With rst
        If .Fields("ResultLetter") = No Then
        .Edit
        .Fields("Memo") = .Fields("Memo").Value & vbCrLf & "Result letter sent on " & Now() & " by " & objNetwork.UserName
        .Fields("ResultLetter") = Yes
        .Update
        End If
    End With
    
    rst.MoveNext

    Counter = Counter - 1
Wend

rst.Close
dbs.Close

    'Dim stDocName As String

    'stDocName = "TickAllRecords"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_ClearAllButton_Click:
    Exit Sub

Err_ClearAllButton_Click:
    MsgBox Err.Description
    Resume Exit_ClearAllButton_Click
    
End Sub

Any clues as to what I muffed up?

 

[HiTechCoach]

Using a recordset, I would so something like this:

Private Sub TickAllButton_Click()
On Error GoTo Err_ClearAllButton_Click

Dim rst As Recordset
Dim objNetwork

Set objNetwork = CreateObject("WScript.Network")

' only select records that that need to be modified
Set rst = CurrentDb.OpenRecordset("Select [Memo], ResultLetter FROM Practitioner Where ResultLetter = No", dbOpenDynaset)

 If rst.RecordCount > 0 Then
       Do
            rst.Edit
            rst![Memo] = rst![Memo].Value & vbCrLf & "Result letter sent on " & Now() & " by " & objNetwork.UserName
            rst![ResultLetter] = Yes
            rst.Update
        
            rst.MoveNext
    
       Loop Until rst.EOF
 End If

rst.Close

Set rst = Nothing

Set objNetwork = Nothing


    'Dim stDocName As String

    'stDocName = "TickAllRecords"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_ClearAllButton_Click:
    Exit Sub

Err_ClearAllButton_Click:
    MsgBox Err.Description
    Resume Exit_ClearAllButton_Click
    
End Sub

 

[HiTechCoach]

If it were me, I would use an update query. Something like:

Private Sub TickAllButton_Click()
On Error GoTo Err_ClearAllButton_Click

Dim objNetwork
Dim strSQL As String

Set objNetwork = CreateObject("WScript.Network")

strSQL = "UPDATE Practitioner SET Practitioner.ResultLetter = True, Practitioner.[Memo] = Practitioner.[Memo] & Chr(13) & Chr(10) &  """Result letter sent on " & Now() & " by '"  & objNetwork.UserName & "'""" 
strSQL = strSQL & " WHERE (((Practitioner.ResultLetter)=False));"

CurrentDb.Execute strSQL, dbFailOnError

Set objNetwork = Nothing

Exit_ClearAllButton_Click:
    Exit Sub

Err_ClearAllButton_Click:
    MsgBox Err.Description
    Resume Exit_ClearAllButton_Click
    
End Sub

 

[Atomic Shrimp]

I agree with HiTechCoach - an update query is simpler and the query engine handles posting the changes.

Also... 'Memo' is a reserved word - using it as a field name might cause problems somewhere...

 

[dickohead]

Hi guys,

Thanks for the wonderful replies!

I am now using an update query, but I am getting this error:

"Too few parameters. Expected 1."

I've searched for quite a while to find a cause or fix, and apparently it's due to incorrectly named fields (typos?).
Is there a way I can test each part of the SQL statement to see exactly where the error is kicking in? I placed message boxes in as shown below, and number 4 does not display - which makes sense as dbFailOnError would kick in, but it doesn't help me debug the SQL statement...

Private Sub TickAllButton_Click()
On Error GoTo Err_ClearAllButton_Click

Dim objNetwork
Dim strSQL As String
Set objNetwork = CreateObject("WScript.Network")

'MsgBox ("1")

strSQL = "UPDATE Practitioner SET Practitioner.ResultLetter = True, Practitioner.PracMemo = Practitioner.PracMemo & Chr(13) & Chr(10) & 'Result letter sent on ' & Now() & ' by ' & objNetwork.UserName & '.'"
'MsgBox ("2")
strSQL = strSQL & " WHERE (((Practitioner.ResultLetter)=False));"
'MsgBox ("3")
CurrentDb.Execute strSQL, dbFailOnError
'MsgBox ("4")
Set objNetwork = Nothing

Exit_ClearAllButton_Click:
    Exit Sub

Err_ClearAllButton_Click:
    MsgBox Err.Description
    Resume Exit_ClearAllButton_Click
    
End Sub

 

[jal]

You'll tend to get fewer syntax errors if you use parameters.

Dim qDef as Dao.QueryDef
Set qdef = CurrentDb.CreateQueryDef("")
qdef.Sql = "UPDATE Practitioner SET Practitioner.ResultLetter = True, Practitioner.PracMemo = Practitioner.PracMemo & @Practitioner.PracMemo"
qDef.Parameters("@Practitioner.PracMemo").Value = "Result letter sent on" & Now() & " by " & objNetwork.UserName
qDef.Execute