MS Access Forms and Reports Question

[Allison]

Hey folks,

I am trying to make a form that has a button linked to a report. Instead of
when pressing the button just bringing up the specific records information,
it is searching the whole table that the form is linked to and bringing that
up in the report.

I know there is a way to just print the selected record in the form and turn
that into a report and print that. Does anyone know how to do this? Does a
query need to be made? I tried searching the help files to no avail. I
have done this before a long time ago, I just don't remember how.

Thanks

Allison

[Rick Brandt]

Allison wrote:

Hey folks,

I am trying to make a form that has a button linked to a report.
Instead of when pressing the button just bringing up the specific
records information, it is searching the whole table that the form is
linked to and bringing that up in the report.

I know there is a way to just print the selected record in the form
and turn that into a report and print that. Does anyone know how to
do this? Does a query need to be made? I tried searching the help
files to no avail. I have done this before a long time ago, I just
don't remember how.
Thanks

Allison

It is very common in Access to need to...

Open a Form pre-filtered based on the value of one or more controls on the
current form.

Open a Report pre-filtered based on the value of one or more controls on the
current form.

Even though these are both about as common, the New Command Button wizard only
provides a method to build for you a button that does this for Forms. Why they
don't provide a wizard to do this for reports has always baffled me.

The resulting syntax is almost exactly the same. If you look at the code in the
Click event of your existing button that opens your report you will see that it
uses the OpenReport method of the DoCmd object. That method has an optional
WHERE argument that can be used to apply a filter to the report being opened.
All you have to do is populate that argument including references to controls on
your form. Here is a simple example where the form and report both have a
common single numeric field [ID] to be used for the filter.

DoCmd.OpenReport "ReportName", acViewPreview, , "ID = " & Me!ID
--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[fredg]

On Sun, 24 Sep 2006 15:44:49 +1000, Allison wrote:

Hey folks,

I am trying to make a form that has a button linked to a report. Instead of
when pressing the button just bringing up the specific records information,
it is searching the whole table that the form is linked to and bringing that
up in the report.

I know there is a way to just print the selected record in the form and turn
that into a report and print that. Does anyone know how to do this? Does a
query need to be made? I tried searching the help files to no avail. I
have done this before a long time ago, I just don't remember how.

Thanks

Allison

Your table should have a unique prime key field.
In my example it is named [RecordID].

On the Form's command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail