Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    You canceled previous operation error with report

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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.
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    Man. You replied so quickly that I didn't have a chance to edit my original question. [img]/forums/images/smilies/smile.gif[/img]

    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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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?
    Regards
    John



  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: You canceled previous operation error with rep

    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.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    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

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    Thanks. I'll try that later tonight.

    Brent

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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.
    Regards
    John



  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    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? <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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.
    Regards
    John



  11. #11
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    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 <font color=red> **This line was highlighted in yellow and the strRecordSource is showing qryLogNotes which is correct.**</font color=red>
    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! [img]/forums/images/smilies/sad.gif[/img]

    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.

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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.
    Regards
    John



  13. #13
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    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. [img]/forums/images/smilies/smile.gif[/img]

    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!

  14. #14
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: You canceled previous operation error with rep

    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.
    Regards
    John



  15. #15
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: You canceled previous operation error with rep

    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. [img]/forums/images/smilies/smile.gif[/img]

    Thanks so much for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •