Number lines in a subform

Running sum is only for reports.

There are a few different ways to do this, but overall the best approach is to place a field in the table for "Display Order", then when adding a record, in the BeforeInsert event set the field with:

 Me.DisplayOrder = Nz(DMax("DisplayOrder", "myTable", myKeyField = " & [myKeyFieldControl]), 0) + 1

Changing the field/control names above as appropriate.

Jim.

Jim, the record source is a query.   So the first record needs to be "1" no matter which records the user selects via a form that has a start date and an end date driving the sub-form.

As I said, there are a number of ways to do this, the best of which is to include a field in the underlying table.

The reason is that in a continuous subform, you need a control bound to a field for it to be different on every row.   So if the table doesn't already have the numbers and you want to do this on the fly, then you will need to build a temp table, filling in the #'s, then displaying the form.

The other way is to do it in a query with a sub query, but then you need some field that can be ordered.

Jim.

You can use my RowCounter function in the query:

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));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) 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.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String
  
  On Error GoTo Err_RowCounter
  
  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  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

Select allOpen in new window

It is very fast, and you can use the foreign key of the subform as the group key - see in-line comments for typical usage.

I'm getting a compile error.  Expected: List separator or )

Then you have a typo somewhere. Double-check.

I mean in Jim's suggestion.

What you're trying to do is only possible in Access Report or by use of Function.

Copy the following Function and save it in a Module.

Create a Column  in your query and name it whatever you like - say LineNumber.

Make sure the data source for the query has a unique field.

See attached on how to call the function in your query.

The Function:

Option Compare Database
Option Explicit

Dim varArray() As Variant, i As Long

Public Function QrySeq(ByVal fldvalue, ByVal fldName As String, ByVal QryName As String) As Long
'-------------------------------------------------------------------
'Purpose: Create Sequence Numbers in Query in a new Column
'Author : a.p.r. pillai
'Date : Dec. 2009
'All Rights Reserved by www.msaccesstips.com
'-------------------------------------------------------------------
'Parameter values
'-------------------------------------------------------------------
'1 : Column Value - must be unique Values from the Query
'2 : Column Name  - the Field Name from Unique Value Taken
'3 : Query Name   - Name of the Query this Function is Called from
'-------------------------------------------------------------------
'Limitations - Function must be called with a Unique Field Value
'            - as First Parameter
'            - Need to Save the Query after change before opening
'            - in normal View.
'-------------------------------------------------------------------
Dim K As Long
On Error GoTo QrySeq_Err
restart:
If i = 0 Or DCount("*", QryName) <> i Then
Dim J As Long, db As Database, rst As Recordset
    i = DCount("*", QryName)
ReDim varArray(1 To i, 1 To 3) As Variant
Set db = CurrentDb
Set rst = db.OpenRecordset(QryName, dbOpenDynaset)
For J = 1 To i
    varArray(J, 1) = rst.Fields(fldName).Value
    varArray(J, 2) = J
    varArray(J, 3) = fldName
    rst.MoveNext
Next
rst.Close
End If

If varArray(1, 3) & varArray(1, 1) <> (fldName & DLookup(fldName, QryName)) Then
    i = 0
    GoTo restart
End If

For K = 1 To i
If varArray(K, 1) = fldvalue Then
    QrySeq = varArray(K, 2)
    Exit Function
End If
Next

QrySeq_Exit:
Exit Function

QrySeq_Err:
MsgBox Err & " : " & Err.Description, , "QrySeqQ"
Resume QrySeq_Exit

End Function

Select allOpen in new window

AutoNumbering.png

Opeyemi:  I copy/pasted your code into a module.  Then in my query I added a column that looks like this:


But when I run the form I get this:


What am I doing wrong?

You didn't call the function well. Look at the screenshot I attached to my answer.

Call it like so..

LineNumber: QrySeq([SN],"SN","QueryName")

Where [SN] is a field in your table/query that serves as source for your query.

Please see the attached screenshot above.

I have this but got stuck in a terrible loop when I tried to run the report.

Well, I can't say what's causing the so called loop. It looks like you applied grouping/sum/expression to your query. Try and avoid that by using another Select Query then use the function in the new query NOT where you used Grouping.

That got rid of the loop.  But now with the new query I get all zeroes in the Line Number field on the sub-report.  Here is what I have now:

Well, my function works - and have done for 16 years(!) in numerous scenarios.

Gustav, I don't know how to add it into this SQL:

SELECT tblBuyerLeads.ID, tblBuyerLeads.Date, tblBuyerLeads.RREG_Agent, tblBuyerLeads.LeadSource, tblBuyerLeads.LeadName, tblBuyerLeads.Email, tblBuyerLeads.PhoneNumber, tblBuyerLeads.Price, tblBuyerLeads.StreetAddress, tblBuyerLeads.City, tblBuyerLeads.Zip, tblBuyerLeads.SpecialRequest, tblBuyerLeads.CallOrEmail
FROM tblBuyerLeads
WHERE (((tblBuyerLeads.Date) Between [Forms]![frmSelectDateRange]![txtStartDate] And [Forms]![frmSelectDateRange]![txtEndDate]) AND ((tblBuyerLeads.RREG_Agent) Is Not Null) AND ((tblBuyerLeads.Type) Not Like "junk")) OR (((tblBuyerLeads.Date) Between [Forms]![frmSelectDateRange]![txtStartDate] And [Forms]![frmSelectDateRange]![txtEndDate]) AND ((tblBuyerLeads.Type)="renter"));

Just follow the documentation:

' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));

Select allOpen in new window

As a start, use your current query as source. I don't know what your foreign key for the subform is, but if it is RReg_Agent and is numeric, then:

' Usage (with group key):
SELECT RowCounter(CStr([ID]),False,CStr[RREG_Agent])) AS RowID, *
FROM YourQuery
WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));

Select allOpen in new window

If it is Text, then remove CStr():

' Usage (with group key):
SELECT RowCounter(CStr([ID]),False,[RREG_Agent]) AS RowID, *
FROM YourQuery
WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));

Select allOpen in new window

Now, either use this new query as source for your subform, or modify your original query to include the RowID field and the extra WHERE clause.

Well there is a way but without some data i am not sure if it works...
In the txtLineNumber put this as your ControlSource

=DCount("YOUR_PRIMARY_KEY";"THE_RECORD_SOURCE_OF_YOUR_FORM";"YOUR_PRIMARY_KEY <=" & [YOUR_PRIMARY_KEY])

Select allOpen in new window

I have tested in both continuous and datasheet and it seems working (I don't work with datasheet so...my comments go mainly to continuous)

@John

 That will certainly work (it's no different than running a sub-select in the query, which is how you do ranking in a query - it just fires more often), but it does have a loop hole; an Auto number can loop around back to negative numbers and then increase.   It's rather a moot point though as I've never seen that happen yet.

 But I have seen auto number seed values all of a sudden jump due to db corruption, so it's not out of the realm of possibility.  If that does happen, then a ranking check based on it will fail.

@All,

  Gustav's rowcounter is probably the most flexible, but still it all boils down to:

a. Assigning the value to a field in the table.

or

b. Using a sub-select to rank the rows in a query.

 With "a" being the better choice and doing that on the main table when the record is created.   Sub-selects as we all know are slow performance wise.  Probably not too bad on small sets of records, but if the sub form is displaying hundreds of rows, I probably would not want to live with it.

 and of course there are variations on each of those (using a temp table and then assigning a number, etc), but fundamentally it boils down to one of the two.

Maybe a few more details on how this is going to be used.  ie. does the number need to remain assigned once it is, or can the rows be re-numbered each time they are displayed, how they should be ordered, etc would help with what direction this needs to go.

and apologies if you've covered this.  I haven't read back through the entire thread.   Got really busy yesterday and will be most of the day today as well.

Jim.

@Jim...this is something i used in the past (rather rarely but it did the job)..for this case i have made a simple test with around 50 records and it did work
Another  solution probably  would be to add an OrderNo field in the underlying table...and during  the opening  of the form i would count the records populate it  and finally display the records...

@John,

<<If that does happen, then a ranking check based on it will fail.>>

  Just to be clear, I meant basing the check on an autonumber PK.  Certainly if you use this with another field that you know to be ascending, then there is no issue.  Like I said, even with an AN PK, I haven't seen one fail yet, but the spec for AN's do allow them to wrap around to negative values and then start ascending to zero.

Jim.

I did test it against PK autonumber...
check my screenshot...Red Numbers the Dcount trick the RED inside Yellow the actual PK

<<I did test it against PK autonumber...>>

  Your missing the point a bit.  Yes it does work unless the AN wraps around to a negative number.  

  AN's increase in value by one until they hit the max value of 2,147,483,647, at which point they wrap around to the max negative value of  -2,147,483,648 and start counting back towards zero.

 I have never seen an AN wrap in normal circumstances (someone creating over 2 billion records), but I have seen a DB corrupt and jump to a value half way there.

 Your probably 99.999% safe in using something like this, but my main point was that I'd use something other than an AN PK if you can as I've learned never to say never..

Jim.

Probably other methods will blow just before mine...:)

I can't get any of this to work.  I'll try to get a demo database put together and send tomorrow.