Automating Reports from a Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

HI,
I have a Command button set up to generate a report through a Query that
requires that a Parameter Value to be entered. I have set up a list box
that is displaying a list of parameters. I would like for the User to be
able to simply Click on the desired parameter in the list and then have that
selection to be automatically entered as the Parameter Value and then for the
report to execute. How do you do that? I'm not much of a VB coder so if I
need to write and Onclick event please give me an example.

Thanks for the help,
Larry
 
Larry,

Probably the easiest is to not use a Parameter Query at all. Replace
the parameter prompt in the Query that the report is based on, with a
Criteria that refers to the listbox on the form, using syntax such as...
[Forms]![NameOfForm]![YourListbox]

Please post back if this doesn't do the trick.
 
Steve,

I tried it but apparently the Query is not seeing that anything had been
selected in the List Box. So when the Report and/or Query runs it asks me to
input a Parameter.

There is an example in the Northwind Data base "Sales Report Dialog" Form
where they generate a "Sales By Category" report. Basically they pick a
category from a list box and use that value in a Sub routine that they've
created to manage printing operations. I see what their doing but so far all
attempts to adapt what they did to my applications have failed. Its because
I'm not very competent in VB.

Steve Schapel said:
Larry,

Probably the easiest is to not use a Parameter Query at all. Replace
the parameter prompt in the Query that the report is based on, with a
Criteria that refers to the listbox on the form, using syntax such as...
[Forms]![NameOfForm]![YourListbox]

Please post back if this doesn't do the trick.

--
Steve Schapel, Microsoft Access MVP

HI,
I have a Command button set up to generate a report through a Query that
requires that a Parameter Value to be entered. I have set up a list box
that is displaying a list of parameters. I would like for the User to be
able to simply Click on the desired parameter in the list and then have that
selection to be automatically entered as the Parameter Value and then for the
report to execute. How do you do that? I'm not much of a VB coder so if I
need to write and Onclick event please give me an example.

Thanks for the help,
Larry
Click to expand...
Click to expand...
 
Larry,

Cound you please post back with the SQL view of the query that you are
using as the record source of the report?
 
Steve,

I've just pasted the last line in the SQL view that my Report is linked to,
there's lots of stuff but nothing unique. I think its the [] that's halting
the query and asking for a Parameter. But I don't know what to do about it.

FROM Z_PPE_Audit_Table_Alternate
WHERE
((([Z_PPE_Audit_Table_Alternate].[PPE_Observer])=[Forms]!Supervisors_Safety_Log_Control_Panel!PPE_Observer_Text));

Supervisors_Safety_Log_Control_Panel is the Form where I have a Text Box
called "Ovserver_Text"; I'm populating that Text field with a Combo Box
that's on the same Form. The Query is pulling data from only one Table -
"Z_PPE_Audit_Table_Alternate". Nothing fancy about it.

Shown here is the VB Code that I've been trying to make work. It does
everything that it is suppose to until it gets to the last line. The
"strWhereCategory" register contains the right information but I get an error
3075: Syntax error(missing operator) in query expression '(Jerry Potter)'.
Note that "Jerry Potter" is the info that's in the "strWhereCategory".

Thanks,
Larry

Private Sub Combo93_Click()
Me!PPE_Observer_Text = Me!Combo93
Me!PPE_Observer_2 = Me!PPE_Observer_Text
Me!Text100 = Me!PPE_Observer_Text
Dim strWhereCategory As String
strWhereCategory = Me!Text100
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acPreview, ,
strWhereCategory
End Sub
 
Larry,

Thanks for the information. There are a few issues here. First of all,
as I understand it, the criteria to restrict your report data to the
Observer_Text on the form, is already taken care of in the query. So it
seems to me that any Where Condition in the OpenReport code that
attempts to do the same thing is totally redundant and can be dispensed
with. Mind you, there is a confusion in your description, which is that
the control on the form where you are referring for your criteria is
Observer_Text whereas the SQL of the query shows it as
PPE_Observer_Text, so I'm not sure what's going on here.

However, assuming it is correct to use a Where Condition in the
OpenReport code, you have the syntax wrong, which is what the error
message is all about. I think it should be like this...
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]=" & strWhereCategory

I could not follow the purpose of your data manipulations. It looks
like you end up with the same value assigned to Combo93,
PPE_Observer_Text, PPE_Observer_2, Text100, and strWhereCategory. Why?
Wouldn't it be just as easy to leave all of that out, and just do like
this?...
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]='" & Me.Combo93 & "'"
 
Hi Steve,

I can't tell you how much I appreciate your help. I lose a lot of sleep
over things like this. The only thing I have in the Click Procedure is this
line that you gave me:

DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]='" & Me.Combo93 & "'"

It works great. All that other crazy stuff that I wrote into the Procedure
were just my failed attempts to get something to work. The syntax is always
my undoing when it comes to VB code. For example - I don't have a clue what
'"& _____&'" does when it executes. Short of taking classes on VB do you
know of some kind of "cheat sheet" or "quick reference" that would help me
learn the logic behind the Syntax?

Thanks for the help,
Larry

Steve Schapel said:
Larry,

Thanks for the information. There are a few issues here. First of all,
as I understand it, the criteria to restrict your report data to the
Observer_Text on the form, is already taken care of in the query. So it
seems to me that any Where Condition in the OpenReport code that
attempts to do the same thing is totally redundant and can be dispensed
with. Mind you, there is a confusion in your description, which is that
the control on the form where you are referring for your criteria is
Observer_Text whereas the SQL of the query shows it as
PPE_Observer_Text, so I'm not sure what's going on here.

However, assuming it is correct to use a Where Condition in the
OpenReport code, you have the syntax wrong, which is what the error
message is all about. I think it should be like this...
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]=" & strWhereCategory

I could not follow the purpose of your data manipulations. It looks
like you end up with the same value assigned to Combo93,
PPE_Observer_Text, PPE_Observer_2, Text100, and strWhereCategory. Why?
Wouldn't it be just as easy to leave all of that out, and just do like
this?...
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]='" & Me.Combo93 & "'"

--
Steve Schapel, Microsoft Access MVP

Steve,

I've just pasted the last line in the SQL view that my Report is linked to,
there's lots of stuff but nothing unique. I think its the [] that's halting
the query and asking for a Parameter. But I don't know what to do about it.

FROM Z_PPE_Audit_Table_Alternate
WHERE
((([Z_PPE_Audit_Table_Alternate].[PPE_Observer])=[Forms]!Supervisors_Safety_Log_Control_Panel!PPE_Observer_Text));

Supervisors_Safety_Log_Control_Panel is the Form where I have a Text Box
called "Ovserver_Text"; I'm populating that Text field with a Combo Box
that's on the same Form. The Query is pulling data from only one Table -
"Z_PPE_Audit_Table_Alternate". Nothing fancy about it.

Shown here is the VB Code that I've been trying to make work. It does
everything that it is suppose to until it gets to the last line. The
"strWhereCategory" register contains the right information but I get an error
3075: Syntax error(missing operator) in query expression '(Jerry Potter)'.
Note that "Jerry Potter" is the info that's in the "strWhereCategory".

Thanks,
Larry

Private Sub Combo93_Click()
Me!PPE_Observer_Text = Me!Combo93
Me!PPE_Observer_2 = Me!PPE_Observer_Text
Me!Text100 = Me!PPE_Observer_Text
Dim strWhereCategory As String
strWhereCategory = Me!Text100
DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acPreview, ,
strWhereCategory
End Sub
Click to expand...
Click to expand...
 
Larry,

I don't know of any qick reference guides. It is always good to have an
Access book handy, and "Building Access Applications" by John Viescas is
great. In the meantime, I imagine that every time you try something
like this, and finally get it right, you learn how to do it for next
time :-)

As regards the specific question, the Where Condition argument of the
OpenReport method requires a String. The '&' character is a string
concatenator. And Me.Combo93 returns the value of the control on the
form. So, if you did like this:
"Fred was" & " here"
.... you would get:
"Fred was here"
in other words concatenating two strings into one.
When we put:
"[PPE_Observer]='" & Me.Combo93 & "'"
we are concatenating 3 items together, same process. If the value of
Combo93 is Jerry Potter, it gives:
"[PPE_Observer]='Jerry Potter'"
.... which is a well-formed criteria syntax. The ' 's have to be around
the Jerry Potter because it is text. Just as if you put a criteria in a
text field in a query it has to be enclosed. If Combo93 was numerical,
let's say with a value of 33, we would not include the ' 's. We would put:
"[PPE_Observer]=" & Me.Combo93
.... which would give:
"[PPE_Observer]=33"
 
Steve,

Thanks again. Using what you've taught me in resolving this problem has
already helepd me build some more functionallity into my application. I will
certainly pick up a copy of "Building Access Applications"

Best Regards,
Larry

Steve Schapel said:
Larry,

I don't know of any qick reference guides. It is always good to have an
Access book handy, and "Building Access Applications" by John Viescas is
great. In the meantime, I imagine that every time you try something
like this, and finally get it right, you learn how to do it for next
time :-)

As regards the specific question, the Where Condition argument of the
OpenReport method requires a String. The '&' character is a string
concatenator. And Me.Combo93 returns the value of the control on the
form. So, if you did like this:
"Fred was" & " here"
.... you would get:
"Fred was here"
in other words concatenating two strings into one.
When we put:
"[PPE_Observer]='" & Me.Combo93 & "'"
we are concatenating 3 items together, same process. If the value of
Combo93 is Jerry Potter, it gives:
"[PPE_Observer]='Jerry Potter'"
.... which is a well-formed criteria syntax. The ' 's have to be around
the Jerry Potter because it is text. Just as if you put a criteria in a
text field in a query it has to be enclosed. If Combo93 was numerical,
let's say with a value of 33, we would not include the ' 's. We would put:
"[PPE_Observer]=" & Me.Combo93
.... which would give:
"[PPE_Observer]=33"

--
Steve Schapel, Microsoft Access MVP

Hi Steve,

I can't tell you how much I appreciate your help. I lose a lot of sleep
over things like this. The only thing I have in the Click Procedure is this
line that you gave me:

DoCmd.OpenReport "Z_PPE_Compliance_Audit_Report", acViewPreview, ,
"[PPE_Observer]='" & Me.Combo93 & "'"

It works great. All that other crazy stuff that I wrote into the Procedure
were just my failed attempts to get something to work. The syntax is always
my undoing when it comes to VB code. For example - I don't have a clue what
'"& _____&'" does when it executes. Short of taking classes on VB do you
know of some kind of "cheat sheet" or "quick reference" that would help me
learn the logic behind the Syntax?

Thanks for the help,
Larry
Click to expand...
Click to expand...
 
Back
Top