Macro to produce a report from a query

[- Butch]

I have an intermediate knowledge of Access97 and wondered if someone would
help me with a macro.

I have a database that keeps track of the contents of a hundred of my home
video tapes. It has fields like "Tape #", "People", "Event", "Time counter",
"Date", "Setting", etc.

I created both a query and a report that produces the contents of the tape #
I manually enter into the query. It works fine if I manually go into the
query, enter the tape # I want, and manually save it before I open the
report.

I created a Switchboard with a button labeled "Tape contents" and when I
click on it, a form I created pops up and asks for a tape number. That's
when things go wrong. When I click on the View Report Preview button also on
the form, the report appears but always with the last tape # that was
manually entered, and saved, in the query design screen. I want to be able
to enter a tape # and have the Report Preview appear with the information
about that tape # (- appear either automatically or by clicking the View
Report Preview button I put on that form.)

The macro I created contains lines: OpenForm, GoToControl, OpenQuery, Save,
RunCommand, OpenReport. What have I done wrong?

Also, after hours of trial & error, I am now also presented with a pop-up
form that prompts: Enter Parameter Value. I have no idea what I did to get
that and since I don't know what it refers to, I can't find any way to get
rid of it.

I'd appreciate any advice. Thanks.

 

[Guest]

well....be careful of using # as it is a control character.....but that is
just an aside....

in design view of your query...clear out any entries in criteria row for all
columns....

just for fun do the ! and be sure it is returning all records.....sanity
check....

then back in design view put in the tape# column criteria:
=[Forms]![PopUpForm'sName].[TextBoxName]

only =[Forms]! is literal...after that you substitute your actual names of
your form and the textbox where you put the tape#

that form has to be open for the query to get those values....don't close it
before the query does it's thing....

 

[- Butch]

Thank you. This works with the exception that, in between the form opening
and the report opening, I get several boxes asking for "parameter values",
one for "Forms! Find tape number", and another asking for the parameter
values of an unimportant field in the database. What the heck are these
parameter values it seeks?

well....be careful of using # as it is a control character.....but that is
just an aside....

in design view of your query...clear out any entries in criteria row for
all
columns....

just for fun do the ! and be sure it is returning all records.....sanity
check....

then back in design view put in the tape# column criteria:
=[Forms]![PopUpForm'sName].[TextBoxName]

only =[Forms]! is literal...after that you substitute your actual names
of
your form and the textbox where you put the tape#

that form has to be open for the query to get those values....don't close
it
before the query does it's thing....

--
NTC

I have an intermediate knowledge of Access97 and wondered if someone
would
help me with a macro.

I have a database that keeps track of the contents of a hundred of my
home
video tapes. It has fields like "Tape #", "People", "Event", "Time
counter",
"Date", "Setting", etc.

I created both a query and a report that produces the contents of the
tape #
I manually enter into the query. It works fine if I manually go into the
query, enter the tape # I want, and manually save it before I open the
report.

I created a Switchboard with a button labeled "Tape contents" and when I
click on it, a form I created pops up and asks for a tape number. That's
when things go wrong. When I click on the View Report Preview button also
on
the form, the report appears but always with the last tape # that was
manually entered, and saved, in the query design screen. I want to be
able
to enter a tape # and have the Report Preview appear with the information
about that tape # (- appear either automatically or by clicking the View
Report Preview button I put on that form.)

The macro I created contains lines: OpenForm, GoToControl, OpenQuery,
Save,
RunCommand, OpenReport. What have I done wrong?

Also, after hours of trial & error, I am now also presented with a pop-up
form that prompts: Enter Parameter Value. I have no idea what I did to
get
that and since I don't know what it refers to, I can't find any way to
get
rid of it.

I'd appreciate any advice. Thanks.

 

[Guest]

presuming that what you are saying is that the query opens fine......which
means we are only at the point of having the popup form completed...and then
manually opening the query....this much works fine....

then the issues you state are all part of the report opening.... the report
is looking for values that it can't find.... you can open that report in
design view and seek out all the fields that can't find values and delete
them or correct them.....

or it might simply be easier to create a brand new report based on this
query that you know works using the Report Wizard....
--
NTC

Thank you. This works with the exception that, in between the form opening
and the report opening, I get several boxes asking for "parameter values",
one for "Forms! Find tape number", and another asking for the parameter
values of an unimportant field in the database. What the heck are these
parameter values it seeks?

well....be careful of using # as it is a control character.....but that is
just an aside....

in design view of your query...clear out any entries in criteria row for
all
columns....

just for fun do the ! and be sure it is returning all records.....sanity
check....

then back in design view put in the tape# column criteria:
=[Forms]![PopUpForm'sName].[TextBoxName]

only =[Forms]! is literal...after that you substitute your actual names
of
your form and the textbox where you put the tape#

that form has to be open for the query to get those values....don't close
it
before the query does it's thing....

--
NTC

I have an intermediate knowledge of Access97 and wondered if someone
would
help me with a macro.

I have a database that keeps track of the contents of a hundred of my
home
video tapes. It has fields like "Tape #", "People", "Event", "Time
counter",
"Date", "Setting", etc.

I created both a query and a report that produces the contents of the
tape #
I manually enter into the query. It works fine if I manually go into the
query, enter the tape # I want, and manually save it before I open the
report.

I created a Switchboard with a button labeled "Tape contents" and when I
click on it, a form I created pops up and asks for a tape number. That's
when things go wrong. When I click on the View Report Preview button also
on
the form, the report appears but always with the last tape # that was
manually entered, and saved, in the query design screen. I want to be
able
to enter a tape # and have the Report Preview appear with the information
about that tape # (- appear either automatically or by clicking the View
Report Preview button I put on that form.)

The macro I created contains lines: OpenForm, GoToControl, OpenQuery,
Save,
RunCommand, OpenReport. What have I done wrong?

Also, after hours of trial & error, I am now also presented with a pop-up
form that prompts: Enter Parameter Value. I have no idea what I did to
get
that and since I don't know what it refers to, I can't find any way to
get
rid of it.

I'd appreciate any advice. Thanks.