Strange Problem. - Reports

[Olu Solaru]

Sorry for the long post, thought I was done with this project, just happened
to stumble upon this little problem..


In my report generation form (Reports Menu).
If I select a report to run, it displays a parameter form, with a combo box
to select my parameters.

The problem I am having is that if I decide to exit from the parameter form
(..decide not to run the report), Access then proceeds to display the actual
criteria field from the query from which the report was based on.

Explanation
On my paramter form is a quit button, once I click quit, I expect it close
the parameter form, but the reports menu should still be displayed (...which
it is), but I am not sure why the criteria field is displaying. So what I am
getting is a parameter query with the following caption - Forms!frmTraining
Summary By SOP!Combo0.

Here is my event click code for my quit button(query paramter form)
On Error GoTo Err_Exit_Click


DoCmd.Close

Exit_Exit_Click:
Exit Sub

Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click

End Sub.

Conventional wisdom tells me that I may need to actually specify which form
is to be closed (within the DoCmd line).

Sorry for the long post, thought I was done with this project, just happened
to stumble upon this little problem..

 

[Marshall Barton]

Sorry for the long post, thought I was done with this project, just happened
to stumble upon this little problem..


In my report generation form (Reports Menu).
If I select a report to run, it displays a parameter form, with a combo box
to select my parameters.

The problem I am having is that if I decide to exit from the parameter form
(..decide not to run the report), Access then proceeds to display the actual
criteria field from the query from which the report was based on.

Explanation
On my paramter form is a quit button, once I click quit, I expect it close
the parameter form, but the reports menu should still be displayed (...which
it is), but I am not sure why the criteria field is displaying. So what I am
getting is a parameter query with the following caption - Forms!frmTraining
Summary By SOP!Combo0.

Here is my event click code for my quit button(query paramter form)
On Error GoTo Err_Exit_Click

DoCmd.Close

Exit_Exit_Click:
Exit Sub

Err_Exit_Click:
MsgBox Err.Description
Resume Exit_Exit_Click

End Sub.

Conventional wisdom tells me that I may need to actually specify which form
is to be closed (within the DoCmd line).

I do not understand the problem, but if your guess is valid,
use this:

DoCmd.Close acForm, Me.Name, acSaveNo

 

[Olu Solaru]

I will try your suggestion, but let me explain a little better.

1. I have Reports menu form

2. All my reports are based on queries.

3. I created custom forms form setting parameters to my queries.

4. In the open event of the report, I wrote some code to call the custom
form that contain the parameters.

All these work - Here is problem scenario:

i) My reports menu is open
ii) I select a report that I want to run
iii) The custom form for my parameter opens. - Now lets assume that I
realised I selected the wrong report , and want to exit the Parameters form;

This is where my problem is occuring. Once I click on the quit button, it
displays another query parameter, instead of just closing out the parameter
form

 

[Marshall Barton]

Well, that might be a little clearer ;-)

If I've got the correct picture, the second prompt is coming
from the report's query (because the form is no longer
open). What you would need is for a way for the form to
tell the report that the user quit the form so the report
can cancel itself. Your original form will then get an
error saying the operation was canceled, which you can use
code to trap and ignore.

IMO, you are going the long way around to filter the report.
The report should not be aware of the filtering operation,
instead the report's menu form should be in charge of
gathering the filter's criteria and apply it using the
OpenReport method's WhereCondition argument. This way,
neither the report's query nor the report itself have
anything to do with all this stuff.

 

[Olu Solaru]

Now, I can fully explain the proble I am having. Once I choose the option
not to print the report, by clicking the quit button, I get the 'Enter
Parameter Value" dialog box.

 

[Marshall Barton]

Now, I can fully explain the proble I am having. Once I choose the option
not to print the report, by clicking the quit button, I get the 'Enter
Parameter Value" dialog box.

Right. See my previous reply.

 

[Olu Solaru]

Which event am I using within the Reports menu Form to gather the Filter's
criteria. Because remember I have three different reports, with their
corresponding queries and filter forms. will I have to create a module, am I
not exactly sure how to go about this.

Well, that might be a little clearer ;-)

If I've got the correct picture, the second prompt is coming
from the report's query (because the form is no longer
open). What you would need is for a way for the form to
tell the report that the user quit the form so the report
can cancel itself. Your original form will then get an
error saying the operation was canceled, which you can use
code to trap and ignore.

IMO, you are going the long way around to filter the report.
The report should not be aware of the filtering operation,
instead the report's menu form should be in charge of
gathering the filter's criteria and apply it using the
OpenReport method's WhereCondition argument. This way,
neither the report's query nor the report itself have
anything to do with all this stuff.
--
Marsh
MVP [MS Access]

I will try your suggestion, but let me explain a little better.

1. I have Reports menu form

2. All my reports are based on queries.

3. I created custom forms form setting parameters to my queries.

4. In the open event of the report, I wrote some code to call the custom
form that contain the parameters.

All these work - Here is problem scenario:

i) My reports menu is open
ii) I select a report that I want to run
iii) The custom form for my parameter opens. - Now lets assume that I
realised I selected the wrong report , and want to exit the Parameters form;

This is where my problem is occuring. Once I click on the quit button, it
displays another query parameter, instead of just closing out the parameter
form

 

[Marshall Barton]

The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.

 

[Olu Solaru]

I only have one combo box and two command buttons on the form. Does this
make a difference.?

N.B - I also tried the suggested fix for ""Enter Parameter Value" dialog box
appears when you close a Form" - Didn't work

Your patience is appreciated..

The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.
--
Marsh
MVP [MS Access]

Which event am I using within the Reports menu Form to gather the Filter's
criteria. Because remember I have three different reports, with their
corresponding queries and filter forms. will I have to create a module, am I
not exactly sure how to go about this.

 

[Olu Solaru]

Since my reports menu has a listbox containing the group of available
reports, are you suggesting that I also place all my criteria, for all my
reports on the same form.

As I stated in my earlier post I am using combo boxes for all my criteria,
so are you now suggesting the list box, three combo boxes and two command
buttons. If so, how does that change your suggested code.?

The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.
--
Marsh
MVP [MS Access]

Which event am I using within the Reports menu Form to gather the Filter's
criteria. Because remember I have three different reports, with their
corresponding queries and filter forms. will I have to create a module, am I
not exactly sure how to go about this.

 

[Marshall Barton]

If you use a list/combo box to select the report, then the
code would have to be aware of the differences in each
report's criteria. This means it would have the kind of
code I posted before in separate Select Case blocks. OTOH,
if all the report's have the same fields with the same
criteria, then it would just be a matter of using the report
list box's value in the OpenReport method. I need more
details before I can be specific.

This may be leading you down a path you don't want to
travel, but for only 3 reports, I scrap the reports list box
and just use a separate button (pair?) for each report. If
the criteria are the same for all the reports then one set
of criteria combo boxes is sufficient. If the criteria is
different in each report, then I have a separate set of
criteria controls for each report button.
--
Marsh
MVP [MS Access]

Since my reports menu has a listbox containing the group of available
reports, are you suggesting that I also place all my criteria, for all my
reports on the same form.

As I stated in my earlier post I am using combo boxes for all my criteria,
so are you now suggesting the list box, three combo boxes and two command
buttons. If so, how does that change your suggested code.?

The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.

 

[Olu Solaru]

What I am trying to do is basically print Employee Training Details based on
Employee Id, or ControlNumber(this refers to a particular module that an
employee trains on, and therefore will give me all the employees trained on
that module.) and training summary based on batch number (quick explan.. 15
modules under one batch number, but only have approx 20 employees. Not much
data expected in report, and therefore end-users don't want parameters to cut
down on large data. - Don't ask That's what they want.

Recap
Three reports:
Training summary by EMpID -
Training Summary By Control Number
Training Summary By LotNumber - This report runs fines

Now are you suggesting that I just place three command buttons on the
reports menu , or better yet three checkboxes with three command buttons that
will give the user the option to print , preview and quit the menu. Similar
to the sample database.?

I think I see your point, that maybe moving away from my initial format is a
good idea, at any rate, I just want to eliminate the repetitive display of
the 'enter parameter value' at all costs. I guess I was being lazy, almost at
the end of the project, and have this little issue happen, didn't feel like
starting a new form, but I am prepared to that. Please reply

If you use a list/combo box to select the report, then the
code would have to be aware of the differences in each
report's criteria. This means it would have the kind of
code I posted before in separate Select Case blocks. OTOH,
if all the report's have the same fields with the same
criteria, then it would just be a matter of using the report
list box's value in the OpenReport method. I need more
details before I can be specific.

This may be leading you down a path you don't want to
travel, but for only 3 reports, I scrap the reports list box
and just use a separate button (pair?) for each report. If
the criteria are the same for all the reports then one set
of criteria combo boxes is sufficient. If the criteria is
different in each report, then I have a separate set of
criteria controls for each report button.
--
Marsh
MVP [MS Access]

Since my reports menu has a listbox containing the group of available
reports, are you suggesting that I also place all my criteria, for all my
reports on the same form.

As I stated in my earlier post I am using combo boxes for all my criteria,
so are you now suggesting the list box, three combo boxes and two command
buttons. If so, how does that change your suggested code.?

The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.

 

[Marshall Barton]

Ahh, that make it a lot clearer (and a lot easier?). One
last(?) question, does the report look the same regardless
of how the records are selected? The reason I ask is
because it sounds like there really is only one report that
you want to filter in three different ways. If so, there is
no need to select the report, only the criteria that should
be used, which the report is unaware of.

Regardless of that, I am suggesting that you have three(?)
combo boxes down the left side of the form for selecting the
criteria. On the right side of the form, next to each combo
box there would be two buttons one for Print and one for
Preview. Not sure this will display well on your news
reader:

Employee combo Print by EMpID Preview by EMpID
Control combo Print by Control Preview by Control
Lot combo Print by Lot Preview by Lot

The code behind the print by EMpID button would be like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "EMpID = " & [Employee combo]
DoCmd.OpenReport stDoc, , , stCriteria

The others would be the same except for the criteria line
and/or the view argument.
--
Marsh
MVP [MS Access]

What I am trying to do is basically print Employee Training Details based on
Employee Id, or ControlNumber(this refers to a particular module that an
employee trains on, and therefore will give me all the employees trained on
that module.) and training summary based on batch number (quick explan.. 15
modules under one batch number, but only have approx 20 employees. Not much
data expected in report, and therefore end-users don't want parameters to cut
down on large data. - Don't ask That's what they want.

Recap
Three reports:
Training summary by EMpID -
Training Summary By Control Number
Training Summary By LotNumber - This report runs fines

Now are you suggesting that I just place three command buttons on the
reports menu , or better yet three checkboxes with three command buttons that
will give the user the option to print , preview and quit the menu. Similar
to the sample database.?

I think I see your point, that maybe moving away from my initial format is a
good idea, at any rate, I just want to eliminate the repetitive display of
the 'enter parameter value' at all costs. I guess I was being lazy, almost at
the end of the project, and have this little issue happen, didn't feel like
starting a new form, but I am prepared to that.

If you use a list/combo box to select the report, then the
code would have to be aware of the differences in each
report's criteria. This means it would have the kind of
code I posted before in separate Select Case blocks. OTOH,
if all the report's have the same fields with the same
criteria, then it would just be a matter of using the report
list box's value in the OpenReport method. I need more
details before I can be specific.

This may be leading you down a path you don't want to
travel, but for only 3 reports, I scrap the reports list box
and just use a separate button (pair?) for each report. If
the criteria are the same for all the reports then one set
of criteria combo boxes is sufficient. If the criteria is
different in each report, then I have a separate set of
criteria controls for each report button.

Since my reports menu has a listbox containing the group of available
reports, are you suggesting that I also place all my criteria, for all my
reports on the same form.

As I stated in my earlier post I am using combo boxes for all my criteria,
so are you now suggesting the list box, three combo boxes and two command
buttons. If so, how does that change your suggested code.?


:
The code to do this would be in the form's module.
Specifically, the code to open each report is in the Click
event procedure of the button users click on to open the
report.

Personally, I strongly prefer to put the criteria text boxes
on the form next to each button, but you could (with some
additional code) use the forms your were opening from the
report. I think that it's easier for users when they enter
the criteroa and can see what they've entered before
clicking the button than it is for them to deal with a popup
form that appears after clicking the button. Of less
importance, it's also easier to setup this arrangement than
dealing with separate forms for the criteria.

Your button's Click event procedure already has code to open
the report, so all you need to do is modify it a little.
Assuming you add the criteria text boxes to the reports menu
form, you would edit the code to look more like:

Dim stDoc As String
Dim stCriteria As String
stDoc = "name of report"
stCriteria = "fielda = " & [number criteria text box] _
& " And fieldb = """ & [text criteria text box] & """"
DoCmd.OpenReport stDoc, acViewPreview, , stCriteria

Be sure to replace the names I used with the real names of
the fields in the (report's) table and the text boxes you
add to the form. I used two different types of syntax for
the criteria string, the first is the syntax for a field of
type Number and the second demonstrates the syntax for a
field of type Text.