RecordCount display not showing zero when no records are found

[Jake1776]

Thanks in advance for the help.

The goal: Display the number of results returned by a query in a sub form.

The problem: If the query returns no results the txtBox will continue to display the last record count.

Originally I had this in Form_Current()

Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount

Then I wrapped it in an If statement but that didn't solve it.

Private Sub Form_Current()
    
    If Me.RecordsetClone.RecordCount <> 0 Then
        Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
        Else
            Me!txtjRecordCount = "No jobs found."
    End If

End Sub

I'm missing something...

[jforbes]

Sometimes with Recordsets, the count will be off until the Recordset is navigated, maybe this will work for you:

Dim oRst AS DAO.RecordSet
Set oRst = Me.RecordsetClone
oRst.MoveLast
Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

[Jake1776]

I see what you did with MoveLast but that gave me the same problem. It still displays the previous number when the query returns no results.

Edit: Side note since you mentioned navigation, I am navigating on the form not the subform, as you click through records on the form the subform is requeried.

[jforbes]

I think I missed that you were looking for a RecordCount on a SubForm. Maybe try this then, as it gets the Recordset Clone of the SubForm instead of the Main Form:

Dim oRst AS DAO.RecordSet
Set oRst = Me.[Name of Subform without these Brackets].Form.RecordsetClone
oRst.MoveLast
Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

[Jake1776]

Hate to say it, but I am still getting the same problem.

Side note: I was getting an error saying the object could not be found. I knew the name was correct and tried a few things, looks like I had to provide the path through the main form. Changes are below.

I can't understand why this doesn't work, I really appreciate your help!

    Dim oRst As DAO.Recordset
    Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
        oRst.MoveLast
        Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

[PhilOfWalton]

Hate to butt in guys, but I assume the OnCurrent is on the main form and the TxtjRecordCount is also on the main form.

If that is so, try this modification

Dim oRst As DAO.Recordset
    Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
        if oRST.EOF then
           Me!txtjRecordCount = "No jobs found"
           exit sub
        end if
        oRst.MoveLast
        Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

Phil

[jforbes]

I probably should have be more clear on this line:

Set oRst = Me.[Name of Subform without these Brackets].Form.RecordsetClone

The name to use for the SubForm is the Name of the Control on the Form, not the name of the Form stored in Access. Sometimes they are the same, but not always.

You could try to get to it through the Forms collection, but if it is already on the Form, you don't need to. If you want to read up on it:
Referring to Items on a Sub-Form
Refer to Form and Subform properties and controls

[Jake1776]

Great links! I'm starting to understand how this works. But I must be missing something. I believe the name of the control on the main form is the same as the name of the form.

Am I correct in assuming that if I am in design view on the main form and click the box around subform it goes yellow and can be size adjusted, the property sheet says the name and source object are frmjsubDisplayJ obsToBeLinked.

Edit: This code is still giving me the error "Method or data member not found".

Set oRst = Me.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone

The record source is a query imbedded in the Sub-Form. Should I be using that as the control?

[jforbes]

Yes, the Name of the Control is shown in Name Property when the SubForm is selected in the Designer. The Subform name should also show up in the IntelliSense list when you Type "Me." on a new line in the Designer.

On additional thing to try to verify the name of the SubForm Control is to type this into the Immediate Window, with the Form Open:

?Forms("jfrmJobsNotLinked").frmjsubDisplayJobsToBeLinked.form.name

If everything is named correctly, the name of the Form should be echoed back.


You shouldn't have to mess with the RecordSource of the SubForm. That is what the RecordsetClone is attempting to grab.

[Jake1776]

I did see it pop up with intellisense at some point but I cant get it to now.

Putting that into the immediate window returns the subform name that I have been using, still getting that same error when I compile or run.

At this point it is more work than it's worth, but I am taking it personally and as a result I want to solve it.

Thanks for all your help jforbes!

[jforbes]

That is rather confusing, I think it should be working, or at least not error on finding the RecordsetClone.

We/I must be missing something. Would you mind re-posting your current code, the error number and what line it's erroring on?

[Jake1776]

"Compile error:
Method or data member not found"

Private Sub Form_Current()

    Dim oRst As DAO.Recordset
    Set oRst = Me.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
    
    oRst.MoveLast
    
    Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

End Sub

This section gets highlighted:

.frmjsubDisplayJobsToBeLinked

[jforbes]

Well, with that info, Access definitely does not understand what it is we are referring to in code. Maybe try renaming the SubForm Control both on the Control and in the VBA to something like "Jobs" and see if things will line up?

[Jake1776]

I tried renaming the control on the form and in the code, but I still get the compile error.

After reading though your links above a few times I tried linking through the form then the sub form. With this the error goes away but the problem persists (the same is true of your code in Post #2).

Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone

Honestly at this point my plan is to leave it broken because it works except when the count is zero, but I just want to know why it doesn't work.

[PhilOfWalton]

Have you tried the

        if oRST.EOF then
           Me!txtjRecordCount = "No jobs found"
           exit sub
        end if

code I suggested

Phil