form details in a report

[aman909]

Hello,

What im trying to do is to create a report from a form. I have made a template for the report i would like to use.

What i have is a form which contains details of a customer. What i would like to do is when on a certain customer to be able to view their details that are shown on the form to appear on a report which can be printed off.

What is the best way to be able to do this?

[JKing]

Hi, there. I'll try to explain the basics of what you need to do.

First off you should have a query that youre basing your report off. This query should contain all the customer info you need. Something like this:

SELECT customerID, customerFirstName, customerLastName, customerPhone, customerAddress, customerCity, customerProvince, customerPostalCode
FROM tblCustomer
 

Once you have your report created add a command button to your form. Under the onclick event of your command button you'll add code similar to this:

Private Sub cmdPreview_Click()
 
Dim strLinkCriteria As String
Dim strDoc As String
 
strLinkCriteria = "[CustomerID] = " & Me.CustomerID
strDoc = "rptCustomerDetails"
 
DoCmd.OpenReport strDoc, acViewPreview, , strLinkCriteria, acWindowNormal
 
End Sub
 

Notes:

• strLinkCriteria is a string we build to act as the where statement of the report
• strDoc is the report we want to open
• Docmd.OpenReport is the command used to open the report with our specified parameters
• if CustomerID is a text field rather than a number use this:
  

  strLinkCriteria = "[CustomerID] = '" & Me.CustomerID & "'"
   

Give this a try and if you need any help or clarification I'd be more than happy to assist.

Jking

[aman909]

Hi, there. I'll try to explain the basics of what you need to do.

First off you should have a query that youre basing your report off. This query should contain all the customer info you need. Something like this:

SELECT customerID, customerFirstName, customerLastName, customerPhone, customerAddress, customerCity, customerProvince, customerPostalCode
FROM tblCustomer
 

Once you have your report created add a command button to your form. Under the onclick event of your command button you'll add code similar to this:

Private Sub cmdPreview_Click()
 
Dim strLinkCriteria As String
Dim strDoc As String
 
strLinkCriteria = "[CustomerID] = " & Me.CustomerID
strDoc = "rptCustomerDetails"
 
DoCmd.OpenReport strDoc, acViewPreview, , strLinkCriteria, acWindowNormal
 
End Sub
 

Notes:

• strLinkCriteria is a string we build to act as the where statement of the report
• strDoc is the report we want to open
• Docmd.OpenReport is the command used to open the report with our specified parameters
• if CustomerID is a text field rather than a number use this:
  

  strLinkCriteria = "[CustomerID] = '" & Me.CustomerID "'"
   

Give this a try and if you need any help or clarification I'd be more than happy to assist.

Jking

It is opening up the report but it isn't showing any of the data. The code i have used is:

Private Sub Command690_Click()


Dim strLinkCriteria As String
Dim strDoc As String

strLinkCriteria = "[ID] = ' " & "Me.ID ' "
strDoc = "Customer"

DoCmd.OpenReport strDoc, acViewPreview, , strLinkCriteria, acWindowNormal

End Sub

[JKing]

Try changing this line:

strLinkCriteria = "[ID] = ' " & "Me.ID ' "
 

To:

strLinkCriteria = "[ID] = '" & Me.ID & "'"
 

You can see the difference very well with the colours from the code tags.

Edit: I see now this was my fault as I was missing a & in my previous post. Sorry for the confusion.