Run report based upon Name

[dan231]

I have 9 different reports that currently print based upon a program name. The problem is that it prints every record in that category.

I'd like to have a combobox for "Name" and have the report only print for that record only.

My guess is selecting name, setting that as a variable and then calling that variable into the report? If so I don't know how to do this.

Any help would be greatly appreciated.

Dan

 

[dan231]

or better yet, since all the data is on a form - would it be possible to have a command button setup to grab the data and print the form? Possibly in a where stmt?

Dan

 

[Rich]

Either use the Where clause of the OpenReport method or base the report on a parameter query

 

[dan231]

I'm sorry...can you please repeat that in english.

I have no idea on how to do either of those. Can you give me a starting point? I trid to look up where stmts and haven't gotten anythign useful yet.

 

[Rich]

Something like this
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "InvIssdRpt"
If (IsNull(Me.List23)) Then
MsgBox "Please select item from list"
Else

stLinkCriteria = "[Customer]= " & "'" & Me.List23.Column(2) & "'" & "And [Issued]= #" & Format(Me!List23.Column(1), "mm dd yyyy") & "#"
DoCmd.OpenReport stDocName, , , stLinkCriteria
EndIf

 

[dan231]

Thanks. That'll give me something to work with.

I appreciate the help!

 

[dan231]

ok here's what I did and it is half the battle:

I created a command button on the main form.
In the onClick code I have:

Dim strReport As String ' Name of report to open

If Text12.Value = "Residential Inspector Multi-Discipline" Then
strReport = "rptResidentInspMutliDiscp"

ElseIf Text12.Value = "Property Maintenance Inspector" Then
strReport = "rptPropertyMaintInsp"

ElseIf Text12.Value = "Commercial Building Inspector" Then
strReport = "rptComBuildingInsp"

ElseIf Text12.Value = "Commercial Mechanical Inspector" Then
strReport = "rptComMechanicalInsp"

ElseIf Text12.Value = "Illinois Commercial Plumbing Inspector" Then
strReport = "rptILComPlumbingInsp"

ElseIf Text12.Value = "Residential Inspector - Mechanical" Then
strReport = "rptResidentInspMechanical"

ElseIf Text12.Value = "Residential Inspector - Electrical" Then
strReport = "rptResidentInspElectrical"

ElseIf Text12.Value = "Residential Inspector - Plumbing" Then
strReport = "rptResidentInspPlumbing"

ElseIf Text12.Value = "Residential Inspector - Plan Review" Then
strReport = "rptResidentInspPlanReview"

End If

DoCmd.OpenReport strReport, acViewPreview


Exit_Command223_Click:
Exit Sub

Err_Command223_Click:
MsgBox Err.Description
Resume Exit_Command223_Click

End Sub

This actually previews each of the reports based on what text12.value is (yes I know that is a bad description )

What I need now is to make it so only the record shown on this form is printed and not all the records under text12.value. I tried to get the Where stmt to fit in, but I really don't understand/know the proper syntax for my solution.

 

[Rich]

Why have so many reports, are most of the fields common to each?

 

[dan231]

Yes and no...They all consist of basically the same data(what classes they are enrolled in) but not all classes are in each program. I only know how to do so much, so I fit my program around my skill level.

 

[Brianwarnock]

To print the report based on the form showingplace a command button on the Form and code something like

DoCmd.OpenReport "tablename", acPreview, , "txtname=forms!form1.txtname"
DoCmd.PrintOut acSelection

Which will print the report for the value in the txtname control.

Brian

 

[dan231]

I can't figure out the "txtname=forms!form1.txtname" part.

I get a popup box in which if I enter the student number, it prints only the 1 record. Am I missing a part to not get the popup and to have it just grab that number?

(I had to modify it to the student number instead of the name, and the name query is pulling from 2 fields and the student number is 1 field.)

Here is the code I currently have in place:

DoCmd.OpenReport strReport, acPreview, , "StudentNumber=Me.txtStudentNumber"
DoCmd.PrintOut acSelection

 

[Brianwarnock]

I don't recall using Me.

Nope I used Forms!formname.

Brian
Edit Studentname must be the name of the control in the report source

 

[dan231]

yes, I'm sorry. I had changed that experimenting when I couldn't get the student name to work. I had neglected to change that back.

It is working now. I should have rechecked your code after I changed to student number.

Thank you!

 

[Brianwarnock]

That's OK also I should have mentioned that you can close the report if you don't want it left open after printing
DoCmd.Close acReport, "reportname"
you will realise at this point that it is better to use strDocname="reportname" and avoid any differences in the open and close typing.

Brian

 

[dan231]

That is great. I'll be adding that too.

Thanks again!