An Easy VBA Counter

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

https://www.youtube.com/watch?v=l5Aj8dPH8KY&lc=z234unjqmpymudvjp04t1aokgbwgqi4nrfxkozk3u00rrk0h00410

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)
    
    rst.MoveFirst
    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
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
    'alert us when were are done
    MsgBox "Complete"
End Sub

That’s it. Questions?