I made a report that I want to only show data on a particular customer

[italianfinancier]

I made a report that I want to only show data on a particular customer (based on a customer number that a user enters). How do I do that?

 

[Trevor G]

I made a report that I want to only show data on a particular customer (based on a customer number that a user enters). How do I do that?

Create a macro to open the report in print preview, in the options at the bottom of the macro you can then use the elipse button to build an expression to something around this:

In the Where conditions field is, so keep your form open and then look to create the macro,
Action Select Open Report
Below Select Which Report
Below Change to Print Preview
Below in the Where Conditions use the elipsee button the right and then select Forms from the Expression Builder, select open forms, and then your form, next in middle column select the field (ID) and then add in the = (Sign) then select the tables section at the top, find your table and double click the ID Field, so the criteria will look something like this

Forms![frmEmployee1]![id] = [tblEmployee]![id]

Save the macro, to add this as a button on your form, select your form and go into design view then press F11 and find the macro you just created and drag into your form, this will create a button, save the form, change the view and select a record and click the button

 

[italianfinancier]

Like this?

Private Sub Command5_Click()
On Error GoTo Err_Command5_Click
Dim stDocName As String
stDocName = "Report1"
'DoCmd.OpenReport stDocName, acNormal, , "[ID] = " & Reports frmText7!ID
DoCmd.OpenReport "Report1", acViewPreview, , "[Customer] = " & e.Text4

Exit_Command5_Click:
Exit Sub
Err_Command5_Click:
MsgBox Err.Description
Resume Exit_Command5_Click
End Sub

 

[Trevor G]

My suggestion was to use a MACRO rather than code, the MACRO element is sometimes easier to step through rather than thinking each step in the code screen.

You can always convert form macros to VBA which will give you your error handler as well.

 

[italianfinancier]

AWESOME! I got it! Quick question, there are 7 tables pulling into this report...can I use the macro builder to include them all? In other words, data from each of these 7 different tables is populating into different parts of my report. How do I update the macro to make sure all the lists in my report only display data on the account number the user enters?

Your great...thanks so much!
~ Chris

 

[Trevor G]

Chris pleased to read it has helped.

What I would do is create a query against all the tables so you see the data and make the query the source for your Report, then when you use the MACRO you can still filter all areas to show the selected ID field.

 

[italianfinancier]

Ok, I made the query so that all the data from all the tables is queried...what's the best way to link it to the Form (where the user enters the customer number) and the report (that previews the data)?

 

[Trevor G]

Create a report now based on the query, then that should work for you.

 

[lstodden]

Create a report now based on the query, then that should work for you.

Thanks Trevor, your reply also assisted me greatly!

 

[Trevor G]

Welcome to the Forum,

Pleased to read you have a working solution.