Hiding a dialog form

[PeterK]

I have a command button to run a report. Clicking the button opens a dialog
box with parameter choices. Clicking OK on the dialog box then previews the
report. all this works OK.

My problem is tidying up the screen. The dialog box stays on top, so I need
to manually move it to read the report. I can write code to close the dialog
box, but then I get an error if I print the report (I presume because the
report runs the query again). I have tried to find code that hides or
minimises the dialog box but my efforts haven't worked.

What's the best way to link the form and report?

 

[Albert D. Kallal]

I have a command button to run a report. Clicking the button opens a
dialog
box with parameter choices. Clicking OK on the dialog box then previews
the
report. all this works OK.

Actually, don't make the prompt form a dialog form.

Simply have your button open up the prompt form (it can be a regular
form...you likely can and should set it to model).

Have the person enter the values, and then on this prompt form you have a
view report button.....

There is not need to make this prompt form dialog form if you actually place
the button on this dialog form to launch the report....

I have a whole bunch of screen shots here that shows forms that prompt, and
then launch reports...they might give you some ideas.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

Here is some further ideas:

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

 

[PeterK]

Wow - a very complete answer. I'm not sure I understand everything you've
suggested, but I intend to work through the details and I'll get back to you
if I'm stuck. Thanks for the time you've taken.
--
PeterK

I have a command button to run a report. Clicking the button opens a
dialog
box with parameter choices. Clicking OK on the dialog box then previews
the
report. all this works OK.

Actually, don't make the prompt form a dialog form.

Simply have your button open up the prompt form (it can be a regular
form...you likely can and should set it to model).

Have the person enter the values, and then on this prompt form you have a
view report button.....

There is not need to make this prompt form dialog form if you actually place
the button on this dialog form to launch the report....

I have a whole bunch of screen shots here that shows forms that prompt, and
then launch reports...they might give you some ideas.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

Here is some further ideas:

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

 

[PeterK]

OK, here I am again.

I've spent time today studying your comments and reading some online sites
on SQL language. My programming skills are being sorely stretched!

I tried a simple version of your comments - a prompt form to launch a report
with various student details. On the prompt form is one combo box listing
suburbs, so you have the choice to filter by that field.

This is my coding...

1st attempt:

Private Sub cmdOK_Click()

Dim strWhere As String
strWhere = "strSuburb = 'Titirangi'"

DoCmd.OpenReport "trial_rptStudentDetails", acViewPreview, , strWhere

End Sub

When I clicked OK I got a report filtered on the Titirangi suburb. So far
so good, so I boldly tried...

Private Sub cmdOK_Click()

Dim strWhere As String
strWhere = "strSuburb = '" & cboSuburb & "'"

DoCmd.OpenReport "trial_rptStudentDetails", acViewPreview, , strWhere

End Sub

I then selected Titirangi from the combo box and clicked OK. This time I
got no records at all in the report.

Can you exlpain what I'm doing wrong?

--
PeterK

I have a command button to run a report. Clicking the button opens a
dialog
box with parameter choices. Clicking OK on the dialog box then previews
the
report. all this works OK.

Actually, don't make the prompt form a dialog form.

Simply have your button open up the prompt form (it can be a regular
form...you likely can and should set it to model).

Have the person enter the values, and then on this prompt form you have a
view report button.....

There is not need to make this prompt form dialog form if you actually place
the button on this dialog form to launch the report....

I have a whole bunch of screen shots here that shows forms that prompt, and
then launch reports...they might give you some ideas.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

Here is some further ideas:

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

 

[PeterK]

I have now tried some other ideas:

I checked the fields in the combo box and found there were two - suburb and
student ID. I have removed the student ID field and now can get a report
showing students who live in Titirangi.

However I am now stuck on that particular suburb. Any other susburbs I
choose produce a report with no records at all. Aaaagh!

Look forward to your wisdom.
--
PeterK

I have a command button to run a report. Clicking the button opens a
dialog
box with parameter choices. Clicking OK on the dialog box then previews
the
report. all this works OK.

Actually, don't make the prompt form a dialog form.

Simply have your button open up the prompt form (it can be a regular
form...you likely can and should set it to model).

Have the person enter the values, and then on this prompt form you have a
view report button.....

There is not need to make this prompt form dialog form if you actually place
the button on this dialog form to launch the report....

I have a whole bunch of screen shots here that shows forms that prompt, and
then launch reports...they might give you some ideas.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

Here is some further ideas:

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.