Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Close queries, run Function Macro (Access2000-03)

    Every time I am ruinning Report I am running Macro first by click of the button.
    Queries are opening tables and I need it to be behind scenes. I don't want to see all tables open.

    After all query ran I need to run a Function or Sub...some code to open up Report and give it a value that was selected in combo box. What should I choose in Macro menu?

    Thanks,
    good night.

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

    Re: Close queries, run Function Macro (Access2000-03)

    Please try to explain this more clearly.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    Oh, ok...
    I am running Macro and query I am opening are opening in Datasheet. So when Macro is done running queries - I have to go and manually close 10 opened Datasheet views.
    I think there is a way to avoid it but I do not remember how.
    I've tried to Quit and all but anything I do closes Access for me. And I only need to close Macro.

    Second thing is that Macro after all the query ran should be opening Module with a code that says 'run report with parameter selected in a combo box of the form'....

    Then after Report should get displayd and that is it!

    Was I any clearer? Sorry.

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

    Re: Close queries, run Function Macro (Access2000-03)

    Why are you opening queries if you want to open a report? I don't understand the need for that.

    If you want to open the report with a parameter from a combo box, put a command button on the same form as the combo box, and create an event procedure for the On Click event of the combo box:

    Private Sub cmdReport_Click()
    Dim strWhere As String

    On Error GoTo ErrHandler

    If Not IsNull(Me.ComboBoxName) Then
    strWhere = "FieldName = " & Me.ComboBoxName
    End If
    DoCmd.OpenReport ReportName:="rptMyReport", View:=acViewPreview, WhereCondition:=strWhere
    Exit Sub

    ErrHandler:
    ' Ignore error 2501 (report canceled)
    If Not Err = 2501 Then
    MsgBox Err.Description, vbExclamation
    End If
    End Sub

    In this code, cmdReport is the name of the command button.
    ComboBoxName is the name of the combo box.
    FieldName is the name of the field on which you want to filter.
    rptMyReport is the name of the report.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    Yes I did as you said...a while ago.
    Now I neet to insure that all of mine Make Table queries are populating tables with a fresh data prior to report running.
    So I had decided I am gonna write Macro to run all of my queries, append freshly delivered data and AFTER that I will run the code you kindly shared with me.

    However my queries are not running 'on the back' - they pop open and staying open in Datasheet view.
    I have to then go and manually close them all!
    :-)

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

    Re: Close queries, run Function Macro (Access2000-03)

    A make table query or append query doesn't stay open if you run it - it performs its action and that's it.
    A select query does stay open. So don't open select queries in your macro.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    I knew that....NOT! :-))

    Now I need to open your code.
    It is a Sub...how do I open it in Macro?

    Thanks

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

    Re: Close queries, run Function Macro (Access2000-03)

    Why not use VBA? It is much easier to maintain than macros.

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

    Re: Close queries, run Function Macro (Access2000-03)

    DoCmd.RunQuery doesn't exist, it's DoCmd.OpenQuery.

    If you want a form to be opened automatically with the database, you don't need a macro or code for that. You can specify the startup form in Tools | Startup...
    In this dialog, you can also specify whether the database window is visible, and several other options.
    If you want to bypass the startup options, hold down the Shift key while opening the database until the database window appears.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    OK, I did wrote DoCmd.OpenQuery "" and it works.

    Also can I have user to see only form and not a database?
    Should I save it as .mlb or something?

    Thanks, I am almost ready to be done, thanks to your help.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    All done except I can't see where is the option to have a form open a Full Screen view. Thanks for everything, Hans.

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

    Re: Close queries, run Function Macro (Access2000-03)

    You can't set that in the startup options. You have to put code in the On Open or On Load event of the form, for example:

    Private Function Form_Load()
    DoCmd.Maximize
    End Sub

  13. #13
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close queries, run Function Macro (Access2000-03)

    That easy huh? Thanks :-))

Posting Permissions

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