Make Command Button print Specific Report

[Francis Cunningham, Jr.]

How do I call up a specific report based on a field on a Form?
I have 5 reports, each designed somewhat similar, but paragraphs are
different on each. I would like the form field "Contracttype" determine
which
report opens to print or preview. I have tried this. In the design view I
created a command button, in the properties under On Click I opened the ...
button and entered this code under Code Builder.
If ContractType = “Electric†Then DoCmd.OpenReport "Electric"
If ContractType = “Gas†Then DoCmd.OpenReport "Gas"
If ContractType = “Oil†Then DoCmd.OpenReport "Oil"
If ContractType = “HeatPump†Then DoCmd.OpenReport "HeatPump"
If ContractType = “Cooling†Then DoCmd.OpenReport "Cooling"
Exit and Saved but the command button doesn't take me where I want to go.
Can someone tell me if my coding is wrong. This is Access2000.
Thanks Frank

 

[Duane Hookom]

What is the complete code. If you copied and pasted the code into this
message, why are the types of double-quotes different?

I would write code like:
Dim strReportName as String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case
DoCmd.OpenReport strReportName

Since the report names seem to match the ContractType, you could use:
DoCmd.OpenReport Me.ContractType
However, I use a naming convention where this would not be as simple.

 

[Francis Cunningham, Jr.]

After I inserted the code went back to the form with the button and click the
command button. The visual basic window opened with a pop up stating ‘Compile
Error Syntax error’. The first and last lines was entered – Private Sub
and End Sub. I don’t know how to fix this.

Private Sub Command51_Click() (This line was Highlighted in yellow
with an -> in front of Private)
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case (This line was also highlighted)
DoCmd.OpenReport strReportName

End Sub

 

[fredg]

After I inserted the code went back to the form with the button and click the
command button. The visual basic window opened with a pop up stating ¡¥Compile
Error Syntax error¡¦. The first and last lines was entered ¡V Private Sub
and End Sub. I don¡¦t know how to fix this.

Private Sub Command51_Click() (This line was Highlighted in yellow
with an -> in front of Private)
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case (This line was also highlighted)
DoCmd.OpenReport strReportName

End Sub

Just some thoughts ....

1) Did you Compile your code before attempting to run it?

I don't believe this is a complete Copy and Paste of the actual code.

The VB editor will choke on
End Case

It should be
End Select

t's also possible you didn't completely delete all of your previous
code.

2) When asking a question about code, it's always best to copy and
paste the actual code. Sometimes it is just an inappropriate dot or
perhaps a miss-spelled word causing the problem. Spell it wrong in
your code but spell it right here and no-one will ever be able to
resolve your problem.
Since the rest of this code looks OK, it is probably not identical to
the actual VBA code.

3) After changing End Case to End Select, comile the code.
Debug + Compile.
If you still have a problem, copy and paste your code intg another
reply.

4) But then I don't understand why you are jumping through hoops
trying to do all of this.
In each instance of your Select Case code, if the Case = Something
the strReportName = The same something.
Why not just go directly to:

DoCmd.OpenReport Me.[ContactType]

No need of If ... then, Select Case, or any other VBA.

5) Frankly, if it were me, I would have one report containing all of
the 5 different paragraphs, and make each paragraph Visible or Not
Visible according to the [ContactType] value, i.e.
Me![ParagraphElectric].Visible = Me![ContactType] = "Electric"
Me![ParagraphGas].Visible = Me![ContactType] = "Gas"
etc.
It would make report upkeep simpler. Only one report to maintain, not
five.

6) All of the above assumes the actual stored value of [ContactType]
is Text.
If [ContactType] is actually a Combo Box (or a LookUp field) , what is
stored may not neccesarily be what is displayed.

 

[Francis Cunningham, Jr.]

Now when I enter the code and compile it, go back to the specific form click
on the command button Labeled 51, I get the error message 'Object does not
support this property'. The VB window open and the highlighted line is
'Select Case Me.ContractType'
Again, what I am trying to do is create a command button that will print a
report based on the ContractType. If 'ContractType' is Electric then the
printed report should be the report named 'Electric'.
This is the complete code for the command button:
Private Sub Command51_Click()
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Select
DoCmd.OpenReport strReportName

End Sub

I appreciates everyone input on this.
Frank

After I inserted the code went back to the form with the button and click the
command button. The visual basic window opened with a pop up stating ‘Compile
Error Syntax error’. The first and last lines was entered – Private Sub
and End Sub. I don’t know how to fix this.

Private Sub Command51_Click() (This line was Highlighted in yellow
with an -> in front of Private)
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case (This line was also highlighted)
DoCmd.OpenReport strReportName

End Sub

Just some thoughts ....

1) Did you Compile your code before attempting to run it?

I don't believe this is a complete Copy and Paste of the actual code.

The VB editor will choke on
End Case

It should be
End Select

t's also possible you didn't completely delete all of your previous
code.

2) When asking a question about code, it's always best to copy and
paste the actual code. Sometimes it is just an inappropriate dot or
perhaps a miss-spelled word causing the problem. Spell it wrong in
your code but spell it right here and no-one will ever be able to
resolve your problem.
Since the rest of this code looks OK, it is probably not identical to
the actual VBA code.

3) After changing End Case to End Select, comile the code.
Debug + Compile.
If you still have a problem, copy and paste your code intg another
reply.

4) But then I don't understand why you are jumping through hoops
trying to do all of this.
In each instance of your Select Case code, if the Case = Something
the strReportName = The same something.
Why not just go directly to:

DoCmd.OpenReport Me.[ContactType]

No need of If ... then, Select Case, or any other VBA.

5) Frankly, if it were me, I would have one report containing all of
the 5 different paragraphs, and make each paragraph Visible or Not
Visible according to the [ContactType] value, i.e.
Me![ParagraphElectric].Visible = Me![ContactType] = "Electric"
Me![ParagraphGas].Visible = Me![ContactType] = "Gas"
etc.
It would make report upkeep simpler. Only one report to maintain, not
five.

6) All of the above assumes the actual stored value of [ContactType]
is Text.
If [ContactType] is actually a Combo Box (or a LookUp field) , what is
stored may not neccesarily be what is displayed.

 

[fredg]

Now when I enter the code and compile it, go back to the specific form click
on the command button Labeled 51, I get the error message 'Object does not
support this property'. The VB window open and the highlighted line is
'Select Case Me.ContractType'
Again, what I am trying to do is create a command button that will print a
report based on the ContractType. If 'ContractType' is Electric then the
printed report should be the report named 'Electric'.
This is the complete code for the command button:
Private Sub Command51_Click()
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Select
DoCmd.OpenReport strReportName

End Sub

I appreciates everyone input on this.
Frank

After I inserted the code went back to the form with the button and click the
command button. The visual basic window opened with a pop up stating ¡¥Compile
Error Syntax error¡¦. The first and last lines was entered ¡V Private Sub
and End Sub. I don¡¦t know how to fix this.

Private Sub Command51_Click() (This line was Highlighted in yellow
with an -> in front of Private)
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case (This line was also highlighted)
DoCmd.OpenReport strReportName

End Sub

:

What is the complete code. If you copied and pasted the code into this
message, why are the types of double-quotes different?

I would write code like:
Dim strReportName as String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case
DoCmd.OpenReport strReportName

Since the report names seem to match the ContractType, you could use:
DoCmd.OpenReport Me.ContractType
However, I use a naming convention where this would not be as simple.

--
Duane Hookom
Microsoft Access MVP

:

How do I call up a specific report based on a field on a Form?
I have 5 reports, each designed somewhat similar, but paragraphs are
different on each. I would like the form field "Contracttype" determine
which
report opens to print or preview. I have tried this. In the design view I
created a command button, in the properties under On Click I opened the ...
button and entered this code under Code Builder.
If ContractType = ¡§Electric¡¨ Then DoCmd.OpenReport "Electric"
If ContractType = ¡§Gas¡¨ Then DoCmd.OpenReport "Gas"
If ContractType = ¡§Oil¡¨ Then DoCmd.OpenReport "Oil"
If ContractType = ¡§HeatPump¡¨ Then DoCmd.OpenReport "HeatPump"
If ContractType = ¡§Cooling¡¨ Then DoCmd.OpenReport "Cooling"
Exit and Saved but the command button doesn't take me where I want to go.
Can someone tell me if my coding is wrong. This is Access2000.
Thanks Frank

Just some thoughts ....

1) Did you Compile your code before attempting to run it?

I don't believe this is a complete Copy and Paste of the actual code.

The VB editor will choke on
End Case

It should be
End Select

t's also possible you didn't completely delete all of your previous
code.

2) When asking a question about code, it's always best to copy and
paste the actual code. Sometimes it is just an inappropriate dot or
perhaps a miss-spelled word causing the problem. Spell it wrong in
your code but spell it right here and no-one will ever be able to
resolve your problem.
Since the rest of this code looks OK, it is probably not identical to
the actual VBA code.

3) After changing End Case to End Select, comile the code.
Debug + Compile.
If you still have a problem, copy and paste your code intg another
reply.

4) But then I don't understand why you are jumping through hoops
trying to do all of this.
In each instance of your Select Case code, if the Case = Something
the strReportName = The same something.
Why not just go directly to:

DoCmd.OpenReport Me.[ContactType]

No need of If ... then, Select Case, or any other VBA.

5) Frankly, if it were me, I would have one report containing all of
the 5 different paragraphs, and make each paragraph Visible or Not
Visible according to the [ContactType] value, i.e.
Me![ParagraphElectric].Visible = Me![ContactType] = "Electric"
Me![ParagraphGas].Visible = Me![ContactType] = "Gas"
etc.
It would make report upkeep simpler. Only one report to maintain, not
five.

6) All of the above assumes the actual stored value of [ContactType]
is Text.
If [ContactType] is actually a Combo Box (or a LookUp field) , what is
stored may not neccesarily be what is displayed.

I have no idea what your error message refers to here.

Why are you continuing along this path, anyway?

Private Sub Command51_Click()
If Not IsNull(Me![ContractType]) Then
DoCmd.OpenReport Me.[ContactType]
End if
End Sub

is all you need, assuming, once again, that the actual value stored in
[ContractType] is text (see my #4 and #6 in my previous reply).

 

[Francis Cunningham, Jr.]

Using the Code below, I am still receiving error messages. What I am trying
to accomplish is when a button on a particular form is pushed then a
particular report will open based on a particular field on that form. So if
the ContractType on the form (which is a text field) is ‘Gas’ then the report
to open would be ‘Gas’; if the ContractType is ‘Electric’ then the report to
open would be ‘Electric’ and so on. I though by having the ContractType name
and the Report name the same it would be less complicated.
One Command button for multiple reports based on ‘ContractType’.

Private Sub Command74_Click()
If Not IsNull(Me![ContractType]) Then
DoCmd.OpenReport Me.[ContactType]
End If
End Sub

I do appreciate all the help on this. I not sure I understand coding all
that much.
Frank


Private Sub Command74_Click()
If Not IsNull(Me![ContractType]) Then
DoCmd.OpenReport Me.[ContactType]
End If
End Sub

Now when I enter the code and compile it, go back to the specific form click
on the command button Labeled 51, I get the error message 'Object does not
support this property'. The VB window open and the highlighted line is
'Select Case Me.ContractType'
Again, what I am trying to do is create a command button that will print a
report based on the ContractType. If 'ContractType' is Electric then the
printed report should be the report named 'Electric'.
This is the complete code for the command button:
Private Sub Command51_Click()
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Select
DoCmd.OpenReport strReportName

End Sub

I appreciates everyone input on this.
Frank

On Sat, 3 Nov 2007 07:41:02 -0700, Francis Cunningham, Jr. wrote:

After I inserted the code went back to the form with the button and click the
command button. The visual basic window opened with a pop up stating ‘Compile
Error Syntax error’. The first and last lines was entered – Private Sub
and End Sub. I don’t know how to fix this.

Private Sub Command51_Click() (This line was Highlighted in yellow
with an -> in front of Private)
Dim strReportName As String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case (This line was also highlighted)
DoCmd.OpenReport strReportName

End Sub

:

What is the complete code. If you copied and pasted the code into this
message, why are the types of double-quotes different?

I would write code like:
Dim strReportName as String
Select Case Me.ContractType
Case "Electric"
strReportName = "Electric"
Case "Gas"
strReportName = "Gas"
Case "Oil"
strReportName = "Oil"
Case "HeatPump"
strReportName = "HeatPump"
Case "Cooling"
strReportName = "Cooling"
End Case
DoCmd.OpenReport strReportName

Since the report names seem to match the ContractType, you could use:
DoCmd.OpenReport Me.ContractType
However, I use a naming convention where this would not be as simple.

--
Duane Hookom
Microsoft Access MVP

:

How do I call up a specific report based on a field on a Form?
I have 5 reports, each designed somewhat similar, but paragraphs are
different on each. I would like the form field "Contracttype" determine
which
report opens to print or preview. I have tried this. In the design view I
created a command button, in the properties under On Click I opened the ...
button and entered this code under Code Builder.
If ContractType = “Electric†Then DoCmd.OpenReport "Electric"
If ContractType = “Gas†Then DoCmd.OpenReport "Gas"
If ContractType = “Oil†Then DoCmd.OpenReport "Oil"
If ContractType = “HeatPump†Then DoCmd.OpenReport "HeatPump"
If ContractType = “Cooling†Then DoCmd.OpenReport "Cooling"
Exit and Saved but the command button doesn't take me where I want to go.
Can someone tell me if my coding is wrong. This is Access2000.
Thanks Frank


Just some thoughts ....

1) Did you Compile your code before attempting to run it?

I don't believe this is a complete Copy and Paste of the actual code.

The VB editor will choke on
End Case

It should be
End Select

t's also possible you didn't completely delete all of your previous
code.

2) When asking a question about code, it's always best to copy and
paste the actual code. Sometimes it is just an inappropriate dot or
perhaps a miss-spelled word causing the problem. Spell it wrong in
your code but spell it right here and no-one will ever be able to
resolve your problem.
Since the rest of this code looks OK, it is probably not identical to
the actual VBA code.

3) After changing End Case to End Select, comile the code.
Debug + Compile.
If you still have a problem, copy and paste your code intg another
reply.

4) But then I don't understand why you are jumping through hoops
trying to do all of this.
In each instance of your Select Case code, if the Case = Something
the strReportName = The same something.
Why not just go directly to:

DoCmd.OpenReport Me.[ContactType]

No need of If ... then, Select Case, or any other VBA.

5) Frankly, if it were me, I would have one report containing all of
the 5 different paragraphs, and make each paragraph Visible or Not
Visible according to the [ContactType] value, i.e.
Me![ParagraphElectric].Visible = Me![ContactType] = "Electric"
Me![ParagraphGas].Visible = Me![ContactType] = "Gas"
etc.
It would make report upkeep simpler. Only one report to maintain, not
five.

6) All of the above assumes the actual stored value of [ContactType]
is Text.
If [ContactType] is actually a Combo Box (or a LookUp field) , what is
stored may not neccesarily be what is displayed.

I have no idea what your error message refers to here.

Why are you continuing along this path, anyway?

Private Sub Command51_Click()
If Not IsNull(Me![ContractType]) Then
DoCmd.OpenReport Me.[ContactType]
End if
End Sub

is all you need, assuming, once again, that the actual value stored in
[ContractType] is text (see my #4 and #6 in my previous reply).