How do I code command button to print envelope I am viewing?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created the envelope report.
I am able to access and print it using a command button.
I need to know how to print the envelope only for the current record.
[I am designing this for a staff that is not familiar with queries so I need
something very simple.]
Please help.
Thanks.
Joan
 
I have created the envelope report.
I am able to access and print it using a command button.
I need to know how to print the envelope only for the current record.
[I am designing this for a staff that is not familiar with queries so I need
something very simple.]
Please help.
Thanks.
Joan
Click to expand...

Two ways:

- Base the Envelope report on a Query which references the form. Put a
criterion on the unique ID (whatever that might be) of

=Forms![NameOfForm]![NameOfTextbox]

where you substitute your own form and control names, of course.


- Or, edit the wizard generated button code to reference the unique
value of the envelope you want to report: instead of

DoCmd.OpenReport strDocument

use

DoCmd.OpenReport strDocument, WhereCondition := "[UniqueID] = " & _
Me![NameOfTextbox]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I'm fairly new at this.
Would the [UniqueID] be the case number assigned to the record?
Would the [NameofTextbox] be the name of the command button?
Shall await your reply. Thanks.
Joan

John Vinson said:
I have created the envelope report.
I am able to access and print it using a command button.
I need to know how to print the envelope only for the current record.
[I am designing this for a staff that is not familiar with queries so I need
something very simple.]
Please help.
Thanks.
Joan
Click to expand...

Two ways:

- Base the Envelope report on a Query which references the form. Put a
criterion on the unique ID (whatever that might be) of

=Forms![NameOfForm]![NameOfTextbox]

where you substitute your own form and control names, of course.


- Or, edit the wizard generated button code to reference the unique
value of the envelope you want to report: instead of

DoCmd.OpenReport strDocument

use

DoCmd.OpenReport strDocument, WhereCondition := "[UniqueID] = " & _
Me![NameOfTextbox]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Later: I did find my uniqueID.
DoCmd.OpenReport strDocument, WhereCondition:="[CaseID]=" &
Me![Command12629envpltf]
Error Message: Object doesn't support this property or method.
Please Note: when I entered a _ after & I received an error message so I
deleted it.
Help, please.
 
Later: I did find my uniqueID.
DoCmd.OpenReport strDocument, WhereCondition:="[CaseID]=" &
Me![Command12629envpltf]
Error Message: Object doesn't support this property or method.
Please Note: when I entered a _ after & I received an error message so I
deleted it.
Help, please.
Click to expand...

I assume that the Report named in strDocument is based on a Table or a
Query which contains a unique field CaseID identifying which case you
want to print. I'll assume that CaseID is a Number field - see below
if it's Text.

On your Form (Me! means "this current form") you will need a Textbox
which also contains the CaseID that you want to send to the report.
Let's say that you have a Textbox on the form named txtCaseID, with
its Control Source set to CaseID; this textbox will be showing the ID
of the record you want to print.

If so, the line in the code in Command12629envpltf's Click event
should include a line

DoCmd.OpenReport strDocument, WhereCondition := "[CaseID] = " _
& Me!txtCaseID

So if the textbox txtCaseID contains 8132, the WhereCondition argument
becomes

[CaseID] = 8132

and when the Report is opened, it reports just case 8132.

If CaseID is a Text field, you need some quotemarks around it: the
WhereCondition argument should be

"[CaseID] = '" & Me!txtCaseID & "'"

This will set the WhereCondition to

[CaseID] = '8132'

which will work correctly if the field is of Text type.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I'm doing something wrong.
1. Note command name changed to Command12711 (I deleted the other)
2. Report is based on query named envelope1
3. Query contain unique field that is auto number
4. I put a text box field on my Cases form
5. Text box Control Source: CaseID
6. Text box named: Text12712
In event I did as follows, but received error message:
DoCmd.OpenReport strDocument,WhereCondition :="[CaseID]="_& Me!Text12712
It is obvious I do not understand the theory behind what I am trying to do.
Should the textbox on the Cases form have the same name in the event
property of the Command button?????
Please have patience. Thank you.
Joan

John Vinson said:
Later: I did find my uniqueID.
DoCmd.OpenReport strDocument, WhereCondition:="[CaseID]=" &
Me![Command12629envpltf]
Error Message: Object doesn't support this property or method.
Please Note: when I entered a _ after & I received an error message so I
deleted it.
Help, please.
Click to expand...

I assume that the Report named in strDocument is based on a Table or a
Query which contains a unique field CaseID identifying which case you
want to print. I'll assume that CaseID is a Number field - see below
if it's Text.

On your Form (Me! means "this current form") you will need a Textbox
which also contains the CaseID that you want to send to the report.
Let's say that you have a Textbox on the form named txtCaseID, with
its Control Source set to CaseID; this textbox will be showing the ID
of the record you want to print.

If so, the line in the code in Command12629envpltf's Click event
should include a line

DoCmd.OpenReport strDocument, WhereCondition := "[CaseID] = " _
& Me!txtCaseID

So if the textbox txtCaseID contains 8132, the WhereCondition argument
becomes

[CaseID] = 8132

and when the Report is opened, it reports just case 8132.

If CaseID is a Text field, you need some quotemarks around it: the
WhereCondition argument should be

"[CaseID] = '" & Me!txtCaseID & "'"

This will set the WhereCondition to

[CaseID] = '8132'

which will work correctly if the field is of Text type.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I'm doing something wrong.
1. Note command name changed to Command12711 (I deleted the other)
Click to expand...

The name of the command is COMPLETELY IRRELEVANT.
2. Report is based on query named envelope1
3. Query contain unique field that is auto number
Click to expand...

What's the name of that unique field? CaseID? Tables can have
autonumbers, and queries can include an autonumber field from the
table - but a query cannot have an autonumber in its own right.
4. I put a text box field on my Cases form
5. Text box Control Source: CaseID
6. Text box named: Text12712
In event I did as follows, but received error message:
DoCmd.OpenReport strDocument,WhereCondition :="[CaseID]="_& Me!Text12712
It is obvious I do not understand the theory behind what I am trying to do.
Should the textbox on the Cases form have the same name in the event
property of the Command button?????
Click to expand...

No. It should not. A command button is one type of control; a textbox
is a completely different type of control.

Are you putting this line *directly in the Click event property*? If
so, that's the problem! Delete the command button from your form, and
use the toolbox Wizard to create a new Button, using the option "use
this button to open a report". This will give you

[Event Procedure]

on the Property line and about a 15-line VBA function - *including* a
line with DoCmd.OpenReport on it. You can edit that function as above.

The theory is that you're using the button to run some VBA code to
open a Report. The DoCmd.OpenReport method is the line which opens a
report; its first argument is the name of the Report (the name of that
report's query is not needed here), which the button wizard puts into
the string variable strDocument. An optional argument to the
OpenReport method is its WhereCondition argument. If what you have is
literally what you posted above, get rid of the underscore - I was
putting it in because my newsreader wraps text lines and I was using
the underscore as a line-continuation character.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Before I delete the command button would you please double check the
following to your questions:
1. The CaseID is the unique field
2. My Envelope query (replaces envelope1 query): Field: CaseID Table:Cases
Query
3. I am not putting this*line directly in tjhe Click event property. I
click on the Event Procedure that takes me to Microsoft Visual Basics where I
type the line your gave me. Here is the whole code:
Private Sub Command12711envpltf_Click()
On Error GoTo Err_Command12711envpltf_Click

Dim stDocName As String

stDocName = "Envelope"
DoCmd.OpenReport strDocument, WhereCondition:="[CaseID] =" & Me!Text12712
Exit_Command12711envpltf_Click:
Exit Sub

Err_Command12711envpltf_Click:
MsgBox Err.Description
Resume Exit_Command12711envpltf_Click

End Sub
The CaseID in the envelope report correspondents to the correct record, but
when I go to print selects all the records (1 of 166).
The print option I need is Print Selection Records.
Again, thanks for your help. If I don't hear back from you soon I will go
ahead and delete the command button.
Joan

John Vinson said:
I'm doing something wrong.
1. Note command name changed to Command12711 (I deleted the other)
Click to expand...

The name of the command is COMPLETELY IRRELEVANT.
2. Report is based on query named envelope1
3. Query contain unique field that is auto number
Click to expand...

What's the name of that unique field? CaseID? Tables can have
autonumbers, and queries can include an autonumber field from the
table - but a query cannot have an autonumber in its own right.
4. I put a text box field on my Cases form
5. Text box Control Source: CaseID
6. Text box named: Text12712
In event I did as follows, but received error message:
DoCmd.OpenReport strDocument,WhereCondition :="[CaseID]="_& Me!Text12712
It is obvious I do not understand the theory behind what I am trying to do.
Should the textbox on the Cases form have the same name in the event
property of the Command button?????
Click to expand...

No. It should not. A command button is one type of control; a textbox
is a completely different type of control.

Are you putting this line *directly in the Click event property*? If
so, that's the problem! Delete the command button from your form, and
use the toolbox Wizard to create a new Button, using the option "use
this button to open a report". This will give you

[Event Procedure]

on the Property line and about a 15-line VBA function - *including* a
line with DoCmd.OpenReport on it. You can edit that function as above.

The theory is that you're using the button to run some VBA code to
open a Report. The DoCmd.OpenReport method is the line which opens a
report; its first argument is the name of the Report (the name of that
report's query is not needed here), which the button wizard puts into
the string variable strDocument. An optional argument to the
OpenReport method is its WhereCondition argument. If what you have is
literally what you posted above, get rid of the underscore - I was
putting it in because my newsreader wraps text lines and I was using
the underscore as a line-continuation character.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
Later: I deleted and recreated the Command Button.
Still could not print a single envelope.
I ended up creating a parameter based on the CaseID so when the Command
Button is hit the user will only have to type in the CaseID number that I
have visible on the form.
Wish I understood VBC better. If you spot anything wrong in the code I sent
earlier please let me know because I would love it if the users could just
hit the Command Button without any parameters.
Thanks. Joan
 
Later: I deleted and recreated the Command Button.
Still could not print a single envelope.
I ended up creating a parameter based on the CaseID so when the Command
Button is hit the user will only have to type in the CaseID number that I
have visible on the form.
Wish I understood VBC better. If you spot anything wrong in the code I sent
earlier please let me know because I would love it if the users could just
hit the Command Button without any parameters.
Thanks. Joan
Click to expand...

Sorry! I don't see what's wrong with the code... but I can suggest an
alternative which will have the same effect.

Do use a parameter query, but use the Form textbox itself as the
parameter. Instead of [Enter CaseID:] on the parameter line, put

=[Forms]![TheNameOfYourForm]![Text12712]

I would *really* suggest that you consider using meaningful names for
your controls. You surely don't (Access wouldn't be able to do it!)
have 12712 textboxes; consider naming the control bound to CaseID

txtCaseID

so you can tell what's connected to what more easily.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I probably do have over 1,000 controls. Everytime I try to design something
seems I need to delete several before I hit on the right one. As in this
case.
I will try to go thru the codes to get rid of those that are not in use, if
I can figure it out.
Also, I will do as you suggested. I thought you had to use the name that was
auto assigned to the command or text box.
Thanks for your help.
Joan

John Vinson said:
Later: I deleted and recreated the Command Button.
Still could not print a single envelope.
I ended up creating a parameter based on the CaseID so when the Command
Button is hit the user will only have to type in the CaseID number that I
have visible on the form.
Wish I understood VBC better. If you spot anything wrong in the code I sent
earlier please let me know because I would love it if the users could just
hit the Command Button without any parameters.
Thanks. Joan
Click to expand...

Sorry! I don't see what's wrong with the code... but I can suggest an
alternative which will have the same effect.

Do use a parameter query, but use the Form textbox itself as the
parameter. Instead of [Enter CaseID:] on the parameter line, put

=[Forms]![TheNameOfYourForm]![Text12712]

I would *really* suggest that you consider using meaningful names for
your controls. You surely don't (Access wouldn't be able to do it!)
have 12712 textboxes; consider naming the control bound to CaseID

txtCaseID

so you can tell what's connected to what more easily.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I probably do have over 1,000 controls. Everytime I try to design something
seems I need to delete several before I hit on the right one. As in this
case.
Click to expand...

Warning: Access has a limit of some 700-odd controls on a Form. This
is a LIFETIME LIMIT - deleting a control doesn't recover that "slot"!

You may need to create a new Form, and copy and paste all the controls
from your current incarnation of the form onto it. Otherwise you'll
run into this error.
I will try to go thru the codes to get rid of those that are not in use, if
I can figure it out.
Click to expand...

My sympathies... I'm wrestling with some complex forms too, and trying
to track down unused VBA code. Access doesn't make it easy!
Also, I will do as you suggested. I thought you had to use the name that was
auto assigned to the command or text box.
Click to expand...

Not only do you not have to do so, you're much better off NOT doing
so. Why Access uses the fieldname as the default is beyond me, since
the program often gets confused about whether you mean the *field*
ClientID or the *textbox* also named ClientID. And of course Text1432
is impossible to remember!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Back
Top