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.
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?