473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Print multiple records from a Form into a Report

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

Aug 8 '07 #1
6 9357
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
Aug 9 '07 #2
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
--

Aug 9 '07 #3
On Aug 8, 11:25 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <djwest...@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- 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

Aug 9 '07 #4
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" <AllenBro...@SeeSig.Invalidwrote:
>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" <djwest...@gmail.comwrote 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
Aug 9 '07 #5
On Aug 9, 10:30 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <djwest...@gmail.comwrote in message

news:11*********************@22g2000hsm.googlegrou ps.com...
On Aug 8, 11:25 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
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" <djwest...@gmail.comwrote 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

Aug 9 '07 #6
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
--
Aug 16 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Chris Hall | last post by:
The records in my database are displayed in a form as follows: %> <form action="report-ammend1.42.asp" method="post"name="form"> <table border=1> <% x = 1
8
by: Hank Reed | last post by:
Hello, I have searched through dozens of old responses to this question but have been unable to make it work in my situation. I'm using Access 2000 We have a very old sticker printer on a...
2
by: Paul Mendez | last post by:
I have a form that consists of 150 records and is still growing. there are times when I want to print a certain record of the form, say record 12. I go to file --> print and choose the page number...
3
by: TDIOwa | last post by:
I have a report printing form (Access 97) in which I print different reports from. I have added a combo box that selects the number of copies that I want to print. Here is the rub... The...
2
by: B Garner | last post by:
Hi all Help please. I would like to automate the printing of a report. The report actually is a single page and prints a label. I would like to be able to print the report multiple times...
2
by: ghat12 | last post by:
Hi, I am trying to print a subform containing approx 50 columns in datasheet view. The printout cannot fit more than 8-10 columns. Does anyone know of any way to print all columns; i don't care...
7
by: itm | last post by:
I have a mail out to send to a group of owners with multiple accounts. I want to limit the number of accounts that print on the first page to 20. I want remaining accounts to print on a second...
2
by: Ste | last post by:
Hi, I would like to print the current showing record from the Form screen but don't know where to start. Here are what I have: I can print any records from report built from query that has...
12
by: Studiotyphoon | last post by:
Hi, I have report which I need to print 3 times, but would like to have the following headings Customer Copy - Print 1 Accounts Copy - Print 2 File Copy -Print 3 I created a macro to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.