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?






