An Easy VBA Counter

This post is actually based on a question I received on my YouTube video:

The counter can be implemented a bit easier by using VBA.

Here’s how:

Sub AddCounter()

    Dim intCounter As Integer
    Dim strSQL As String
    Dim strSQLUpdate As String
    Dim rst As Recordset
    'counter is done based on the sort order
    'strSQL = "SELECT CustomerID, counter FROM Customers ORDER BY City DESC"
    strSQL = "SELECT CustomerID, counter FROM Customers ORDER BY CompanyName"
    Set rst = CurrentDb.OpenRecordset(strSQL)
    intCounter = 1
    Do Until rst.EOF
        'update the counter column as the counter variable increases:
        strSQLUpdate = "UPDATE Customers SET counter=" & intCounter & " WHERE CustomerID='" & rst("CustomerID") & "'"
        CurrentDb.Execute strSQLUpdate
        intCounter = intCounter + 1
    Set rst = Nothing
    'alert us when were are done
    MsgBox "Complete"
End Sub

That’s it. Questions?

Offsite Related Information:

“Go Fund Me” Page

($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course