Results 1 to 10 of 10
  1. #1
    Join Date
    May 2008
    Posts
    18

    Unanswered: Input paramters on macros

    I have set up a macro to run a batch of reports in my db. However each report requires a set of dates to be input. As the dates for each report will be the same I am wondering if there is a way to just input the dates once to cover all the reports?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi araford

    I have no idea. Why? Because I *never*, *ever* use macros. I consider them evil and wish they were deprecated. Others on here might not feel quite as strongly but I doubt there are many, if any, that make any serious use of macros.

    Everything you can do in a macro you can do very simply in VBA plus you can do so much more in code.

    Is porting your macros to code an option?

  3. #3
    Join Date
    May 2008
    Posts
    18
    I suppose they could I am trying to use code as much as possible but I am still somewhat of a beginner in that field. All the macro is doing is opening and printing a report, then the next one etc.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DoCmd.OpenReport "myReportName", acViewNormal
    This is from memory - look up the DoCmd.Open method in the VBA help or on the web to check syntax.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Go to your macro, right click it and chose "Save As Module" - this will then reveal the exact code it uses to you for your customisation
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    Go to your macro, right click it and chose "Save As Module" - this will then reveal the exact code it uses to you for your customisation
    That's cool - I didn't know that - hardlyt surprising as I don't use macros - have I ever mentioned that?

    All they need now is a message box when you start to create macros "I will give you the VBA code and put it in a module for you - are you still freaking sure you want to create a macro?" and one button: "No"

  7. #7
    Join Date
    May 2008
    Posts
    18
    georgev thanks for that saved me a load of typing and will be a great shortcut for future. I have now just added the code to a command button so how would i just make it respond to the one parameter, at present i have just set it to ask for a lower and upper date in the query criteria. The code is

    Private Sub Print_Board_Reports_Click()
    On Error GoTo Print_Board_Reports_Err

    DoCmd.OpenReport "Delivery Happy with service", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Collection Happy with service", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Feedback area delivery", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Feedback area collection", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Delivery feedback per depot", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Collection feedback per depot", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Delivery summary by depot", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Collection summary by depot", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Delivery summary by Area", acViewNormal, "", "", acNormal
    DoCmd.OpenReport "Collection summary by area", acViewNormal, "", "", acNormal


    Print_Board_Reports_Exit:
    Exit Function

    Print_Board_Reports_Err:
    MsgBox Error$
    Resume Print_Board_Reports_Exit

    End Sub

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The simplest solution (not necessarily the best - difficult to know based on your criteria) is to have some text boxes\ combo boxes\ calendar controls (the latter is probably a bit tricky for you right now) on a form and get the user to enter their dates there.

    Then have the queries pick up the values of those controls. This is what the SQL would look a bit like:
    Code:
    WHERE mydate BETWEEN Forms!myForm!startDate AND Forms!myForm!endDate

  9. #9
    Join Date
    May 2008
    Posts
    18
    Thanks for the help to date guys I am now trying this on one line having created a form to fill in the dates. The code reads

    DoCmd.OpenReport "Delivery Happy with service", acViewNormal, "", "WHERE Date BETWEEN Forms!Date Input!LowerDate AND Forms!Date Input!UpperDate", acNormal

    I get a run time 3075 error why would this be?

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The line "WHERE..." should be in the query. If you want to pass a filter to the report then the syntax is different

Posting Permissions

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