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 frmjsubDisplayJobsToBeLinked.

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

[Jake1776]

Phil, thanks for your response and sorry I forgot to reply. I did try your if statement, I also tried it as and if-then-else (should have the same result I believe). I don't get a compile error, but have the same issue of displaying the previous number rather than the message "No jobs Found."

Edit for clearity: Per your response in post six, the OnCurrent and text box are actually on the subform.

    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!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
 

    Dim oRst As DAO.Recordset
    Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
    oRst.MoveLast
    If oRst.EOF Then
        Me!txtjRecordCount = "No jobs found"
        Exit Sub
        Else
            Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
    End If
 

[PhilOfWalton]

So Frustrating

After a little fiddling I think this may work

  Dim oRst As DAO.Recordset
    Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
    oRst.MoveFirst
    If oRst.EOF Then
        Me!txtjRecordCount = "No jobs found"
        Exit Sub
    Else
        oRst.Movelast
        Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
    End If
 

Phil

[jforbes]

All right, this is about the last thing I can think of. Using a variation of Phil's version:

    Dim oRst As DAO.Recordset
 
    Me!txtjRecordCount = "No jobs found"
 
    Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
    oRst.MoveLast
    If oRst.EOF Then
        Me!txtjRecordCount = "No jobs found"
        Exit Sub
        Else
            Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
    End If

It just assumes there are no records and updates if there are records found.

[PhilOfWalton]

Ah JForbes, I think you've missed the point. You need to move to the FIRST record and if that is also the EOF then there are no records.

Phil

[jforbes]

We posted at the same time, Phil. ...I hope one of them works.

[Jake1776]

Edit: My browser hadn't been updated, this response is to jforbe's post #18 I will try phil's solution in post #17 and comment below.

I cannot fathom why, but this doesn't work either jforbes.

If I comment out everything but Me!txtjRecordCount the message displays all the time. So we know for sure that it is finding the control (we already knew that), it must be an issue with updating?

Edit for afterthought: How can it be updating the textbox if the If statement is not true? Your code means the query must be returning something, right? Maybe I'm missing something.

I also tried your last idea another way by doing away with the DAO.recordset (even though it's essential the same), but I get the exact same results as before.

    Dim strLastTry As String
 
    Me!txtjSubRecordCount = "No jobs found"
 
    strLastTry = Me.RecordsetClone.RecordCount
 
    If strLastTry >= 1 Then
    Me!txtjSubRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
    Else
    Me!txtjSubRecordCount = "No jobs found"
    End If
    Dim strLastTry As String
    strLastTry = Me.RecordsetClone.RecordCount
    If strLastTry >= 1 Then
    Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
    Else
    Me!txtjRecordCount = "No jobs found"
    End If
 

[Jake1776]

In response to post #17:

Phil, no luck there either. It has the same issue. Can you navigate when nothing is returned?


I will probably just stick with the code in the original post since it is simple, even if it doesn't work quite right.

I'm still very curious as to why it isn't working. I can't thank you enough for all the attention you've both given this problem.

[PhilOfWalton]

Very odd. It worked for me in my test database, though I will admit I used a MsgBox rather than the FS_Database to display "No Jobs".

Sorry Out of ideas.

Phil

[Jake1776]

No worries, thanks for the effort!

[jforbes]

Maybe the code isn't being called, so it isn't being updated?

What is really throwing me is that if this line is the first line called:

Me!txtjSubRecordCount = "No jobs found"

then, the TextBox should be getting updated. Maybe OnCurrent isn't being called.

Would you like to put a break point on the first line and see if the code is being executed? Also, this code is in the OnCurrent of Main Form, right? Oh crap, this code is in the SubForm!
.. That kind of came out as a stream of concienciousness.


How is this code being called? Is it in the MainForm or the SubForm? This code was written as it was in the MainForm's OnCurrent:

Set oRst = Me.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone

If it's put in the SubForm's OnCurrent, it will give the Syntax error that you've been getting. Also what Form is Me!txtjSubRecordCount located on? I assumed it was the MainForm.

[Jake1776]

The code is in Form_Current() on the subform.
At some point I changed the text box name to distinguish it from another one. I know that is confusing.
The textbox is located on the subform.
I could change that if it would be easier, but I feel like that would complicate things.

It is displaying the number of records, just not updating when it returns nothing. That means it is finding the textbox, correct?. I assumed it couldn't count nothing which is why I tried an If statement.

[jforbes]

Well, this all makes sense now. I should have put this together earlier. The OnCurrent Event doesn't fire on a SubForm (or any Form) when there are no records to display. I know, it doesn't seem right to me either.

A trick to make this work, (Enabling the OnCurrent Event with zero records to display), is to set the AllowAdditions Property to True. Which will include a NewRecord that fires the OnCurrent when it gets focus. But, you'll then need to put some code in the BeforeInsert Event that Cancel the insert. And this may not be very user friendly.

Probably the better way to fix this is to put the code into the MainForm. Either in the OnCurrent Event or include it in whatever Button, TextBox, or ComboBox Event that you use to select/change which records to show in your SubForm...

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

[Jake1776]

THAT DID IT!

I ended up putting that code (which is essentially what we had from the start) in the navigation buttons and on the OnCurrent on the subform. When the form loads the first time it takes it from the subform OnCurrent and after that the navigation buttons update the text box, when it gets to a record that has no results in the query a zero is displayed. Maybe there's a cleaner way but I don't care, this works. (Probably easier if it is on the mainform OnCurrent, but since it also makes use of a RecordsetClone that interfered somehow. Not worried about that at all, not spending any more time on this detail haha.)

I'm glad you could also figure out why it wasn't working, although it doesn't make sense to me at least now I know.
Thanks again! One upvote can't represent my appreciation for your help!