Results 1 to 9 of 9

Thread: If Statement to Run Macro

  1. #1
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default If Statement to Run Macro

    Kind of an If statement.

    I have an If statement that depending on the results will run one or another Macro. But I get a Debug error when I try to run it. The fist is the If statement that errors the second is one of the Macro's (which runs fine on its own - its in a module)

    Code:
    Private Sub Frame244_Click()
    Select Case Frame244
        Case Is = 1
    'run Macro
            updateQuery1
        Case Is = 2
    'run Macro
            updateQuery2
    End Select
    End Sub
    Code:
    Private Sub updateQuery1()
        Dim db As DAO.Database
        Set db = CurrentDb
        Dim qdf As DAO.QueryDef
        
        Set qdf = db.QueryDefs("qry_NIS_TSL")
        qdf.SQL = "SELECT tbl_NIS_TSL.ID_NISTSL, tbl_NIS_TSL.VendorName, tbl_NIS_TSL.VendorID, tbl_NIS_TSL.CityName, tbl_NIS_TSL.MailState, tbl_NIS_TSL.PostalCode, tbl_NIS_TSL.CountryCode, tbl_NIS_TSL.Vendor_Status, tbl_NIS_TSL.CommType, tbl_NIS_TSL.Debarred, tbl_NIS_TSL.Approval_Status, tbl_NIS_TSL.TSL_Trend, tbl_NIS_TSL.Complexity_Low, tbl_NIS_TSL.Complexity_Medium, tbl_NIS_TSL.Complexity_High, tbl_NIS_TSL.Volume_Low, tbl_NIS_TSL.Volume_Medium, tbl_NIS_TSL.Volume_High, tbl_NIS_TSL.Responsiveness_Rating, tbl_NIS_TSL.RTV_Support, tbl_NIS_TSL.Failure_Analysis, tbl_NIS_TSL.Other_Capabilities, tbl_NIS_TSL.NumberOf_Assemblies, tbl_NIS_TSL.Materials, tbl_NIS_TSL.Restrictions, tbl_NIS_TSL.Comments, tbl_NIS_TSL.CreatedBy, tbl_NIS_TSL.CreatedDate " & _
        "FROM tbl_NIS_TSL INNER JOIN SubQry_NIS_TSL ON (tbl_NIS_TSL.VendorName = SubQry_NIS_TSL.VendorName) AND (tbl_NIS_TSL.CommType = SubQry_NIS_TSL.CommType) AND (tbl_NIS_TSL.CreatedDate = SubQry_NIS_TSL.MaxOfCreatedDate)" & _
        "WHERE (((tbl_NIS_TSL.VendorName) Like [Forms]![frm_NIS_TSL]![Combo227]) And ((tbl_NIS_TSL.CommType) Like [Forms]![frm_NIS_TSL]![Combo232]) And ((tbl_NIS_TSL.Debarred) Like [Forms]![frm_NIS_TSL]![Combo229]) And ((tbl_NIS_TSL.Approval_Status) Like [Forms]![frm_NIS_TSL]![Combo234]))ORDER BY tbl_NIS_TSL.VendorName, tbl_NIS_TSL.CreatedDate DESC;"
    End Sub
    thanks for the help
    Last edited by gheyman; Oct 16th, 2019 at 09:05 AM.
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: If Statement to Run Macro

    If the two code blocks you posted above are in DIFFERENT modules, then you need to remove the word "Private" from:
    Code:
    Private Sub updateQuery1()
    Marking it as "Private" means that it cannot be seen by anything outside of that module it is contained in. You cannot call things that you cannot see.

    Sometimes, people will replace the word "Private" with "Public", but I think if you leave the word off altogether, the default is "Public".
    Last edited by Joe4; Oct 16th, 2019 at 09:35 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Sep 2012
    Posts
    131
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Quote Originally Posted by Joe4 View Post
    Marking it as "Private" means that it cannot be seen by anything outside of that module it is contained in. You cannot call things that you cannot see.
    You can call a private macro from another module by using "application.run".

    Application.Run "ModuleName.MacroName"
    Last edited by petertenthije; Oct 16th, 2019 at 10:11 AM.

  4. #4
    Board Regular
    Join Date
    Jul 2010
    Posts
    464
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Quote Originally Posted by petertenthije View Post
    You can call a private macro from another module by using "application.run".

    Application.Run "ModuleName.MacroName"
    Think this is referring to Excel, Macros are a different object in Access, furthermore, I don't think this works with Access subroutines.
    Last edited by stumac; Oct 16th, 2019 at 10:24 AM.

  5. #5
    Board Regular gheyman's Avatar
    Join Date
    Nov 2005
    Location
    Orlando, FL USA
    Posts
    1,665
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Thank You!

    Is there a way to set the Frame value to 1 other than using the default value?
    G Heyman
    Northrop Grumman - Laser Systems'
    Apopka, FL

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    51,060
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Is there a way to set the Frame value to 1 other than using the default value?
    Yes, go into the Properties of the Frame, and you can rename it (as long as you choose a name that isn't already being used on that Form).
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    667
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Quote Originally Posted by stumac View Post
    Think this is referring to Excel, Macros are a different object in Access, furthermore, I don't think this works with Access subroutines.
    No you can do it, do not even need the Application.Run, just use ModuleName.SuborFunctionName if the code is marked Private.

    HTH
    Office 2007
    Access novice. Sometimes trying to give something back

  8. #8
    Board Regular
    Join Date
    Jul 2010
    Posts
    464
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Quote Originally Posted by welshgasman View Post
    No you can do it, do not even need the Application.Run, just use ModuleName.SuborFunctionName if the code is marked Private.

    HTH

    In Access 2016 this doesn't work. I have tested it as per the OP's scenario, without the private statement it works fine, with the private statement the following error is returned.

    ---------------------------
    Microsoft Visual Basic for Applications
    ---------------------------
    Compile error:


    Method or data member not found
    ---------------------------
    OK Help
    ---------------------------

  9. #9
    Board Regular
    Join Date
    May 2013
    Posts
    667
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Access: If Statement to Run Macro

    Hmm, strange.

    I did test in 2007 before I posted, as I was keen to know.
    Good to know though. Thank you.
    Office 2007
    Access novice. Sometimes trying to give something back

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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