[AccessD] Report Data Order Differs From Form or Query DataOrder

Hi Mark and Max

Then my clever function for counting and numbering rows - which I have posted a couple of times and revised recently - should interest you.
Note that you may choose _not_ to reset the counter if you wish to run the query more than once:


Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long
  
' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Or strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
  Else
    col.Add col.Count + 1, strKey
  End If
  
  RowCounter = col(strKey)
  
Exit_RowCounter:
  Exit Function
  
Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

/gustav


>>> markamatte at hotmail.com 18-03-2008 17:06 >>>

Max,

This isn't so much about solving your issue...just stating some highlights that may or may not help.  Sorting of data can be in many places.  Table, query, form and report can all have different sorts for the same data.  Is it possible 1 of the 4 is out of sync?  You can sort in a query...but display it sorted differently in the form...and have a report on the same exact query...but the report sorted differently.

For my postcard printing the print order is essential because I am printing duplex with customer info on both sides of the card...so I have to maintain the order at all cost.  I use an autonumber generator to insure the order is preserved.  I use an APPEND query to a temp table to preserve the order because a SELECT query display will cause the code to fire again as you scroll.  I just have to reset lngTableCounter  before I run it each time.  Then my form and report are sorted off this number

Good Luck,

Mark A. Matte

*******************
Option Compare Database
Option Explicit
Global lngTableCounter As Long

Function MyAutoCtr(prmAny)
'Trick is to pass a field from the input table(s) so that function called for each record
'otherwise Access thinks that the function will always return the same value and
'only calls it once, and every output record gets the same value
MyAutoCtr = lngTableCounter
lngTableCounter = lngTableCounter + 1
End Function
********************