Macro automatically runs report

**yes sir** · 08-23-2010, 10:09 PM

Hello yet again,

I was wondering if it were to possible to configure a macro that would enable me to run a report with just the click of one button. I would also want the report to already be formatted the way I want and for any logos to be added as well.

Is this even remotely possible?

**split_atom18** · 08-24-2010, 09:33 AM

Yes, it is possible.
You can tie a hotkey to a macro, and have it run vba code.
I have a macro called AUTOKEYS
In that macro:

MacroName {F10}
Action OpenReport

Now instead of run report you could have it run code. And through vba your possibilities are extremely large.

Hope this helps,

Dan

**yes sir** · 08-24-2010, 12:02 PM

Originally Posted by split_atom18

Yes, it is possible.
You can tie a hotkey to a macro, and have it run vba code.
I have a macro called AUTOKEYS
In that macro:

MacroName {F10}
Action OpenReport

Now instead of run report you could have it run code. And through vba your possibilities are extremely large.

Hope this helps,

Dan

Thanks Dan, I'll give it a shot. If I have more questions then I shall return.

**yes sir** · 08-24-2010, 04:51 PM

Dan or anyone else who can help,

I guess the VBA coding might be a little out of my league without assistance.

Just to be more exact, here is what I want: I currently have a Form that lists every record. For the purposes of this database (for a professional basketball scout), he wants to be able to sort and filter his information however he pleases, and then wants an easy way to turn that data into a neat Report. So again, I have the Form set that lists every record and I have added a button called "Generate Report." Currently all this button will do is open up a Report I have created based off of this form. However, the Report doesn't update based on any kind of sorting or filtering that is done on the Form.

I would love to be able to set this button, once it gets clicked, to Generate a Report exactly the way the data is laid out in the Form (after any sorting or filtering of any kind). And I would love if that Report was in the precise format that my current Report is in that is linked to the Form.

If anyone could help, that would be great. Thanks!

**yes sir** · 08-24-2010, 06:32 PM

I'm not sure if this is possible, but can I add a hyperlink to my column titles on my report and once you click it runs a macro that sorts the column?

That is something I may be able to pull off by myself.

**yes sir** · 08-25-2010, 02:40 AM

I think this link kind of explains what I want finished.

"http://msdn.microsoft.com/en-us/library/bb243780%28office.12%29.aspx"

The thing is I don't quite understand how to make this happen.

**split_atom18** · 08-25-2010, 11:44 AM

This can be extremely complex.

I don't really use filters anymore, cause it pulls the full datasheet and slows things down.

I change the recordsource of the form or report with vba/SQL.

Here is a quick example:

I have "Dan" typed in the searchbox and Select my Active Contacts filter.

It changes my recordsource from:

Private Sub ApplyAdvancedFilter()
    Dim strSQL As String

On Error GoTo HandleError
    
    strSQL = "SELECT * FROM qryContactsExtended"
    
    If Not IsNull(Me.SearchBox) And Not IsNull(Me.cboFilters) Then
        strSQL = strSQL & " Where (ContactID Like '*" & Me.SearchBox & "*'" & _
                        " Or LastName Like '*" & Me.SearchBox & "*'" & _
                        " Or FirstName Like '*" & Me.SearchBox & "*'" & _
                        " Or [E-mailAddress] Like '*" & Me.SearchBox & "*'" & _
                        " Or Company Like '*" & Me.SearchBox & "*'" & _
                        " Or JobTitle Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingZip Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCity Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingState Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCountry Like '*" & Me.SearchBox & "*'" & _
                        " Or [File As] Like '*" & Me.SearchBox & "*'" & _
                        " Or [Contact Name] Like '*" & Me.SearchBox & "*'" & _
                        " Or BusinessPhone Like '*" & Me.SearchBox & "*'" & _
                        " Or HomePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or MobilePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or FaxNumber Like '*" & Me.SearchBox & "*'" & _
                        " Or Notes Like '*" & Me.SearchBox & "*'" & _
                        " Or AccountNumber Like '*" & Me.SearchBox & "*')" & _
                        " AND " & LookupListFormFilter(Me.cboFilters)
        Me.cmdShowAll.Enabled = True
    ElseIf Not IsNull(Me.SearchBox) Then
        strSQL = strSQL & " Where ContactID Like '*" & Me.SearchBox & "*'" & _
                        " Or LastName Like '*" & Me.SearchBox & "*'" & _
                        " Or FirstName Like '*" & Me.SearchBox & "*'" & _
                        " Or [E-mailAddress] Like '*" & Me.SearchBox & "*'" & _
                        " Or Company Like '*" & Me.SearchBox & "*'" & _
                        " Or JobTitle Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingZip Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCity Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingState Like '*" & Me.SearchBox & "*'" & _
                        " Or BillingCountry Like '*" & Me.SearchBox & "*'" & _
                        " Or [File As] Like '*" & Me.SearchBox & "*'" & _
                        " Or [Contact Name] Like '*" & Me.SearchBox & "*'" & _
                        " Or BusinessPhone Like '*" & Me.SearchBox & "*'" & _
                        " Or HomePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or MobilePhone Like '*" & Me.SearchBox & "*'" & _
                        " Or FaxNumber Like '*" & Me.SearchBox & "*'" & _
                        " Or Notes Like '*" & Me.SearchBox & "*'" & _
                        " Or AccountNumber Like '*" & Me.SearchBox & "*'"
        Me.cmdShowAll.Enabled = True
    ElseIf Not IsNull(Me.cboFilters) Then
        strSQL = strSQL & " Where " & LookupListFormFilter(Me.cboFilters) 'This looks up the string from my filters management form/dataset.
        Me.cmdShowAll.Enabled = True
    End If

ApplyFilter:
    Me.RecordSource = strSQL
    Me.Requery
    
ExitHere:
    Exit Sub
    
HandleError:
    MsgBox Err.Description
    Resume ExitHere
    
End Sub

It would return any record that is not marked as inactive and has Dan someone in one of the searched fields, ordered by ContactID(you can't see this part of the statement it was brought in via the lookuppart.
Not sure this will help you,

Dan

P.S. It can get extremely complex if you don't know vba/sql very well.

**yes sir** · 08-25-2010, 12:36 PM

Here are some screenshots of what I'm working with, so basically I would like for whatever sorting or filtering is done on the form would be replicated on my report when the "Generate Report" button is clicked. Currently, the report is in the original tab order when I first made that report. I'm sure this would be very tricky especially for me.

And then again, I'm not even sure this is possible.

**split_atom18** · 08-25-2010, 05:38 PM

One possible simpler solution would be to pass the current forms filter property to the report and apply it there. This isn't something that I use, so I didn't test it, but here are the ideas.

Filter Property
http://office.microsoft.com/en-us/access-help/filter-property-HA001232736.aspx

OpenReport Method
http://msdn.microsoft.com/en-us/libr...ice.12%29.aspx

The only issue is passing only the filter will not do in itself what you want. You have to pass the OrderBy property also and add it to the report.

Hope this helps,

Dan

**yes sir** · 08-26-2010, 04:15 PM

Dan,

Thanks for your help, but I think this may be way out of my league and too complicated for me to figure out. I'm not even quite sure what is meant when you refer to Filter Property. I feel like I am close, but the VBA code is just way too much for me to handle.

I guess I'm just going to have to sort and filter the Reports by using the Report's layout view. Which this way works fine and dandy, but if it would be awesome if the Report would just automatically be sorted and filtered based on how the record source, whether it is a query or a form, is sorted and filtered.