how to display the data from a query into a report?

[uk]

I am trying to output the results of a query into a report. The query is
dynamically created from a form where the user select some criteria. I can
run the query using docmd.openquery command and get the output as a dataset
(table format). So, I know the query is working. Now, I am trying to output
the results into a report using docmd.openreport command, but I get a blank
report with no data in it. Can anyone help me with this?

 

[KARL DEWEY]

Set the query as report source, no VBA needed, just run report.

 

[ukodali76 via AccessMonster.com]

Set the query as report source, no VBA needed, just run report.

I am trying to output the results of a query into a report. The query is
dynamically created from a form where the user select some criteria. I can

[quoted text clipped - 4 lines]

.

Thanks for the reply. The reason I need to use VBA here is, I need to run
the report by clicking on a command button on a form. I can not run the
report on its own.

 

[John_G via AccessMonster.com]

Hi -

If your form is setting up only the criteria for the report, you can use the
WhereCondition parameter of the docmd.openreport command to specify the
record selection criteria:

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

where the [] indicate optional parameters.

This is I think what you are looking for.

John

Set the query as report source, no VBA needed, just run report.

I am trying to output the results of a query into a report. The query is
dynamically created from a form where the user select some criteria. I can

[quoted text clipped - 4 lines]

.

Thanks for the reply. The reason I need to use VBA here is, I need to run
the report by clicking on a command button on a form. I can not run the
report on its own.

 

[John_G via AccessMonster.com]

Hi --

I left out an important part above - the report must have a table or query
specified as its recordsource in order to use the WhereCondition in the
OpenReport method. The WhereCondition specifies which records to report.

John

Set the query as report source, no VBA needed, just run report.

I am trying to output the results of a query into a report. The query is
dynamically created from a form where the user select some criteria. I can

[quoted text clipped - 4 lines]

.

Thanks for the reply. The reason I need to use VBA here is, I need to run
the report by clicking on a command button on a form. I can not run the
report on its own.

 

[ukodali76 via AccessMonster.com]

Hi --

I left out an important part above - the report must have a table or query
specified as its recordsource in order to use the WhereCondition in the
OpenReport method. The WhereCondition specifies which records to report.

John
[quoted text clipped - 6 lines]

the report by clicking on a command button on a form. I can not run the
report on its own.

Thanks. But my form has other selections, like report grouping selection and
what fields need to be displayed on the report. My form allows the user to
select filter criteria as well as grouping options and display fields. So, I
would I incorporate the grouping and field selection option which are also
dynamic(may change each time the user runs the report).

 

[Marshall Barton]

I left out an important part above - the report must have a table or query
specified as its recordsource in order to use the WhereCondition in the
OpenReport method. The WhereCondition specifies which records to report.

Set the query as report source, no VBA needed, just run report.
I am trying to output the results of a query into a report. The query is

[quoted text clipped - 6 lines]

the report by clicking on a command button on a form. I can not run the
report on its own.

Thanks. But my form has other selections, like report grouping selection and
what fields need to be displayed on the report. My form allows the user to
select filter criteria as well as grouping options and display fields. So, I
would I incorporate the grouping and field selection option which are also
dynamic(may change each time the user runs the report).

That's not a reason to avoid the WhereCondition argument.
The report's record source query can include fields and
criteria even if they are not used in the report.

You can have the report look at the form's grouping options
in it's Open event and set the ControlSource of any/all
*existing* GroupLevels. To "disable" a group level, set the
group level's ControlSource to a constant expression. E.g.
Me.GroupLevel(N),ControlSource = "=1"
and make its group header/footer invisible:
Me.Section(X).Visible = False

To set the fields to display, leave the text boxes (named
txt1, txt2,...) control source empty and set it according to
the field list on the form. E.g.
Me("txt" & i).ControlSource = Forms!theForm.fieldi
and make the text box visible:
Me("txt" & i).Visible = True

If this seems complicated it's only because you haven't done
it before.

 

[ukodali76 via AccessMonster.com]

Thanks for the suggestion. Yes, I have never done that earlier. Do you have
any sample code that I can refer to? Please help me with some code, as I do
not have too much time to finish this task.

[quoted text clipped - 11 lines]

would I incorporate the grouping and field selection option which are also
dynamic(may change each time the user runs the report).

That's not a reason to avoid the WhereCondition argument.
The report's record source query can include fields and
criteria even if they are not used in the report.

You can have the report look at the form's grouping options
in it's Open event and set the ControlSource of any/all
*existing* GroupLevels. To "disable" a group level, set the
group level's ControlSource to a constant expression. E.g.
Me.GroupLevel(N),ControlSource = "=1"
and make its group header/footer invisible:
Me.Section(X).Visible = False

To set the fields to display, leave the text boxes (named
txt1, txt2,...) control source empty and set it according to
the field list on the form. E.g.
Me("txt" & i).ControlSource = Forms!theForm.fieldi
and make the text box visible:
Me("txt" & i).Visible = True

If this seems complicated it's only because you haven't done
it before.

 

[ukodali76 via AccessMonster.com]

Some clarification- the code I requested is to set the controlsource and
grouplevel properties.

[quoted text clipped - 11 lines]

would I incorporate the grouping and field selection option which are also
dynamic(may change each time the user runs the report).

That's not a reason to avoid the WhereCondition argument.
The report's record source query can include fields and
criteria even if they are not used in the report.

You can have the report look at the form's grouping options
in it's Open event and set the ControlSource of any/all
*existing* GroupLevels. To "disable" a group level, set the
group level's ControlSource to a constant expression. E.g.
Me.GroupLevel(N),ControlSource = "=1"
and make its group header/footer invisible:
Me.Section(X).Visible = False

To set the fields to display, leave the text boxes (named
txt1, txt2,...) control source empty and set it according to
the field list on the form. E.g.
Me("txt" & i).ControlSource = Forms!theForm.fieldi
and make the text box visible:
Me("txt" & i).Visible = True

If this seems complicated it's only because you haven't done
it before.

 

[Marshall Barton]

I can't be specific without understanding everything you are
doing, but if you have a specific problem, post back with
the problem's details and I'll see what I can do.

There's a simple example at:
http://allenbrowne.com/ser-33.html

Also, check VBA Help on "GroupLevel Object"

Another issue is how you manage the text boxes on the report
and relate them to the user field selections on the form.
It's not particularly complexm but there are lots of details
to deal with.
--
Marsh
MVP [MS Access]

Thanks for the suggestion. Yes, I have never done that earlier. Do you have
any sample code that I can refer to? Please help me with some code, as I do
not have too much time to finish this task.

I left out an important part above - the report must have a table or query
specified as its recordsource in order to use the WhereCondition in the

[quoted text clipped - 11 lines]

would I incorporate the grouping and field selection option which are also
dynamic(may change each time the user runs the report).

That's not a reason to avoid the WhereCondition argument.
The report's record source query can include fields and
criteria even if they are not used in the report.

You can have the report look at the form's grouping options
in it's Open event and set the ControlSource of any/all
*existing* GroupLevels. To "disable" a group level, set the
group level's ControlSource to a constant expression. E.g.
Me.GroupLevel(N),ControlSource = "=1"
and make its group header/footer invisible:
Me.Section(X).Visible = False

To set the fields to display, leave the text boxes (named
txt1, txt2,...) control source empty and set it according to
the field list on the form. E.g.
Me("txt" & i).ControlSource = Forms!theForm.fieldi
and make the text box visible:
Me("txt" & i).Visible = True

If this seems complicated it's only because you haven't done
it before.