You canceled previous operation error with report

Topic

New Reply

I’m getting an error message, “You have canceled the previous operation” (Error 2001) whenever I try to open a report via “Main Menu” that was created by Helen Feddema.

I tested the query and the report via Access window and it runs properly but gives the Error 2001 message when I try to run it via Main Menu and I am not sure why it refuses to run properly when trying to open it via Main Menu add-in but I can open other two reports with similiar set up just fine?

Code for the OnClick property is attached, let me know if I need to supply more information.

Private Sub cmdReports_Click()
‘Created by Helen Feddema 9-2-2003
‘Last modified 9-2-2003

On Error GoTo ErrorHandler

Dim strReportName As String
Dim strRecordSource As String

If Nz(Me![cboReports]) “” Then
strReportName = Me![cboReports]
strRecordSource = Me![cboReports].Column(2)
If Nz(DCount(“*”, strRecordSource)) > 0 Then
If Me![fraReportMode] = 1 Then
DoCmd.OpenReport ReportName:=strReportName, view:=acPreview
Me.Visible = False
ElseIf Me![fraReportMode] = 2 Then
DoCmd.OpenReport ReportName:=strReportName, view:=acNormal
End If
Else
MsgBox “No records for this report”
GoTo ErrorHandlerExit
End If
Else
Me![cboReports].SetFocus
Me![cboReports].Dropdown
End If

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & _
Err.Description
Resume ErrorHandlerExit

End Sub

Reply | Quote

Replies

This code checks whether the report has any data, and if it thinks there is no data it does not open the report.

The combo box on the menu specifies the Recordsource of the report in column 2.

For this to work properly, the Recordsource of you report needs to be a table or a saved query, not just an sql statement.

The data for the combo box is held in the table tlkpReports – have a look in that table and see what it shows as the recordsource of this report.

Reply | Quote

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

Reply | Quote

I am puzled by : Between FromDate() and ToDate()

Have you defined some custom functions ?

I would expect to use:

Between forms![frmMainMenu]![FromDate] and forms![frmMainMenu]![toDate]

with frmMainMenu replaced by the actual name of your main form.

Is the problem that your report has no data, or is that the line of code :

If Nz(DCount(“*”, strRecordSource)) > 0 Then

is getting it wrong? Try commenting out this line and its associated ‘else, end if’ and see what happens?

Reply | Quote

Yeah, it is custom function that I didn’t paste. I’ll paste the entire code so you can see how it works. I am at work right now (The database is an “on the side” work), but will paste the entire code tonight when I get home.

Brent

Reply | Quote

I have got a copy of this whole menu system now, and the custom functions, and can see how it works. Needless to say, it all works properly in Helen’s examples. There are quite a few components that have to be correct for it all to work properly.

Is it only one of the reports that gives problems? Is there any code in that report that is different from the others?
Edited a bit later – I notice that you have already said two other reports open ok. Do they also use the dates as parameters?

I notice that the custom functions Fromdate() and ToDate() don’t actually take the query parameters from the menu form – instead they take them from the table tblInfo which the Menu form is bound to. So when you open the report from the db window, the query looks at this table.

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

I am not sure what to suggest.

If you remove the date range criteria from the query does it work OK then.

If that fixes it, it confirms where the problem is.

You could also try replacing the custom functions with the more usual forms references to see what happens.

Reply | Quote

I am not sure what to suggest.

If you remove the date range criteria from the query does it work OK then.

If that fixes it, it confirms where the problem is.

You could also try replacing the custom functions with the more usual forms references to see what happens.

Reply | Quote

I have got a copy of this whole menu system now, and the custom functions, and can see how it works. Needless to say, it all works properly in Helen’s examples. There are quite a few components that have to be correct for it all to work properly.

Is it only one of the reports that gives problems? Is there any code in that report that is different from the others?
Edited a bit later – I notice that you have already said two other reports open ok. Do they also use the dates as parameters?

I notice that the custom functions Fromdate() and ToDate() don’t actually take the query parameters from the menu form – instead they take them from the table tblInfo which the Menu form is bound to. So when you open the report from the db window, the query looks at this table.

Reply | Quote

Yeah, it is custom function that I didn’t paste. I’ll paste the entire code so you can see how it works. I am at work right now (The database is an “on the side” work), but will paste the entire code tonight when I get home.

Brent

Reply | Quote

I am puzled by : Between FromDate() and ToDate()

Have you defined some custom functions ?

I would expect to use:

Between forms![frmMainMenu]![FromDate] and forms![frmMainMenu]![toDate]

with frmMainMenu replaced by the actual name of your main form.

Is the problem that your report has no data, or is that the line of code :

If Nz(DCount(“*”, strRecordSource)) > 0 Then

is getting it wrong? Try commenting out this line and its associated ‘else, end if’ and see what happens?

Reply | Quote

To find out where the error occurs, temporarily make the line

On Error GoTo ErrorHandler

into a comment by inserting an apostrophe ‘ before it. When you click the command button, you’ll be given a choice: End, Debug, etc. Click Debug and see which line is highlighted. You can also set a breakpoint on the line

Private Sub cmdReports_Click()

(click inside it and press F9). When you click the command button, the code will pause; you can single step through it by pressing F8, and you can inspect the value of variables by hovering the mouse pointer above them.

Reply | Quote

Thanks. I’ll try that later tonight.

Brent

Reply | Quote

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.

Reply | Quote

This line is trying to work out whether there are going to be any records in the report before it opens it.

(It is doing what is normally done by the “on no data” event, but before the report is even opened.)

DCount(“*”, strRecordSource) counts the number of records in the query strRecordSource.

This whole expression is wrapped inside the NZ function that deals with the Dcount funcion returning a null value.

Try replacing Nz(DCount(“*”, strRecordSource)) with

Nz(DCount(“*”, strRecordSource),0)

The seond part of a NZ function is the value you want returned when it finds a Null. The default is a zero length string. Putting in the zero overrides that.

Reply | Quote

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!

Reply | Quote

Are you saying that the query had another parameter prompt in it?

That is definetely the cause of the problem. I can recreate the problem knowing that.

Dcount(“*”, strRecordsource) can’t do a count because it can’t run the query.

Your solution of putting the name on the menu is a good one.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Are you saying that the query had another parameter prompt in it?

That is definetely the cause of the problem. I can recreate the problem knowing that.

Dcount(“*”, strRecordsource) can’t do a count because it can’t run the query.

Your solution of putting the name on the menu is a good one.

Reply | Quote

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!

Reply | Quote

This line is trying to work out whether there are going to be any records in the report before it opens it.

(It is doing what is normally done by the “on no data” event, but before the report is even opened.)

DCount(“*”, strRecordSource) counts the number of records in the query strRecordSource.

This whole expression is wrapped inside the NZ function that deals with the Dcount funcion returning a null value.

Try replacing Nz(DCount(“*”, strRecordSource)) with

Nz(DCount(“*”, strRecordSource),0)

The seond part of a NZ function is the value you want returned when it finds a Null. The default is a zero length string. Putting in the zero overrides that.

Reply | Quote

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.

Reply | Quote

Thanks. I’ll try that later tonight.

Brent

Reply | Quote

To find out where the error occurs, temporarily make the line

On Error GoTo ErrorHandler

into a comment by inserting an apostrophe ‘ before it. When you click the command button, you’ll be given a choice: End, Debug, etc. Click Debug and see which line is highlighted. You can also set a breakpoint on the line

Private Sub cmdReports_Click()

(click inside it and press F9). When you click the command button, the code will pause; you can single step through it by pressing F8, and you can inspect the value of variables by hovering the mouse pointer above them.

Reply | Quote

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

Reply | Quote