Report, filter, pop-up

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

Guest

I would like to have my report (when launched) open a pop-up form that contains a list box. The list box would give the user a list of all of the valid names present in the database table. The user can then double click on the name desired, the value would be passed back to the report and the report (through code) would filter the data based on the selected name.

I have been able to open the pop-up form from the report by using the report's open event, then using docmd.openform, but I cannot get the value selected by the user when they double click on the name in the list box. How do I retieve it?

I prefer to run the report and have it drive the form rather than the other way around. Any suggestions?

Thanks Larry
 
Sorry, Larry, I don't think this is possible to do what you ask. Once
the report is opened, it is too late to manipulate the report's data.
Setting the data criteria on the form, and using this in the report's
underlying query, before the report is run, is definitely the way to
go.

- Steve Schapel, Microsoft Access MVP
 
I would like to have my report
(when launched) open a pop-up
form that contains a list box. The
list box would give the user a list
of all of the valid names present
in the database table. The user
can then double click on the name
desired, the value would be passed
back to the report and the report
(through code) would filter the data
based on the selected name.
Click to expand...

I'm not sure why it is important for the "report to drive the form", but,
yes, you can do what you want, at least with the following changes: Combo
Box instead of List Box, single click to select as is standard with both
Combo and List Boxes, and retrieve only the single Record instead of
"filtering".

In the report's Open event, open your "selection form" using the "acDialog"
option for Window mode. Here's the statement I used:

DoCmd.OpenForm "frmSelectEmployee", acNormal, , , , acDialog

When the form is opened in this window mode, the report pauses (isn't even
visible).

In the AfterUpdate event of the Combo or List Box, create an SQL statement
to replace the Record Source of the Report. Here's the code I used:

Dim strSQL As String
strSQL = "SELECT * FROM Employees WHERE [EmployeeID] =" & Me!Combo0
Reports!rptEmployeeDetail.RecordSource = strSQL

When you close the Form, the report will run, using the SQL you created as
its RecordSource.

It's possible that, with some care, and using the OpenArgs argument of
DoCmd.OpenForm, that you could make a generic "selection form". But on
simple things like this, I often find it simpler to create and easier to
maintain if I have multiple separate dedicated objects rather than one
generic object with lots of code to make it generic.

Good luck with your project.

Larry Linson
Microsoft Access MVP
 
The reason I want the report to drive the form is because I want to be able to add reports to the database adhoc without changing anything in the main form or its underlying code.

By creating a report that dynamically creates a pop-up forms on the fly, I can do this easily. The man form allows the user to select a report, the report does the rest. Otherwise I'm always manipulating code on the main form. (hope i explained this so that it makes sense).

I'll try your code in the am and see what happens. Thanks for the help!
 
The reason I want the report to drive the
form is because I want to be able to add
reports to the database adhoc without
changing anything in the main form or its
underlying code.

By creating a report that dynamically
creates a pop-up forms on the fly, I can
do this easily. The man form allows the
user to select a report, the report does
the rest. Otherwise I'm always manipulating
code on the main form. (hope i explained
this so that it makes sense).
Click to expand...

Clearly, the solution I proposed does not, repeat NOT, "dynamically create a
dialog on the fly" -- it simply opens a form that already exists.

I'm not certain what would have to be changed on your "main form"... it
could open a specific report's selection form as easily as it could open a
specific report. As I said in my earlier reply, I find small, simple,
straightforward objects to be preferrable to complex generic ones (in almost
every case), and I think that may be the point you are trying to make.

Still, I do not see that having the main form

DoCmd.OpenForm "someform"

is any worse than

DoCmd.OpenReport "somereport"

I've never found it appropriate to just list all the Reports in a Combo or
List Box and click-to-open -- there's always been _some_ context in which
particular Reports would be needed. But, if there was a good reason for
listing _all_ Reports, those with no selection, and those with selection,
too, in the same Combo or List, then it makes sense to me that your approach
would simplify... that is, just open the report and the report will open its
own selection form, if one is needed.

And, it makes it simpler to transport to another database, if that proves
useful.

Oh, by the way, I am sure you can do what you want with a List Box, if you
prefer. There is a performance advantage of replacing the RecordSource,
however, over Filtering if you are in a multiuser or client-server
environment; if it is standalone, with the data and the user interface on
the same machine, I suspect the difference would be undetectable sitting in
front of the monitor.

Larry Linson
Microsoft Access MVP
 
Back
Top