number of records in a subform

  • Thread starter Thread starter JKlein
  • Start date Start date
J

JKlein

I need a way to retreve the number of records listed in a subform. I am
going to use this value to determine if a scroll bar is needed. I have
tried to put a text box in each subform and set its control source to
"=count(*)". My idea is to have code that looks at this value and make the
approprate scroll bar value. The problem is that I can not find a way to
return a "0" value if no records are showing.
Maybe I am using the wrong approach. Any help is appreciated.
 
Hey JKlein,

I do the same thing on one of my subforms and below is the code I use.
Someone else may have a better way of doing it but I do know this has worked
for me.

This is in the MainForms Current event:

Private Sub Form_Current()

If Me.subEquipHist.Form.RecordsetClone.RecordCount <= 0 Then
Me.subEquipHist.Visible = False
Me.lblHistory.Caption = "No Related Orders To This Equipment"
ElseIf Me.subEquipHist.Form.RecordsetClone.RecordCount = 1 Then
Me.subEquipHist.Visible = True
Me.subEquipHist.Form.ScrollBars = 0
Me.lblHistory.Caption = "Related Orders To This Equipment"
Else
Me.subEquipHist.Visible = True
Me.subEquipHist.Form.ScrollBars = 2
Me.lblHistory.Caption = "Related Orders To This Equipment"
End If

HTH,
Shane
 
THat's the right idea, but there may be a couple of issues
in there. First, A2003 might not like the shorthand
reference to controls on the subform. Second, there are
times when the RecordCount property returns 1 when it hasn't
finished loading all the records.

To deal with these possibilities, I recommend code like:

With Me.subEquipHist.Form
.RecordsetClone.MoveLast
If .RecordsetClone.RecordCount <= 0 Then
Me.subEquipHist.Visible = False
Me.lblHistory.Caption = "No Related Orders To This
Equipment"
Else
Me.subEquipHist.Visible = True
Me.lblHistory.Caption = "Related Orders To This
Equipment"
If .RecordsetClone.RecordCount = 1 Then
.ScrollBars = 0
Else
.ScrollBars = 2
End If
End If
End With
 
Hey Mr. Barton,

Thanks for your reply and helping me with shoring up my code.

Shane

Marshall said:
THat's the right idea, but there may be a couple of issues
in there. First, A2003 might not like the shorthand
reference to controls on the subform. Second, there are
times when the RecordCount property returns 1 when it hasn't
finished loading all the records.

To deal with these possibilities, I recommend code like:

With Me.subEquipHist.Form
.RecordsetClone.MoveLast
If .RecordsetClone.RecordCount <= 0 Then
Me.subEquipHist.Visible = False
Me.lblHistory.Caption = "No Related Orders To This
Equipment"
Else
Me.subEquipHist.Visible = True
Me.lblHistory.Caption = "Related Orders To This
Equipment"
If .RecordsetClone.RecordCount = 1 Then
.ScrollBars = 0
Else
.ScrollBars = 2
End If
End If
End With
I do the same thing on one of my subforms and below is the code I use.
Someone else may have a better way of doing it but I do know this has worked
Click to expand...
[quoted text clipped - 24 lines]
Click to expand...
 
Okay....I have tried to use this method but I am getting an error: Run-time
error 7951
You entered an expression that has an invalid reference to the
RecordsetClone property.
What causes this error?


Marshall Barton said:
THat's the right idea, but there may be a couple of issues
in there. First, A2003 might not like the shorthand
reference to controls on the subform. Second, there are
times when the RecordCount property returns 1 when it hasn't
finished loading all the records.

To deal with these possibilities, I recommend code like:

With Me.subEquipHist.Form
.RecordsetClone.MoveLast
If .RecordsetClone.RecordCount <= 0 Then
Me.subEquipHist.Visible = False
Me.lblHistory.Caption = "No Related Orders To This
Equipment"
Else
Me.subEquipHist.Visible = True
Me.lblHistory.Caption = "Related Orders To This
Equipment"
If .RecordsetClone.RecordCount = 1 Then
.ScrollBars = 0
Else
.ScrollBars = 2
End If
End If
End With
--
Marsh
MVP [MS Access]

I do the same thing on one of my subforms and below is the code I use.
Someone else may have a better way of doing it but I do know this has
worked
for me.

This is in the MainForms Current event:

Private Sub Form_Current()

If Me.subEquipHist.Form.RecordsetClone.RecordCount <= 0 Then
Me.subEquipHist.Visible = False
Me.lblHistory.Caption = "No Related Orders To This Equipment"
ElseIf Me.subEquipHist.Form.RecordsetClone.RecordCount = 1 Then
Me.subEquipHist.Visible = True
Me.subEquipHist.Form.ScrollBars = 0
Me.lblHistory.Caption = "Related Orders To This Equipment"
Else
Me.subEquipHist.Visible = True
Me.subEquipHist.Form.ScrollBars = 2
Me.lblHistory.Caption = "Related Orders To This Equipment"
End If
Click to expand...
Click to expand...
 
Sounds like your not referencing your subforms recordset properly. Be sure
the name is spelled correctly. Also, as Mr. Barton said, you may not be able
to get away with 'Me' in A2003. If you are using 'Me' then try typing out
the full path and see if that helps.

Shane
Okay....I have tried to use this method but I am getting an error: Run-time
error 7951
You entered an expression that has an invalid reference to the
RecordsetClone property.
What causes this error?
THat's the right idea, but there may be a couple of issues
in there. First, A2003 might not like the shorthand
Click to expand...
[quoted text clipped - 51 lines]
Click to expand...
 
Whoa there. Shane, I was not referring to Me in that
comment about abbreviated references to subform controls.
It was the absence of the FORM property that should not be
omitted. After reviewing your code more carefully, I see
that you were correct and that I had misread the lines about
setting the subform control's Visible property. Somehow I
thought that was a referenve to a control on the subform. I
apologize for the confusion.

To be clear, a shorthand reference such as
Me.subform.control that used to be tolerated in earlier
versions has been tightened up in A2003. The correct
reference is Me.subform.FORM.control.
--
Marsh
MVP [MS Access]

Sounds like your not referencing your subforms recordset properly. Be sure
the name is spelled correctly. Also, as Mr. Barton said, you may not be able
to get away with 'Me' in A2003. If you are using 'Me' then try typing out
the full path and see if that helps.

Okay....I have tried to use this method but I am getting an error: Run-time
error 7951
You entered an expression that has an invalid reference to the
RecordsetClone property.
What causes this error?
THat's the right idea, but there may be a couple of issues
in there. First, A2003 might not like the shorthand
Click to expand...
[quoted text clipped - 51 lines]
return a "0" value if no records are showing.
Maybe I am using the wrong approach. Any help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks for clearing that up, Mr. Barton. I use A2000 and have never seen
A2003 so when I read what you wrote then I thought it was something that must
have changed in A2003. It did seem usual to me. :)

Shane

Marshall said:
Whoa there. Shane, I was not referring to Me in that
comment about abbreviated references to subform controls.
It was the absence of the FORM property that should not be
omitted. After reviewing your code more carefully, I see
that you were correct and that I had misread the lines about
setting the subform control's Visible property. Somehow I
thought that was a referenve to a control on the subform. I
apologize for the confusion.

To be clear, a shorthand reference such as
Me.subform.control that used to be tolerated in earlier
versions has been tightened up in A2003. The correct
reference is Me.subform.FORM.control.
Sounds like your not referencing your subforms recordset properly. Be sure
the name is spelled correctly. Also, as Mr. Barton said, you may not be able
Click to expand...
[quoted text clipped - 12 lines]
Click to expand...
 
Back
Top