Print just one record from a report

[Guest]

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

 

[Guest]

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Thanks, your reply to filo66 really helped. When I click on the print
button, it opens the report with only one record just like I want and prints
that record automatically. What command can I add to close the report?
Thanks for the help.

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Sprinks,

I copied the code and placed it in the proper place. It brought up the
report with only one record as required but it did not let it print
automatically. I got it to work once and only once. So I started all over
and I still can't get it to work. Please help because I'm going to half to
use this routine a lot.

Also I took a course in Access 2003 and I'm having a great time designing
our database at work, however the course did not go into vbl.

Do you have any suggestions as to what to take next. I see vbl V6, Vbl.net
and sql that other students have taken. I want to take another course but I
need it to go with Acess. Any suggestions?
Thank,
Jackson

 

[Guest]

Hi, Jackson.

Re: Procedure That Isn't Working
Please post it (cut and paste directly from the VBA Editor), and the names
of your report, the primary key of its RecordSource, and the corresponding
control on your form that has the key data, and I'll see if I can figure it
out.

Re: Learning Access
I'm afraid I can't help you much with formal courses to take; if it's been a
useful experience for you, try to get a detailed list of topics for each
available course and match them up with your needs & interests.

There are other excellent sources available in learning to develop
applications:

- the Northwind database that comes with Access demonstrates forms and
subforms, and many programming issues. Use the application, then investigate
the code behind command buttons, and the table structures, etc. See the Help
menu.

- create command buttons for various events with the wizard, and study the
code the wizard creates.

- Access 2002 Developer's Handbook by Litwin, Getz, & Gilbert. Parts of it
are advanced; there are other texts aimed at the basics, but I don't know
what they are.

- check out Jeff Conrad's recent (2-3 months) comprehensive post about
Access web resources. It's been awesome.

Hope that helps.
Sprinks

 

[Guest]

Thanks for the help, I finally figured it out. There is one other item I'd
like to try. I have a lot of products and I would like a menu of some sort
to selected certain products and just print the selected records. Can this
be done using a check box?
Jackson

 

[Guest]

Hi, Jackson. Glad you got the last one to work. I was very curious as to
why it wasn't working; I test, then cut & paste all the code I recommend
here, and it worked as advertised.

Sure, a checkbox would be fine. Add a Yes/No field to your Products table
first. If your form is based on the table directly, you can edit it and add
the Yes/No checkbox directly. If the form is based on a query, add the field
to the query first.

Then add a command button to print the records, using a WHERE statement in
the OpenReport call. Then, optionally, allow the user to reset all the
checks back to false. Alternatively, this portion of the code could go on a
separate command button.

Dim stDocName As String
Dim strSQL As String
Dim strResponse As String

stDocName = "YourReportName"

' Save any changes made in this edit session
Me.Requery

' Open report
DoCmd.OpenReport stDocName, acViewNormal, , "[Print]= True"

' See if user wants to reset the checkboxes
strResponse = MsgBox("Uncheck all records?", vbOKCancel, "Option To
Reset Print Status")

' If so, run an update query and refresh the screen
If strResponse = vbOK Then
strSQL = "UPDATE YourTable SET YourTable.YourField = False;"
DoCmd.RunSQL strSQL
Me.Refresh
End If

Hope that helps.
Sprinks

 

[Guest]

I am new to Access. Until now, I've been lucky and used code found here
without problems. BUT, in this exchange, I have been unable to get the
report to print. I am having problems with the Access language. PLEASE
help. Should "YourPrimaryKey=" be replaced with the name of the primary key
for my database? And what is the reference to "[YourPrimaryKeyControl]" mean?
I am also hoping that "Reports! in this code doesn't need to be replaced
with "Forms!" because I am wanting to print from a form?

Thank you for your help.
--
Glenda

Hi, Jackson. Glad you got the last one to work. I was very curious as to
why it wasn't working; I test, then cut & paste all the code I recommend
here, and it worked as advertised.

Sure, a checkbox would be fine. Add a Yes/No field to your Products table
first. If your form is based on the table directly, you can edit it and add
the Yes/No checkbox directly. If the form is based on a query, add the field
to the query first.

Then add a command button to print the records, using a WHERE statement in
the OpenReport call. Then, optionally, allow the user to reset all the
checks back to false. Alternatively, this portion of the code could go on a
separate command button.

Dim stDocName As String
Dim strSQL As String
Dim strResponse As String

stDocName = "YourReportName"

' Save any changes made in this edit session
Me.Requery

' Open report
DoCmd.OpenReport stDocName, acViewNormal, , "[Print]= True"

' See if user wants to reset the checkboxes
strResponse = MsgBox("Uncheck all records?", vbOKCancel, "Option To
Reset Print Status")

' If so, run an update query and refresh the screen
If strResponse = vbOK Then
strSQL = "UPDATE YourTable SET YourTable.YourField = False;"
DoCmd.RunSQL strSQL
Me.Refresh
End If

Hope that helps.
Sprinks

Thanks for the help, I finally figured it out. There is one other item I'd
like to try. I have a lot of products and I would like a menu of some sort
to selected certain products and just print the selected records. Can this
be done using a check box?
Jackson

 

[Guest]

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi, Glenda.

Glad you got it to work. Yes, sometimes at the beginning of developing an
application, a macro can provide a simpler solution. And if it's working for
you, that's great. As things get more complicated, macros' limitations
become troubling, and VBA's flexibility is useful.

In the code I gave you, yes, replace "YourPrimaryKey" with the name of your
primary key field. The reference to YourPrimaryKeyControl is the control on
your form that is bound to your primary key field. This *may* have the same
name as your primary key, but could be different--most developers name their
controls with a naming convention. For example, a textbox bound to a field
named ID might be named txtID.

So, if we assume the following names:

Your report name: MyReport
Your primary key: MyID
The name of the form control bound to MyID: txtID

then:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

becomes:

' Open the report in preview mode
DoCmd.OpenReport "MyReport", acPreview
' Set the filter, filtering my primary key by the value in the current form
record
Reports![MyReport].Filter = "MyID = " & Me![txtMyID]
' Turn the filter on to filter the records for the report
Reports![MyReport].FilterOn = True


Hope that helps.
Sprinks

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

:

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi,
I used the 3lines of code u provided for filo in my db.
When using acPreview it filters the records and only reports the one record.
However when i changed acPreview to print, it prints all records then gives
me an error on the 2 filter lines of code..."report isn't open, or doesn't
exist"
the 2 lines of code dont give me a problem when i preview however...

any ideas sprinks?

Hi, Glenda.

Glad you got it to work. Yes, sometimes at the beginning of developing an
application, a macro can provide a simpler solution. And if it's working for
you, that's great. As things get more complicated, macros' limitations
become troubling, and VBA's flexibility is useful.

In the code I gave you, yes, replace "YourPrimaryKey" with the name of your
primary key field. The reference to YourPrimaryKeyControl is the control on
your form that is bound to your primary key field. This *may* have the same
name as your primary key, but could be different--most developers name their
controls with a naming convention. For example, a textbox bound to a field
named ID might be named txtID.

So, if we assume the following names:

Your report name: MyReport
Your primary key: MyID
The name of the form control bound to MyID: txtID

then:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

becomes:

' Open the report in preview mode
DoCmd.OpenReport "MyReport", acPreview
' Set the filter, filtering my primary key by the value in the current form
record
Reports![MyReport].Filter = "MyID = " & Me![txtMyID]
' Turn the filter on to filter the records for the report
Reports![MyReport].FilterOn = True


Hope that helps.
Sprinks

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

:

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

:

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Hi, Jae.

In VBA Help, acNormal mode is defined as "Prints the report immediately.",
i.e., before the filter can be applied. Once the report prints, it closes,
then goes on to execute the next commands. Since the report is no longer
open, Access generates the error. So, on-the-fly filtering only works in
Preview mode.

The only workaround I can see is "hard-wiring" the report--setting the
Filter to a query name, and the FilterOn to Yes, and then programmatically
creating the query object prior to the OpenReport call. However, I've never
done this myself. Another post on this issue should get you an answer.

Sprinks

Hi,
I used the 3lines of code u provided for filo in my db.
When using acPreview it filters the records and only reports the one record.
However when i changed acPreview to print, it prints all records then gives
me an error on the 2 filter lines of code..."report isn't open, or doesn't
exist"
the 2 lines of code dont give me a problem when i preview however...

any ideas sprinks?

Hi, Glenda.

Glad you got it to work. Yes, sometimes at the beginning of developing an
application, a macro can provide a simpler solution. And if it's working for
you, that's great. As things get more complicated, macros' limitations
become troubling, and VBA's flexibility is useful.

In the code I gave you, yes, replace "YourPrimaryKey" with the name of your
primary key field. The reference to YourPrimaryKeyControl is the control on
your form that is bound to your primary key field. This *may* have the same
name as your primary key, but could be different--most developers name their
controls with a naming convention. For example, a textbox bound to a field
named ID might be named txtID.

So, if we assume the following names:

Your report name: MyReport
Your primary key: MyID
The name of the form control bound to MyID: txtID

then:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

becomes:

' Open the report in preview mode
DoCmd.OpenReport "MyReport", acPreview
' Set the filter, filtering my primary key by the value in the current form
record
Reports![MyReport].Filter = "MyID = " & Me![txtMyID]
' Turn the filter on to filter the records for the report
Reports![MyReport].FilterOn = True


Hope that helps.
Sprinks

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda


:

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

:

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

:

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

Sprinks,
Thanks for the reply.
Here's what I just did to get it working:

Took the filter lines out of my button click event procedure to avoid
getting the debug message when printing the report.
The report was based on a query I had created, so I opened the query in
design view and added this into the criteria for my orderid field:
[Forms]![Orders]![OrderID]

I guess this approach is similar to your hard-coded one, except that the
filter is placed on the query and not the report.

In case anyone else needs this the format for the query criteria is this:
[Forms]![YourForm]![YourPrimaryKey]

This makes the query lookup the data for whichever field you specified
criteria using the value thats on your form, similar to Sprinks VBA method.

Thanks again,
Jae

Hi, Jae.

In VBA Help, acNormal mode is defined as "Prints the report immediately.",
i.e., before the filter can be applied. Once the report prints, it closes,
then goes on to execute the next commands. Since the report is no longer
open, Access generates the error. So, on-the-fly filtering only works in
Preview mode.

The only workaround I can see is "hard-wiring" the report--setting the
Filter to a query name, and the FilterOn to Yes, and then programmatically
creating the query object prior to the OpenReport call. However, I've never
done this myself. Another post on this issue should get you an answer.

Sprinks

Hi,
I used the 3lines of code u provided for filo in my db.
When using acPreview it filters the records and only reports the one record.
However when i changed acPreview to print, it prints all records then gives
me an error on the 2 filter lines of code..."report isn't open, or doesn't
exist"
the 2 lines of code dont give me a problem when i preview however...

any ideas sprinks?

Hi, Glenda.

Glad you got it to work. Yes, sometimes at the beginning of developing an
application, a macro can provide a simpler solution. And if it's working for
you, that's great. As things get more complicated, macros' limitations
become troubling, and VBA's flexibility is useful.

In the code I gave you, yes, replace "YourPrimaryKey" with the name of your
primary key field. The reference to YourPrimaryKeyControl is the control on
your form that is bound to your primary key field. This *may* have the same
name as your primary key, but could be different--most developers name their
controls with a naming convention. For example, a textbox bound to a field
named ID might be named txtID.

So, if we assume the following names:

Your report name: MyReport
Your primary key: MyID
The name of the form control bound to MyID: txtID

then:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

becomes:

' Open the report in preview mode
DoCmd.OpenReport "MyReport", acPreview
' Set the filter, filtering my primary key by the value in the current form
record
Reports![MyReport].Filter = "MyID = " & Me![txtMyID]
' Turn the filter on to filter the records for the report
Reports![MyReport].FilterOn = True


Hope that helps.
Sprinks


:

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda


:

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

:

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

:

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[Guest]

see if you can figure mine out: "Report name: recon form" "Primary Key:
Record Number" "Control Source: Record Number (i think?)



Private Sub print_reconform_Click()
On Error GoTo Err_print_reconform_Click

Dim stDocName As String

strWhere = "Record number = " & Me![Record Number]
stsstDocName = "Recon form"
DoCmd.OpenReport stDocName, acViewPreview, , strWhere

Exit_print_reconform_Click:
Exit Sub

Err_print_reconform_Click:
MsgBox Err.Description
Resume Exit_print_reconform_Click

End Sub

 

[Very Basic User]

Hello Sprigs,

Hoping you can help...

My cose is as follows


Private Sub Form_Close()
DoCmd.OpenReport "Mail Option Report", acPreview
Reports![Mail Option Report].Filter = "Why - Why Number = " & Me![Why - Why
Number]
Reports![Mail Option Report].FilterOn = True

End Sub


I get the following error message when I try to get it to work...

Syntax error (cissing operatro) in query expression ‘(Why – Why Number = 24)’


The # 24 in the Control section is the first record in the form. I get the
same message even if I'm in a different record.

Please help!

--
Thank you for your time!
John

Hi, Glenda.

Glad you got it to work. Yes, sometimes at the beginning of developing an
application, a macro can provide a simpler solution. And if it's working for
you, that's great. As things get more complicated, macros' limitations
become troubling, and VBA's flexibility is useful.

In the code I gave you, yes, replace "YourPrimaryKey" with the name of your
primary key field. The reference to YourPrimaryKeyControl is the control on
your form that is bound to your primary key field. This *may* have the same
name as your primary key, but could be different--most developers name their
controls with a naming convention. For example, a textbox bound to a field
named ID might be named txtID.

So, if we assume the following names:

Your report name: MyReport
Your primary key: MyID
The name of the form control bound to MyID: txtID

then:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

becomes:

' Open the report in preview mode
DoCmd.OpenReport "MyReport", acPreview
' Set the filter, filtering my primary key by the value in the current form
record
Reports![MyReport].Filter = "MyID = " & Me![txtMyID]
' Turn the filter on to filter the records for the report
Reports![MyReport].FilterOn = True


Hope that helps.
Sprinks

I just found a much easier method (Access 2003). Instead of all of the code,
I used a Macro. I clicked on Macros, chose "PrintOut" in the dropdown in the
Action Menu. Then at the bottom, chose pages from 1 and page to 1. Then
setup a command button as described in this and instead of choosing
CodeBuilder, chose the Macro and it works.

THANKS again for everyone's help,
--
Glenda

Hi, Jackson.

To simply print the report, rather than open it in Preview mode, change the
Visual Basic instrinsic constant "acPreview" to "acNormal".

Hope that helps.
Sprinks

:

I goofed - I had it working once and can't get it to print and then close
automatically. Can you help?

:

Hi, Filo.

I'll slow down. Access forms and their controls all have "events"
associated with them. When the event occurs, any procedure defined for the
event runs, giving you a great deal of control over how data is entered, what
the user sees, etc. For example, among others, textboxes have OnGotFocus,
BeforeUpdate, AfterUpdate, OnLostFocus events. Command buttons have, among
others, OnClick and OnDblClick events.

I'd assumed that you would be using a command button on your form to print
the report for the current record. The code I included would be placed in
the CommandButton's OnClick Event procedure. If you've never done this
before, open your form, and display the Toolbox (View, Toolbox). First turn
the wizard off (toggle off the Wand & Stars button on the toolbox). Click on
the command button icon, and draw one on your form. If you wish to rename it
from the Access default name given it, do so now before proceeding.

Right click on the button and select Properties, and click on the Event tab.
Click into the OnClick field, then press the ellipsis to the right of the
field, and select CodeBuilder. Access will create the shell of an OnClick
procedure for you. Cut and paste the code I posted between the Sub and End
Sub lines, change all occurences of "YourReport" to the name of your report,
and save it. This code will now be run when the user clicks on the button.

Hope that helps.
Sprinks

:

what??? I didn't understand a word, where I gona put all wat you write?? a
macro i supose???

:

Hi, Filo.

Assign a Filter using the primary key of the current record, and turn the
filter on:

DoCmd.OpenReport "YourReport", acPreview
Reports![YourReport].Filter = "YourPrimaryKey = " & Me![YourPrimaryKeyControl]
Reports![YourReport].FilterOn = True

Hope that helps.
Sprinks

:

Hello, I want to crate a macro that prints just one record of one form linked
to a table with 54 records, when I try to print the report the 54 records are
printed¡¡¡

 

[RasJr.]

Hi, Sprinks

I hope you're not burned out on answering "Print" questions; but I have one
for you, if you don't mind?

I've read through this thread and found several answers to my questions, but
the one that still hasn't been answered...once the code you provided is
entered, how do you tell the database which printer to print from?

I have a split database that has 8 front-ends feeding into it from a shared
folder on an isolated network. How can I arrange it so each user can print
indivual forms from his own PC.

To give you an idea of what I need specifically...I converted the "Assets"
template from Access 07 to fit my needs; and what I need to print is the
"Assets Details" form.

Of course any and all assistance is appreciated. And thanks for saving our
newb butts.

V/r
RasJr.