Faster method to update records in Recordset?

[kramerica]

I have a table that I am opening as a recordset.

I am looping through the recordset and on each record I am processing an 'Update Statement'. Code works great.. just slower than I expected.. Any ideas?

I am using Dcount to give me a total of the number of PC's that meet my criteria. Then I am using this subtotal field as a sort order when I do my summary reporting.

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblAppsXPC&quot

If rs.RecordCount = 0 Then
MsgBox "No Records in the Record Set"
GoTo endofprocedure
End If


With rs
.MoveLast
.MoveFirst
End With

DoCmd.SetWarnings False

While Not rs.EOF
currvalue = rs!ID
Me.Caption = rs!ID

SQLWHERE = "((PCNAME)= " & Chr$(34) & rs!PCNAME & Chr$(34) & " ) AND ((APPNAME)= " & Chr$(34) & rs!APPNAME & Chr$(34) & " )"
'MsgBox SQLWHERE

XCOUNT = DCount("[ID]", "tblAppsXPC", SQLWHERE)

strsql = "UPDATE tblAppsXPC SET GCount =" & XCOUNT & " WHERE (((ID)= " & rs!ID & " ) AND ((PCNAME)= " & Chr$(34) & rs!PCNAME & Chr$(34) & " ) AND ((APPNAME)= " & Chr$(34) & rs!APPNAME & Chr$(34) & " ));"

DoCmd.RunSQL (strsql)

rs.MoveNext
Wend

endofprocedure:


rs.Close

Kramerica

 

[BGrego]

Instead of calling the "runSQL strSQL" command, use the following code

rs.Edit
rs!GCount = XCOUNT
rs.Update