Thanks all for your input. Not knowing VBA and needing to move forward, I found a solution that worked for what I needed to accomplish. Just in case someone else is struggling at my level of VBA skills I'm posting my end solution. Attached is a list of Cities, for convenience, in notepad to create a Table: Cities. Below is the Module: RowID and Query: qry_AddRowNumber. You should be able to cut-n-paste all and open query.
Module: RowID
Code:
Private lngRowNumber As Long
Private colPrimaryKeys As VBA.Collection
Public Function ResetRowNumber() As Boolean
Set colPrimaryKeys = New VBA.Collection
lngRowNumber = 0
ResetRowNumber = True
End Function
Public Function RowNumber(UniqueKeyVariant As Variant) As Long
Dim lngTemp As Long
On Error Resume Next
lngTemp = colPrimaryKeys(CStr(UniqueKeyVariant))
If Err.Number Then
lngRowNumber = lngRowNumber + 1
colPrimaryKeys.Add lngRowNumber, CStr(UniqueKeyVariant)
lngTemp = lngRowNumber
End If
RowNumber = lngTemp
End Function
Query: qry_AddRowNumber
Code:
SELECT Cities.City, RowNumber([Cities]![City]) AS RowID
FROM Cities
WHERE (((ResetRowNumber())<>False))
ORDER BY Cities.City;
Thanks!