RecordCount display not showing zero when no records are found

[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!txtjRecordCo unt 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 concienciousnes s.


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!txtjSubRecor dCount 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!