Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

Count x of x on a continuous form

I have a continuous form that has a text field named txtCountOfRecords.  The 1st record's text field would indicate "1 of 25" (if there are 25 total records on the form.  The 2nd record's text box would indicate "2 of 25", etc.

I realize that the field won't know the total number of records added to the form until the form has been completed so it would have to use the total count of records at that point in time as the user enters data.

Can this be done?
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Initially I was going to say no, but after thinking about it for a while, I came up with the idea in the attached file. In my limited testing, it did work both for main and sub form, and even works if the user apply's a filter, or changes the sort order.
RecordOfRecordCount.zip
Avatar of SteveL13

ASKER

Hmmm.  On my test sub-form I have 4 records.  All 4 text fields say "1 of 4"

I'm sure I'm missing something.
Well it is working on my end, and since you didn't share how you implemented it, I can't really tell you could be the issue.
Here is what I did:

I added this code to a new module:

Option Compare Database
Option Explicit

Public Function RecordOfRecords(F As Form, IDFieldName As String, ID As Variant) As String
   If IsNull(ID) Then Exit Function
   With F.RecordsetClone
      .MoveLast
      .FindFirst IDFieldName & "=" & ID
      RecordOfRecords = .AbsolutePosition + 1 & " of " & .RecordCount
   End With
End Function


Public Sub PopulateTables()
   Dim rsMain As DAO.Recordset
   Dim rsSub As DAO.Recordset
   
   Set rsMain = CurrentDb.OpenRecordset("tblMain", dbOpenDynaset)
   Set rsSub = CurrentDb.OpenRecordset("tblSub", dbOpenDynaset)
   Dim lMain As Long
   Dim lSub As Long
   For lMain = 1 To 10
      rsMain.AddNew
         rsMain!mainText = Chr(64 + lMain) + Chr(64 + lMain) + Chr(64 + lMain)
      rsMain.Update
      For lSub = 1 To CInt(Rnd() * 1000)
         rsSub.AddNew
            rsSub!MainID = lMain
            rsSub!subText = Chr(64 + CInt(Rnd() * 28))
         rsSub.Update
      Next
   Next
End Sub

Open in new window


And I added this as the control source for the field on my sub-form:

=RecordOfRecords([Form],"InventoryRecID",[InventoryRecID])

Hopefully you can tell me what I missed.
And to just to confirm, InventoryRecID is a unique ID? It is not the foreign Key to the main table/form?

Which version of Access are you using? I created and tested the code in 2010 32bit under windows 7.

Is your table a local Access table,  linked access table, linked SQL server table, or something else?
Yes, InventoryRecID  is the primary key field main form record source.  It is also a field in the sub-forms record source so the subform can be linked to the main form;

Access 2013.  Windows 10.  64 bit.

The database is not yet split so the tables are all local.
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I just noticed a quirk.  As I add a new record in the sub-form, the "counter" indicates "1 of x" until I tab off or complete the record entry.  Then It does show the correct data.

??
Try this instead:
Public Function RecordOfRecords(F As Form, IDFieldName As String, ID As Variant) As String
   If IsNull(ID) Then Exit Function
   With F.RecordsetClone
      If F.NewRecord Then
         '.movelast  I dont think this is required, so commented it out.
         RecordOfRecords = .RecordCount + 1 & " of " & .RecordCount + 1
      Else
         .MoveLast
         .FindFirst IDFieldName & "=" & ID
         RecordOfRecords = .AbsolutePosition + 1 & " of " & .RecordCount
      
      End If
   End With
End Function

Open in new window


I don't know if there are other quirks to be found.