Counting records in a subform and displaying it

Hi

I have a main form and a subform. On my main form. I have a text box which will display the number of records returned from the subform. The name of the text field on main form is numberRecords.

On my main form there are a number of text fields which users can type in. Based on the information entered eg search for surname in database of people called "Smith" the results will get filtered and displayed on the subform.

I want to display the number of records returned in the subform on a text box on the main form. For example if there were 5 people in the database with the surname of Smith there will be 5 records displayed and it will display on the main form in the textbox named numberRecords the number 5.

To do this I have used an expression in my code on my subform on the "On Current" event procedure using this code...

Dim rst As Recordset

Set rst = Me.RecordsetClone

If (rst.EOF And rst.BOF) Then
[Forms]![MainForm]![numberRecords] = 0
MsgBox "No records found"
Else
rst.MoveLast
[Forms]![MainForm]![numberRecords] = rst.RecordCount
End If

The problem is that when no records are returned (that is all the navigation button on the subform is greyed out (ie not pressable)
it doesn't seem to display the number of records as 0 it remains the same as the previous results. For example, if I search main form Smith and it displays 5 records and then the next search I do has 0 records it just displays 5 records. However if there are records returned eg displays 5 records, then next record is 3 then it will display 3.

I have also tried this as well

Dim numRec As Recordset
Set numRec = Me.RecordsetClone

numRec.MoveLast

If numRec.RecordCount > 0 Then
[Forms]![MainForm]![numberRecords] = numRec.RecordCount
Else
[Forms]![MainForm]![numberRecords] = 0
MsgBox "No records to Display"
End If

but it still has same problem that is doesn't seem to show 0 records or recognise it.

Does anyone know what the problem is or how I can fix it?

this line
If (rst.EOF And rst.BOF) Then
is not possible as both BOF AND EOF cannot be true at the same time.
try
If (rst.EOF OR rst.BOF) Then

I'm trying to do the same sort of thing. One thing I noticed is that when there are no records in the subform, the on current event does not seem to fire. (Check by adding Msgbox "Hello")
Have you come up with a solution ?
Dave

What I came up with:

On the main forms OnCurrent Event:

Dim RC As Integer 'Record Count

RC = DCount("*", "tblconsultation", "PatientID = forms![frmPatients].PatientID")

Me.numberRecords = RC

The DCount looks up the no of records in the table the subform
is based on with the criteria (PatientID = forms![frmPatients].PatientID) being the link field (on the main form) between your main form and subform.

HTH
Dave

=Count(*) in an unbound texbox in the subform footer.

Rich, this doesn't display 0 when there are no records which I think is what moshi is trying to achive.
Dave

It does on mine so I'm puzzled why it doesn't on yours Dave, however, =Iif(Not (IsNumeric(MySubFormControlEtc),0,MySubFormControlEtc) will do it

Thanks for all your help.
It still doesn't appear to be working

-------------------
I tried Fizzio's solution

That is ...
If (rst.EOF OR rst.BOF) but it still is the same. However, when no record is displayed it still does not display the message box "No records found"
ie
on subform "On Current" event used

Dim rst As Recordset

Set rst = Me.RecordsetClone
If (rst.EOF Or rst.BOF) Then
[Forms]![MainForm]![numberRecords] = 0
MsgBox "No records found"
Else
rst.MoveLast
[Forms]![MainForm]![numberRecords] = rst.RecordCount
End If
-------------
oldsoftboss
Tried using the Dcount but am not sure exactly how to use it
was having a bit of trouble as on the mainform
I have about 5 or more unbound text box which a person uses to search information in the database which has a seach button to search the record.
When you click on the button it filters the subform using the "on click" event procedure.

eg

Dim strFilter As String

strFilter = ""

If Not IsNull(Me!surnameInitial) Then strFilter = strFilter & "[SHORTNAME] Like " & Chr(34) & "*" & Me!surnameInitial & "*" & Chr(34) & " AND "
(etc .. for each of the unbound text box)

Then we have a filter, to filter string to subform
eg
If strFilter = "" Then
Me!Details.Form.FilterOn = False
Else
Me!Details.Form.Filter = strFilter
Me!Details.Form.FilterOn = True
End If

so with the Dcount how would you filter by wildcard each of the search entered in to (if a person enters more than one criteria for their search)?
---------------------------
Rich
I also tried the expression in the "on current" event procedure of the subform

Dim numRec As Recordset
Set numRec = Me.RecordsetClone

numRec.MoveLast

If (Not (IsNumeric(numRec.RecordCount))) Then
MsgBox "No records"
Else
MsgBox numRec.RecordCount
End If

but it still does not give me the message "No records"
It still displays a pop up box with number of records filtered when there is a record but not when there is no records..

I hate to state the obvious but, put a control on the footer of the subform. Use - =Count(*) - as its recordsource. You can then reference the control and copy its contents if you would rather see it on the mainform than the subform.

You missed the method I posted, which Pat has posted again, you don't need vba to do this

I'm still lost
If I put a control in the footer of my subform and set the Control Source (Sorry Pat, I couldn't find the record source) to =Count(*), when there are records in the subform (NB: the subform has additions set to no) the control is blank. When referenced on the main form, the main form control displays #Error
Dave

I'LL TRY AGAIN, WITH A SLIGHT MODIFICATION
=Iif(IsNumeric([Forms]![MainForm]![SubForm]![Control]),[Forms]![MainForm]![SubForm]![Control],0) as the control source for your textbox on the main form, replace the Forms etc with your own field/form names

Hi,

oh I see now...
Thank you Rich your solution worked !!!!!

It does display 0 in the textbox (in the numberRecords textbox) on the main form when no records are present in the subform.
--------------------------------------
Old softBoss this is what I did.

On the subform I created in the footer of the subform (like Rich said) a text box and set the control source to =Count(*) and named the unbound text box in the Name field to counterRecords

Then in my main form in my unbound textbox which I named numberRecords (to display number records showing in subform) I set the following in the Control Source

=IIf(IsNumeric([Details].[Form]![counterRecords]),[Details].[Form]![counterRecords],0)

If you are unsure exactly the code used to reference the subform control field with the count of the records (ie CounterRecords) ie how to reference it properly from the main form just do the following,

In the main form on the text box that you want to put the above code ie =IIf(IsNumeric([Details].[Form]![counterRecords]),[etc...

1. Click on the ... option next to the control source. This should open the expression builder
2. Then in the first column click on forms
3. Forms should expand to show loaded forms and all forms (since you have the main form opened it is loaded form) so click on loaded form, then click on the main form which is currently opened (in my case it is MainForm).
4 Then when you click on the MainForm it should expand to show the name of the subform, click on the subform name and in the middle column under the <form> heading you should get a list of names of textboxes, labels, etc which can be found on your subform.
5. Locate the name of the textbox which you have set the source to =Counter(*) in your subform (in my example above I named this textbox to counterRecords)
6. Double click on the this name
7. And the way to reference this field from the main form is displayed in the expression builder (in my case it was [Details].[Form]![counterRecords] but will be different depending on name of form and controls you used)

Then all you have to do is use Rich's code
=Iif(IsNumeric([Forms]![MainForm]![SubForm]![Control]),[Forms]![MainForm]![SubForm]![Control],0)

and where you see [Forms]![MainForm]![SubForm]![Control] in the above code just copy, paste and replace it with the code displayed in step 7 above.

I hope this helps...

Thank you Rich, Oldsoftboss, fizzio and Pat Hartman for responding and helping me with this.

Much appreciated !!

Hi all

Just looking at this post, as its exactly what i'm looking to do in a small database i'm seeting up. I've done the counter at the bottom of my subform and this displays a value if there are records available. when i try to reference that field from outside the subform on the main form, all i get is #Name?

As i'm sure this is something simple that i'm doing wrong i hope someone can help me out and let me know what the problem is.

Thanks in advance.

Be sure you have the bombs & dots in the right spots.

Forms!MainFormName!SubFormName.ControlName

After checking one of my Db's I found I have as the control source on the main form text box as: =[SubFormName].[Form]![ControlName]

HTH
Dave

Thanks for the reply. Wasn't sure if anyone would check this post again.

I've managed to get that working. thanks for your help, its much appreciated.