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...
Jake1776
Last Activity: Oct 5 '17, 05:50 PM
Joined: Oct 21 '16
Location:
-
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...Leave a comment:
-
No worries, thanks for the effort!Leave a comment:
-
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.Leave a comment:
-
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:...Leave a comment:
-
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.
...Code:Dim oRst
Leave a comment:
-
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).
Honestly at this point my plan is...Code:Set oRst = Forms!jfrmJobsNotLinked.frmjsubDisplayJobsToBeLinked.Form.RecordsetClone
Leave a comment:
-
"Compile error:
Method or data member not found"
This section gets highlighted:Code: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
...Code:.frmjsub
Leave a comment:
-
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!Leave a comment:
-
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...Leave a comment:
-
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!
...Code:Dim oRst As DAO.Recordset Set oRst = Forms!jfrmJobsNotLinked!frmjsubDisplayJobsToBeLinked.Form.RecordsetCloneLeave a comment:
-
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.Leave a comment:
-
RecordCount display not showing zero when no records are found
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()
Then I wrapped it in an If statement...Code:Me!txtjRecordCount = "Number of jobs found: " & Me.RecordsetClone.RecordCount
-
So I made it work, though I'm still not certain I understand why the code in Post #1 needs the subreport launched and the code in Post #5 does not, but I think it's because Post #5 links to the txtBox and not to the actual control source (on the subreport). Like I said, I'm not sure.
To solve my problem I simply changed the name of the text box from Print to PrintNum so that I could use the exact same format as in Post #5 and not worry...Leave a comment:
-
So I have to open the report in the background and then make sure both are closed when exiting the form. Seems like a lot of work.
So here's another question.. why does this code work with the report not being open?
...Code:Private Sub Req_Num_Click() Dim strReqNum As String strReqNum = Req_Num Forms!frmIPACHistory!txtSearchReqNum.SetFocus Forms!frmIPACHistory!txtSearchReqNum.TextLeave a comment:
-
Okay that will not work because Print is reserved. How do I get around this?
But the error it gives is that "the report isn't open or does not exist" but it does exist, and it's open on the form.
Any other sugestions?Leave a comment:
-
Jake1776 started a topic Retrieving a field from the current record from a subreport plus complicationin AccessRetrieving a field from the current record from a subreport plus complication
So the real problem is that I have a field named Print (reserved word). I can't change the field name because of reasons outside my control.
The goal of this code is to be able to click on a field and have that field populate a txtbox which can then be used as search criteria.
The code below gives an error because the subreport is not open. (Coincidentally this is the reason for the clunky code being used to populate... -
If anyone comes across this post looking for an answer here it is.
I used DCount with multiple criteria to see how many times a job is found. This is good because it also tells me if the criteria matched more than one time (which would change more than one job). It works and was actually a fairly simple solution. There may be other ways to do it but I couldn't find any type of query that did this. I will probably eventually store the...Leave a comment:
-
So I added
and it returns the last job's print number repeatedly. I believe that is because the if then statement finds that the last job did not match in the table PRINT_JOBS thousands of times until it did find it, but maybe the immediate screen doesn't hold all of the times it printed.Code:Else debug.print rsIPAC!PRINT
I will continue looking for a solution, at this point I am mainly documenting in case...Leave a comment:
-
I could be wrong, but wouldn't that return all failed matches and not just the jobs that never matched?
In other words if it didn't match up with the first thousand records then the else clause would catch all of that as well as the ones that could never be found. Not totally sure on that though...Leave a comment:
No activity results to display
Leave a comment: