Solved print report from public function

[bwc]

I am stuck. I am trying to create a public function that will print a report so that i can call the report from different forms. i have this code that opens rptFlu (bound to qryClinicData - query is not filtered);

Option Compare Database
Option Explicit
Function printFlu()
    DoCmd.OpenReport "rptFlu", acViewNormal, , "clstrFlu = True Or clstrHep = True Or clstrTdap = True"
End Function

and frmCheckIn (unbound) writes the data to the table;

Option Compare Database
Option Explicit
Public Function checkIn()
    Dim db As DAO.Database
    Set db = CurrentDb()
 
    db.Execute " INSERT INTO tblClinicData " _
        & "(clstrNameInd,clstrSSN,clstrLocation,cldatTimeIn,cldatDate,clstrPHA,clstrHIV,clstrOver40lab, " _
        & "clstrFlu,clstrHep,clstrTdap, clstrProfileReview,clstrRemarks,clstrCell)" _
        & " VALUES " _
        & "('" & Forms!frmCheckIn.cboSoldierName.Column(0) & "', '" & Forms!frmCheckIn.cboSoldierName.Column(1) & "', " _
        & "'" & Forms!frmCheckIn.txtclstrLocation & "', '" & Time() & "','" & Date & "','" & Forms!frmCheckIn.ckclstrPHA & "'," _
        & "'" & Forms!frmCheckIn.ckclstrHIV & "','" & Forms!frmCheckIn.ckclstrOver40lab & "', '" & Forms!frmCheckIn.ckclstrFlu & "'," _
        & "'" & Forms!frmCheckIn.ckclstrHep & "','" & Forms!frmCheckIn.ckclstrTdap & "','" & Forms!frmCheckIn.ckclstrProfileReview & " '," _
        & "'" & Forms!frmCheckIn.txtclstrRemarks & "','" & Forms!frmCheckIn.txtclstrCell & "');"
 
'    db.Close
 
    If Forms!frmCheckIn.ckclstrFlu = True [COLOR=red]And tblClinicData!clstrNameInd = Forms!frmCheckIn.cboSoldierName.Column(0)[/COLOR] Then Call printFlu
End Function

and then prints rptFlu. the problem i am having is that it prints all the records, so i put

...And tblClinicData!clstrNameInd = Forms!frmCheckIn.cboSoldierName.Column(0)...

to filter the report, but now i get a "variable not defined" error

clstrPHA,clstrHIV,clstrOver40lab,clstrFlu,clstrHep,clstrTdap, clstrProfileReview are yes/no fields in table and check boxs on form

 

[pr2-eugin]

What is tblClinicaData? It is not defined.. Thus your Error.. If you want to filter the Report, then pass that as the argument..

Option Compare Database
Option Explicit
Sub printFlu(optArgs As String)
    Dim repFilStr As String
    repFilStr = "clstrFlu = True Or clstrHep = True Or clstrTdap = True" & IIF(Len(optArgs)>0, " AND " & optArgs, vbNullString)
    DoCmd.OpenReport "rptFlu", acViewNormal, , repFilStr
End Sub

Then when calling, just use...

If Forms!frmCheckIn.ckclstrFlu = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"

 

[bwc]

Thank you for your quick reply, Paul,

works great

Thanks again for setting me on the right path!!

 

[bwc]

i am having trouble with the "Or" statement;

Option Compare Database
Option Explicit
Sub printFlu(optArgs As String)
Dim repFilStr As String
repFilStr = "clstrFlu = True Or clstrHep = True Or clstrTdap = True" & IIF(Len(optArgs)>0, " AND " & optArgs, vbNullString)
DoCmd.OpenReport "rptFlu", acViewNormal, , repFilStr
End Sub 
 
Sub cmdCloseSaveForm_Click()
DoCmd.RunCommand acCmdSaveRecord
If Forms!frmCheckIn.ckclstrFlu = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"
End Sub

The report prints Soldier name if ckclstrFlu is true but not ckclstrHep or ckclstrTdap. A blank report is what I get if I uncheck ckclstrFlu and check ckclstrHep or ckclstrTdap. So then I tryed adding

If Forms!frmCheckIn.ckclstrFlu Or Forms!frmCheckIn.ckclstrHep Or Forms!frmCheckIn.ckclstrTdap  = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"

but now i get an "out of memory" error

 

[pr2-eugin]

I am unable to track down the error.. Before using that in a Report, try doing a plain copy of the Query and see if you get the desired result..

PS: Sorry for not getting back sooner.. I do not go online on weekends..

 

[bwc]

PS: Sorry for not getting back sooner.. I do not go online on weekends..

no worries. i will give your qry suggestion a try. you have been much help, thank you

 

[bwc]

i am stilll stumped on this. this is where i am at:

Function printFlu(optArgs As String)
    Dim repFilStr As String
    repFilStr = "clstrFlu = True" & IIf(Len(optArgs) > 0, " AND " & optArgs, vbNullString)
    repFilStr = "clstrHep = True" & IIf(Len(optArgs) > 0, " AND " & optArgs, vbNullString)
    repFilStr = "clstrTdap = True" & IIf(Len(optArgs) > 0, " AND " & optArgs, vbNullString)
    DoCmd.OpenReport "rptFlu", acViewNormal, , repFilStr
End Function

and call it with 3 separate IF statements:

    If Forms!frmCheckIn.ckclstrFlu = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"
    If Forms!frmCheckIn.ckclstrHep = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"
    If Forms!frmCheckIn.ckclstrTdap = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"

sometimes it works, sometimes not. what am i missing?

can the selections be made with a select case function? if so, how?