Form to filter for reports

[April Slater via AccessMonster.com]

I have a form built to allow users to filter the database using three
critieria. I have an unbound lookup table for ?Contract Type?,
?Discipline? and ?Project Name?. On the form I have three command buttons
for ?View Report?, ?Print Report? and ?Exit?. I can?t seem to figure out
how to write the code to make the reports come out with the specified data.
Not only that, but I need it to accept Null values in any of the criteria
if any of the three match for the report. Can you help me?? Yes, I?m a
newbie trying to work it out. Here is my code at this point, but I?m only
testing on the View Report Command.

Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click
Dim strWhere As String
strWhere = "[Contract Type]" & strWhere = "[Discipline]" & strWhere = "
[Project Name]"
stDocName = "Lessons Learned"
DoCmd.OpenReport stDocName, acPreview
Exit_View_Report_Click:
Exit Sub
Err_View_Report_Click:
MsgBox Err.Description
Resume Exit_View_Report_Click
End Sub

Private Sub Exit_Click()
On Error GoTo Err_Exit_Click
DoCmd.Close
Exit_Exit_Click:
Exit Sub
Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click
End Sub

Private Sub Print_Report_Click()
On Error GoTo Err_Print_Report_Click
Dim stDocName As String
stDocName = "Lessons Learned"
DoCmd.OpenReport stDocName, acNormal
Exit_Print_Report_Click:
Exit Sub
Err_Print_Report_Click:
MsgBox Err.Description
Resume Exit_Print_Report_Click
End Sub

 

[Guest]

There are a number of ways to filter the report.
The one way i think would be best for u is.

First make a report with a record source = to a query
Next in that query in those fields u want filtered put in the criteria
something like this: [Forms]![Form2]![Text5]

You can have a wizard help u make the correct syntax by r-chicking in the
criteria feild and select Buid... then open the Forms folder and then the All
Forms forlder. Select your form and DBClick on the control that has the data.

In doing this u now have set this report to filter off the form. The only
problem is this report will not run correctly without that form being open.

 

[Marshall Barton]

I have a form built to allow users to filter the database using three
critieria. I have an unbound lookup table for ?Contract Type?,
?Discipline? and ?Project Name?. On the form I have three command buttons
for ?View Report?, ?Print Report? and ?Exit?. I can?t seem to figure out
how to write the code to make the reports come out with the specified data.
Not only that, but I need it to accept Null values in any of the criteria
if any of the three match for the report. Can you help me?? Yes, I?m a
newbie trying to work it out. Here is my code at this point, but I?m only
testing on the View Report Command.

Private Sub View_Report_Click()
On Error GoTo Err_View_Report_Click
Dim strWhere As String
strWhere = "[Contract Type]" & strWhere = "[Discipline]" & strWhere = "
[Project Name]"
stDocName = "Lessons Learned"
DoCmd.OpenReport stDocName, acPreview
Exit_View_Report_Click:
Exit Sub
Err_View_Report_Click:
MsgBox Err.Description
Resume Exit_View_Report_Click
End Sub

If you want to skip a criteria when its text box is Null,
you have to check for that situation:

If Not IsNull(txtContactType) Then
strWhere = " AND [Contract Type] = " & txtContactType
End If

If Not IsNull(txtDiscipline) Then
strWhere = " AND [Discipline] = " & txtDiscipline
End If

If Not IsNull(txtProjectName) Then
strWhere = " AND [Project Name] = " & txtProjectName
End If

stDocName = "Lessons Learned"
DoCmd.OpenReport stDocName, acPreview, , Mid(strWhere, 6)