Report from Record In Form

[FIECA]

Question: I have a form which provides details on projects my company has
been awarded. I have also formulated a report to provide the same details
in a printed form. Is there a way to create a button on the form to print
the report using the information currently displayed (or the information for
the current record), as opposed to having to exit the form and then open the
report and print it.

Any help would be greatly appreciated.

Thank you,


Tom Pratt

 

[fredg]

Question: I have a form which provides details on projects my company has
been awarded. I have also formulated a report to provide the same details
in a printed form. Is there a way to create a button on the form to print
the report using the information currently displayed (or the information for
the current record), as opposed to having to exit the form and then open the
report and print it.

Any help would be greatly appreciated.

Thank you,

Tom Pratt

Your table should have a unique prime key field.
In my example it is named [RecordID].

Add a command button to your form.
On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'

 

[FIECA]

Fred.

Thanks for the code. However, I need to tweak it a little and need your
help again.

I guess because the report is based on a query, the query opens and I have
to type in the project number (that's what the query is based on).

I tried making a copy of the report and making it dependent on the table
only, thereby eliminating the need to put in the project number, but that
creates some other problems, as the information in the report is drawn from
a few different tables. So when the command executes, it begins asking for
information that is contained in the other tables, that information being
info I couldn't possibly remember to be able to input.

So I guess my question is how do I modify the code to have the command
button transfer the project number (which is the same as RecordID in your
code) into the query so that it can automatically generate the report.
Either that or how do I fix it so that I don't have to remember all of the
other information to make the report come out right (I guess make it pull up
the information automatically, as it does in the query version)

One last question... When the report actually does come up (in the query
version), it is opened in the background, and I have to minimize everything
else to view it. Any way to make it open on top of everything else?

I hope my questions make sense. And I really really appreciate your help.
I am learning, albeit slowly via the help of others and mainly through trial
and error.

Thanks again,

Tom Pratt

Question: I have a form which provides details on projects my company
has
been awarded. I have also formulated a report to provide the same
details
in a printed form. Is there a way to create a button on the form to
print
the report using the information currently displayed (or the information
for
the current record), as opposed to having to exit the form and then open
the
report and print it.

Any help would be greatly appreciated.

Thank you,

Tom Pratt

Your table should have a unique prime key field.
In my example it is named [RecordID].

Add a command button to your form.
On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'

 

[fredg]

Fred.

Thanks for the code. However, I need to tweak it a little and need your
help again.

I guess because the report is based on a query, the query opens and I have
to type in the project number (that's what the query is based on).

I tried making a copy of the report and making it dependent on the table
only, thereby eliminating the need to put in the project number, but that
creates some other problems, as the information in the report is drawn from
a few different tables. So when the command executes, it begins asking for
information that is contained in the other tables, that information being
info I couldn't possibly remember to be able to input.

So I guess my question is how do I modify the code to have the command
button transfer the project number (which is the same as RecordID in your
code) into the query so that it can automatically generate the report.
Either that or how do I fix it so that I don't have to remember all of the
other information to make the report come out right (I guess make it pull up
the information automatically, as it does in the query version)

One last question... When the report actually does come up (in the query
version), it is opened in the background, and I have to minimize everything
else to view it. Any way to make it open on top of everything else?

I hope my questions make sense. And I really really appreciate your help.
I am learning, albeit slowly via the help of others and mainly through trial
and error.

Thanks again,

Tom Pratt

Question: I have a form which provides details on projects my company
has
been awarded. I have also formulated a report to provide the same
details
in a printed form. Is there a way to create a button on the form to
print
the report using the information currently displayed (or the information
for
the current record), as opposed to having to exit the form and then open
the
report and print it.

Any help would be greatly appreciated.

Thank you,

Tom Pratt

Your table should have a unique prime key field.
In my example it is named [RecordID].

Add a command button to your form.
On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'

Here are two ways.

1) Keep the query as the record source for the Report.
In the query, on the ProjectID column's criteria line, write:
forms!FormName!ProjectID

In the Form, change the code I gave your to:

DoCmd.OpenReport "ReportName", acViewPreview

Or...
2) You could remove the criteria from the query ProjectID completly
(in other words the report will show all of the Projects.).
Then use the previous code to open the report filtered to just the
record shown in the form.

DoCmd.OpenReport "ReportName", acViewPreview, , "[ProjectID] = " &
[ProjectID]

It's up to you. Try them both.

In either event, keep the DoCmd.RunCommand line to save any newly
entered data before the report is run.

Any form that is already open in Pop-up or Modal will always be on
top. You'll have to see what is running when you open the report to
determine why the report is in the back.
If you are using Access 2000 or newer, you can also open the report in
Dialog mode:

DoCmd.OpenReport "ReportName", acViewPreview, , , acDialog

It will then be on top.

 

[FIECA]

Fred,

Not sure what I am doing wrong, but I still can't get it to work. I have
revised the code, but I am still getting the dialog boxes asking me for the
other information which should be pulled from related tables based on the
ProjectID. I have a feeling that if it wasn't for this, it would be
working, but somehow it is not connecting the information from the main
table with the information in the related table(s) as it normally would when
the query is run. Any advice would be appreciated.

Thanks,

Tom

Fred.

Thanks for the code. However, I need to tweak it a little and need your
help again.

I guess because the report is based on a query, the query opens and I
have
to type in the project number (that's what the query is based on).

I tried making a copy of the report and making it dependent on the table
only, thereby eliminating the need to put in the project number, but that
creates some other problems, as the information in the report is drawn
from
a few different tables. So when the command executes, it begins asking
for
information that is contained in the other tables, that information being
info I couldn't possibly remember to be able to input.

So I guess my question is how do I modify the code to have the command
button transfer the project number (which is the same as RecordID in your
code) into the query so that it can automatically generate the report.
Either that or how do I fix it so that I don't have to remember all of
the
other information to make the report come out right (I guess make it pull
up
the information automatically, as it does in the query version)

One last question... When the report actually does come up (in the query
version), it is opened in the background, and I have to minimize
everything
else to view it. Any way to make it open on top of everything else?

I hope my questions make sense. And I really really appreciate your
help.
I am learning, albeit slowly via the help of others and mainly through
trial
and error.

Thanks again,

Tom Pratt

On Wed, 28 Feb 2007 14:36:30 -0500, FIECA wrote:

Question: I have a form which provides details on projects my company
has
been awarded. I have also formulated a report to provide the same
details
in a printed form. Is there a way to create a button on the form to
print
the report using the information currently displayed (or the
information
for
the current record), as opposed to having to exit the form and then
open
the
report and print it.

Any help would be greatly appreciated.

Thank you,

Tom Pratt

Your table should have a unique prime key field.
In my example it is named [RecordID].

Add a command button to your form.
On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'

Here are two ways.

1) Keep the query as the record source for the Report.
In the query, on the ProjectID column's criteria line, write:
forms!FormName!ProjectID

In the Form, change the code I gave your to:

DoCmd.OpenReport "ReportName", acViewPreview

Or...
2) You could remove the criteria from the query ProjectID completly
(in other words the report will show all of the Projects.).
Then use the previous code to open the report filtered to just the
record shown in the form.

DoCmd.OpenReport "ReportName", acViewPreview, , "[ProjectID] = " &
[ProjectID]

It's up to you. Try them both.

In either event, keep the DoCmd.RunCommand line to save any newly
entered data before the report is run.

Any form that is already open in Pop-up or Modal will always be on
top. You'll have to see what is running when you open the report to
determine why the report is in the back.
If you are using Access 2000 or newer, you can also open the report in
Dialog mode:

DoCmd.OpenReport "ReportName", acViewPreview, , , acDialog

It will then be on top.

 

[fredg]

Fred,

Not sure what I am doing wrong, but I still can't get it to work. I have
revised the code, but I am still getting the dialog boxes asking me for the
other information which should be pulled from related tables based on the
ProjectID. I have a feeling that if it wasn't for this, it would be
working, but somehow it is not connecting the information from the main
table with the information in the related table(s) as it normally would when
the query is run. Any advice would be appreciated.

Thanks,

Tom

Fred.

Thanks for the code. However, I need to tweak it a little and need your
help again.

I guess because the report is based on a query, the query opens and I
have
to type in the project number (that's what the query is based on).

I tried making a copy of the report and making it dependent on the table
only, thereby eliminating the need to put in the project number, but that
creates some other problems, as the information in the report is drawn
from
a few different tables. So when the command executes, it begins asking
for
information that is contained in the other tables, that information being
info I couldn't possibly remember to be able to input.

So I guess my question is how do I modify the code to have the command
button transfer the project number (which is the same as RecordID in your
code) into the query so that it can automatically generate the report.
Either that or how do I fix it so that I don't have to remember all of
the
other information to make the report come out right (I guess make it pull
up
the information automatically, as it does in the query version)

One last question... When the report actually does come up (in the query
version), it is opened in the background, and I have to minimize
everything
else to view it. Any way to make it open on top of everything else?

I hope my questions make sense. And I really really appreciate your
help.
I am learning, albeit slowly via the help of others and mainly through
trial
and error.

Thanks again,

Tom Pratt
On Wed, 28 Feb 2007 14:36:30 -0500, FIECA wrote:

Question: I have a form which provides details on projects my company
has
been awarded. I have also formulated a report to provide the same
details
in a printed form. Is there a way to create a button on the form to
print
the report using the information currently displayed (or the
information
for
the current record), as opposed to having to exit the form and then
open
the
report and print it.

Any help would be greatly appreciated.

Thank you,

Tom Pratt

Your table should have a unique prime key field.
In my example it is named [RecordID].

Add a command button to your form.
On the command button's property sheet write
[Event Procedure]
on the Click event line.
Then click on the little button with 3 dots that will appear on that
line.
When the code window opens, the cursor will be flashing between 2
already existing lines of code.
Between those 2 lines write:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "ReportName", acViewPreview, , "[RecordID] = " &
[RecordID]

The above assumes a [RecordID] field that is a Number Datatype.

If, however, [RecordID] is Text Datatype, then use:

DoCmd.OpenReport "ReportName", acViewPreview, ,"[RecordID] = '" &
[RecordID] & "'"

as the Where clause.

For clarity, the single and double quotes are..
"[RecordID] = ' " & [RecordID] & " ' "
Change [RecordID] to whatever the actual field name is that you are
using.

See VBA Help files for:
Where Clause + Restrict data to a subset of records'

Here are two ways.

1) Keep the query as the record source for the Report.
In the query, on the ProjectID column's criteria line, write:
forms!FormName!ProjectID

In the Form, change the code I gave your to:

DoCmd.OpenReport "ReportName", acViewPreview

Or...
2) You could remove the criteria from the query ProjectID completly
(in other words the report will show all of the Projects.).
Then use the previous code to open the report filtered to just the
record shown in the form.

DoCmd.OpenReport "ReportName", acViewPreview, , "[ProjectID] = " &
[ProjectID]

It's up to you. Try them both.

In either event, keep the DoCmd.RunCommand line to save any newly
entered data before the report is run.

Any form that is already open in Pop-up or Modal will always be on
top. You'll have to see what is running when you open the report to
determine why the report is in the back.
If you are using Access 2000 or newer, you can also open the report in
Dialog mode:

DoCmd.OpenReport "ReportName", acViewPreview, , , acDialog

It will then be on top.

All I can suggest, at this point, is that you copy and paste into a
new message all of the code in the report's Open and Close event, as
well as the form's command button event, so I can see exactly what you
have written. Include the entire event code, from the Private Sub ()
line to the End Sub line.
Also, when you get the parameter prompt, EXACTLY what is it prompting
you for? Perhaps you should also post the exact query SQL.