Hello, all. I hope the new year has found you doing well!
The nature of my question is an odd interaction between a bound textbox and its query, but the reason I have posted it in teh Programming forum is it's programmatically driven.
What I was looking to do was have a query's output data set have its own field of numbers starting from 1 on up to however many records were returned in the query. I cannot just use the ID/Autonumber field from the table as the records in the query are never in order and/or have random missing numbers due to different records being in between the ones being returned by the query.
So, I stumbled across this YouTube video showing a solution (3m 37sec): https://www.youtube.com/watch?v=HWbpzETe-M0
The steps include creating a module with the below code:
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
, then add a field to the query in question that contains: NO:RowNumber([ID])
And lastly, add the following to the end of the WHERE SQL statement for the same query: AND ResetRowNumber() <> False
This actually works just as I was hoping (almost). When the query is run the newly added field has ascending numbers starting at 1 on up to however many records there are. What has me baffled is when I have a textbox bound to that query field and scroll through the records in the bound form it is in, the textbox's number does not reflect the auto-numbered value for that record in the query itself. They are whole numbers but are random. Usually between 1 and 3. Sometimes up to 5. And if I scroll back to a previously viewed record the value may or may not be the same (all other records' values are what one would expect and are consistent).
I will admit I am somewhat out of my depth here with the code. It's more that the bound textbox seems like it should be rendering one result but instead shows something seemingly illogical.
Any ideas?
Thank you!