Select Report to Open by CheckBox

[David Davis]

NeoPa,

I am just about done. Can you throw me another bone on another stump I hit. I have a form that contains information that can be applied to two reports. A yes/no field distinguishes what type of report it would refer to(for example). If I install a command button on the form how can I get it to got to (for example) report1 with all field data when the yes/no field is yes.

David

[NeoPa]

This thread has been split from the Getting Rid of #Error thread as it is a separate question.

I'm happy enough to post an answer, but we request that questions are kept to their own threads for manageability.

[NeoPa]

Assuming you have a form (frmChoose) with a CommandButton (cmdReport) and a CheckBox (chkNight) on it. You want to run one report (rptDay) if chkNight is False and another (rptNight) if it's True.

You would have an OnClick procedure for cmdReport something like the following :

Private Sub cmdReport_Click()
  Dim strReport As String

  strReport = IIf(Me.chkNight, "rptNight", "rptDay")
  Call DoCmd.OpenReport(strReport, acViewPreview)
End Sub

Clearly this is at its simplest, but it should give you the idea of how to go about things.

Good luck :)

[David Davis]

Assuming you have a form (frmChoose) with a CommandButton (cmdReport) and a CheckBox (chkNight) on it. You want to run one report (rptDay) if chkNight is False and another (rptNight) if it's True.

You would have an OnClick procedure for cmdReport something like the following :

Private Sub cmdReport_Click()
  Dim strReport As String

  strReport = IIf(Me.chkNight, "rptNight", "rptDay")
  Call DoCmd.OpenReport(strReport, acViewPreview)
End Sub

Clearly this is at its simplest, but it should give you the idea of how to go about things.

Good luck :)

Neopa,

How do I cobine your code for the Checkbox with the the code that already exist their(code below). I would like your code to change however to only send data to rptNight only if the box is checked and it does nothing when it is unchecked. How do we go about doing this? You can change the coding below to reflect this if you want.

Private Sub PrintCCCertificates_Click()
On Error GoTo Err_PrintCCCertificates_Click

    Dim stDocName As String

    stDocName = "(Attendance) - Matt's signature"
    
    If Me.Dirty = True Then Me.Dirty = False
 
DoCmd.OpenReport stDocName, acPreview, , "[Instructor] = '" & Me![Instructor] & "'" & _
                 " And [InstTitle] = '" & Me![InstTitle] & "' And [CertSerialNum] > " & _
                 lngLastValueEntered

    'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)

Exit_PrintCCCertificates_Click:
    Exit Sub

Err_PrintCCCertificates_Click:
    MsgBox Err.Description
    Resume Exit_PrintCCCertificates_Click
    
End Sub

David

[NeoPa]

My original code would then look like :

Private Sub cmdReport_Click()
  If Me.chkNight Then Call DoCmd.OpenReport("rptNight", acViewPreview)
End Sub

Let me know if you need help changing your code to reflect this.

[David Davis]

My original code would then look like :

Private Sub cmdReport_Click()
  If Me.chkNight Then Call DoCmd.OpenReport("rptNight", acViewPreview)
End Sub

Let me know if you need help changing your code to reflect this.

Neopa,

Yes I do need help. I am not sure where to put your code at in the previous coding I sent out.

David

[NeoPa]

I can't put any code in as you haven't shared the name of your CheckBox control?

With this, and assuming all else is equal and you want the report only to run when the value of the CheckBox is True, I can redo your code in the way you want.

[David Davis]

The control name of the Checkbox is "CCClass". I just want to put clarity to the coding. When the box is checked I do want the report only to run when the value of the Checkbox is True but also I want only the input that was entered for the present day (the control name of the date is "CertPrintDate" ) and not previous days in conjunction with the text above to where it will work. Is that all you need.

David

[NeoPa]

I think it does - and congratulations on reaching full-member status :)

I was thinking of using the same logic for the filtering as you already had.

I will try to get back to this sometime this evening for you.

[NeoPa]

Try this (I'm off home now for the evening so if there are any problems or questions they'll probably have to wait until tomorrow) :

Private Sub PrintCCCertificates_Click()
On Error GoTo Err_PrintCCCertificates_Click

  Dim stDocName As String, strWhere As String

  If Me.Dirty = True Then Me.Dirty = False

  If Me.CCClass Then
    stDocName = "(Attendance) - Matt's signature"
    strWhere = "([Instructor] = '%I') AND " & _
               "([InstTitle] = '%T') AND " & _
               "([CertSerialNum] > %S) AND " & _
               "([CertPrintDate] = #%D#)"
    strWhere = Replace(strWhere, "%I", Me.[Instructor])
    strWhere = Replace(strWhere, "%T", Me.[InstTitle])
    strWhere = Replace(strWhere, "%S", lngLastValueEntered)
    strWhere = Replace(strWhere, "%D", Format(Date(), "m/d/yyyy"))

    Call DoCmd.OpenReport(stDocName, acPreview, , strWhere)
    'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)
  End If

Exit_PrintCCCertificates_Click:
  Exit Sub

Err_PrintCCCertificates_Click:
  MsgBox Err.Description
  Resume Exit_PrintCCCertificates_Click
  
End Sub

[NeoPa]

I included the date selection as well as the previous selections you already had (from post #4).

Let me know how you get on with this.

[David Davis]

I included the date selection as well as the previous selections you already had (from post #4).

Let me know how you get on with this.

Neopa,

I installed the coding but now instead of coming up with all recordson the report now it comes up with no records. I wasnt sure what the date is suppose to do. Actually I am confused on that because my form has three different dates one for start date of class, one for end date of class and one for date of certificate printing. I am going to assume that the date is based off of the computer not the form.

David

[David Davis]

Neopa,

I installed the coding but now instead of coming up with all recordson the report now it comes up with no records. I wasnt sure what the date is suppose to do. Actually I am confused on that because my form has three different dates one for start date of class, one for end date of class and one for date of certificate printing. I am going to assume that the date is based off of the computer not the form.

David

Neopa,

I corrected some coding that was previously there prior to me using yours and now a window pops up and says

Extra) in query expression

(([Instructor] ='Chris McJean')

and

([InstTitle] = 'Technical Instructor/Developer')

and

([CertSerialNum] >)

and

([CertPrintDat] = #27 March 2008#))

What do you think could be the problem?


David

[NeoPa]

Your [CertSerialNum] line has no comparison value in it. This explains your error message.

As to your logic, that's another matter. Can you post the whole module just for reference, then I can look through it for precise answers.

[David Davis]

Your [CertSerialNum] line has no comparison value in it. This explains your error message.

As to your logic, that's another matter. Can you post the whole module just for reference, then I can look through it for precise answers.

NeoPa,

Here is the whole module

Option Compare Database
Private lngLastValueEntered As Long

Private Sub CCClass_AfterUpdate()
Me![CCClass].DefaultValue = "'" & Me![CCClass] & "'"
End Sub

Private Sub CertPrintDate_AfterUpdate()
Me![CertPrintDate].DefaultValue = "'" & Me![CertPrintDate] & "'"
End Sub

Private Sub ClassSize_AfterUpdate()
Me![ClassSize].DefaultValue = "'" & Me![ClassSize] & "'"
End Sub

Private Sub Detail_Click()

End Sub

Private Sub Form_AfterUpdate()

End Sub

Private Sub Form_Open(Cancel As Integer)
  lngLastValueEntered = DLast("[CertSerialNum]", "Master Cert Database")
End Sub

Private Sub StudentCompany_AfterUpdate()
Me![StudentCompany].DefaultValue = "'" & Me![StudentCompany] & "'"
End Sub

Private Sub StudentName_AfterUpdate()
Me![StudentName].DefaultValue = "'" & Me![StudentName] & "'"
End Sub

Private Sub System_AfterUpdate()
Me![System].DefaultValue = "'" & Me![System] & "'"
End Sub

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click


    DoCmd.Close

Exit_CloseForm_Click:
    Exit Sub

Err_CloseForm_Click:
    MsgBox Err.Description
    Resume Exit_CloseForm_Click
    
End Sub


Private Sub PLMCertificates_Click()
On Error GoTo Err_PLMCertificates_Click

    Dim stDocName As String

    stDocName = "ChristineMcLaurinCert"
    DoCmd.OpenReport stDocName, acPreview

Exit_PLMCertificates_Click:
    Exit Sub

Err_PLMCertificates_Click:
    MsgBox Err.Description
    Resume Exit_PLMCertificates_Click
    
End Sub


Private Sub PCCCertificates_Click()
On Error GoTo Err_PCCCertificates_Click
 
  Dim stDocName As String, strWhere As String
 
  If Me.Dirty = True Then Me.Dirty = False
 
  If Me.CCClass Then
    stDocName = "(Attendance) - Matt's signature"
    strWhere = "([Instructor] = '%I') AND " & _
               "([InstTitle] = '%T') AND " & _
               "([CertSerialNum] > %S) AND " & _
               "([CertPrintDate] = #%D#)"
    strWhere = Replace(strWhere, "%I", Me.[Instructor])
    strWhere = Replace(strWhere, "%T", Me.[InstTitle])
    strWhere = Replace(strWhere, "%S", lngLastValueEntered)
    strWhere = Replace(strWhere, "%D", Format(Date, "dd/mmmm/yyyy"))
 
    Call DoCmd.OpenReport(stDocName, acPreview, , strWhere)
    'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)
  End If
 
Exit_PCCCertificates_Click:
  Exit Sub
 
Err_PCCCertificates_Click:
  MsgBox Err.Description
  Resume Exit_PCCCertificates_Click
  
End Sub

David