Record "#" of "#" records in a subform 1

[MADDPM]

In an earlier posting a similar question was asked: "asfhgwt (TechnicalUser) 14 Aug 06 10:56 -Anyone know an easy way to show the current record number on a record-filtered form -- without showing the navigation arrows? (I don't use control numbers.) Example: There are 1000 records in the "Main" table. The form is filtered and shows only those west of the Mississippi river -- 400 records. I'd like to see the numbers 1 through 400 on my form when scrolling using my own scroll buttons."

In the first part, which seemed extremely simple, you answered that using a text box with its control property set to =[Current Record] would display the number of a record within a set of records - unfortunately, this isn't working for me. The second part, which seemed a bit more complex and took a few tries in the previous posting (=RecordsetClone.RecordCount), is working for me.

My main form displays an employee - the subform (continuous forms) displays contract(s) related to that employee.

I'm at a loss as to why =[Current Record] repeatedly gives the result of "1". In other words, "Appointment 1 of 11" appears for all 11 contract records.

Please let me know what I'm doing wrong - or if you need additional information.
Access 2003, (11.8166.8221) SP3
Thanks in advance.

 

[ck1999]

Is your text box based off of the main form or subform records?

ck1999

 

[MADDPM]

Text box is desired in the subform "Appointments" - based on a query "Appointment Details-Active" (combining three tables).

Please let me know if you need further informtion. Thanks.

 

[MADDPM]

After extensive research on the I'net, I've come to realize what I'm seeking is probably not possible. What I am trying to do: While an employee's identification record is displayed on the "Main Screen", the parent form (left-hand side of the screen), to be able to drag the cursor in the "Appointment/Contracts Screen" which is the child subform (right-hand side of the screen) and then be able to tell "at a glance" which contract I'm viewing by the "Record # of # Records" in the title bar of each contract record. The subform usually displays 3.25 records on most monitors in our offices. Unfortunately, I've discovered the record must have "focus" in order to update the first part of the equation "Record # . . . ". In other words, if I advance the records using the record navigator at the bottom of the form, ALL of the records read identically - for record #3, all 11 contracts display "Record 3 of 11 Records" - which is useless when dragging the scroll bar down to quickly find the academic year of say the 6th contract - that is without repeatedly clicking to advance through the records.

I've tried, DCount, subqueries, recordset clones, etc.

However, if someone can come up with something, please let me know. Many thanks in advance!

 

[Remou]

Here is a possibility. It may be more trouble than it's worth.

First, add a countrol to the detail of your subform and set the control source to:

=RecCounter()

Then, add some code:

Option Explicit
Dim intRec As Integer

Function RecCounter()
Dim intRecCount

    intRecCount = Me.Recordset.RecordCount
    If intRec > intRecCount Then
        intRec = 0
        Me.Requery
    End If

    intRec = intRec + 1
    If intRec = intRecCount + 1 Then
        RecCounter = "New Record"
    Else
        RecCounter = intRec & " records of " _
           & Me.RecordsetClone.RecordCount
    End If
End Function

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
    intRec = 0
    Me.Requery
End Sub

Private Sub Form_Filter(Cancel As Integer, FilterType As Integer)
    intRec = 0
    Me.Requery
End Sub

Private Sub Form_Load()
    intRec = 0
End Sub

 

[MADDPM]

Hello Remou,

Thank you so much for your time - but, unfortunately, this, too, does not work. The resulting display in the control reads "1 Record of 1" . . . for all 11 contract records for the first employee in the test instance of the db.

Ah, well. You are correct, it's probably more trouble than it's worth - but it would have made things easier for the staff in my office.

Again, thanks for your time, but feel free to let me know if something "pops" into your mind down the road.

 

[Remou]

I am greatly surprised by your result (Record 1 of 1). I tested this before I posted. Is intRec defined at module level? Have you stepped though to see what happens to the value of intRec?

 

[MADDPM]

Ooops - that would have been, no, not defined at Module level before, but it is now.

However, now I receive the error message: "Compile Error: Invalid use of Me keyword" resulting from the line of code that reads "intRecCount = Me.Recordset.RecordCount".

Thanks for your persistence.

 

[Remou]

Where is this code? It is intended to run in the module related to the subform.

 

[MADDPM]

Hi Remou,

The first time it was defined at the Class Object level for the form - resulting in the "1 Record of 1" display.

I then moved it to it's own module in the db - resulting in the last error.

Please let me know where it should be. Thanks in advance for your patience!

 

[Remou]

It should be pasted as is into the module belonging to the subform. When I said defined at module level, I should have made it clear that I meant at module level for the subform. Perhaps you could attach a zipped copy?

 

[MADDPM]

Kudos Remou!!! It WORKS!

Your original post should have sufficed - except I have two identical forms - one is "Appointments-DataEntry" and the other is "Appointments-Secured" ("secured" simply meaning the fields are locked - this is the form that loads for viewing). Anyway, I should have been paying closer attention to the title bar. I put the =RecCounter() control in one form and the code in the module for the other form. Duh.

But you've saved my sanity! Thanks so, so much.

 

[FixAte]

Your post help me to display the total record count, which I had trouble with. This is how I implemented it...

I have a label named lblRecordNumber next to first / previous / next / last buttons on my form.
In the forms On Current event I used the following code:
Private Sub Form_Current()
Me.lblRecordNumber.Caption = "Record # " & Me.CurrentRecord & " of " & Me.RecordsetClone.RecordCount
End Sub

This works as intended, thanks.