Getting the Record X of Y value for subform

  • Thread starter Thread starter Kurt
  • Start date Start date
K

Kurt

I'd like to have an unbound text box on my main form
(frmMain) show the "Record X of Y" value for the
RecordCount in a subform (fsubForm).

Right now I'm getting a #Name error in the text box.

Here's what I have:

Current Event for the SubForm:

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount > 0 Then
Me.RecordsetClone.MoveLast
End If
End Sub

Control Source of the unbound text box (which is on the
main form):

=Me!fsubForm.Form.CurrentRecord & " of " & Me!
fsubForm.Form!Recordset.RecordCount

I assume the problem is the Control Source syntax.

Thanks. Kurt
 
Kurt said:
I'd like to have an unbound text box on my main form
(frmMain) show the "Record X of Y" value for the
RecordCount in a subform (fsubForm).

Right now I'm getting a #Name error in the text box.

Here's what I have:

Current Event for the SubForm:

Private Sub Form_Current()
If Me.RecordsetClone.RecordCount > 0 Then
Me.RecordsetClone.MoveLast
End If
End Sub

Control Source of the unbound text box (which is on the
main form):

=Me!fsubForm.Form.CurrentRecord & " of " & Me!
fsubForm.Form!Recordset.RecordCount
Click to expand...


Me is a VBA class module object, it is not know in the
expression service. Just drop it:

=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!Recordset.RecordCount
 
Me is a VBA class module object, it is not know in the
expression service. Just drop it:

=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!Recordset.RecordCount
Click to expand...

I made this change but I'm still getting the #Name error in the unbound text box.
 
Kurt said:
I made this change but I'm still getting the #Name error in the unbound text box.
Click to expand...

You left out the clone part:
=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!RecordsetCLONE.RecordCount

Also, double check that fsubForm is the name of the subform
CONTROL, which may be different from the name of the subform
it is displaying.
 
You left out the clone part:
=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!RecordsetCLONE.RecordCount
Click to expand...

I added the Clone part and I'm still getting #Name. And I
made sure that I'm referring to the name of the subform
CONTROL. Not sure if this matters but the syntax objects
are being put in brackets automatically, as in:

=[fsubForm].Form.[CurrentRecord] & " of " &
[fsubForm].Form![RecordsetClone].[RecordCount]

By the way, I didn't use Clone because I've never had to
when using an X of Y unbound on a main form to refer to
the main table. When I wasn't dealing with a subform, the
following syntax in combination with the On Current code
has always worked:

=[CurrentRecord] & " of " & [Form].Recordset.RecordCount

With that being said, I took a different approach and now
have it working. I put a lable called "lblNavigate" on the
main form. I then put this in the sub form's On Current
event:

If Me.NewRecord Then
Me.Parent!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me.Parent!lblNavigate.Caption = "Record " & _
Me.CurrentRecord _
& " of " & .RecordCount
End With
End If

Thanks for your help.

Kurt
 
Kurt said:
You left out the clone part:
=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!RecordsetCLONE.RecordCount
Click to expand...

I added the Clone part and I'm still getting #Name. And I
made sure that I'm referring to the name of the subform
CONTROL. Not sure if this matters but the syntax objects
are being put in brackets automatically, as in:

=[fsubForm].Form.[CurrentRecord] & " of " &
[fsubForm].Form![RecordsetClone].[RecordCount]
Click to expand...

The ! above is incorrect, is has to be a dot.

As long as they're in the right places, the square brackets
don't matter.


By the way, I didn't use Clone because I've never had to
when using an X of Y unbound on a main form to refer to
the main table. When I wasn't dealing with a subform, the
following syntax in combination with the On Current code
has always worked:

=[CurrentRecord] & " of " & [Form].Recordset.RecordCount
Click to expand...

You must be use A2K or later. I ran my tests in A97.

With that being said, I took a different approach and now
have it working. I put a lable called "lblNavigate" on the
main form. I then put this in the sub form's On Current
event:

If Me.NewRecord Then
Me.Parent!lblNavigate.Caption = "New Record"
Else
With Me.RecordsetClone
.Bookmark = Me.Bookmark
Me.Parent!lblNavigate.Caption = "Record " & _
Me.CurrentRecord _
& " of " & .RecordCount
End With
End If
Click to expand...

As far as I can tell, except for avoiding the reference to
the subform control, that uses the same syntax and
expression in VBA code as the other did in a control source
expression.

Whatever works.
 
Back
Top