Converting a form to a report

[Guest]

ok, on the highwind example database it takes the information selected in a
form invoice and puts it on a report. i cant figure out how they did it so i
need somebody's help. i have two drop boxes, one for category and one
requeried for the product from the first drop box. then it displays the
price from its column in the query the second drop box is based on. then you
input text in the quantity space and it shows the total price. then i press
a button at the bottom and it totals the subtotal, tax, and final total. i
want all of that information on the report so i can save a copy of each
invoice in a file on my computer because i cant save from the form.

 

[Arvin Meyer]

You don't really need to save from a form because you can recreate it
(assuming that you are storing all the data necessary to recreate it) If you
want to make a form into a report, it can be done easily by simply
right-clicking on the form in the database window, and choosing "SaveAs".
Change the type from form to report and give it a meaningful name.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Guest]

that "Save As" thing doesnt work all it does is save what the form looks
like, i need it to show all the entered data from the form like something
selected in a drop box or something typed in a text box, i need it like the
highwind example or close to it.

 

[Rick Brandt]

that "Save As" thing doesnt work all it does is save what the form
looks like, i need it to show all the entered data from the form like
something selected in a drop box or something typed in a text box, i
need it like the highwind example or close to it.

Um, it will do that. It will create a report that is bound to the same
table/query that the form is bound to (the form is bound isn't it?) Now, by
default the resulting report will show ALL records in the RecordSource so
you would normally open it from a button on the form that applies a filter
so that only the record currently displayed in the form is included in the
report.

Example with a primary key named [ID]...
(It's a good idea to save the record in the form first)

Me.Dirty = False
DoCmd.OpenReport "ReportName",,,,"[ID] = " & Me.ID

 

[Guest]

ok lets assume i know nothing about this program which isnt much of an
overstatement, but i need step by step to help here because ive been using
this program for maybe a few days, but what i need is to be able to convert
my form to a report just like the highwind example so that i can save it into
a file on my computer. yes i know i dont need to because i can call up a
form over and over again but i WANT a physical copy of it in a different
file. i dont know anything about filter queries or whatever i need to know
how to do this though, i need step-by-step detailed instructions, and dont
assume i know the lingo of the program. please help me out here, if its
possible maybe i can send the form or something but i need help.

that "Save As" thing doesnt work all it does is save what the form
looks like, i need it to show all the entered data from the form like
something selected in a drop box or something typed in a text box, i
need it like the highwind example or close to it.

Um, it will do that. It will create a report that is bound to the same
table/query that the form is bound to (the form is bound isn't it?) Now, by
default the resulting report will show ALL records in the RecordSource so
you would normally open it from a button on the form that applies a filter
so that only the record currently displayed in the form is included in the
report.

Example with a primary key named [ID]...
(It's a good idea to save the record in the form first)

Me.Dirty = False
DoCmd.OpenReport "ReportName",,,,"[ID] = " & Me.ID

 

[Rick Brandt]

ok lets assume i know nothing about this program which isnt much of an
overstatement, but i need step by step to help here because ive been
using this program for maybe a few days, but what i need is to be
able to convert my form to a report just like the highwind example so
that i can save it into a file on my computer. yes i know i dont
need to because i can call up a form over and over again but i WANT a
physical copy of it in a different file. i dont know anything about
filter queries or whatever i need to know how to do this though, i
need step-by-step detailed instructions, and dont assume i know the
lingo of the program. please help me out here, if its possible maybe
i can send the form or something but i need help.

Right-click the form in the db window and choose "Save as Report".

Open the resulting report in design view and find the RecordSource property in
the property sheet. Click the build button [...] next to that and it will bring
up the query designer for the RecordSource query. In the criteria row of the
Primary Key field enter...

Forms!NameOfYourForm!PrimaryKeyFieldName

That will cause the RecordSource of the report to only include the record that
is currently being displayed on the form.

The only way to save an image of a form or report as an external file that will
retain all of the graphical elements is to save it as a Snapshot file. Any
other format will give you text only. So with the form on a particular record,
open the report. While in Preview mode you can select "File - Save As/Export".

Post back with any questions you have to get that far.

 

[Guest]

ok i did what you said so far. it still didnt put all the values in the
report, my report looks just like the empty form. and how does that primary
key thing work, how does it know to get those certain values? maybe if i
explain my form better youll know what im talking about: i have one table
that lists only types of products (ex. hard drives, floppy drives,
processors, etc.) it looks like this:
name = "Products2"
[ID] [Product]
01 Case Fans
02 Mother Boards
etc.

this is linked through to another table from the first's ID to the second's
product.
name = "SubMenu2"
[ID] [Product] [Distrib.] [Manufact.] [Descr.] [U. Price] [S. Price] [In
Stock] [Order]
01 Hard drive D&H W. Digital 80GB $60.00 $85.00 2
2
etc.

on my form i have two text boxes where in one i enter the customer's name
and number, it is not bound to a table or query. then the other is where i
enter comments or special instructions to the customer (ex. warranty - no
charge), this is also not bound seeing as how it is just inputting text.
then there are ten sets of two drop boxes, a quantity text box, a unit price
text box, and then a total price text box. these are on there ten times
because the invoice can sell up to ten items on one invoice, trust me that's
enough for invoice. the first drop box has the row source of a query that
has the id and product of the first table, the drop box only displays the
product. when a product is selected it requeries the second table which has
a row source of another query which in includes from the second table, id,
description, product, and sale price. the second drop box only displays the
descriptions of each item under the product in the first drop box. after you
select the description of i certain item it displays the unit price from the
third column in the unit price text box field. because it comes from the
column and not a source it only displays it as a number so it then multiplies
by one in a currency expression. then you input a number in the quantity
field and it takes that number and multiplies it by the unit price and then
displays the total price just a currency expression. the quantity, unit
price, and sale price are unbound and so are all the drop boxes. it looks
like this:
product \/ description \/ quantity unit
price total price
_______________ _______________ _________ _________ ________
|____________|\/| |____________|\/| |________| |________| |_______|

it has this ten times going down the page. then at the bottom i have 5
buttons, the first resets the form, the second prints one copy and the third
prints two copies. the forth does a total pricing. it takes all the total
prices and adds them and displays a subtotal, then it multiplies by .065 and
puts that number as the tax, and then it adds them together and displays a
grand total. all of these things i just explain have to go onto the report,
and i cant change the way the form looks. the form is bound to a query that
has id and product from the first table, and id, decription, product, sale
price, and in stock. i dont know how much of a challenge this is but i hope
you can help me. ask me anything you need to know. btw the drop boxes each
have their own query, making twenty queries because when it requeries it can
only refer to one drop box, so it refers to the first drop box by name and
each query must have a different criteria to refer to that certain drop box.
enjoy! thanks a whole bunch if you can help.

ok lets assume i know nothing about this program which isnt much of an
overstatement, but i need step by step to help here because ive been
using this program for maybe a few days, but what i need is to be
able to convert my form to a report just like the highwind example so
that i can save it into a file on my computer. yes i know i dont
need to because i can call up a form over and over again but i WANT a
physical copy of it in a different file. i dont know anything about
filter queries or whatever i need to know how to do this though, i
need step-by-step detailed instructions, and dont assume i know the
lingo of the program. please help me out here, if its possible maybe
i can send the form or something but i need help.

Right-click the form in the db window and choose "Save as Report".

Open the resulting report in design view and find the RecordSource property in
the property sheet. Click the build button [...] next to that and it will bring
up the query designer for the RecordSource query. In the criteria row of the
Primary Key field enter...

Forms!NameOfYourForm!PrimaryKeyFieldName

That will cause the RecordSource of the report to only include the record that
is currently being displayed on the form.

The only way to save an image of a form or report as an external file that will
retain all of the graphical elements is to save it as a Snapshot file. Any
other format will give you text only. So with the form on a particular record,
open the report. While in Preview mode you can select "File - Save As/Export".

Post back with any questions you have to get that far.

 

[Rick Brandt]

ok i did what you said so far. it still didnt put all the values in
the report, my report looks just like the empty form.

Is there data in your tables? If the RecordSource is a query does the query
return any records?

Quick test example. Select any table in your db window that contains data
and then in the toolbar find the "New Object - AutoForm" button and use the
AutoReport option. Access will build a simple report based on that table
and it will contain all the records found in the table.

Your report works on the same principle. The Report is bound to a table or
query and if that table or query contains any records then you should see
those records when you preview the report.

and how does
that primary key thing work, how does it know to get those certain
values?

Imagine a query against a table with a Primary Key name [ID] with a numeric
DataType. The following query returns the one record with an [ID] value of
123...

SELECT * FROM TableName
WHERE [ID] = 123

If I have a form bound to the same table and it is currently positioned on
the record with an [ID] value of 123 then this query will also return that
record...

SELECT * FROM TableName
WHERE [ID] = Forms!FormName![ID]

The difference is that the criteria in the above query is dynamic. Every
time I move to a different record in the form and re-run the query I will
always get the same record that is being shown on the form. If I use such a
query as the RecordSource for a report the result is a report that returns
only the record currently displayed on the form.

maybe if i explain my form better youll know what im talking
about: i have one table that lists only types of products (ex. hard
drives, floppy drives, processors, etc.) it looks like this:
name = "Products2"
[ID] [Product]
01 Case Fans
02 Mother Boards
etc.

this is linked through to another table from the first's ID to the
second's product.
name = "SubMenu2"
[ID] [Product] [Distrib.] [Manufact.] [Descr.] [U. Price] [S. Price]
[In Stock] [Order]
01 Hard drive D&H W. Digital 80GB $60.00 $85.00
2 2
etc.

on my form i have two text boxes where in one i enter the customer's
name and number, it is not bound to a table or query. then the other
is where i enter comments or special instructions to the customer
(ex. warranty - no charge), this is also not bound seeing as how it
is just inputting text. then there are ten sets of two drop boxes, a
quantity text box, a unit price text box, and then a total price text
box. these are on there ten times because the invoice can sell up to
ten items on one invoice, trust me that's enough for invoice. the
first drop box has the row source of a query that has the id and
product of the first table, the drop box only displays the product.
when a product is selected it requeries the second table which has a
row source of another query which in includes from the second table,
id, description, product, and sale price. the second drop box only
displays the descriptions of each item under the product in the first
drop box. after you select the description of i certain item it
displays the unit price from the third column in the unit price text
box field. because it comes from the column and not a source it only
displays it as a number so it then multiplies by one in a currency
expression. then you input a number in the quantity field and it
takes that number and multiplies it by the unit price and then
displays the total price just a currency expression. the quantity,
unit price, and sale price are unbound and so are all the drop
boxes. it looks like this: product \/ description \/
quantity unit price total price _______________
_______________ _________ _________ ________

____________|\/| |____________|\/| |________| |________|
|_______|

it has this ten times going down the page. then at the bottom i have 5
buttons, the first resets the form, the second prints one copy and
the third prints two copies. the forth does a total pricing. it
takes all the total prices and adds them and displays a subtotal,
then it multiplies by .065 and puts that number as the tax, and then
it adds them together and displays a grand total. all of these
things i just explain have to go onto the report, and i cant change
the way the form looks. the form is bound to a query that has id and
product from the first table, and id, decription, product, sale
price, and in stock. i dont know how much of a challenge this is but
i hope you can help me. ask me anything you need to know. btw the
drop boxes each have their own query, making twenty queries because
when it requeries it can only refer to one drop box, so it refers to
the first drop box by name and each query must have a different
criteria to refer to that certain drop box. enjoy! thanks a whole
bunch if you can help.

Sounds like a mess to me. Essentially you are entering a bunch of data into
all unbound controls instead of saving this data into tables. That is why
your form print prevview and your report print preview don't show anything,
because you are not storing any of this in a table. Your report would need
to have ControlSources on EVERY control that pulls the data entered in the
corresponding form control, like...

=Forms!FormName!ControlName

....but believe me, you really need to delete this entire mess and start over
from scratch by first building a proper set of tables for creating an
invoice. What you have now is a fancy typewriter tool, not a database.

 

[Guest]

ok well seeing as how im starting over walk me through this so i can do this
exactly the way it should be with no mistakes. tell me how to structure the
tables, but i still have to have all that same information in them. be
detailed please. you have no idea how much you are helping if you can do
this for me, thanks for it all.

ok i did what you said so far. it still didnt put all the values in
the report, my report looks just like the empty form.

Is there data in your tables? If the RecordSource is a query does the query
return any records?

Quick test example. Select any table in your db window that contains data
and then in the toolbar find the "New Object - AutoForm" button and use the
AutoReport option. Access will build a simple report based on that table
and it will contain all the records found in the table.

Your report works on the same principle. The Report is bound to a table or
query and if that table or query contains any records then you should see
those records when you preview the report.

and how does
that primary key thing work, how does it know to get those certain
values?

Imagine a query against a table with a Primary Key name [ID] with a numeric
DataType. The following query returns the one record with an [ID] value of
123...

SELECT * FROM TableName
WHERE [ID] = 123

If I have a form bound to the same table and it is currently positioned on
the record with an [ID] value of 123 then this query will also return that
record...

SELECT * FROM TableName
WHERE [ID] = Forms!FormName![ID]

The difference is that the criteria in the above query is dynamic. Every
time I move to a different record in the form and re-run the query I will
always get the same record that is being shown on the form. If I use such a
query as the RecordSource for a report the result is a report that returns
only the record currently displayed on the form.

maybe if i explain my form better youll know what im talking
about: i have one table that lists only types of products (ex. hard
drives, floppy drives, processors, etc.) it looks like this:
name = "Products2"
[ID] [Product]
01 Case Fans
02 Mother Boards
etc.

this is linked through to another table from the first's ID to the
second's product.
name = "SubMenu2"
[ID] [Product] [Distrib.] [Manufact.] [Descr.] [U. Price] [S. Price]
[In Stock] [Order]
01 Hard drive D&H W. Digital 80GB $60.00 $85.00
2 2
etc.

on my form i have two text boxes where in one i enter the customer's
name and number, it is not bound to a table or query. then the other
is where i enter comments or special instructions to the customer
(ex. warranty - no charge), this is also not bound seeing as how it
is just inputting text. then there are ten sets of two drop boxes, a
quantity text box, a unit price text box, and then a total price text
box. these are on there ten times because the invoice can sell up to
ten items on one invoice, trust me that's enough for invoice. the
first drop box has the row source of a query that has the id and
product of the first table, the drop box only displays the product.
when a product is selected it requeries the second table which has a
row source of another query which in includes from the second table,
id, description, product, and sale price. the second drop box only
displays the descriptions of each item under the product in the first
drop box. after you select the description of i certain item it
displays the unit price from the third column in the unit price text
box field. because it comes from the column and not a source it only
displays it as a number so it then multiplies by one in a currency
expression. then you input a number in the quantity field and it
takes that number and multiplies it by the unit price and then
displays the total price just a currency expression. the quantity,
unit price, and sale price are unbound and so are all the drop
boxes. it looks like this: product \/ description \/
quantity unit price total price _______________
_______________ _________ _________ ________

____________|\/| |____________|\/| |________| |________|
|_______|

it has this ten times going down the page. then at the bottom i have 5
buttons, the first resets the form, the second prints one copy and
the third prints two copies. the forth does a total pricing. it
takes all the total prices and adds them and displays a subtotal,
then it multiplies by .065 and puts that number as the tax, and then
it adds them together and displays a grand total. all of these
things i just explain have to go onto the report, and i cant change
the way the form looks. the form is bound to a query that has id and
product from the first table, and id, decription, product, sale
price, and in stock. i dont know how much of a challenge this is but
i hope you can help me. ask me anything you need to know. btw the
drop boxes each have their own query, making twenty queries because
when it requeries it can only refer to one drop box, so it refers to
the first drop box by name and each query must have a different
criteria to refer to that certain drop box. enjoy! thanks a whole
bunch if you can help.

Sounds like a mess to me. Essentially you are entering a bunch of data into
all unbound controls instead of saving this data into tables. That is why
your form print prevview and your report print preview don't show anything,
because you are not storing any of this in a table. Your report would need
to have ControlSources on EVERY control that pulls the data entered in the
corresponding form control, like...

=Forms!FormName!ControlName

....but believe me, you really need to delete this entire mess and start over
from scratch by first building a proper set of tables for creating an
invoice. What you have now is a fancy typewriter tool, not a database.

 

[Guest]

oh and btw i need the quantity field on the form to automatically subtract
from the "in stock" field in the table, so tell me how to work update queries
too.

ok i did what you said so far. it still didnt put all the values in
the report, my report looks just like the empty form.

Is there data in your tables? If the RecordSource is a query does the query
return any records?

Quick test example. Select any table in your db window that contains data
and then in the toolbar find the "New Object - AutoForm" button and use the
AutoReport option. Access will build a simple report based on that table
and it will contain all the records found in the table.

Your report works on the same principle. The Report is bound to a table or
query and if that table or query contains any records then you should see
those records when you preview the report.

and how does
that primary key thing work, how does it know to get those certain
values?

Imagine a query against a table with a Primary Key name [ID] with a numeric
DataType. The following query returns the one record with an [ID] value of
123...

SELECT * FROM TableName
WHERE [ID] = 123

If I have a form bound to the same table and it is currently positioned on
the record with an [ID] value of 123 then this query will also return that
record...

SELECT * FROM TableName
WHERE [ID] = Forms!FormName![ID]

The difference is that the criteria in the above query is dynamic. Every
time I move to a different record in the form and re-run the query I will
always get the same record that is being shown on the form. If I use such a
query as the RecordSource for a report the result is a report that returns
only the record currently displayed on the form.

maybe if i explain my form better youll know what im talking
about: i have one table that lists only types of products (ex. hard
drives, floppy drives, processors, etc.) it looks like this:
name = "Products2"
[ID] [Product]
01 Case Fans
02 Mother Boards
etc.

this is linked through to another table from the first's ID to the
second's product.
name = "SubMenu2"
[ID] [Product] [Distrib.] [Manufact.] [Descr.] [U. Price] [S. Price]
[In Stock] [Order]
01 Hard drive D&H W. Digital 80GB $60.00 $85.00
2 2
etc.

on my form i have two text boxes where in one i enter the customer's
name and number, it is not bound to a table or query. then the other
is where i enter comments or special instructions to the customer
(ex. warranty - no charge), this is also not bound seeing as how it
is just inputting text. then there are ten sets of two drop boxes, a
quantity text box, a unit price text box, and then a total price text
box. these are on there ten times because the invoice can sell up to
ten items on one invoice, trust me that's enough for invoice. the
first drop box has the row source of a query that has the id and
product of the first table, the drop box only displays the product.
when a product is selected it requeries the second table which has a
row source of another query which in includes from the second table,
id, description, product, and sale price. the second drop box only
displays the descriptions of each item under the product in the first
drop box. after you select the description of i certain item it
displays the unit price from the third column in the unit price text
box field. because it comes from the column and not a source it only
displays it as a number so it then multiplies by one in a currency
expression. then you input a number in the quantity field and it
takes that number and multiplies it by the unit price and then
displays the total price just a currency expression. the quantity,
unit price, and sale price are unbound and so are all the drop
boxes. it looks like this: product \/ description \/
quantity unit price total price _______________
_______________ _________ _________ ________

____________|\/| |____________|\/| |________| |________|
|_______|

it has this ten times going down the page. then at the bottom i have 5
buttons, the first resets the form, the second prints one copy and
the third prints two copies. the forth does a total pricing. it
takes all the total prices and adds them and displays a subtotal,
then it multiplies by .065 and puts that number as the tax, and then
it adds them together and displays a grand total. all of these
things i just explain have to go onto the report, and i cant change
the way the form looks. the form is bound to a query that has id and
product from the first table, and id, decription, product, sale
price, and in stock. i dont know how much of a challenge this is but
i hope you can help me. ask me anything you need to know. btw the
drop boxes each have their own query, making twenty queries because
when it requeries it can only refer to one drop box, so it refers to
the first drop box by name and each query must have a different
criteria to refer to that certain drop box. enjoy! thanks a whole
bunch if you can help.

Sounds like a mess to me. Essentially you are entering a bunch of data into
all unbound controls instead of saving this data into tables. That is why
your form print prevview and your report print preview don't show anything,
because you are not storing any of this in a table. Your report would need
to have ControlSources on EVERY control that pulls the data entered in the
corresponding form control, like...

=Forms!FormName!ControlName

....but believe me, you really need to delete this entire mess and start over
from scratch by first building a proper set of tables for creating an
invoice. What you have now is a fancy typewriter tool, not a database.

 

[Rick Brandt]

ok well seeing as how im starting over walk me through this so i can
do this exactly the way it should be with no mistakes. tell me how to
structure the tables, but i still have to have all that same
information in them. be detailed please. you have no idea how much
you are helping if you can do this for me, thanks for it all.

While I don't have the time to design an entire invoicing system for you I
will tell you that you need to start off with two tables. One would hold
the data in the InvoiceHeader (all of the data that is unique to the invoice
generally). Then you need a table to contain the Line-Items on the invoice
(all of the per-item data). There will be a one-to-many relationship
between the Invoice header table and the line-items table and you would
(typically) then have a form with a subform to handle data entry.