By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,027 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,027 IT Pros & Developers. It's quick & easy.

Access command to select from multiple reports for printing

P: 4
I have a command button on a form that I want to use to print one or
two of four letters (reports) based on the criteria tied to the
persons account. Right now my statement is this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Invitation_Click() 
  2. On Error GoTo Err_Print_Invitation_Click 
  3.  
  4.  
  5.     Dim stDocName As String 
  6.     Dim stLinkCriteria As String 
  7.     stDocName = "Evaluator Invitation Letters" 
  8.     DoCmd.OpenReport stDocName, , , stlLinkCriteria 
  9.  
  10.  
  11.     stDocName = "FA Pre-Festival Evaluator Invitation Letters" 
  12.     DoCmd.OpenReport stDocName, , , stlLinkCriteria 
  13.  
  14.  
  15.     stDocName = "KT Evaluator Invitation Letters" 
  16.     DoCmd.OpenReport stDocName, , , stlLinkCriteria 
  17.  
  18.  
  19.     stDocName = "KT Pre-Festival Evaluator Invitation Letters" 
  20.     DoCmd.OpenReport stDocName, , , stlLinkCriteria 
  21.  
  22.  
  23. Exit_Print_Invitation_Click: 
  24.     Exit Sub 
  25.  
  26.  
  27. Err_Print_Invitation_Click: 
  28.     MsgBox Err.Description 
  29.     Resume Exit_Print_Invitation_Click 
  30.  
  31.  
  32. End Sub
Right now it prints out the entirety of all reports.I want it to only print the letter(s) that relate the the individual account. Can this be done?
I have tried the same thing using DIM stWhere As String code and I can't seem to get it to work that way either.

Thanks!!
Dec 12 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,615
Your problem is twofold.
  1. stLinkCriteria is misspelled as stlLinkCriteria when you use it in the DoCmd.OpenReport lines.
  2. stLinkCriteria is never set to show just the account you're interested in.
Basically, stLinkCriteria allows you to specify a WHERE condition for the data that drives your report. You haven't told us enough for us to be able to tell you what it should be set to. However, that's what you need to do and we can help further assuming you share with us the relevant details of what data you're working with and what's available where on your forms.
Dec 13 '07 #2

P: 4
Thank you for your reply! I did catch the misspelling and corrected it already. Since I'm not very knowledgeable about Code (all self-taught so I have many deficiencies), I may give more or less information than needed, so please bear with me!!

My set-up is this:
I have one basic name and address table and form with 3 checkboxes that connect to four queries to determine what report they are associated with; 1. FA invitations, 2. KT invitations, 3. pre-festival.

There are four possibile reports 1. FA on-site, 2. FA pre- festival, 3. KT on-site, 4 KT pre-festival (these are the reports shown in the code example).

What I want to accomplish is using the command on the individual's form to automatically select report(s) that is(are) associated to him/her to print.

Here are the fields that are both on the form and these reports:
ID (hidden)
first name
last name
Address1
Address2
City
St
PostalCode

If it helps I am using ACCESS 2003 and am on Microsoft offic XP. Let me know if I need to give more information. Thanks again!
Dec 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,615
There seems to be two questions outstanding then :
  1. Which control contains the data that specifies which account should be used?
  2. Which controls contain the information that determines which of the four reports are required for the current (selected) account (and how)?
I think we can proceed if these two questions are answered clearly and accurately.
Dec 13 '07 #4

P: 4
Thank you for your time and I'm sorry to have bothered you! I really appreciate your willingness to help.

I obtained some assistance elsewhere and here is my end result:

I changed the NoData in my reports to Cancel = True
and then changed the Code under my command button to:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Invitation_Click()
  2. On Error GoTo Err_Print_Invitation_Click
  3.  
  4. On Error Resume Next
  5.  
  6.     Dim stDocName As String
  7.     Dim stLinkCriteria As String
  8.  
  9.     stDocName = "Evaluator Invitation Letters"
  10.     If IsNull(Me!Field1) Then Exit Sub
  11.     strLinkCriteria = "Field1 = " & Me![Field1]
  12.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  13.     If Err.Number <> 2501 Then
  14.         MsgBox Err.Number & " " & Err.Description
  15.         Exit Sub
  16.     End If
  17.  
  18.  
  19.     stDocName = "FA Pre-Festival Evaluator Invitation Letters"
  20.     If IsNull(Me!Field1) Then Exit Sub
  21.     strLinkCriteria = "Field1 = " & Me![Field1]
  22.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  23.     If Err.Number <> 2501 Then
  24.         MsgBox Err.Number & " " & Err.Description
  25.         Exit Sub
  26.     End If
  27.  
  28.     stDocName = "KT Evaluator Invitation Letters"
  29.     If IsNull(Me!Field1) Then Exit Sub
  30.     strLinkCriteria = "Field1 = " & Me![Field1]
  31.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  32.     If Err.Number <> 2501 Then
  33.         MsgBox Err.Number & " " & Err.Description
  34.         Exit Sub
  35.     End If
  36.  
  37.     stDocName = "KT Pre-Festival Evaluator Invitation Letters"
  38.     If IsNull(Me!Field1) Then Exit Sub
  39.     strLinkCriteria = "Field1 = " & Me![Field1]
  40.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  41.     If Err.Number <> 2501 Then
  42.         MsgBox Err.Number & " " & Err.Description
  43.         Exit Sub
  44.     End If
  45.  
  46. Exit_Print_Invitation_Click:
  47.     Exit Sub
  48.  
  49. Err_Print_Invitation_Click:
  50.     MsgBox Err.Description
  51.     Resume Exit_Print_Invitation_Click
  52.  
  53. End Sub
Dec 13 '07 #5

NeoPa
Expert Mod 15k+
P: 31,615
That's fine, but there are a few small issues with this code.
  1. There seems no way that the individual letters are selected. If one is printed they all are.
  2. strLinkCriteria is set four times but always exactly the same way.
  3. Me!Field1 is checked four times but again, always the same way. If it's null the subsequent lines will never be executed anyway.
  4. strLinkCriteria is never DIMmed (stLinkCriteria is - which is never used). Use "Option Explicit" in ALL your VBA modules. There's no down-side.
The following should give you exactly the same results but without so much redundant code (This doesn't fix the problem - just tidies away the redundancies somewhat).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Invitation_Click()
  2. On Error GoTo Err_Print_Invitation_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim strLinkCriteria As String
  6.  
  7.     If IsNull(Me!Field1) Then Exit Sub
  8.     strLinkCriteria = "Field1 = " & Me![Field1]
  9.  
  10.     stDocName = "Evaluator Invitation Letters"
  11.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  12.  
  13.     stDocName = "FA Pre-Festival Evaluator Invitation Letters"
  14.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  15.  
  16.     stDocName = "KT Evaluator Invitation Letters"
  17.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  18.  
  19.     stDocName = "KT Pre-Festival Evaluator Invitation Letters"
  20.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  21.  
  22. Exit_Print_Invitation_Click:
  23.     Exit Sub
  24.  
  25. Err_Print_Invitation_Click:
  26.     MsgBox Err.Number & " " & Err.Description
  27.     Resume Exit_Print_Invitation_Click
  28.  
  29. End Sub
Good luck however you proceed anyway :)
Dec 13 '07 #6

NeoPa
Expert Mod 15k+
P: 31,615
...
I changed the NoData in my reports to Cancel = True
...
I skipped over this a little quickly. I wasn't sure what you meant at first.
I'm glad I gave it a closer look :) This is gold. I didn't even realise handling this was so easy. I just wish I'd noticed it before I'd developed so many hundreds of reports.

I think I'll be using this quite heavily in future. Thanks for the tip.
Dec 13 '07 #7

P: 4
That's fine, but there are a few small issues with this code.
  1. There seems no way that the individual letters are selected. If one is printed they all are.
  2. strLinkCriteria is set four times but always exactly the same way.
  3. Me!Field1 is checked four times but again, always the same way. If it's null the subsequent lines will never be executed anyway.
  4. strLinkCriteria is never DIMmed (stLinkCriteria is - which is never used). Use "Option Explicit" in ALL your VBA modules. There's no down-side.
The following should give you exactly the same results but without so much redundant code (This doesn't fix the problem - just tidies away the redundancies somewhat).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Print_Invitation_Click()
  2. On Error GoTo Err_Print_Invitation_Click
  3.  
  4.     Dim stDocName As String
  5.     Dim strLinkCriteria As String
  6.  
  7.     If IsNull(Me!Field1) Then Exit Sub
  8.     strLinkCriteria = "Field1 = " & Me![Field1]
  9.  
  10.     stDocName = "Evaluator Invitation Letters"
  11.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  12.  
  13.     stDocName = "FA Pre-Festival Evaluator Invitation Letters"
  14.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  15.  
  16.     stDocName = "KT Evaluator Invitation Letters"
  17.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  18.  
  19.     stDocName = "KT Pre-Festival Evaluator Invitation Letters"
  20.     DoCmd.OpenReport stDocName, , , strLinkCriteria
  21.  
  22. Exit_Print_Invitation_Click:
  23.     Exit Sub
  24.  
  25. Err_Print_Invitation_Click:
  26.     MsgBox Err.Number & " " & Err.Description
  27.     Resume Exit_Print_Invitation_Click
  28.  
  29. End Sub
Good luck however you proceed anyway :)

Thanks! :) My command works beautifully. My users thank you!
Dec 13 '07 #8

NeoPa
Expert Mod 15k+
P: 31,615
You're welcome ;)
Wishing you success in your project.
Dec 13 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.