Solved

How add a field with sequential numbers to a form

Posted on 2016-11-28
7
36 Views
Last Modified: 2016-12-01
I have a form that is a continuous form.  I want to add a field to the form that just shows a number that is not bound to the record source.

The first record would show "1", the second record would show "2", etc.  Is there a way to do this?
0
Comment
Question by:SteveL13
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904423
What is the point of the sequence number?  If you sort the recordset, would the sequence number now refer to a different row?  You can do something using DCount("*", "PK < " & PK) +1 which will count the number of rows with primary key values (autonumber is assumed here0 less than that of the current record.  If the data is grouped some way, the where argument of the DCount() would need to include the grouping field to count over the group.  This count would work ONLY if the rows were sorted in PK order.

This is quite easy to do with a report because the recordset isn't "live" and can't be sorted without rerunning the entire report.
0
 

Author Comment

by:SteveL13
ID: 41904444
The purpose is just to show the records as 1, 2, 3, etc.  No relationship to the PK.  So DCount("*", "PK < " & PK) +1 won't work.  The PK numbers are not sequential.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41904479
Unless you can utilize the actual sort sequence AND it is UNIQUE, then you won't be able to assign a sequential number.  How are the rows sorted?  Try concatenating the sort fields and using that to determine the count of earlier records.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 19
ID: 41904801
the key is "that just shows a number that is not bound to the record source."

CAN you modify the structure? If so you could add:
TempRecNum, Long Integer, Default Value =Null

This temporary field could be updated as necessary.

While there is a way to use code to add a number to show as a "record number", it could recalculate and throw values off as the user moves if there is no way to bind it to values in the table.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 41904839
You can expand the query that you use as source with a field holding a rownumber obtained from this function:
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

Open in new window

Typical usage for a select query is shown in the in-line notes.
Note please, that this function is very fast - no DCount or the like - thus impact on the display will be minimal during load, and none during browsing.

/gustav
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41904843
The solution suggested by Gus uses a function that makes use of the concatenated sortKey I mentioned a few posts ago.  So, you can use DCount() with the concatenated sortkey or you can implement Gus' function.

Personally, I generally stick to built-in SQL functions and the few VBA functions that the ODBC drivers can translate directly to T-SQL since my databases are almost always SQL Server and so I cannot use VBA or UDF functions.  That allows me to upsize applications at the drop of a hat rather than having to take days to ferret out all the non-portable code.
0
 
LVL 19
ID: 41904861
Gustav often has great solutions -- this, however, was already suggested by Pat.  But he gave code with usage examples  ...

bottom line is this:
if there is a sort key that is unique, code can be used that ties the sort key to values in the table.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now