Results 1 to 6 of 6
  1. #1
    enquiries is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Location
    Samoa
    Posts
    69

    Cancel Event in macro triggering module that checks if all forms are loaded

    Hi there,

    I'm trying to add a Cancel Event to my macro that opens up 4 reports where the last 3 reports will take their criteria from the ID entered in when prompted with the first report. The problem is, I chose to cancel the event - I'll have to cancel the prompt for the next 3 reports which is why I'm trying to create a Cancel Event, which will just cancel the progression of opening the next 3 reports. However I have a module that checks that forms are open and I'm trying to work around it - any suggestions please - I didn't code the module, it was part of a procedure in creating a filter for a form Below is the code of the module:

    Function IsLoaded(ByVal strFormName As String) As Boolean
    Dim oAccessObject As AccessObject


    Set oAccessObject = CurrentProject.AllForms(strFormName)

    If oAccessObject.IsLoaded Then
    If oAccessObject.CurrentView <> acCurViewDesign Then
    IsLoaded = True
    End If
    End If

    End Function

    It comes up with a "debug error" when I put in Cancel Event and highlights the
    Set oAccessObject = CurrentProject.AllForms(strFormName)
    part.

    Any help greatly appreciated thanks

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    When you say Cancel, why do you need to? You try to open and then change your mind? You just want to open a specific Report? Then why not just do that by itself?

  3. #3
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    What exactly are you trying to Cancel, and under what circumstances? The code you show is a Function, not an event procedure - functions can't be "canceled" - only "exited" as part of the code.

    Can you give more information as to what you want to do?

    John

  4. #4
    enquiries is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2013
    Location
    Samoa
    Posts
    69
    Hi, thanks for replying - sorry for the late one.

    Yes, I'm trying to cancel as in I change my mind to open the report. The function is completely irrelevant to what I'm trying to cancel as in I put it there for a different form (with a filter) but I think the code is a general checker of "if all the forms are open or something similar" and it's giving me an error when I try to cancel opening the report.

    What I have is a switchboard that I'm putting a macro on, in the macro I have 4 actions to open 4 reports which are all based on underlying queries, the first report is based on a query that will prompt you with a criteria input "Please enter Applicant ID" it works well if I enter the criteria and it will open the three reports following but when I select cancel (this is without the CancelEvent macro) I will have to cancel for the next 3 reports, as the underlying queries to those 3 reports are taking the "Applicant ID" entered in the combo in the first report that was opened.

    So in an effort to stop it from prompting cancel 4 times, I want to cancel the progression of opening the next 3 reports if I cancel on the criteria prompt for the first one hence trying to put in a CancelEvent as in Is NotLoaded("reportName") Action: CancelEvent but then it'll come up with an error and when I select debug it will show that line in the above function.

    Hope that makes sense Thanks very much for your time & help.

  5. #5
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383

    Cancel Event

    Sorry, I don't use macros. But you need to code it so ONLY the 1st Report Opens and then Cancel the Code. Edit to suit your needs.
    If vbNo = MsgBox("Do you Really want to Preview this Report?", _
    vbYesNo + vbDefaultButton2 + vbQuestion, _
    "Confirm Change") Then
    Cancel = True


    Dim strReportName As String
    Dim strCriteria As String

    strReportName = "MyReportNameHere"
    strCriteria = "[MyPrimaryKeyHere] = " & Me![MyPrimaryKeyHere]
    DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
    Exit Sub
    End If

    HTH
    Last edited by burrina; 05-08-2014 at 09:31 PM. Reason: Mistake

  6. #6
    John_G is offline VIP
    Windows XP Access 2003
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Hi -

    I don't use macros either, and this is one of the reasons why!

    Try this:

    First, take the parameters out of the report queries so that they won't prompt you

    Then, in a VBA code module (On Click event in the switchboard, maybe) do this:

    1. Use the inputbox function to query for the Applican ID and assign it to a local variable
    2. Check the value of that variable to see if the request was canceled. That could be by entering 0, or nothing (Null) - it's up to you. You could also check that the entered ID is valid if you want.
    3. If the request was canceled or an invalid ID was entered, exit the procedure
    4. For a valid ID, run each of the four reports, using the WhereCondition argument:

    DoCmd.OpenReport "Report1", acViewNormal, , "Applicant_ID = " & ApplicantID

    acViewNormal prints the report immediately
    Applicant_ID is the field name for the Applicant ID in the query
    ApplicantID is the name of the variable containing the requested Applicant (see InputBox above)

    This is a general idea of what you want to do, I think.

    HTH

    John

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Malfunctioning forms when loaded from switchboard
    By justphilip2003 in forum Access
    Replies: 4
    Last Post: 04-26-2013, 07:43 PM
  2. Triggering a form event from a report
    By Paul H in forum Forms
    Replies: 2
    Last Post: 10-12-2012, 09:48 AM
  3. Replies: 2
    Last Post: 05-05-2012, 02:34 AM
  4. Cancel form navigation event
    By tuna in forum Forms
    Replies: 3
    Last Post: 08-15-2010, 01:46 PM
  5. Cancel Selection Event for ListBox
    By is49460 in forum Forms
    Replies: 2
    Last Post: 08-04-2010, 05:53 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums