Access command to select from multiple reports for printing

[MuzicK622]

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:

Private Sub Print_Invitation_Click() 
On Error GoTo Err_Print_Invitation_Click 
 
 
    Dim stDocName As String 
    Dim stLinkCriteria As String 
    stDocName = "Evaluator Invitation Letters" 
    DoCmd.OpenReport stDocName, , , stlLinkCriteria 
 
 
    stDocName = "FA Pre-Festival Evaluator Invitation Letters" 
    DoCmd.OpenReport stDocName, , , stlLinkCriteria 
 
 
    stDocName = "KT Evaluator Invitation Letters" 
    DoCmd.OpenReport stDocName, , , stlLinkCriteria 
 
 
    stDocName = "KT Pre-Festival Evaluator Invitation Letters" 
    DoCmd.OpenReport stDocName, , , stlLinkCriteria 
 
 
Exit_Print_Invitation_Click: 
    Exit Sub 
 
 
Err_Print_Invitation_Click: 
    MsgBox Err.Description 
    Resume Exit_Print_Invitation_Click 
 
 
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!!

[NeoPa]

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.

[MuzicK622]

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!

[NeoPa]

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.

[MuzicK622]

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:

Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click
 
On Error Resume Next
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "Evaluator Invitation Letters"
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
    DoCmd.OpenReport stDocName, , , strLinkCriteria
    If Err.Number <> 2501 Then
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End If
 
 
    stDocName = "FA Pre-Festival Evaluator Invitation Letters"
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
    DoCmd.OpenReport stDocName, , , strLinkCriteria
    If Err.Number <> 2501 Then
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End If
 
    stDocName = "KT Evaluator Invitation Letters"
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
    DoCmd.OpenReport stDocName, , , strLinkCriteria
    If Err.Number <> 2501 Then
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End If
 
    stDocName = "KT Pre-Festival Evaluator Invitation Letters"
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
    DoCmd.OpenReport stDocName, , , strLinkCriteria
    If Err.Number <> 2501 Then
        MsgBox Err.Number & " " & Err.Description
        Exit Sub
    End If
 
Exit_Print_Invitation_Click:
    Exit Sub
 
Err_Print_Invitation_Click:
    MsgBox Err.Description
    Resume Exit_Print_Invitation_Click
 
End Sub

[NeoPa]

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).

Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click
 
    Dim stDocName As String
    Dim strLinkCriteria As String
 
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
 
    stDocName = "Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "FA Pre-Festival Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "KT Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "KT Pre-Festival Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
Exit_Print_Invitation_Click:
    Exit Sub
 
Err_Print_Invitation_Click:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Print_Invitation_Click
 
End Sub

Good luck however you proceed anyway :)

[NeoPa]

...
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.

[MuzicK622]

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).

Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click
 
    Dim stDocName As String
    Dim strLinkCriteria As String
 
    If IsNull(Me!Field1) Then Exit Sub
    strLinkCriteria = "Field1 = " & Me![Field1]
 
    stDocName = "Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "FA Pre-Festival Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "KT Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
    stDocName = "KT Pre-Festival Evaluator Invitation Letters"
    DoCmd.OpenReport stDocName, , , strLinkCriteria
 
Exit_Print_Invitation_Click:
    Exit Sub
 
Err_Print_Invitation_Click:
    MsgBox Err.Number & " " & Err.Description
    Resume Exit_Print_Invitation_Click
 
End Sub

Good luck however you proceed anyway :)

Thanks! :) My command works beautifully. My users thank you!

[NeoPa]

You're welcome ;)
Wishing you success in your project.