Wierd bound textbox/query interaction

[smaier]

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:

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!

[June7]

By "auto-numbered value" do you mean your calculated sequence number?
Why do you want this, what purpose does it serve on form?

I tested this. Form displays sequential numbers even when scrolling, sorting, filtering.

[smaier]

By "auto-numbered value" do you mean your calculated sequence number?
Why do you want this, what purpose does it serve on form?

I tested this. Form displays sequential numbers even when scrolling, sorting, filtering.

Hi, June7. Yes By "auto-numbered value" I mean the values in the query that the code/module and additions to the query should be generating.

The purpose for this is not for my direct benefit, it's for the benefit of people downstream of manufacturing who need to review inspection reports before closing the work order for a particular job. For any given work order/production run, there needs to be a certain number of in-process inspections performed by machinists and inspectors. The people who review these records know how many records there need to be, and with the method I was trying to implement it gives additional certainty that the code and queries are correct as they scroll through them (I already have a DCOUNT("*", queryname) that says what is should be. The table is dimensional data that is generated by measuring equipment. Since there are multiple machinists and inspectors using the same equipment to measure their differing parts, the table's records (one record contains all the recorded dimensions for a given part) for any part number aren't contiguous but have other parts' records in between them. This is why I can't just use the auto-numbered ID (also the Primary Key).

Anyway, it's not like the system will break down without this, it's more of a convenience for someone else down the line. I was just perplexed how in my case, the query's added field for row numbers works fine (they're in sequence and ascending) until I tried binding a textbox to it in the for scrolling through the query's dataset. It just seems like it should work like it seems it shoudl after running the query.

It's interesting it works for you. Maybe it has something to do with some other property in the db I'm using (it's a copy of the one currently being used). Maybe I'll try it with a fresh/new db and see if that narrows it down?


Thank you!