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?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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
0
SteveL13Author Commented:
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.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

SteveL13Author Commented:
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.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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?
0
SteveL13Author Commented:
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.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
You would need to use the primary key of the subforms recordsource. If all the rows have the same ID, then the code provided will not work.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
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.

??
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.