Need Help Creating Report Based on User Entry

[italianfinancier]

Hello everyone...I am stumped. I need to create a report in Access 2010 that provides data from three Excel sheets and two Access tables with the "link" method so that changes to these data sources update the reports when they are run.



I've gotten the external data loaded into Access and created the report, but am stumped as to how to create a spot for the user to enter a customer number and also how to get that customer number entered to be the only data pulled from the data sources and displayed.

Please Help!

[alansidman]

Create a form and put a text box on it that will list the customer number. Perhaps using a combobox. Then create a command button and put VBA code behind it that says run the report but only show the information for the listed customer.

Here is code that I have used for a similar situation

Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
    Dim stDocName As String
    stDocName = "Submission"
    'DoCmd.OpenReport stDocName, acNormal, , "[ID] = Forms!frmClients!ID"
    DoCmd.OpenReport stDocName, acViewPreview, , "[ID]=Forms!frmClients!ID"
Exit_Command28_Click:
    Exit Sub
Err_Command28_Click:
    MsgBox Err.Description
    Resume Exit_Command28_Click
    
End Sub

You will have to change the forms and field names appropriately, but htis should give you a step in the right direction.

Alan

[italianfinancier]

Thanks so much for the fast reply...what exactly do I need to change in the VBA code and to what do I need to change it?

Thanks again very much...you've already been a great help!

[italianfinancier]

Here is the code I have so far, but it is telling me "The Action or Method requires a Report Name Argument"...

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click
Dim stDocName As String
stDocName = "Report1"
'DoCmd.OpenReport stReport1, acNormal, , "[ID] = Reports!frmText4!ID"
DoCmd.OpenReport stReport1, acViewPreview, , "[ID]=Reports!frmText4!ID"
DoCmd.Maximize
DoCmd.RunCommand acCmdFitToWindow
Exit_Command6_Click:
Exit Sub
Err_Command6_Click:
MsgBox Err.Description
Resume Exit_Command6_Click

End Sub

The text box I created is named Text4 and the report I created is named Report1


Any suggestions would be greatly appreciated!

[alansidman]

You have defined stDocName = "Report1" but in your DoCmd line you are telling Access to run stReport1. You need to have this as stDocName

BTW--the two lines of docmd are for you to choose which one you want. If you want the report in preview format then leave as is. If you want it in normal presentation, then put an ' before the line having acPreview and delete the one on the line above.

Alan

[italianfinancier]

Great, the error disappeared, but the number i enter in the text box just disappears and all the data for all accounts still shows in the list. Any idea how to get it to display only the data on the account I enter into the text box? We're getting close!

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Dim stDocName As String
stDocName = "Report1"
'DoCmd.OpenReport stDocName, acNormal, , "[ID] = Reports!frmText7!ID"

Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

[alansidman]

Do you have the text box field from your form on your report?

[italianfinancier]

I went into my form and re-named the text box the same name as the text box on my report. I have attached screenshots to give you a better idea of what it looks like. Thanks so much and I look forward to upping your Reputation when we are done.

[ssanfu]

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Dim stDocName As String
stDocName = "Report1"
'DoCmd.OpenReport stDocName, acNormal, , "[ID] = Reports!frmText7!ID"

Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

You need to have the reference to the text box outside of the quotes

Private Sub Command9_Click()
   On Error GoTo Err_Command9_Click
   Dim stDocName As String
   stDocName = "Report1"
   'DoCmd.OpenReport stDocName, acNormal, , "[ID] = " & Reports!frmText7!ID

' you might need to change "Reports" to "FORMS"
   DoCmd.OpenReport stDocName, acViewPreview, , "[ID] = " & Reports!frmText4!id

Exit_Command9_Click:
   Exit Sub
Err_Command9_Click:
   MsgBox Err.Description
   Resume Exit_Command9_Click

End Sub

I don't have A2K10, but I would think that the text box that has the ID you want to print would be on a form, not on the report you are trying to print.

In your code you have: Reports!frmText4!ID
I would have: FORMS!frmText4!ID

[italianfinancier]

Thanks for writing...it didn't work. Below is my code... If I use a form, do I need to do any programming on it? Will the users pull up the form, enter the customer number and then the report will automatically create for them?

Private Sub Command9_Click()
On Error GoTo Err_Command9_Click
Dim stDocName As String
stDocName = "Report1"
'DoCmd.OpenReport stDocName, acNormal, , "[ID] = " & Forms!frmText4!id


Exit_Command9_Click:
Exit Sub
Err_Command9_Click:
MsgBox Err.Description
Resume Exit_Command9_Click

End Sub

[ssanfu]

' DoCmd.OpenReport stDocName, acNormal, , "[ID] = " & Forms!frmText4!id

The apostrophe at the start of the line makes the line a comment. It doesn't get executed.

"acNormal" will cause the report to be automatically printed. Use "acViewPreview" to be able to look at the report on screen before printing.

Do you have a form named "frmText4"?
On the form, is there a text box named "ID"?

[italianfinancier]

No the form is named Rep Account # Form and the text box is named Text4

Do I attach the code to the text box on the Form or somewhere on the Report?

Also, do I need to right click on any items and go to Properties and set any field a certain way?

[ssanfu]

No the form is named Rep Account # Form and the text box is named Text4

Then your code should look something like:

Private Sub Command9_Click()
   On Error GoTo Err_Command9_Click
   Dim stDocName As String
   stDocName = "Report1"
   'DoCmd.OpenReport stDocName, acNormal, , "[ID] = " & Reports!frmText7!ID

   DoCmd.OpenReport stDocName, acViewPreview, , "[ID] = " & Forms![Rep Account # Form]!Text4
 
Exit_Command9_Click:
   Exit Sub
Err_Command9_Click:
   MsgBox Err.Description
   Resume Exit_Command9_Click

End Sub

Note: Using spaces and special characters (the shifted number keys !@#$()%^&*+=<>? ) in object names will cause you major headaches when creating queries, reports, etc.
If you feel you must separate words, use the underscore... ex: Rep_Account_#_Form
(I would use Rep_Account_Form or RepAccountForm)

[italianfinancier]

Do I attach the code to the text box on the Form or somewhere on the Report?

Also, do I need to right click on any items and go to Properties and set any field a certain way?

The Command9 in the code referred to a command button on the report. Now that I am using a Form what should this be changed to?

I'm soooooo grateful...we are getting close! Almost done!

[ssanfu]

Do I attach the code to the text box on the Form or somewhere on the Report?

Also, do I need to right click on any items and go to Properties and set any field a certain way?

The Command9 in the code referred to a command button on the report. Now that I am using a Form what should this be changed to?

I'm soooooo grateful...we are getting close! Almost done!

Add a button to the form.
Open the properties
Name the button "btnShowreport" (no quotes).
In the Click event, paste the code