Pass Parameters to Record Source Query

[Guest]

I know that I can pass a variable's value to a query by using the following
construct:

....
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
....

Is there any other method? The ideal would be a reference in the form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists attendees in two
parallel coulumns. The content of a column comes from a subreport, one for
the left column and a similar one for the right column of names. The record
source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error is raised because
the field txtRightSide cannot be found.

Any suggestions willingly entertained!

 

[Larry Daugherty]

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you want to see
it. Intercept the Close event to make it hidden instead, then cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the form. No
need to hide the form, no ambiguities.

HTH

 

[Guest]

Larry:

Thank you. That was to be my next solution but I had hoped that there was a
viable solution that did not rely on using a form in the fashion you
describe. The application has a "splash screen" that is closed after five
seconds. I will hide it instead and use that as my source.

(Sure would have been nice if Access allowed a [Modules]! construct, though!)

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you want to see
it. Intercept the Close event to make it hidden instead, then cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the form. No
need to hide the form, no ambiguities.

HTH
--
-Larry-
--

I know that I can pass a variable's value to a query by using the following
construct:

...
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
...

Is there any other method? The ideal would be a reference in the form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists attendees in two
parallel coulumns. The content of a column comes from a subreport, one for
the left column and a similar one for the right column of names. The record
source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error is raised because
the field txtRightSide cannot be found.

Any suggestions willingly entertained!

 

[Larry Daugherty]

There may still be other ways...

You started defining the problem from inside and then positing limited
solutions. At the highest level, WHY are you trying to do this thing?
I won't play 20 questions but if you explain the motivating objective
before the means to a solution, maybe someone can help.

I wouldn't have posted back but it seems you're going to place some
information in global variables on the splash screen's module. That's
surely workable but the question is "How did you know in advance what
to place there"? And, still, what are you trying to do?

HTH
--
-Larry-
--

Larry:

Thank you. That was to be my next solution but I had hoped that there was a
viable solution that did not rely on using a form in the fashion you
describe. The application has a "splash screen" that is closed after five
seconds. I will hide it instead and use that as my source.

(Sure would have been nice if Access allowed a [Modules]! construct, though!)

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you want to see
it. Intercept the Close event to make it hidden instead, then cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the form. No
need to hide the form, no ambiguities.

HTH
--
-Larry-
--

I know that I can pass a variable's value to a query by using

the
following

construct:

...
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
...

Is there any other method? The ideal would be a reference in the form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists attendees in two
parallel coulumns. The content of a column comes from a

subreport,
one for

the left column and a similar one for the right column of names.

The
record

source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error is

raised
because

the field txtRightSide cannot be found.

Any suggestions willingly entertained!

 

[Guest]

Larry:

You're right: I should have been more forthcoming in explaining why.

I have a simple meeting agenda/minutes database that has been used for about
a year and the user wants to add a query facility: "What meetings have notes
where 'x' or 'y' were discussed?"

The user records a new meeting and particulars about it on a single form
(frm_Meeting). The user views and prints meeting agenda and minutes for a
single meeting at a time from this form. Access to a given meeting is from
the main menu. This menu allows the user to update an existing meeting
("frm_Meeting") by selecting one from a treeview list of all meetings
organized by type and date or to add a new meeting (also "frm_Meeting") by
clicking on a command button. Various administrative functions are accessed
from the main menu as well. I plan on adding the query button to the main
menu.

When the user has a meeting in view, he or she may display and print that
meeting's agenda only. The values needed by the subreport record source
queries are recorded in (hidden) text fields on the meeting form itself. Note
that "frm_Meeting" displays only one meeting at a time. To view another
meeting, the user closes the form and returns to the main menu to work on
another meeting's record.

Due to the structure of the one-meeting-at-a-time form, the query function
is implemented on a separate form that is opened from a command button on the
application's main menu form. Because of this, the main meeting form will
have been closed.

Since I wanted to use the original report and subreports I was looking for a
way to pass the variables (there are only two) other than via textboxes on
the main meeting form since that form will have been closed by the user.

As I see the problem, I have the following choices:

1) Use a form that I could simply hide rather than close to hold the
variables (hence the idea of using the spash screen but a different form such
as main menu would be better).

2) Find another way to pass the values (hence the original question of using
global variable references).

3) Duplicate the report and its subreports along with revised references
just for the query function.

I realize that I still may have not provided you with all of the information
you would like in order to make an informed suggestion. But I'm willing to
keep the dialog going. Perhaps we'll both learn something.

Regards: Tait Milliken (NoVAFolk)

There may still be other ways...

You started defining the problem from inside and then positing limited
solutions. At the highest level, WHY are you trying to do this thing?
I won't play 20 questions but if you explain the motivating objective
before the means to a solution, maybe someone can help.

I wouldn't have posted back but it seems you're going to place some
information in global variables on the splash screen's module. That's
surely workable but the question is "How did you know in advance what
to place there"? And, still, what are you trying to do?

HTH
--
-Larry-
--

Larry:

Thank you. That was to be my next solution but I had hoped that there was a
viable solution that did not rely on using a form in the fashion you
describe. The application has a "splash screen" that is closed after five
seconds. I will hide it instead and use that as my source.

(Sure would have been nice if Access allowed a [Modules]! construct, though!)

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you want to see
it. Intercept the Close event to make it hidden instead, then cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the form. No
need to hide the form, no ambiguities.

HTH
--
-Larry-
--

I know that I can pass a variable's value to a query by using the
following
construct:

...
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
...

Is there any other method? The ideal would be a reference in the
form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists
attendees in two
parallel coulumns. The content of a column comes from a subreport,
one for
the left column and a similar one for the right column of names. The
record
source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error is raised
because
the field txtRightSide cannot be found.

Any suggestions willingly entertained!

 

[Larry Daugherty]

Thanks for trying to clarify. I still don't have a clear picture but
that may be my problem. I don't see any material difference between
my suggestion and the use of global variables.

Back to another issue: modules. I don't see why you need a separate
module for your global variables. Just put them in any standard
module.

FWIW I usually use the database components to calculate results. I.e.
I might conduct a text search on the records that might have the text
I seek. Each time I get a hit, I record the Primary Key value of that
record and pass on to the next. At the end of that run I'll have a
list of records with relevant text. I'll then display and massage the
results as best fits my application.

HTH
--
-Larry-
--

Larry:

You're right: I should have been more forthcoming in explaining why.

I have a simple meeting agenda/minutes database that has been used for about
a year and the user wants to add a query facility: "What meetings have notes
where 'x' or 'y' were discussed?"

The user records a new meeting and particulars about it on a single form
(frm_Meeting). The user views and prints meeting agenda and minutes for a
single meeting at a time from this form. Access to a given meeting is from
the main menu. This menu allows the user to update an existing meeting
("frm_Meeting") by selecting one from a treeview list of all meetings
organized by type and date or to add a new meeting (also "frm_Meeting") by
clicking on a command button. Various administrative functions are accessed
from the main menu as well. I plan on adding the query button to the main
menu.

When the user has a meeting in view, he or she may display and print that
meeting's agenda only. The values needed by the subreport record source
queries are recorded in (hidden) text fields on the meeting form itself. Note
that "frm_Meeting" displays only one meeting at a time. To view another
meeting, the user closes the form and returns to the main menu to work on
another meeting's record.

Due to the structure of the one-meeting-at-a-time form, the query function
is implemented on a separate form that is opened from a command button on the
application's main menu form. Because of this, the main meeting form will
have been closed.

Since I wanted to use the original report and subreports I was looking for a
way to pass the variables (there are only two) other than via textboxes on
the main meeting form since that form will have been closed by the user.

As I see the problem, I have the following choices:

1) Use a form that I could simply hide rather than close to hold the
variables (hence the idea of using the spash screen but a different form such
as main menu would be better).

2) Find another way to pass the values (hence the original question of using
global variable references).

3) Duplicate the report and its subreports along with revised references
just for the query function.

I realize that I still may have not provided you with all of the information
you would like in order to make an informed suggestion. But I'm willing to
keep the dialog going. Perhaps we'll both learn something.

Regards: Tait Milliken (NoVAFolk)

There may still be other ways...

You started defining the problem from inside and then positing limited
solutions. At the highest level, WHY are you trying to do this thing?
I won't play 20 questions but if you explain the motivating objective
before the means to a solution, maybe someone can help.

I wouldn't have posted back but it seems you're going to place some
information in global variables on the splash screen's module. That's
surely workable but the question is "How did you know in advance what
to place there"? And, still, what are you trying to do?

HTH
--
-Larry-
--

Larry:

Thank you. That was to be my next solution but I had hoped that there was a
viable solution that did not rely on using a form in the fashion you
describe. The application has a "splash screen" that is closed

after
five

seconds. I will hide it instead and use that as my source.

(Sure would have been nice if Access allowed a [Modules]!

construct,
though!)

:

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you

want to
see

it. Intercept the Close event to make it hidden instead, then cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the

form.
No

need to hide the form, no ambiguities.

HTH

using
the

following
construct:

...
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
...

Is there any other method? The ideal would be a reference in the
form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists
attendees in two
parallel coulumns. The content of a column comes from a subreport,
one for
the left column and a similar one for the right column of

names.
The

record
source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error

is
raised

because
the field txtRightSide cannot be found.

Any suggestions willingly entertained!

 

[Guest]

Larry:

Thanks for the effort you've put yourself through! I realize that it's
difficult to provide a solution when you don't have all of the information or
can't see the application itself.

In any event, I've implemented the function by placing the two variables on
the main menu form where they can be used irrespective of the form from which
the report is run. It works as required and provides a way to implement
further inhancements.

(Back to the original issue: It sure would have been handy if Microsoft had
added a "Modules![ModuleName].[VariableName]" construct to enable a more
flexible method of passing values to queries. Oh well.)

Again, thanks!

Thanks for trying to clarify. I still don't have a clear picture but
that may be my problem. I don't see any material difference between
my suggestion and the use of global variables.

Back to another issue: modules. I don't see why you need a separate
module for your global variables. Just put them in any standard
module.

FWIW I usually use the database components to calculate results. I.e.
I might conduct a text search on the records that might have the text
I seek. Each time I get a hit, I record the Primary Key value of that
record and pass on to the next. At the end of that run I'll have a
list of records with relevant text. I'll then display and massage the
results as best fits my application.

HTH
--
-Larry-
--

Larry:

You're right: I should have been more forthcoming in explaining why.

I have a simple meeting agenda/minutes database that has been used for about
a year and the user wants to add a query facility: "What meetings have notes
where 'x' or 'y' were discussed?"

The user records a new meeting and particulars about it on a single form
(frm_Meeting). The user views and prints meeting agenda and minutes for a
single meeting at a time from this form. Access to a given meeting is from
the main menu. This menu allows the user to update an existing meeting
("frm_Meeting") by selecting one from a treeview list of all meetings
organized by type and date or to add a new meeting (also "frm_Meeting") by
clicking on a command button. Various administrative functions are accessed
from the main menu as well. I plan on adding the query button to the main
menu.

When the user has a meeting in view, he or she may display and print that
meeting's agenda only. The values needed by the subreport record source
queries are recorded in (hidden) text fields on the meeting form itself. Note
that "frm_Meeting" displays only one meeting at a time. To view another
meeting, the user closes the form and returns to the main menu to work on
another meeting's record.

Due to the structure of the one-meeting-at-a-time form, the query function
is implemented on a separate form that is opened from a command button on the
application's main menu form. Because of this, the main meeting form will
have been closed.

Since I wanted to use the original report and subreports I was looking for a
way to pass the variables (there are only two) other than via textboxes on
the main meeting form since that form will have been closed by the user.

As I see the problem, I have the following choices:

1) Use a form that I could simply hide rather than close to hold the
variables (hence the idea of using the spash screen but a different form such
as main menu would be better).

2) Find another way to pass the values (hence the original question of using
global variable references).

3) Duplicate the report and its subreports along with revised references
just for the query function.

I realize that I still may have not provided you with all of the information
you would like in order to make an informed suggestion. But I'm willing to
keep the dialog going. Perhaps we'll both learn something.

Regards: Tait Milliken (NoVAFolk)

There may still be other ways...

You started defining the problem from inside and then positing limited
solutions. At the highest level, WHY are you trying to do this thing?
I won't play 20 questions but if you explain the motivating objective
before the means to a solution, maybe someone can help.

I wouldn't have posted back but it seems you're going to place some
information in global variables on the splash screen's module. That's
surely workable but the question is "How did you know in advance what
to place there"? And, still, what are you trying to do?

HTH
--
-Larry-
--

Larry:

Thank you. That was to be my next solution but I had hoped that
there was a
viable solution that did not rely on using a form in the fashion you
describe. The application has a "splash screen" that is closed after
five
seconds. I will hide it instead and use that as my source.

(Sure would have been nice if Access allowed a [Modules]! construct,
though!)

:

The answer to your question: Yes but there are caveats.

Open the form hidden and only make it visible when you want to
see
it. Intercept the Close event to make it hidden instead, then
cancel
the close event. The risk is that you may not know what record is
exposed by the form.

In situations like you describe, I run the report from the form.
No
need to hide the form, no ambiguities.

HTH
--
-Larry-
--

I know that I can pass a variable's value to a query by using
the
following
construct:

...
WHERE [TableName].[ColumnName]=[Forms]![FormName].[ControlName]
...

Is there any other method? The ideal would be a reference in the
form
"[Modules]![ModuleName].[VariableName]"

Some background: I have a meeting minutes report that lists
attendees in two
parallel coulumns. The content of a column comes from a
subreport,
one for
the left column and a similar one for the right column of names.
The
record
source of a subreport is a query looks like this:

SELECT LineNumber
, MeetingKey
, PersonKey
, Division
, Attended
FROM AgendaReportInvitees
WHERE LineNumber<[Forms]![frm_Meeting].[txtRightSide]
ORDER BY LineNumber;

My problem is that I want to be able to run this report even if
"frm_Meeting" is closed. Obviously when I do that an error is
raised
because
the field txtRightSide cannot be found.

Any suggestions willingly entertained!