browse: forums | FAQ
Connecting Tech Pros Worldwide
bytes > topic > access > answers

Hey there! Do you need Microsoft Access / VBA help?

Get answers from our community of Microsoft Access / VBA experts on BYTES! It's free.

Select Report to Open by CheckBox

Member
  
Join Date: Mar 2008
Posts: 41
#1: Mar 18 '08
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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#2: Mar 18 '08

re: Select Report to Open by CheckBox

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#3: Mar 18 '08

re: Select Report to Open by CheckBox

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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport_Click()
  2.   Dim strReport As String
  3.  
  4.   strReport = IIf(Me.chkNight, "rptNight", "rptDay")
  5.   Call DoCmd.OpenReport(strReport, acViewPreview)
  6. End Sub
Clearly this is at its simplest, but it should give you the idea of how to go about things.

Good luck :)
Member
  
Join Date: Mar 2008
Posts: 41
#4: Mar 26 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by 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 :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport_Click()
  2.   Dim strReport As String
  3.  
  4.   strReport = IIf(Me.chkNight, "rptNight", "rptDay")
  5.   Call DoCmd.OpenReport(strReport, acViewPreview)
  6. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintCCCertificates_Click()
  2. On Error GoTo Err_PrintCCCertificates_Click
  3.  
  4.     Dim stDocName As String
  5.  
  6.     stDocName = "(Attendance) - Matt's signature"
  7.  
  8.     If Me.Dirty = True Then Me.Dirty = False
  9.  
  10. DoCmd.OpenReport stDocName, acPreview, , "[Instructor] = '" & Me![Instructor] & "'" & _
  11.                  " And [InstTitle] = '" & Me![InstTitle] & "' And [CertSerialNum] > " & _
  12.                  lngLastValueEntered
  13.  
  14.     'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)
  15.  
  16. Exit_PrintCCCertificates_Click:
  17.     Exit Sub
  18.  
  19. Err_PrintCCCertificates_Click:
  20.     MsgBox Err.Description
  21.     Resume Exit_PrintCCCertificates_Click
  22.  
  23. End Sub
David
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#5: Mar 26 '08

re: Select Report to Open by CheckBox

My original code would then look like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport_Click()
  2.   If Me.chkNight Then Call DoCmd.OpenReport("rptNight", acViewPreview)
  3. End Sub
Let me know if you need help changing your code to reflect this.
Member
  
Join Date: Mar 2008
Posts: 41
#6: Mar 26 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by NeoPa

My original code would then look like :

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdReport_Click()
  2.   If Me.chkNight Then Call DoCmd.OpenReport("rptNight", acViewPreview)
  3. 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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#7: Mar 26 '08

re: Select Report to Open by CheckBox

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.
Member
  
Join Date: Mar 2008
Posts: 41
#8: Mar 26 '08

re: Select Report to Open by CheckBox

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#9: Mar 26 '08

re: Select Report to Open by CheckBox

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#10: Mar 26 '08

re: Select Report to Open by CheckBox

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) :
Expand|Select|Wrap|Line Numbers
  1. Private Sub PrintCCCertificates_Click()
  2. On Error GoTo Err_PrintCCCertificates_Click
  3.  
  4.   Dim stDocName As String, strWhere As String
  5.  
  6.   If Me.Dirty = True Then Me.Dirty = False
  7.  
  8.   If Me.CCClass Then
  9.     stDocName = "(Attendance) - Matt's signature"
  10.     strWhere = "([Instructor] = '%I') AND " & _
  11.                "([InstTitle] = '%T') AND " & _
  12.                "([CertSerialNum] > %S) AND " & _
  13.                "([CertPrintDate] = #%D#)"
  14.     strWhere = Replace(strWhere, "%I", Me.[Instructor])
  15.     strWhere = Replace(strWhere, "%T", Me.[InstTitle])
  16.     strWhere = Replace(strWhere, "%S", lngLastValueEntered)
  17.     strWhere = Replace(strWhere, "%D", Format(Date(), "m/d/yyyy"))
  18.  
  19.     Call DoCmd.OpenReport(stDocName, acPreview, , strWhere)
  20.     'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)
  21.   End If
  22.  
  23. Exit_PrintCCCertificates_Click:
  24.   Exit Sub
  25.  
  26. Err_PrintCCCertificates_Click:
  27.   MsgBox Err.Description
  28.   Resume Exit_PrintCCCertificates_Click
  29.  
  30. End Sub
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#11: Mar 27 '08

re: Select Report to Open by CheckBox

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.
Member
  
Join Date: Mar 2008
Posts: 41
#12: Mar 27 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by 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.

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
Member
  
Join Date: Mar 2008
Posts: 41
#13: Mar 27 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by 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
Expand|Select|Wrap|Line Numbers
  1. (([Instructor] ='Chris McJean')
and
Expand|Select|Wrap|Line Numbers
  1. ([InstTitle] = 'Technical Instructor/Developer')
and
Expand|Select|Wrap|Line Numbers
  1. ([CertSerialNum] >)
and
Expand|Select|Wrap|Line Numbers
  1. ([CertPrintDat] = #27 March 2008#))

What do you think could be the problem?


David
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#14: Mar 28 '08

re: Select Report to Open by CheckBox

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.
Member
  
Join Date: Mar 2008
Posts: 41
#15: Mar 28 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by 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.

NeoPa,

Here is the whole module

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private lngLastValueEntered As Long
  3.  
  4. Private Sub CCClass_AfterUpdate()
  5. Me![CCClass].DefaultValue = "'" & Me![CCClass] & "'"
  6. End Sub
  7.  
  8. Private Sub CertPrintDate_AfterUpdate()
  9. Me![CertPrintDate].DefaultValue = "'" & Me![CertPrintDate] & "'"
  10. End Sub
  11.  
  12. Private Sub ClassSize_AfterUpdate()
  13. Me![ClassSize].DefaultValue = "'" & Me![ClassSize] & "'"
  14. End Sub
  15.  
  16. Private Sub Detail_Click()
  17.  
  18. End Sub
  19.  
  20. Private Sub Form_AfterUpdate()
  21.  
  22. End Sub
  23.  
  24. Private Sub Form_Open(Cancel As Integer)
  25.   lngLastValueEntered = DLast("[CertSerialNum]", "Master Cert Database")
  26. End Sub
  27.  
  28. Private Sub StudentCompany_AfterUpdate()
  29. Me![StudentCompany].DefaultValue = "'" & Me![StudentCompany] & "'"
  30. End Sub
  31.  
  32. Private Sub StudentName_AfterUpdate()
  33. Me![StudentName].DefaultValue = "'" & Me![StudentName] & "'"
  34. End Sub
  35.  
  36. Private Sub System_AfterUpdate()
  37. Me![System].DefaultValue = "'" & Me![System] & "'"
  38. End Sub
  39.  
  40. Private Sub CloseForm_Click()
  41. On Error GoTo Err_CloseForm_Click
  42.  
  43.  
  44.     DoCmd.Close
  45.  
  46. Exit_CloseForm_Click:
  47.     Exit Sub
  48.  
  49. Err_CloseForm_Click:
  50.     MsgBox Err.Description
  51.     Resume Exit_CloseForm_Click
  52.  
  53. End Sub
  54.  
  55.  
  56. Private Sub PLMCertificates_Click()
  57. On Error GoTo Err_PLMCertificates_Click
  58.  
  59.     Dim stDocName As String
  60.  
  61.     stDocName = "ChristineMcLaurinCert"
  62.     DoCmd.OpenReport stDocName, acPreview
  63.  
  64. Exit_PLMCertificates_Click:
  65.     Exit Sub
  66.  
  67. Err_PLMCertificates_Click:
  68.     MsgBox Err.Description
  69.     Resume Exit_PLMCertificates_Click
  70.  
  71. End Sub
  72.  
  73.  
  74. Private Sub PCCCertificates_Click()
  75. On Error GoTo Err_PCCCertificates_Click
  76.  
  77.   Dim stDocName As String, strWhere As String
  78.  
  79.   If Me.Dirty = True Then Me.Dirty = False
  80.  
  81.   If Me.CCClass Then
  82.     stDocName = "(Attendance) - Matt's signature"
  83.     strWhere = "([Instructor] = '%I') AND " & _
  84.                "([InstTitle] = '%T') AND " & _
  85.                "([CertSerialNum] > %S) AND " & _
  86.                "([CertPrintDate] = #%D#)"
  87.     strWhere = Replace(strWhere, "%I", Me.[Instructor])
  88.     strWhere = Replace(strWhere, "%T", Me.[InstTitle])
  89.     strWhere = Replace(strWhere, "%S", lngLastValueEntered)
  90.     strWhere = Replace(strWhere, "%D", Format(Date, "dd/mmmm/yyyy"))
  91.  
  92.     Call DoCmd.OpenReport(stDocName, acPreview, , strWhere)
  93.     'DoCmd.OpenReport stDocName, acPreview (keep if need to change back to original script)
  94.   End If
  95.  
  96. Exit_PCCCertificates_Click:
  97.   Exit Sub
  98.  
  99. Err_PCCCertificates_Click:
  100.   MsgBox Err.Description
  101.   Resume Exit_PCCCertificates_Click
  102.  
  103. End Sub
David
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#16: Mar 28 '08

re: Select Report to Open by CheckBox

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.
Member
  
Join Date: Mar 2008
Posts: 41
#17: Mar 28 '08

re: Select Report to Open by CheckBox

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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#18: Mar 28 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by 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

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 (#)).
Member
  
Join Date: Mar 2008
Posts: 41
#19: Mar 28 '08

re: Select Report to Open by CheckBox

Quote:

Originally Posted by NeoPa

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:
Expand|Select|Wrap|Line Numbers
  1. "([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's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 16,406
#20: Mar 29 '08

re: Select Report to Open by CheckBox

Can you add the following line to your code after line #90 and let me know what it shows when run.
Expand|Select|Wrap|Line Numbers
  1. Call MsgBox(strWhere)
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/other

Forums
Visit our community forums for general discussions and latest on Bytes

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 231,143 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

Copyright 1995-2010 BYTES. All rights Reserved About Bytes | Help | Site Map
Bytes Expert Network: 231,143 Members (3286 Online)