Getting the Record X of Y value for subform

[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

 

[Marshall Barton]

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

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

 

[Kurt]

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

I made this change but I'm still getting the #Name error in the unbound text box.

 

[Marshall Barton]

I made this change but I'm still getting the #Name error in the unbound text box.

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.

 

[Kurt]

You left out the clone part:

=fsubForm.Form.CurrentRecord & " of " &
fsubForm.Form!RecordsetCLONE.RecordCount

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

 

[Marshall Barton]

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

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]

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

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

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.