print report from public function (1 Viewer)

bwc

Registered User.
Local time
Today, 02:46
Joined
Feb 7, 2013
Messages
22
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);
Code:
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;
Code:
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
Code:
...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

Super Moderator
Local time
Today, 07:46
Joined
Nov 30, 2011
Messages
8,494
What is tblClinicaData? It is not defined.. Thus your Error.. If you want to filter the Report, then pass that as the argument..
Code:
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...
Code:
If Forms!frmCheckIn.ckclstrFlu = True Then printFlu "clstrNameInd = '" & Forms!frmCheckIn.cboSoldierName.Column(0) & "'"
 

bwc

Registered User.
Local time
Today, 02:46
Joined
Feb 7, 2013
Messages
22
Thank you for your quick reply, Paul,

works great

Thanks again for setting me on the right path!!
 
Last edited:

bwc

Registered User.
Local time
Today, 02:46
Joined
Feb 7, 2013
Messages
22
i am having trouble with the "Or" statement;
Code:
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
Code:
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

Super Moderator
Local time
Today, 07:46
Joined
Nov 30, 2011
Messages
8,494
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

Registered User.
Local time
Today, 02:46
Joined
Feb 7, 2013
Messages
22
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

Registered User.
Local time
Today, 02:46
Joined
Feb 7, 2013
Messages
22
i am stilll stumped on this. this is where i am at:
Code:
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:
Code:
    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?
 
Last edited:

Users who are viewing this thread

Top Bottom