Using forms to print reports based off of queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a couple of reports that pull their information off of a single query
and want to know if a form can add a filter to the query results for the
reports.

i.e. I have a sales query and I have a form that the user selects the month
and it will print out the sales report for January.
 
Rick gave you one way, another way will be
when you give the command to print you can add the filter
' incase the field is number type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= " &
me.[MyFieldOnform]

' incase the field is text type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= '" &
me.[MyFieldOnform] & "'"
 
That way you can print the same report for several of forms, it doesnt limit
you to one form.
 
Thank you,

Is there a way to create a control that would export the results of a query
into Excel?

Thanks again in advance,
Keith

Ofer said:
Rick gave you one way, another way will be
when you give the command to print you can add the filter
' incase the field is number type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= " &
me.[MyFieldOnform]

' incase the field is text type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= '" &
me.[MyFieldOnform] & "'"

Keith Meier said:
I have a couple of reports that pull their information off of a single query
and want to know if a form can add a filter to the query results for the
reports.

i.e. I have a sales query and I have a form that the user selects the month
and it will print out the sales report for January.
Click to expand...
Click to expand...
 
Yes, you can choose it in your toolbar.
right click on the tool bar, choose customise, commands, choose tools
there youll see few commands, one of them is anlayse with excel, or word and
more
Keith Meier said:
Thank you,

Is there a way to create a control that would export the results of a query
into Excel?

Thanks again in advance,
Keith

Ofer said:
Rick gave you one way, another way will be
when you give the command to print you can add the filter
' incase the field is number type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= " &
me.[MyFieldOnform]

' incase the field is text type
docmd.OpenReport "ReportName",acViewPreview,,"[MyFieldInQuery]= '" &
me.[MyFieldOnform] & "'"

Keith Meier said:
I have a couple of reports that pull their information off of a single query
and want to know if a form can add a filter to the query results for the
reports.

i.e. I have a sales query and I have a form that the user selects the month
and it will print out the sales report for January.
Click to expand...
Click to expand...
Click to expand...
 
I was hoping to integrate the export into the command that adds the filter to
the query...

i.e. click export sales report button in form, it will create the query and
the next screen the user sees is the window to save the file.
 
create an empty query called GlobalQuery, now you can try that code

Function aaaaa()
Dim DBS As Database
Dim rst As Recordset, SqlStr As String

Set DBS = CodeDb
SqlStr = "SELECT * FROM MyTable WHERE " & TheFilterFromTheReport
DBS.QueryDefs("GlobalQuery").SQL = SqlStr

' Transfer the query we created to c: drive
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "GlobalQuery",
"c:\GlobalQuery.xls"
' The location to save to can be a parameter pass to the function.
End Function
 
Back
Top