Printing specific mailing label from form?

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

Guest

I have a form with [Salons] table, and a subform with [Salon_addresses]
table. I would like to add a command button: "Print Mailing Label" which
would print a label with the data from the current Salon in the form view. I
have ship to, main, home, mailing addresses for some clients. A query was
set up with PK [Advantage_Number] from Salons Table, and Address fields from
Addresses Table. Yes, the address table has a foreign key Advantage_Number.
Then I ran a label wizard in Reports and used the fields from the query.

This is what I have patched up so far from other's advice on this site:

Private Sub PrintSalonLabel_Click()
On Error GoTo Err_PrintSalonLabel_Click

Dim stDocName As String

stDocName = "Label_Salon"
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number]=" &
Me![Advantage_Number]

Exit_PrintSalonLabel_Click:
Exit Sub

Err_PrintSalonLabel_Click:
MsgBox Err.Description
Resume Exit_PrintSalonLabel_Click

When I click on the command button, it says the data may not fit the page,
when I waive the warning, another error comes up saying the Data types do not
match. The only data issues I can think of is PK's, and the rest is text.

Thank You for your help!
 
Ivan,
The "May not fit on page" means your reoport controls exceed the width of
the label (including margins), so you'll have size down those fields.
Make sure [Advantage_Number] is the same Type and Size in both tables.
I'd try the following syntax...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmYourForm![Advantage_Number]"

hth
Al Camp
 
WE ARE GETTING THERE. Thank you so far, the label prints, but it is not a
single label. If you recall in my initial description, my Salons form has an
addresses sub form. What the Print Label Command does is it prints Main and
Ship to address. Obviously, the trick to solve is to get the code to tell
the command which address (ship to) to print. Worse case scenario, I will
keep it as it is, and print labels with all addresses per salon.

Thanks again,
Ivan

AlCamp said:
Ivan,
The "May not fit on page" means your reoport controls exceed the width of
the label (including margins), so you'll have size down those fields.
Make sure [Advantage_Number] is the same Type and Size in both tables.
I'd try the following syntax...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmYourForm![Advantage_Number]"

hth
Al Camp

Ivan said:
I have a form with [Salons] table, and a subform with [Salon_addresses]
table. I would like to add a command button: "Print Mailing Label" which
would print a label with the data from the current Salon in the form view.
I
have ship to, main, home, mailing addresses for some clients. A query was
set up with PK [Advantage_Number] from Salons Table, and Address fields
from
Addresses Table. Yes, the address table has a foreign key
Advantage_Number.
Then I ran a label wizard in Reports and used the fields from the query.

This is what I have patched up so far from other's advice on this site:

Private Sub PrintSalonLabel_Click()
On Error GoTo Err_PrintSalonLabel_Click

Dim stDocName As String

stDocName = "Label_Salon"
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number]="
&
Me![Advantage_Number]

Exit_PrintSalonLabel_Click:
Exit Sub

Err_PrintSalonLabel_Click:
MsgBox Err.Description
Resume Exit_PrintSalonLabel_Click

When I click on the command button, it says the data may not fit the page,
when I waive the warning, another error comes up saying the Data types do
not
match. The only data issues I can think of is PK's, and the rest is text.

Thank You for your help!
Click to expand...
Click to expand...
 
Ivan,
Where is the [Advantage_Number] on the Salon Form? On the Main form?
On the Subform?
Do you have multiple addresses in the subform, (continuous forms) each
with an individual Adv_No?

If so, you could place a button on your subform record/s with the
following OnClick code...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmSalon!frmSalonAddresses.Form![Advantage_Number]"

If that doesn't do it, please send more details as to your setup.

hth
Al Camp

Ivan said:
WE ARE GETTING THERE. Thank you so far, the label prints, but it is not a
single label. If you recall in my initial description, my Salons form has
an
addresses sub form. What the Print Label Command does is it prints Main
and
Ship to address. Obviously, the trick to solve is to get the code to tell
the command which address (ship to) to print. Worse case scenario, I will
keep it as it is, and print labels with all addresses per salon.

Thanks again,
Ivan

AlCamp said:
Ivan,
The "May not fit on page" means your reoport controls exceed the width
of
the label (including margins), so you'll have size down those fields.
Make sure [Advantage_Number] is the same Type and Size in both tables.
I'd try the following syntax...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmYourForm![Advantage_Number]"

hth
Al Camp

Ivan said:
I have a form with [Salons] table, and a subform with [Salon_addresses]
table. I would like to add a command button: "Print Mailing Label"
which
would print a label with the data from the current Salon in the form
view.
I
have ship to, main, home, mailing addresses for some clients. A query
was
set up with PK [Advantage_Number] from Salons Table, and Address fields
from
Addresses Table. Yes, the address table has a foreign key
Advantage_Number.
Then I ran a label wizard in Reports and used the fields from the
query.

This is what I have patched up so far from other's advice on this site:

Private Sub PrintSalonLabel_Click()
On Error GoTo Err_PrintSalonLabel_Click

Dim stDocName As String

stDocName = "Label_Salon"
DoCmd.OpenReport "Label_Salon", acViewPreview, ,
"[Advantage_Number]="
&
Me![Advantage_Number]

Exit_PrintSalonLabel_Click:
Exit Sub

Err_PrintSalonLabel_Click:
MsgBox Err.Description
Resume Exit_PrintSalonLabel_Click

When I click on the command button, it says the data may not fit the
page,
when I waive the warning, another error comes up saying the Data types
do
not
match. The only data issues I can think of is PK's, and the rest is
text.

Thank You for your help!
Click to expand...
Click to expand...
Click to expand...
 
OK Al,

You are correct. Since salons and distributors have multiple addresses, I
have a Salons form with Salons_Addresses subform. I am open for new ideas.
I agree with your idea to place the print label button on the subform. I did
the following below:

I placed the command button on the subform. When I test it, it opens a
window asking to "Enter Parameter Value"
Forms!frmSalons!frmSalons_Addresses.Form!Advantage_Number

Thanks again for your help. This community is Priceless!!!

Ivan.


AlCamp said:
Ivan,
Where is the [Advantage_Number] on the Salon Form? On the Main form?
On the Subform?
Do you have multiple addresses in the subform, (continuous forms) each
with an individual Adv_No?

If so, you could place a button on your subform record/s with the
following OnClick code...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmSalon!frmSalonAddresses.Form![Advantage_Number]"

If that doesn't do it, please send more details as to your setup.

hth
Al Camp

Ivan said:
WE ARE GETTING THERE. Thank you so far, the label prints, but it is not a
single label. If you recall in my initial description, my Salons form has
an
addresses sub form. What the Print Label Command does is it prints Main
and
Ship to address. Obviously, the trick to solve is to get the code to tell
the command which address (ship to) to print. Worse case scenario, I will
keep it as it is, and print labels with all addresses per salon.

Thanks again,
Ivan

AlCamp said:
Ivan,
The "May not fit on page" means your reoport controls exceed the width
of
the label (including margins), so you'll have size down those fields.
Make sure [Advantage_Number] is the same Type and Size in both tables.
I'd try the following syntax...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmYourForm![Advantage_Number]"

hth
Al Camp

I have a form with [Salons] table, and a subform with [Salon_addresses]
table. I would like to add a command button: "Print Mailing Label"
which
would print a label with the data from the current Salon in the form
view.
I
have ship to, main, home, mailing addresses for some clients. A query
was
set up with PK [Advantage_Number] from Salons Table, and Address fields
from
Addresses Table. Yes, the address table has a foreign key
Advantage_Number.
Then I ran a label wizard in Reports and used the fields from the
query.

This is what I have patched up so far from other's advice on this site:

Private Sub PrintSalonLabel_Click()
On Error GoTo Err_PrintSalonLabel_Click

Dim stDocName As String

stDocName = "Label_Salon"
DoCmd.OpenReport "Label_Salon", acViewPreview, ,
"[Advantage_Number]="
&
Me![Advantage_Number]

Exit_PrintSalonLabel_Click:
Exit Sub

Err_PrintSalonLabel_Click:
MsgBox Err.Description
Resume Exit_PrintSalonLabel_Click

When I click on the command button, it says the data may not fit the
page,
when I waive the warning, another error comes up saying the Data types
do
not
match. The only data issues I can think of is PK's, and the rest is
text.

Thank You for your help!
Click to expand...
Click to expand...
Click to expand...
 
IT ALSO MAY BE HELPFUL
In the Salon_Addresses subform, I have S_Address_ID as PK, and a combo box
for address type (home, work, main, ship to, mail to). The PK Advantage_ID
from Salons is a Foreign Key in Salons_Addresses subform.

I have put together a query with Advantage_ID from Salons, and Address info
from Addresses table. The label report wizard uses this query to make the
label.

Thanks, Ivan.

Ivan said:
OK Al,

You are correct. Since salons and distributors have multiple addresses, I
have a Salons form with Salons_Addresses subform. I am open for new ideas.
I agree with your idea to place the print label button on the subform. I did
the following below:

I placed the command button on the subform. When I test it, it opens a
window asking to "Enter Parameter Value"
Forms!frmSalons!frmSalons_Addresses.Form!Advantage_Number

Thanks again for your help. This community is Priceless!!!

Ivan.


AlCamp said:
Ivan,
Where is the [Advantage_Number] on the Salon Form? On the Main form?
On the Subform?
Do you have multiple addresses in the subform, (continuous forms) each
with an individual Adv_No?

If so, you could place a button on your subform record/s with the
following OnClick code...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmSalon!frmSalonAddresses.Form![Advantage_Number]"

If that doesn't do it, please send more details as to your setup.

hth
Al Camp

Ivan said:
WE ARE GETTING THERE. Thank you so far, the label prints, but it is not a
single label. If you recall in my initial description, my Salons form has
an
addresses sub form. What the Print Label Command does is it prints Main
and
Ship to address. Obviously, the trick to solve is to get the code to tell
the command which address (ship to) to print. Worse case scenario, I will
keep it as it is, and print labels with all addresses per salon.

Thanks again,
Ivan

:

Ivan,
The "May not fit on page" means your reoport controls exceed the width
of
the label (including margins), so you'll have size down those fields.
Make sure [Advantage_Number] is the same Type and Size in both tables.
I'd try the following syntax...
DoCmd.OpenReport "Label_Salon", acViewPreview, , "[Advantage_Number] =
Forms!frmYourForm![Advantage_Number]"

hth
Al Camp

I have a form with [Salons] table, and a subform with [Salon_addresses]
table. I would like to add a command button: "Print Mailing Label"
which
would print a label with the data from the current Salon in the form
view.
I
have ship to, main, home, mailing addresses for some clients. A query
was
set up with PK [Advantage_Number] from Salons Table, and Address fields
from
Addresses Table. Yes, the address table has a foreign key
Advantage_Number.
Then I ran a label wizard in Reports and used the fields from the
query.

This is what I have patched up so far from other's advice on this site:

Private Sub PrintSalonLabel_Click()
On Error GoTo Err_PrintSalonLabel_Click

Dim stDocName As String

stDocName = "Label_Salon"
DoCmd.OpenReport "Label_Salon", acViewPreview, ,
"[Advantage_Number]="
&
Me![Advantage_Number]

Exit_PrintSalonLabel_Click:
Exit Sub

Err_PrintSalonLabel_Click:
MsgBox Err.Description
Resume Exit_PrintSalonLabel_Click

When I click on the command button, it says the data may not fit the
page,
when I waive the warning, another error comes up saying the Data types
do
not
match. The only data issues I can think of is PK's, and the rest is
text.

Thank You for your help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top