Print multiple records from a Form into a Report

[Dave]

On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

I've tried using a macro with the code:

Macro Name: cmdGenerateReport : On Click
Action: OpenReport

Report Name: generate report from form
View: Print Preview
Filter Name: Form to Report
Where Condition: =[Forms]![frmSolution]
Window Mode: Normal

The Filter "Form to Report" I made with all the of list boxes I am
wanting to send from the form to the report.

After having information in my list boxes and when I click the
Generate Report button (using the Macro cmdGenerateReport) from my
form, I get the following error message:

"A property of the Automation object requires or returns a data type
that isn't supported by Visual Basic"

then a bunch of dialog boxes come up for each individual list box that
i am trying to generate into my report asking for names.

I am lost at what to do.

Thanks for trying to help me out, this is frustrating!

Dave

[Allen Browne]

Dave, the core concept is that you must craft a WhereCondition for
OpenReport to limit the report to only the records you want in your form.

Here's a very simple example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
The WhereCondition contains the primary key value, so it prints just one
record. If the criteria matched several records, the report would print
those records.

It's not clear if your list box is multi-select, or how you are assigning
the desired records to the list box. Perhaps you use the AfterUpdate of the
combo(s) to set the RowSource of the listbox? If so, you are already
building the WHERE clause you need for OpenReport.

If it is a multi-select list box, here's how to build the WhereCondition:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <dj*******@gmail.comwrote in message
news:11**********************@19g2000hsx.googlegro ups.com...

On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

I've tried using a macro with the code:

Macro Name: cmdGenerateReport : On Click
Action: OpenReport

Report Name: generate report from form
View: Print Preview
Filter Name: Form to Report
Where Condition: =[Forms]![frmSolution]
Window Mode: Normal

The Filter "Form to Report" I made with all the of list boxes I am
wanting to send from the form to the report.

After having information in my list boxes and when I click the
Generate Report button (using the Macro cmdGenerateReport) from my
form, I get the following error message:

"A property of the Automation object requires or returns a data type
that isn't supported by Visual Basic"

then a bunch of dialog boxes come up for each individual list box that
i am trying to generate into my report asking for names.

I am lost at what to do.

Thanks for trying to help me out, this is frustrating!

Dave

[Chuck]

On Thu, 9 Aug 2007 12:25:38 +0800, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:

>Dave, the core concept is that you must craft a WhereCondition for
OpenReport to limit the report to only the records you want in your form.

Here's a very simple example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
The WhereCondition contains the primary key value, so it prints just one
record. If the criteria matched several records, the report would print
those records.

It's not clear if your list box is multi-select, or how you are assigning
the desired records to the list box. Perhaps you use the AfterUpdate of the
combo(s) to set the RowSource of the listbox? If so, you are already
building the WHERE clause you need for OpenReport.

If it is a multi-select list box, here's how to build the WhereCondition:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

I've been fighting this very problem for two days.
Thank you very much.

Chuck
--

[Dave]

On Aug 8, 11:25 pm, "Allen Browne" <[e-mail]:

Dave, the core concept is that you must craft a WhereCondition for
OpenReport to limit the report to only the records you want in your form.

Here's a very simple example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
The WhereCondition contains the primary key value, so it prints just one
record. If the criteria matched several records, the report would print
those records.

It's not clear if your list box is multi-select, or how you are assigning
the desired records to the list box. Perhaps you use the AfterUpdate of the
combo(s) to set the RowSource of the listbox? If so, you are already
building the WHERE clause you need for OpenReport.

If it is a multi-select list box, here's how to build the WhereCondition:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <[e-mail] in message

news:11**********************@19g2000hsx.googlegro ups.com...

On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

I've tried using a macro with the code:

Macro Name: cmdGenerateReport : On Click
Action: OpenReport

Report Name: generate report from form
View: Print Preview
Filter Name: Form to Report
Where Condition: =[Forms]![frmSolution]
Window Mode: Normal

The Filter "Form to Report" I made with all the of list boxes I am
wanting to send from the form to the report.

After having information in my list boxes and when I click the
Generate Report button (using the Macro cmdGenerateReport) from my
form, I get the following error message:

"A property of the Automation object requires or returns a data type
that isn't supported by Visual Basic"

then a bunch of dialog boxes come up for each individual list box that
i am trying to generate into my report asking for names.

I am lost at what to do.

Thanks for trying to help me out, this is frustrating!

Dave- Hide quoted text -

- Show quoted text -

Allen,

This has been very helpful thank you. The report generates in the
correct format!
So there's a success. However, the report is not being populated
using the primary key I specified. The report is generated with blank
fields (but in the right format). I tested my SQL that is in the
report using the criteria:

=@PrimaryKey

and "!" (the run button in the query design toolbar) ran the query
right there and it worked. So now I believe my problem lies in my
report.

Now, to describe the RecordSource of the report:

SELECT [Server List].[Server Name], [Server List].Type, [Server List].
[Data Center], [Server List].OS, [Server List].Stakeholder, [Server
List].Status, [Server List].[MAC Address], [Server List].[IP Address],
[Server List].Location, Application.[App Name], [Backup
Policies].Policy, Application.[Database Name]
FROM (Application INNER JOIN [Backup Policies] ON Application.[Server
Name]=[Backup Policies].[Server Name]) INNER JOIN [Server List] ON
Application.[App ID]=[Server List].[App ID] WHERE ((([Server List].
[Server Name])=[@Server Name]));

The report is selecting this information from 3 tables with
relationships tying them together routed through the Server Name.
AppName and DBName are in table Application, Server Name and it's
information is in table Server List, and Policy comes from table
Backup Policies.

When I click the btnGenReport on my form, I get an "Enter Parameter
Value" box asking for the @Server Name. I enter the server name and i
get an empty report in the correct format (labels but no
information). There are somtimes multiple policies or applications or
databases (or a combination of any 3 of them), but I even tested using
a server that has 1 policy, 1 application, and no database with no
avail. I feel the problem is in my report, but I am not finding a
solution. I will keep trying, but this report set-up is tricky. Any
help or tests I can run?

Thank you,
Dave

[Allen Browne]

Assuming this data is in JET tables, omit the @ and change the parameter
name so it is not the same as a field, e.g.:
WHERE [Server List].[Server Name] = [What Server Name];

It would also be a good idea to declare that parameter name: Parameters on
Query menu (in query design view.) Use the same data type as the Server Name
field (presumably Text.)

Then run the query and check that it returns the values you expect. If so,
the report will return the same records if you do *not* use a WhereCondition
for OpenReport. (You don't need the WhereCondition if you have already
filtered it in the query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <dj*******@gmail.comwrote in message
news:11*********************@22g2000hsm.googlegrou ps.com...

On Aug 8, 11:25 pm, "Allen Browne" <[e-mail]:

>Dave, the core concept is that you must craft a WhereCondition for
OpenReport to limit the report to only the records you want in your form.

Here's a very simple example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
The WhereCondition contains the primary key value, so it prints just one
record. If the criteria matched several records, the report would print
those records.

It's not clear if your list box is multi-select, or how you are assigning
the desired records to the list box. Perhaps you use the AfterUpdate of
the
combo(s) to set the RowSource of the listbox? If so, you are already
building the WHERE clause you need for OpenReport.

If it is a multi-select list box, here's how to build the WhereCondition:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <[e-mail] in message

news:11**********************@19g2000hsx.googlegr oups.com...

On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

I've tried using a macro with the code:

Macro Name: cmdGenerateReport : On Click
Action: OpenReport

Report Name: generate report from form
View: Print Preview
Filter Name: Form to Report
Where Condition: =[Forms]![frmSolution]
Window Mode: Normal

The Filter "Form to Report" I made with all the of list boxes I am
wanting to send from the form to the report.

After having information in my list boxes and when I click the
Generate Report button (using the Macro cmdGenerateReport) from my
form, I get the following error message:

"A property of the Automation object requires or returns a data type
that isn't supported by Visual Basic"

then a bunch of dialog boxes come up for each individual list box that
i am trying to generate into my report asking for names.

I am lost at what to do.

Thanks for trying to help me out, this is frustrating!

Dave- Hide quoted text -

- Show quoted text -

Allen,

This has been very helpful thank you. The report generates in the
correct format!
So there's a success. However, the report is not being populated
using the primary key I specified. The report is generated with blank
fields (but in the right format). I tested my SQL that is in the
report using the criteria:

=@PrimaryKey

and "!" (the run button in the query design toolbar) ran the query
right there and it worked. So now I believe my problem lies in my
report.

Now, to describe the RecordSource of the report:

SELECT [Server List].[Server Name], [Server List].Type, [Server List].
[Data Center], [Server List].OS, [Server List].Stakeholder, [Server
List].Status, [Server List].[MAC Address], [Server List].[IP Address],
[Server List].Location, Application.[App Name], [Backup
Policies].Policy, Application.[Database Name]
FROM (Application INNER JOIN [Backup Policies] ON Application.[Server
Name]=[Backup Policies].[Server Name]) INNER JOIN [Server List] ON
Application.[App ID]=[Server List].[App ID] WHERE ((([Server List].
[Server Name])=[@Server Name]));

The report is selecting this information from 3 tables with
relationships tying them together routed through the Server Name.
AppName and DBName are in table Application, Server Name and it's
information is in table Server List, and Policy comes from table
Backup Policies.

When I click the btnGenReport on my form, I get an "Enter Parameter
Value" box asking for the @Server Name. I enter the server name and i
get an empty report in the correct format (labels but no
information). There are somtimes multiple policies or applications or
databases (or a combination of any 3 of them), but I even tested using
a server that has 1 policy, 1 application, and no database with no
avail. I feel the problem is in my report, but I am not finding a
solution. I will keep trying, but this report set-up is tricky. Any
help or tests I can run?

Thank you,
Dave

[Dave]

On Aug 9, 10:30 am, "Allen Browne" <[e-mail]:

Assuming this data is in JET tables, omit the @ and change the parameter
name so it is not the same as a field, e.g.:
WHERE [Server List].[Server Name] = [What Server Name];

It would also be a good idea to declare that parameter name: Parameters on
Query menu (in query design view.) Use the same data type as the Server Name
field (presumably Text.)

Then run the query and check that it returns the values you expect. If so,
the report will return the same records if you do *not* use a WhereCondition
for OpenReport. (You don't need the WhereCondition if you have already
filtered it in the query.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <[e-mail] in message

news:11*********************@22g2000hsm.googlegrou ps.com...

On Aug 8, 11:25 pm, "Allen Browne" <[e-mail]:

Dave, the core concept is that you must craft a WhereCondition for
OpenReport to limit the report to only the records you want in your form.

Here's a very simple example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html
The WhereCondition contains the primary key value, so it prints just one
record. If the criteria matched several records, the report would print
those records.

It's not clear if your list box is multi-select, or how you are assigning
the desired records to the list box. Perhaps you use the AfterUpdate of
the
combo(s) to set the RowSource of the listbox? If so, you are already
building the WHERE clause you need for OpenReport.

If it is a multi-select list box, here's how to build the WhereCondition:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <[e-mail] in message

>news:11**********************@19g2000hsx.googlegr oups.com...

On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

I've tried using a macro with the code:

Macro Name: cmdGenerateReport : On Click
Action: OpenReport

Report Name: generate report from form
View: Print Preview
Filter Name: Form to Report
Where Condition: =[Forms]![frmSolution]
Window Mode: Normal

The Filter "Form to Report" I made with all the of list boxes I am
wanting to send from the form to the report.

After having information in my list boxes and when I click the
Generate Report button (using the Macro cmdGenerateReport) from my
form, I get the following error message:

"A property of the Automation object requires or returns a data type
that isn't supported by Visual Basic"

then a bunch of dialog boxes come up for each individual list box that
i am trying to generate into my report asking for names.

I am lost at what to do.

Thanks for trying to help me out, this is frustrating!

Dave- Hide quoted text -

- Show quoted text -

Allen,

This has been very helpful thank you. The report generates in the
correct format!
So there's a success. However, the report is not being populated
using the primary key I specified. The report is generated with blank
fields (but in the right format). I tested my SQL that is in the
report using the criteria:

=@PrimaryKey

and "!" (the run button in the query design toolbar) ran the query
right there and it worked. So now I believe my problem lies in my
report.

Now, to describe the RecordSource of the report:

SELECT [Server List].[Server Name], [Server List].Type, [Server List].
[Data Center], [Server List].OS, [Server List].Stakeholder, [Server
List].Status, [Server List].[MAC Address], [Server List].[IP Address],
[Server List].Location, Application.[App Name], [Backup
Policies].Policy, Application.[Database Name]
FROM (Application INNER JOIN [Backup Policies] ON Application.[Server
Name]=[Backup Policies].[Server Name]) INNER JOIN [Server List] ON
Application.[App ID]=[Server List].[App ID] WHERE ((([Server List].
[Server Name])=[@Server Name]));

The report is selecting this information from 3 tables with
relationships tying them together routed through the Server Name.
AppName and DBName are in table Application, Server Name and it's
information is in table Server List, and Policy comes from table
Backup Policies.

When I click the btnGenReport on my form, I get an "Enter Parameter
Value" box asking for the @Server Name. I enter the server name and i
get an empty report in the correct format (labels but no
information). There are somtimes multiple policies or applications or
databases (or a combination of any 3 of them), but I even tested using
a server that has 1 policy, 1 application, and no database with no
avail. I feel the problem is in my report, but I am not finding a
solution. I will keep trying, but this report set-up is tricky. Any
help or tests I can run?

Thank you,
Dave- Hide quoted text -

- Show quoted text -

Allen,
Thank you for your advice, it is highly regarded! I found that my
list boxes on my report were unbound! It was that simple, and now
with a little tweaking it works. Again, thank you for your time/help!

Dave

[Chuck]

On Wed, 08 Aug 2007 21:25:11 -0000, Dave <dj*******@gmail.comwrote:

>On my form I have combo boxes. These combo boxes, after updating
them, populate respective listboxes that are located below the combo
boxes on the same form. I am trying to use a "generate report" button
located on my form to print all of the list box values (that have been
updated via selection from combo boxes) from the form to the report.

How do you hold off the list boxes from trying to populate until after the
combo boxes are filled?

Chuck
--