Filter report with code

[ezpc]

I hope I can describe this correctly.
I want a user to be able to select criteria from drop down boxes on a form. (TestID and Teacher). I can get the required results but not without generating an error message "Object Required", which is referring to the Reports object. Here is the piece of code.

Dim strType As String
Dim strTeacher As String
strType = "TestName = (Forms!frmReport!cboTestID)"
strTeacher = "ABBREVNAME = (Forms!frmReport!cboTeacher)"
Dim strSQL As String
strSQL = "SELECT Students.TESTID, Students.ABBREVNAME FROM Students WHERE TESTID = strType AND ABBREVNAME = strTeacher"

docmd.OpenReport "HighPartProfLowProfbyTeacher", PrintMode ', , strTeacher

Reports!HighPartProfLowProfbyTeacher].Filter = strSQL
Reports!HighPartProfLowProfbyTeacher].FilterOn = True

Any help would be appreciated.

 

[Carol]

If I understand your question, you are trying to print a report, using criteria from 2 different fields. Try using the following.


Private Sub Print_Click(Cancel As Integer)
On Error GoTo Err_Print_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "HighPartProfLowProfbyTeacher"

stLinkCriteria = "[TestID] & [Teacher]=" & Me![TestID] & Me![Teacher]
DoCmd.OpenReport stDocName, , , stLinkCriteria


Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub

 

[ezpc]

Carol, thank you so much for your help. It is working correctly now. I think I was trying to make it too complicated!

 

[Pat Hartman]

I think you mean:
stLinkCriteria = "[TESTID] = " & Me.[cboTestID] & " AMD [ABBREVNAME] = '" & Me.[cboTeacher] & "'"

I'm assuming that ABBREVNAME is text and TESTID is numeric. If that is not correct, adjust the single quotes as necessary.

 

[ezpc]

Yes, Pat, I forgot to mention that I changed it to this:


stLinkCriteria = "[cboTestID] & [cboTeacher]=" & Me![cboTestID] & Me![cboTeacher]

It seems to be working correctly.

Thanks.