-
Yep. The particular query had two parameters that needed to be passed through first.
I didn’t bother mentioning it because I tried removing the Name parameter and the query still failed to run. But, much later (until last night), I found out that I had misconfigured the query and broke it. After your explaination about the Dcount, I realized what was actually going on.
Thanks so much for your help.
-
Yep. The particular query had two parameters that needed to be passed through first.
I didn’t bother mentioning it because I tried removing the Name parameter and the query still failed to run. But, much later (until last night), I found out that I had misconfigured the query and broke it. After your explaination about the Dcount, I realized what was actually going on.
Thanks so much for your help.
-
John,
Thanks for your suggestion. I seriously considered it but then realized that instead of opening report then typing in the name of client, I wondered why couldn’t I just add an extra text box similiar to From Date and To Date and code it the same way the From Date and To Date text boxes were coded. That way, it would be far easier for the staff to simply type in the name and the dates at the main menu and that would also solve the “null report” issue.
So, I did just that. I modified the Main Menu to allow entry of a client name and it works PERFECTLY! I’m so thrilled. Without you and Han’s help, I wouldn’t have came up with an elegant solution to a problem.
Hans, your suggestion to turn off the Error Handling helped me identify the issue
John, your help in figuring out and explaining to me how code worked helped me come up with a solution that works.If you ever are in Seattle, I’ll treat ya to a dinner!
-
John,
Thanks for your suggestion. I seriously considered it but then realized that instead of opening report then typing in the name of client, I wondered why couldn’t I just add an extra text box similiar to From Date and To Date and code it the same way the From Date and To Date text boxes were coded. That way, it would be far easier for the staff to simply type in the name and the dates at the main menu and that would also solve the “null report” issue.
So, I did just that. I modified the Main Menu to allow entry of a client name and it works PERFECTLY! I’m so thrilled. Without you and Han’s help, I wouldn’t have came up with an elegant solution to a problem.
Hans, your suggestion to turn off the Error Handling helped me identify the issue
John, your help in figuring out and explaining to me how code worked helped me come up with a solution that works.If you ever are in Seattle, I’ll treat ya to a dinner!
-
Hans and John:
Here’s what I discovered when I followed Han’s suggestion:
1) I turned off the Error Handler by commenting it out.
2) When the error message popped up, I clicked on “Debug” and I saw this:If Nz(Me![cboReports]) “” Then
strReportName = Me![cboReports]
strRecordSource = Me![cboReports].Column(2)
If Nz(DCount(“*”, strRecordSource)) > 0 Then **This line was highlighted in yellow and the strRecordSource is showing qryLogNotes which is correct.**
If Me![fraReportMode] = 1 Then
DoCmd.OpenReport ReportName:=strReportName, view:=acPreview
Me.Visible = False
(I didn’t cut and paste the entire If…Then section)Good grief. I figured out why. The line I pointed out checks to make sure that the records isn’t null, hence, it refuses to print report as blank. Unfortunately, that particular query REQUIRES two parameters to be passed through before it will open the report.
1) It first asks me for name of client I want to perform a search on.
2) It asks me for the date range.Since it “checks” the report BEFORE it opens for any records, since I didn’t “enter” the name of client, the main menu finds no records, then it cancels the operation THEN the Name parameter kicks in!
My solution is to add another “field” next to the From Date and To Date and call it “Client Name” or whatever and have the staff enter the name of person and dates at the main form, and it would then enter those parameters before it checks if the records are null. Do you think it will work?
Thanks so much.
-
Hans and John:
Here’s what I discovered when I followed Han’s suggestion:
1) I turned off the Error Handler by commenting it out.
2) When the error message popped up, I clicked on “Debug” and I saw this:If Nz(Me![cboReports]) “” Then
strReportName = Me![cboReports]
strRecordSource = Me![cboReports].Column(2)
If Nz(DCount(“*”, strRecordSource)) > 0 Then **This line was highlighted in yellow and the strRecordSource is showing qryLogNotes which is correct.**
If Me![fraReportMode] = 1 Then
DoCmd.OpenReport ReportName:=strReportName, view:=acPreview
Me.Visible = False
(I didn’t cut and paste the entire If…Then section)Good grief. I figured out why. The line I pointed out checks to make sure that the records isn’t null, hence, it refuses to print report as blank. Unfortunately, that particular query REQUIRES two parameters to be passed through before it will open the report.
1) It first asks me for name of client I want to perform a search on.
2) It asks me for the date range.Since it “checks” the report BEFORE it opens for any records, since I didn’t “enter” the name of client, the main menu finds no records, then it cancels the operation THEN the Name parameter kicks in!
My solution is to add another “field” next to the From Date and To Date and call it “Client Name” or whatever and have the staff enter the name of person and dates at the main form, and it would then enter those parameters before it checks if the records are null. Do you think it will work?
Thanks so much.
-
Yeah. Only one query, I’m having problems with. I have it on three other queries and they work perfectly fine.
So, is it something to do with how I designed the query? It is not picking up the dates, somehow?
I’ll look at it tonight, but if I can’t figure anything out, short of sending you the entire database and violating my confidentality agreement with the said agency, how do I show you the query I’m having trouble with?
-
Yeah. Only one query, I’m having problems with. I have it on three other queries and they work perfectly fine.
So, is it something to do with how I designed the query? It is not picking up the dates, somehow?
I’ll look at it tonight, but if I can’t figure anything out, short of sending you the entire database and violating my confidentality agreement with the said agency, how do I show you the query I’m having trouble with?
-
-
-
-
-
Man. You replied so quickly that I didn’t have a chance to edit my original question.
I realized that I forgot to add some pertinent details…
1) It is a saved query
2) The query contains a “code” that basically says that I want all dates from specific date to specific date and it is coded as such: Between FromDate() and ToDate() because the dates I enter is located on the main form as From Date and To Date.
3) I’ve successfully used that very line of code for another query, and it works fine? (It is a crosstab query, might make a difference?)
4) It actually works when I go into Database Window and open the report and it does pull down the correct records according to dates I enter in the Main Menu but when I try to open the report via the Main Menu, it gives me that “You canceled the previous action” error.Brent
-
Man. You replied so quickly that I didn’t have a chance to edit my original question.
I realized that I forgot to add some pertinent details…
1) It is a saved query
2) The query contains a “code” that basically says that I want all dates from specific date to specific date and it is coded as such: Between FromDate() and ToDate() because the dates I enter is located on the main form as From Date and To Date.
3) I’ve successfully used that very line of code for another query, and it works fine? (It is a crosstab query, might make a difference?)
4) It actually works when I go into Database Window and open the report and it does pull down the correct records according to dates I enter in the Main Menu but when I try to open the report via the Main Menu, it gives me that “You canceled the previous action” error.Brent
-
That teaches me a valuable lesson in NEVER work late in the night !
After waking up after a good night’s sleep, now your suggestions make a lot of sense. I was too tired to notice that you were trying to suggest that I display the minutes in the conventional way (and using Hans’ example). Also, was too tired to notice that indeed, one bit of code was missing “txt” (txtTotalHours) and that was what caused it to act strangely.
I’ve changed both combo boxes to Fixed format, do I need to change the table properties to Fixed too? (I can’t remember if I had set the Total Hours column to Fixed or not, it is at someone’s PC right now)
Many, many thanks for your help.
Author
