How to create a report template

[Johanb26]

HI guys,Is it possible to create a report template ?At the moment customer details are entered onto two different forms: One form for customer details and another form for customer assets.I created a custom report and it includes customer details and showing the assets for each customer.But when creating a query to show only customer A and all of his assets, I have to create a new report for each query or customer. What I want to do is set the report I created to be the standard report when selecting different customers and their assets can this be done ?

[ssanfu]

But when creating a query to show only customer A and all of his assets

How did you create the query for just Customer A?

[Johanb26]

How did you create the query for just Customer A?

I designed the report to include fields from both of my input forms.

What I want to do is display the report only for certain customers and not for all of the customers. That is why I want to use a report template so that I can run an SQL query to only select certain fields and only those fields selected must be displayed in the report.

IS this possible to do or do I need to design a new report each time ?

[ssanfu]

IS this possible to do or do I need to design a new report each time ?

Yes this is possible. It would be overwhelming to have to create 2000 reports!!

You use a query for your report? HOW did you limit it to only CustomerA?
Did you set criteria to select only Customer A? You would do the same thing, except you would reference a form to get the customerID.

Would you post the SQL of the report query?

[Johanb26]

Yes this is possible. It would be overwhelming to have to create 2000 reports!!

You use a query for your report? HOW did you limit it to only CustomerA?
Did you set criteria to select only Customer A? You would do the same thing, except you would reference a form to get the customerID.

Would you post the SQL of the report query?

I am at home now, but its basically something like:

SELECT cust_ID.customer, cust_name.customer, asset_id.assets, asset_location
FROM customer INNER JOIN assets
WHERE cust_name = 'name';

Something similar to the above only with a lot more columns selected etc..

[ssanfu]

SELECT cust_ID.customer, cust_name.customer, asset_id.assets, asset_location
FROM customer INNER JOIN assets
WHERE cust_name = 'name';

FYI, "Name" is a reserved word in Access and shouldn't be used for object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

The customer name is also a bad choice to use to select customers - what happens when you have two customers named "John Smith"?

Actually, you should have (at least) two fields for the customer name - FName and LName. It is easier to combine the names than split one field into First and Last names.
.
A better field for selecting a customer would be the cust_ID field. The SQL would look something like:

SELECT customer.cust_ID, customer.cust_name, assets.asset_id, assets.asset_location
FROM customer INNER JOIN assets
WHERE customer.cust_ID =  2;

Yes it is hard to know that Customer A's ID is 2.
So create a form, add a combo box and a button.
Let's name:
the form "frmSelect"
the combo box "cboCustomer"
the button "cmdPreview"


The recordsource for the combo box "cboCustomer" would be

SELECT customer.cust_ID, customer.cust_name
FROM customer 
ORDER BY customer.cust_name

Set the column count to 2
Set the column widths to 0,2


The code behind the button for the click event would be something like:
(to preview)

Private Sub cmdPreview_Click()
    Dim stDocName As String

    stDocName = "Report1"
    DoCmd.OpenReport stDocName, acPreview
End Sub

Change the report name (in red) to your report name.


Edit the report query to look like: (plus other columns)

SELECT customer.cust_ID, customer.cust_name, assets.asset_id, assets.asset_location
FROM customer INNER JOIN assets
WHERE customer.cust_ID = [forms]![frmSelect].[cboCustomer];

Open the form "frmSelect", select a customer using the combo box and click the button to view the report.
Close the report, select a different customer and click the button.

This is the basic method to select and view/print a customer.

[Johanb26]

Thanks a lot for the help!

Just to point out the WHERE clause in the SQL statement I made was just an example and not the actual statement. But your advice is still valuable.

Thank you.

FYI, "Name" is a reserved word in Access and shouldn't be used for object names.
Here is a list of reserved words: http://allenbrowne.com/AppIssueBadWord.html

The customer name is also a bad choice to use to select customers - what happens when you have two customers named "John Smith"?

Actually, you should have (at least) two fields for the customer name - FName and LName. It is easier to combine the names than split one field into First and Last names.
.
A better field for selecting a customer would be the cust_ID field. The SQL would look something like:

SELECT customer.cust_ID, customer.cust_name, assets.asset_id, assets.asset_location
FROM customer INNER JOIN assets
WHERE customer.cust_ID =  2;

Yes it is hard to know that Customer A's ID is 2.
So create a form, add a combo box and a button.
Let's name:
the form "frmSelect"
the combo box "cboCustomer"
the button "cmdPreview"


The recordsource for the combo box "cboCustomer" would be

SELECT customer.cust_ID, customer.cust_name
FROM customer 
ORDER BY customer.cust_name

Set the column count to 2
Set the column widths to 0,2


The code behind the button for the click event would be something like:
(to preview)

Private Sub cmdPreview_Click()
    Dim stDocName As String

    stDocName = "Report1"
    DoCmd.OpenReport stDocName, acPreview
End Sub

Change the report name (in red) to your report name.


Edit the report query to look like: (plus other columns)

SELECT customer.cust_ID, customer.cust_name, assets.asset_id, assets.asset_location
FROM customer INNER JOIN assets
WHERE customer.cust_ID = [forms]![frmSelect].[cboCustomer];

Open the form "frmSelect", select a customer using the combo box and click the button to view the report.
Close the report, select a different customer and click the button.

This is the basic method to select and view/print a customer.

[ssanfu]

It is easier to give good answers if the info provided is "real".

As long as it helps you move forward, all is good..

[ssanfu]

From a PM.....

I want to ask you if you can help me one last time just how to add a date filter to the above report ?

At the moment users can view the reports by clicking the 'show report' button. But I want them to also be able to select the date range.

OK, no info - form name, control names.... so you will have to adapt the following:

This code is for a data set that has two date fields; the code would be somewhat different if there is only one date field.


Let's name:
the form "frmSelect"

On the form are the controls:
a button named: "cmdPreview", Caption "Show Report"
a combo box named: "cboCustomer"

a text box for the start date named: txtDateFrom
a text box for the date end named: txtDateThru

The Input Mask (Data Tab) for the date controls are set to:
99/99/0000;0;_

The recordsource (query) for the report would look like: ( NO Where clause)

SELECT customer.cust_ID, customer.cust_name, assets.asset_id, assets.asset_location
FROM customer INNER JOIN assets

The code behind the button for the click event would be something like:
(to preview)

Private Sub cmdPreview_Click()
   Dim stDocName As String
   Dim strWhere As String
   Dim dtStart As Date
   Dim dtEnd As Date
   Dim Tmp As Date

   stDocName = "Report1"

   If Len(Trim(Me.cboCustomer)) > 0 Then
      strWhere = "[cust_ID] = " & Me.cboCustomer & " AND "
   Else
      MsgBox " A customer is REQUIRED!!!"
      Exit Sub
   End If

   If IsDate(Me.txtDateFrom) And IsDate(Me.txtDateThru) Then
      'between two dates
      dtStart = Me.txtDateFrom
      dtEnd = Me.txtDateThru

      'start date must be less than end date
      If dtStart > dtEnd Then
         Tmp = dtStart
         dtStart = dtEnd
         dtEnd = Tmp
         Tmp = Empty
         Me.txtDateFrom = dtStart
         Me.txtDateThru = dtEnd
      End If
      strWhere = strWhere & "[DateStart] >= #" & dtStart & "# AND [DateEnd] <= #" & dtEnd & "# AND "
   ElseIf IsDate(Me.txtDateFrom) Then
      ' greater than date Start
      strWhere = strWhere & "[DateStart] >= #" & dtStart & "# AND "
   ElseIf IsDate(Me.txtDateThru) Then
      'less than end date
      strWhere = strWhere & "[DateEnd] <= #" & dtEnd & "# AND "
   End If

   If Len(strWhere) > 0 Then
      'remove the trailing " AND "
      strWhere = Left(strWhere, Len(strWhere) - 5)
   Else
      strWhere = ""
   End If

   DoCmd.OpenReport stDocName, acPreview, , strWhere
End Sub

The customer is required, the dates are optional.
If you want to make the customer optional, comment out/remove the lines in BLUE.