Access command to select from multiple reports

[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

I want it to only print the letter(s) that relate the the individual
account. Can this be done? (Bear with me, I don't know codes too well
yet!)

 

[Klatuu]

You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:

strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"

The syntax above assumes AccountNo is a text field. If it is a numeric field:

strLinkCriteria = "AccountNo = " & Me.txtAccountNo

 

[MuzicK622]

You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:

strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"

The syntax above assumes AccountNo is a text field. If it is a numeric field:

strLinkCriteria = "AccountNo = " & Me.txtAccountNo
--
Dave Hargis, Microsoft Access MVP














- Show quoted text -

Sorry - clicked the wrong reply first!!!
I plugged in your code and changed it to my field name:

DoCmd.OpenReport stDocName, , , strLinkCriteria "Field1 = " &
Me.txtField1

This gives me an error of Compile error:
Expected:end of statement. Did I leave something out?

 

[MuzicK622]

You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:

strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"

The syntax above assumes AccountNo is a text field. If it is a numeric field:

strLinkCriteria = "AccountNo = " & Me.txtAccountNo
--
Dave Hargis, Microsoft Access MVP














- Show quoted text -

ok, scratch my last message(s) please! I realized that I had left out
an =. however with this string:
DoCmd.OpenReport stDocName, , , strLinkCriteria = "Field1 = " &
Me.txtField1
It is giving me "Method or data member not found" it is a number field
so would I still use the ".txt"?

 

[Klatuu]

That is not correct.
It should be:

strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria

Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"

 

[MuzicK622]

That is not correct.
It should be:

strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria

Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"
--
Dave Hargis, Microsoft Access MVP








- Show quoted text -

I must still be doing something wrong. It is still printing all four
reports, but not the entirety of each report;yeah for small steps! I
am getting the one report that I want, but one page from each of the
other reports that give the #error in the fields. Is there a way to
tell the reports that do not relate to this one ID not to print?

Here again, is my revised code:
Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "FA Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria


stDocName = "KT Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria


Exit_Print_Invitation_Click:
Exit Sub

Err_Print_Invitation_Click:
MsgBox Err.Description
Resume Exit_Print_Invitation_Click

End Sub

 

[MuzicK622]

That is not correct.
It should be:

strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria

Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"

- Show quoted text -

I must still be doing something wrong. It is still printing all four
reports, but not the entirety of each report;yeah for small steps! I
am getting the one report that I want, but one page from each of the
other reports that give the #error in the fields. Is there a way to
tell the reports that do not relate to this one ID not to print?

Here again, is my revised code:
Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "FA Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

Exit_Print_Invitation_Click:
Exit Sub

Err_Print_Invitation_Click:
MsgBox Err.Description
Resume Exit_Print_Invitation_Click

End Sub- Hide quoted text -

- Show quoted text -

Also - I entered the text the way you showed it, but when I closed it,
it converted to the ![] expression... Is that normal?

 

[Klatuu]

If it changed the syntax, then it need it like that, not to worry.

You can use the NoData event of the report to close it

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

When you do this, you will get an error 2501 in your code, so you need to
add to your code to allow this error. At the beginning of the procedure,

On Error Resume Next.

Then after each OpenReprt statement:

If err.Number <> 2501 Then
MsgBox err.Number & " " & Err.Description
Exit Sub (or function)
End If

--
Dave Hargis, Microsoft Access MVP

That is not correct.
It should be:

strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria

Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"

:
You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:

strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"

The syntax above assumes AccountNo is a text field. If it is a numeric field:

strLinkCriteria = "AccountNo = " & Me.txtAccountNo

:
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

I want it to only print the letter(s) that relate the the individual
account. Can this be done? (Bear with me, I don't know codes too well
yet!)- Hide quoted text -

- Show quoted text -

Sorry - clicked the wrong reply first!!!
I plugged in your code and changed it to my field name:

DoCmd.OpenReport stDocName, , , strLinkCriteria "Field1 = " &
Me.txtField1

This gives me an error of Compile error:
Expected:end of statement. Did I leave something out?- Hide quoted text -

- Show quoted text -

I must still be doing something wrong. It is still printing all four
reports, but not the entirety of each report;yeah for small steps! I
am getting the one report that I want, but one page from each of the
other reports that give the #error in the fields. Is there a way to
tell the reports that do not relate to this one ID not to print?

Here again, is my revised code:
Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "FA Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

Exit_Print_Invitation_Click:
Exit Sub

Err_Print_Invitation_Click:
MsgBox Err.Description
Resume Exit_Print_Invitation_Click

End Sub- Hide quoted text -

- Show quoted text -

Also - I entered the text the way you showed it, but when I closed it,
it converted to the ![] expression... Is that normal?

 

[MuzicK622]

If it changed the syntax, then it need it like that, not to worry.

You can use the NoData event of the report to close it

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

When you do this, you will get an error 2501 in your code, so you need to
add to your code to allow this error. At the beginning of the procedure,

On Error Resume Next.

Then after each OpenReprt statement:

If err.Number <> 2501 Then
MsgBox err.Number & " " & Err.Description
Exit Sub (or function)
End If

--
Dave Hargis, Microsoft Access MVP

That is not correct.
It should be:
strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria
Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"
--
Dave Hargis, Microsoft Access MVP
:
You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:
strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"
The syntax above assumes AccountNo is a text field. If it is a numeric field:
strLinkCriteria = "AccountNo = " & Me.txtAccountNo
--
Dave Hargis, Microsoft Access MVP
:
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
I want it to only print the letter(s) that relate the the individual
account. Can this be done? (Bear with me, I don't know codes too well
yet!)- Hide quoted text -
- Show quoted text -
Sorry - clicked the wrong reply first!!!
I plugged in your code and changed it to my field name:
DoCmd.OpenReport stDocName, , , strLinkCriteria "Field1 = " &
Me.txtField1
This gives me an error of Compile error:
Expected:end of statement. Did I leave something out?- Hide quoted text -
- Show quoted text -
I must still be doing something wrong. It is still printing all four
reports, but not the entirety of each report;yeah for small steps! I
am getting the one report that I want, but one page from each of the
other reports that give the #error in the fields. Is there a way to
tell the reports that do not relate to this one ID not to print?
Here again, is my revised code:
Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria
stDocName = "FA Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria
stDocName = "KT Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria
stDocName = "KT Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria
Exit_Print_Invitation_Click:
Exit Sub
Err_Print_Invitation_Click:
MsgBox Err.Description
Resume Exit_Print_Invitation_Click
End Sub- Hide quoted text -
- Show quoted text -

Also - I entered the text the way you showed it, but when I closed it,
it converted to the ![] expression... Is that normal?- Hide quoted text -

- Show quoted text -

Mr. Hargis, you are my hero! Thank you for all of your assistance and
your patience with a wanna be access programmer! It works perfectly
now.

 

[Klatuu]

Glad I could help.
--
Dave Hargis, Microsoft Access MVP

If it changed the syntax, then it need it like that, not to worry.

You can use the NoData event of the report to close it

Private Sub Report_NoData(Cancel As Integer)
Cancel = True
End Sub

When you do this, you will get an error 2501 in your code, so you need to
add to your code to allow this error. At the beginning of the procedure,

On Error Resume Next.

Then after each OpenReprt statement:

If err.Number <> 2501 Then
MsgBox err.Number & " " & Err.Description
Exit Sub (or function)
End If

--
Dave Hargis, Microsoft Access MVP

On Dec 13, 10:42 am, (e-mail address removed) wrote:
On Dec 13, 10:22 am, Klatuu <[email protected]> wrote:

That is not correct.
It should be:

strLinkCriteria = "Field1 = " & Me.txtField1
DoCmd.OpenReport stDocName, , , strLinkCriteria

Your syntax says Field1 is a numeric field. It it is a text field, the
first line would be
strLinkCriteria = "Field1 = '" & Me.txtField1 & "'"

:
You need to put the criteria for selecting the accounts in the
strLinkCriteria variable.
It should be like an SQL Where clause without the word Where. For example,
let's say you have a text box on your form that has the Account number and a
field in your table name AccountNo. It would be:

strLinkCriteria = "AccountNo = '" & Me.txtAccountNo & "'"

The syntax above assumes AccountNo is a text field. If it is a numeric field:

strLinkCriteria = "AccountNo = " & Me.txtAccountNo

:
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

I want it to only print the letter(s) that relate the the individual
account. Can this be done? (Bear with me, I don't know codes too well
yet!)- Hide quoted text -

- Show quoted text -

Sorry - clicked the wrong reply first!!!
I plugged in your code and changed it to my field name:

DoCmd.OpenReport stDocName, , , strLinkCriteria "Field1 = " &
Me.txtField1

This gives me an error of Compile error:
Expected:end of statement. Did I leave something out?- Hide quoted text -

- Show quoted text -

I must still be doing something wrong. It is still printing all four
reports, but not the entirety of each report;yeah for small steps! I
am getting the one report that I want, but one page from each of the
other reports that give the #error in the fields. Is there a way to
tell the reports that do not relate to this one ID not to print?

Here again, is my revised code:
Private Sub Print_Invitation_Click()
On Error GoTo Err_Print_Invitation_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "FA Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

stDocName = "KT Pre-Festival Evaluator Invitation Letters"
strLinkCriteria = "Field1 = " & Me![Field1]
DoCmd.OpenReport stDocName, , , strLinkCriteria

Exit_Print_Invitation_Click:
Exit Sub

Err_Print_Invitation_Click:
MsgBox Err.Description
Resume Exit_Print_Invitation_Click

End Sub- Hide quoted text -

- Show quoted text -
Also - I entered the text the way you showed it, but when I closed it,
it converted to the ![] expression... Is that normal?- Hide quoted text -

- Show quoted text -

Mr. Hargis, you are my hero! Thank you for all of your assistance and
your patience with a wanna be access programmer! It works perfectly
now.