Help on Reports

[warroowarrior]

I need your help. I am on a site that has different areas, and in those areas
are different pieces of equipment. Now, I have a table that contains details
of that equipment including in which area it can be located. I have a field
in the table call "Area", and each piece of equipment has an area allocatted
to it..
What I need to be able to do is to have a drop down box on a form from which
I can select a particular area, and that will in turn produce a report that
shows all items within the selected area.
I have posted this before, but unfortunately the reply I received was blank.
Can someone point me in the right direction.
Many Thanks Peter

 

[Armen Stein]

I need your help. I am on a site that has different areas, and in those areas
are different pieces of equipment. Now, I have a table that contains details
of that equipment including in which area it can be located. I have a field
in the table call "Area", and each piece of equipment has an area allocatted
to it..
What I need to be able to do is to have a drop down box on a form from which
I can select a particular area, and that will in turn produce a report that
shows all items within the selected area.
I have posted this before, but unfortunately the reply I received was blank.
Can someone point me in the right direction.

Hi Peter,

One way to have a report prompt the user for criteria before it runs
is to open a form from the report's Open event. Open the form in
Dialog mode so that the report waits for the form to be closed or
hidden before it proceeds. That way you can collect criteria (like
your Area) from the user and build a Where clause for the report. It
also means that you can call the report directly - you don't need to
call it from a form. And the selection form is reusable - it can be
called from multiple reports if they need the same criteria.

I've posted examples of this technique at
www.JStreetTech.com/downloads - see "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Larry Linson]

I can point you in the right direction, but don't have enough information,
likely, to give you the specific information you need. If you're using
Access 2007, then even the information below may not be accurate, and
someone else will have to assist you.

In general... you should be able to create an unbound Form (that is, nothing
in the Record Source property), and on that Form you place a Combo Box (the
Combo Box will be unbound, but it will have a Row Source property containing
a Query or SQL from which your user can select the area (for purpose of
discussion, let's call it cboArea). If you have a table of areas, you can
use that as the basis for the Row Source Query; if not, you can create a
Totals Query, using the Group By option to list each Area just once, and
base it on the Equipment table.

Use the Toolbar to make certain the Control Wizard is on, then select and
place on your unbound Form a Command Button, choose Report Operations,
choose Preview Report... then modify the code generated by the Wizard as
follows:

In the Property Sheet for the Command Button, look for the Event (probably
Click) which has VBA code... within that code, you'll find the Following VBA
statement

stDoc = "<the name of your report>"
DoCmd.OpenReport stDoc, acViewPreview

You'll need to modify that code, to read, thusly:

stDoc = "<the name of your report>"

DoCmd.OpenReport stDoc, acViewPreview, , "[Area] = " & Me!cboArea
(The preceding should be used if Area is a numeric variable. But, if
Area is a Text variable, the following will be required:

DoCmd.OpenReport stDoc, acViewPreview, , "[Area] = """ & Me!cboArea &
""""

(Note that: Within a Quoted String for Display, doubling the quote marks
gives a single quote mark in the string produced... that's why the line
above may appear "strange".

That should limit selection. There's an example of limiting the Record in
the PowerPoint and the Access files in the Shared Documents folder at
http://sp.ntpcug.org/accesssig/ -- it may be more useful than my verbal
description... it doesn't use a combo box, however, but a Field from the
currently active Record.

Regards,

Larry

I need your help. I am on a site that has different areas, and in those
areas
are different pieces of equipment. Now, I have a table that contains
details
of that equipment including in which area it can be located. I have a
field
in the table call "Area", and each piece of equipment has an area
allocatted
to it..
What I need to be able to do is to have a drop down box on a form from
which
I can select a particular area, and that will in turn produce a report
that
shows all items within the selected area.
I have posted this before, but unfortunately the reply I received was
blank.
Can someone point me in the right direction.
Many Thanks Peter

__________ Information from ESET Smart Security, version of virus
signature database 4020 (20090420) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4020 (20090420) __________

The message was checked by ESET Smart Security.

http://www.eset.com