By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,660 Members | 1,939 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,660 IT Pros & Developers. It's quick & easy.

RecordCount display not showing zero when no records are found

P: 29
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()

Expand|Select|Wrap|Line Numbers
  1. Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
Then I wrapped it in an If statement but that didn't solve it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     If Me.RecordsetClone.RecordCount <> 0 Then
  4.         Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
  5.         Else
  6.             Me!txtjRecordCount = "No jobs found."
  7.     End If
  8.  
  9. End Sub
I'm missing something...
Mar 1 '17 #1

✓ answered by 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...
Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.         oRst.MoveLast
  4.         Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount

Share this Question
Share on Google+
27 Replies


jforbes
Expert 100+
P: 1,107
Sometimes with Recordsets, the count will be off until the Recordset is navigated, maybe this will work for you:
Expand|Select|Wrap|Line Numbers
  1. Dim oRst AS DAO.RecordSet
  2. Set oRst = Me.RecordsetClone
  3. oRst.MoveLast
  4. Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
Mar 1 '17 #2

P: 29
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.
Mar 1 '17 #3

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Dim oRst AS DAO.RecordSet
  2. Set oRst = Me.[Name of Subform without these Brackets].Form.RecordsetClone
  3. oRst.MoveLast
  4. Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
Mar 1 '17 #4

P: 29
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!

Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.         oRst.MoveLast
  4.         Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
  5.  
Mar 1 '17 #5

PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim oRst As DAO.Recordset
  3.     Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  4.         if oRST.EOF then
  5.            Me!txtjRecordCount = "No jobs found"
  6.            exit sub
  7.         end if
  8.         oRst.MoveLast
  9.         Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
  10.  
  11.  
Phil
Mar 1 '17 #6

jforbes
Expert 100+
P: 1,107
I probably should have be more clear on this line:
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 1 '17 #7

P: 29
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".

Expand|Select|Wrap|Line Numbers
  1. Set oRst = Me.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  2.  

The record source is a query imbedded in the Sub-Form. Should I be using that as the control?
Mar 1 '17 #8

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. ?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.
Mar 1 '17 #9

P: 29
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!
Mar 1 '17 #10

jforbes
Expert 100+
P: 1,107
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?
Mar 2 '17 #11

P: 29
"Compile error:
Method or data member not found"

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     Dim oRst As DAO.Recordset
  4.     Set oRst = Me.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  5.  
  6.     oRst.MoveLast
  7.  
  8.     Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
  9.  
  10. End Sub
  11.  
This section gets highlighted:

Expand|Select|Wrap|Line Numbers
  1. .frmjsubDisplayJobsToBeLinked
Mar 2 '17 #12

jforbes
Expert 100+
P: 1,107
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?
Mar 2 '17 #13

P: 29
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).
Expand|Select|Wrap|Line Numbers
  1. Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  2.  
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.
Mar 2 '17 #14

PhilOfWalton
Expert 100+
P: 1,430
Have you tried the
Expand|Select|Wrap|Line Numbers
  1.         if oRST.EOF then
  2.            Me!txtjRecordCount = "No jobs found"
  3.            exit sub
  4.         end if
  5.  
code I suggested

Phil
Mar 2 '17 #15

P: 29
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.
Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.     If oRst.EOF Then
  4.         Me!txtjRecordCount = "No jobs found"
  5.         Exit Sub
  6.     End If
  7.     oRst.MoveLast
  8.     Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
  9.  
Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.     oRst.MoveLast
  4.     If oRst.EOF Then
  5.         Me!txtjRecordCount = "No jobs found"
  6.         Exit Sub
  7.         Else
  8.             Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
  9.     End If
  10.  
Mar 3 '17 #16

PhilOfWalton
Expert 100+
P: 1,430
So Frustrating

After a little fiddling I think this may work

Expand|Select|Wrap|Line Numbers
  1.   Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.     oRst.MoveFirst
  4.     If oRst.EOF Then
  5.         Me!txtjRecordCount = "No jobs found"
  6.         Exit Sub
  7.     Else
  8.         oRst.Movelast
  9.         Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
  10.     End If
  11.  
Phil
Mar 3 '17 #17

jforbes
Expert 100+
P: 1,107
All right, this is about the last thing I can think of. Using a variation of Phil's version:
Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.  
  3.     Me!txtjRecordCount = "No jobs found"
  4.  
  5.     Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  6.     oRst.MoveLast
  7.     If oRst.EOF Then
  8.         Me!txtjRecordCount = "No jobs found"
  9.         Exit Sub
  10.         Else
  11.             Me!txtjSubRecordCount = "Number of jobs found: " & oRst.RecordCount
  12.     End If
It just assumes there are no records and updates if there are records found.
Mar 3 '17 #18

PhilOfWalton
Expert 100+
P: 1,430
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
Mar 3 '17 #19

jforbes
Expert 100+
P: 1,107
We posted at the same time, Phil. ...I hope one of them works.
Mar 3 '17 #20

P: 29
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.

Expand|Select|Wrap|Line Numbers
  1.     Dim strLastTry As String
  2.  
  3.     Me!txtjSubRecordCount = "No jobs found"
  4.  
  5.     strLastTry = Me.RecordsetClone.RecordCount
  6.  
  7.     If strLastTry >= 1 Then
  8.     Me!txtjSubRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
  9.     Else
  10.     Me!txtjSubRecordCount = "No jobs found"
  11.     End If
  12.     Dim strLastTry As String
  13.     strLastTry = Me.RecordsetClone.RecordCount
  14.     If strLastTry >= 1 Then
  15.     Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
  16.     Else
  17.     Me!txtjRecordCount = "No jobs found"
  18.     End If
  19.  
Mar 3 '17 #21

P: 29
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.
Mar 3 '17 #22

PhilOfWalton
Expert 100+
P: 1,430
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
Mar 3 '17 #23

P: 29
No worries, thanks for the effort!
Mar 3 '17 #24

jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 3 '17 #25

P: 29
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.
Mar 6 '17 #26

jforbes
Expert 100+
P: 1,107
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...
Expand|Select|Wrap|Line Numbers
  1.     Dim oRst As DAO.Recordset
  2.     Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
  3.         oRst.MoveLast
  4.         Me!txtjRecordCount = "Number of jobs found: " & oRst.RecordCount
Mar 6 '17 #27

P: 29
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!
Mar 6 '17 #28

Post your reply

Sign in to post your reply or Sign up for a free account.