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

[NeoPa]

Right, it would seem that there are some discrepancies between the code posted and the results you claim in the error message posted in post #13. Whatever lngLastValueEntered is set to, the ([CertSerialNum] > n) part must resolve to a valid number as far as I can see with your posted code (See lines #83 through #89).

Also, the date shown doesn't match the format specified in line #90 (which doesn't match the format shown in my post #10 either). Your formatting should work, but only because Access does some non-standard things to cover all bases.

I assume this is your most recent code, so what error messages (as accurately as possible please) do you get when running this code. When we've cleared all the reported error messages and compiled the project, then we can look at the logic producing what we need.

Often the process miraculously starts to work once the syntax etc errors are sorted out.

[David Davis]

NeoPa,

I inserted the number after the CertSerialNum and when cmd button is executed it says invalid use of Null. (I didn't quite understand about the date. I know I change it's format in your original coding to reflect what is in the form)

David

[NeoPa]

NeoPa,

I inserted the number after the CertSerialNum and when cmd button is executed it says invalid use of Null. (I didn't quite understand about the date. I know I change it's format in your original coding to reflect what is in the form)

David

I'm sorry David, you need to be a lot more precise than this. I can't even guess what you're trying to say about what you did and got. Remember I stressed the info must be clear (I can't see it directly you see - I only have what you tell me to go on).

The parenthesised stuff (surrounded by () characters) I get. You should not really be thinking along those lines as the code is prepared for processing as SQL - not how it looks on a form.

The date is actually stored as a real number. SQL doesn't care about that when dealing with date literals. It has its own rules (See Literal DateTimes and Their Delimiters (#)).

[David Davis]

I'm sorry David, you need to be a lot more precise than this. I can't even guess what you're trying to say about what you did and got. Remember I stressed the info must be clear (I can't see it directly you see - I only have what you tell me to go on).

The parenthesised stuff (surrounded by () characters) I get. You should not really be thinking along those lines as the code is prepared for processing as SQL - not how it looks on a form.

The date is actually stored as a real number. SQL doesn't care about that when dealing with date literals. It has its own rules (See Literal DateTimes and Their Delimiters (#)).

Sorry about that NeoPa,

On line 85 I change its coding to:

"([CertSerialNum] > 367288 %S) AND " & _

Next I executed line 74 and its coding (I did this from a command button on a [Form] in form view which is labeled PCCCertificates which is also linked to line 74 and it's coding).

Once line 74 and it's coding was complete with it's execution, a msgbox popped up and sayed "Invalid use of Null".

I acknowledged it by pressing OK and the msg box went away
and that is it.

David

[NeoPa]

Can you add the following line to your code after line #90 and let me know what it shows when run.

Call MsgBox(strWhere)