6 Tables, 1 Report, W/O 6 Qrys

[Andy]

Hi;

Want to use one report to print envelopes..

The main form contains 6 combo boxes.

Each Combo box displays only the name of a company and the contact we deal
with in the list.
Example: Shirt Mfgrs, Hat Mfgrs, Shoe Mfgrs. Etc.

When you dbl-click the combo box; a form opens where you can add an item to
the list including the name of the company, address, phone and contact.
That form is bound to a distinct table, (tblShirts/frmShirts,
tblHats/FrmHats).

Instead of creating 6 different reports bound to 6 different qrys, would
like to create one report that can be used by all 6 forms.

For instance: On the form that opens with the dbl-click there would be a
cmdbutton; when clicked would populate the controls of the report with the
information currently displayed on the form.

If frmShirts, (bound to tblShirts), is open the name of the company and it's
address would be included in the report.
If frmHats, (bound to tblHats), is open the name of the company and it's
address would be included in the report.

The cmdbutton is easy. Populating the controls on an unbound report w/data
from 6 different tables is what I need to learn.

Any suggestions?

Andy

 

[Rob]

I'm not sure I get what you are asking, but assuming you
would be printing one envelop at a time, one way to do it
would be to add a set of unbound text boxes to the form,
and have each of your combo boxes setup so that when one
of them is selected or updated, the appropriate address
information is copied into the unbound text boxes. This
would require some coding.

Your query for the envelop report would then pull the
values from this set of textboxes, rather than having to
deal with hunting through six tables. (You might
need/want to create a dummy table with a single record,
whose sole purpose would be to ensure you got one record
returned by the query.)

I think a better way to handle it would be to combine all
six tables into one, and use an additional field to
indicate the type of vendor. Then each of your combo
boxes would be set up to show only the vendors of a
particular type (your code to allow entry for a new vendor
could plug in this type automatically). Then you could
perhaps use just a single unbound text field to hold the
record ID of the last record touched, and the query would
take it from there.

FWIW...
Rob

 

[Andy]

Rob;

If the form bound to tblHats displays:
Hats, Inc.
Hats Address
Hats C/S/Zip
The cmdButton would open rptGenericEnvelope with that information listed.

If the form bound to tblShirts displays:
Shirts, Inc.
Shirts Address
Shirts C/S/Zip
The cmdButton would open the SAME rptGenericEnvelope with that information
listed.

Thank You for replying.

Andy

 

[Rob]

I see, the same report kicked off by six different buttons.

The same thing basically still applies, the only
difference is the you won't have to mess with the OnFocus
or AfterUpdate events - you can simply have each command
button pull in the appropriate information, stuff it into
the unbound text boxes and go from there. You can hide
these unbound text boxes behind the command buttons, by
the way.

The purpose of a single set of entry points for the label
information is to minimize the complexity of your query.
This would let the query deal with only one set of values
instead of having to pick among six.

How you go about populating these unbound fields depends
on how you have the combo boxes set up. If you used the
wizard to build the combo boxes, they may be set up as
multi-column lists with the key hidden. Or you might have
set it up as a single column to show just the company
name. Either way, you could extend it to include
additional columns for the other address fields and use a
column width of 0 to mask the values. The new code for
the command button could then read the extra values from
the current data in the combo box and plug it into the
unbound text boxes, and kick off the report.

There are any number of other approaches to this,
including doing everything at the query level. The
approach you take is largely a matter of style - this just
happens to be the approach I would take given the
situation you describe.

Trying to make things easier in one place can greatly
complicate things in another, but I like your approach of
making it as easy as possible on the end-user.

Rob

 

[Andy]

Rob;

Thank You for Your replies.

Realized I'm not asking the question correctly.

The form is displaying:
AAA, Inc.
123 Any Street
Your Town, Your State, Your Zip

How do you get what is displayed in the form; displayed in an Un-Bound
Report's Un-Bound Controls, without using a Qry?

Andy

 

[Rob]

Andy,

Assuming the new unbound text fields are called txtAddr1,
txtAddr2, txtAddr3, etc. the code in each button's On
Click event would look something like this:

stDocName = "rptEnvelop"
Me.txtAddr1 = {pointer to desired Addr1}
Me.txtAddr2 = {pointer to desired Addr2}
Me.txtAddr3 = {pointer to desired Addr3}
{etc.}
DoCmd.OpenReport stDocName, acPreview


Without knowing how you have the form set up, I can't tell
you what the {pointer to desired Addr} expressions need to
be - it all depends upon how you set up the form.

You indicated you are using a combo box to select the
vendor, but you didn't go into how you are displaying the
address information. Are you using a subform for each
table? The DLookup() function in txtboxes? A multi-
column list box? Or are you doing something else?

The particular method isn't really that important. The key
is to figure out how to point to it and plug the
appropriate expression into the code example above.

Rob

 

[Andy]

Rob:

Thank You again for Your replies.

I almost understand.

Take the ComboBox out of the equation.

Simply; if Your looking at a form, (not a sub-form), displaying information
in a control; how do You get that information to an Un-Bound Control in an
Un-Bound Report?

Would that be: Me.txtAddr1 = Forms![FormName]!txtAddr1
(Me.txtAddr1 being the name of the control on the Report)

Andy

 

[Rob]

Andy,

Given where you were starting from, I was anticipating a
report bound to a query. The VBA code would place the
correct address info into the right places on the form so
your query could easily get to them, and then the report
would be based on the query.

In the Query By Example editor, the fields would look like
this:

Address1: [Forms]![formName]![controlName]

and the data source would be that one-record "tickle"
table I mentioned earlier. This table would ensure the
query returns one record, and fields as defined above
would pull the appropriate values for the envelop.

As I said, this is merely one way to do it, and not likely
the one I would have choosen had I been doing it myself
(primarily because all of the vendor info would have been
in one table).

Hopefully this will get you going again.

Rob

-----Original Message-----
Rob:

Thank You again for Your replies.

I almost understand.

Take the ComboBox out of the equation.

Simply; if Your looking at a form, (not a sub-form), displaying information
in a control; how do You get that information to an Un- Bound Control in an
Un-Bound Report?

Would that be: Me.txtAddr1 = Forms![FormName]!txtAddr1
(Me.txtAddr1 being the name of the control on the Report)

Andy

Andy,

Assuming the new unbound text fields are called txtAddr1,
txtAddr2, txtAddr3, etc. the code in each button's On
Click event would look something like this:

stDocName = "rptEnvelop"
Me.txtAddr1 = {pointer to desired Addr1}
Me.txtAddr2 = {pointer to desired Addr2}
Me.txtAddr3 = {pointer to desired Addr3}
{etc.}
DoCmd.OpenReport stDocName, acPreview


Without knowing how you have the form set up, I can't tell
you what the {pointer to desired Addr} expressions need to
be - it all depends upon how you set up the form.

You indicated you are using a combo box to select the
vendor, but you didn't go into how you are displaying the
address information. Are you using a subform for each
table? The DLookup() function in txtboxes? A multi-
column list box? Or are you doing something else?

The particular method isn't really that important. The key
is to figure out how to point to it and plug the
appropriate expression into the code example above.

Rob

use
a approach
of assuming
you to
do when
one hold
the company
and dbl-
click controls
of of
the on
an

.

 

[Andy]

Rob;

Thank You for Your replies.

Spent more than an hour re-wording/re-postitioning this code:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
Me.Reports!EnvTest.Report!txtAddr1 = Me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

Always get an error: Either Report is mis-spelled, (It isn't), or not open.

When the procedure is reversed,
DoCmd.OpenReport stDocName, acPreview
Me.Reports!EnvTest.Report!txtAddr1 = Me.txtAddr1

No errors, but nothing displayed in the Reports control.

Spent another 1 1/2 hours searching Microsoft KB, MSDN, and a few dozen MVP
sites.

Have You done anything similar before?
Populate an unbound control in an unbound Report with the data displayed in
an open Form.

Thank You for educating me.

Andy

Andy,

Given where you were starting from, I was anticipating a
report bound to a query. The VBA code would place the
correct address info into the right places on the form so
your query could easily get to them, and then the report
would be based on the query.

In the Query By Example editor, the fields would look like
this:

Address1: [Forms]![formName]![controlName]

and the data source would be that one-record "tickle"
table I mentioned earlier. This table would ensure the
query returns one record, and fields as defined above
would pull the appropriate values for the envelop.

As I said, this is merely one way to do it, and not likely
the one I would have choosen had I been doing it myself
(primarily because all of the vendor info would have been
in one table).

Hopefully this will get you going again.

Rob

-----Original Message-----
Rob:

Thank You again for Your replies.

I almost understand.

Take the ComboBox out of the equation.

Simply; if Your looking at a form, (not a sub-form), displaying information
in a control; how do You get that information to an Un- Bound Control in an
Un-Bound Report?

Would that be: Me.txtAddr1 = Forms![FormName]!txtAddr1
(Me.txtAddr1 being the name of the control on the Report)

Andy

Andy,

Assuming the new unbound text fields are called txtAddr1,
txtAddr2, txtAddr3, etc. the code in each button's On
Click event would look something like this:

stDocName = "rptEnvelop"
Me.txtAddr1 = {pointer to desired Addr1}
Me.txtAddr2 = {pointer to desired Addr2}
Me.txtAddr3 = {pointer to desired Addr3}
{etc.}
DoCmd.OpenReport stDocName, acPreview


Without knowing how you have the form set up, I can't tell
you what the {pointer to desired Addr} expressions need to
be - it all depends upon how you set up the form.

You indicated you are using a combo box to select the
vendor, but you didn't go into how you are displaying the
address information. Are you using a subform for each
table? The DLookup() function in txtboxes? A multi-
column list box? Or are you doing something else?

The particular method isn't really that important. The key
is to figure out how to point to it and plug the
appropriate expression into the code example above.

Rob


-----Original Message-----
Rob;

Thank You for Your replies.

Realized I'm not asking the question correctly.

The form is displaying:
AAA, Inc.
123 Any Street
Your Town, Your State, Your Zip

How do you get what is displayed in the form; displayed
in an Un-Bound
Report's Un-Bound Controls, without using a Qry?

Andy

message
I see, the same report kicked off by six different
buttons.

The same thing basically still applies, the only
difference is the you won't have to mess with the
OnFocus
or AfterUpdate events - you can simply have each command
button pull in the appropriate information, stuff it
into
the unbound text boxes and go from there. You can hide
these unbound text boxes behind the command buttons, by
the way.

The purpose of a single set of entry points for the
label
information is to minimize the complexity of your query.
This would let the query deal with only one set of
values
instead of having to pick among six.

How you go about populating these unbound fields depends
on how you have the combo boxes set up. If you used the
wizard to build the combo boxes, they may be set up as
multi-column lists with the key hidden. Or you might
have
set it up as a single column to show just the company
name. Either way, you could extend it to include
additional columns for the other address fields and use
a
column width of 0 to mask the values. The new code for
the command button could then read the extra values from
the current data in the combo box and plug it into the
unbound text boxes, and kick off the report.

There are any number of other approaches to this,
including doing everything at the query level. The
approach you take is largely a matter of style - this
just
happens to be the approach I would take given the
situation you describe.

Trying to make things easier in one place can greatly
complicate things in another, but I like your approach
of
making it as easy as possible on the end-user.

Rob


-----Original Message-----
Rob;

If the form bound to tblHats displays:
Hats, Inc.
Hats Address
Hats C/S/Zip
The cmdButton would open rptGenericEnvelope with that
information listed.

If the form bound to tblShirts displays:
Shirts, Inc.
Shirts Address
Shirts C/S/Zip
The cmdButton would open the SAME rptGenericEnvelope
with
that information
listed.

Thank You for replying.

Andy
message
I'm not sure I get what you are asking, but assuming
you
would be printing one envelop at a time, one way to
do
it
would be to add a set of unbound text boxes to the
form,
and have each of your combo boxes setup so that when
one
of them is selected or updated, the appropriate
address
information is copied into the unbound text boxes.
This
would require some coding.

Your query for the envelop report would then pull the
values from this set of textboxes, rather than
having to
deal with hunting through six tables. (You might
need/want to create a dummy table with a single
record,
whose sole purpose would be to ensure you got one
record
returned by the query.)

I think a better way to handle it would be to combine
all
six tables into one, and use an additional field to
indicate the type of vendor. Then each of your combo
boxes would be set up to show only the vendors of a
particular type (your code to allow entry for a new
vendor
could plug in this type automatically). Then you
could
perhaps use just a single unbound text field to hold
the
record ID of the last record touched, and the query
would
take it from there.

FWIW...
Rob

-----Original Message-----
Hi;

Want to use one report to print envelopes..

The main form contains 6 combo boxes.

Each Combo box displays only the name of a company
and
the contact we deal
with in the list.
Example: Shirt Mfgrs, Hat Mfgrs, Shoe Mfgrs. Etc.

When you dbl-click the combo box; a form opens where
you
can add an item to
the list including the name of the company, address,
phone and contact.
That form is bound to a distinct table,
(tblShirts/frmShirts,
tblHats/FrmHats).

Instead of creating 6 different reports bound to 6
different qrys, would
like to create one report that can be used by all 6
forms.

For instance: On the form that opens with the dbl-
click
there would be a
cmdbutton; when clicked would populate the controls
of
the report with the
information currently displayed on the form.

If frmShirts, (bound to tblShirts), is open the
name of
the company and it's
address would be included in the report.
If frmHats, (bound to tblHats), is open the name of
the
company and it's
address would be included in the report.

The cmdbutton is easy. Populating the controls on
an
unbound report w/data
from 6 different tables is what I need to learn.

Any suggestions?

Andy




.



.



.

.

 

[Rob]

Andy,

Sounds like you are almost there, but there is still a
piece or two missing. Unfortunately I still know know
what all you are doing, so it's kind of hard to guess
which piece it is.

For starters, is the report based on a query? If so, does
that query return a record? If there is no data for the
report and you don't have a report header defined, it will
appear blank - no detail section = blank report.

To be as far as you are, it looks like you must have
worked out how to get the command buttons to stuff the
correct address information into the unbound boxes on the
form. So, then the issue is making sure you have a valid
(as in, not empty) record set for the report to play with,
or you are doing everything you need to do with the Report
Header and the the detail section would be irrelevant.

This would have been so much easier if all of the vendors
were in the same table - then you could have simply set a
filter when you opened the report.

Anyway, see some other notes below.

Rob

-----Original Message-----
Rob;

Thank You for Your replies.

Spent more than an hour re-wording/re-postitioning this code:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
Me.Reports!EnvTest.Report!txtAddr1 = Me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

Always get an error: Either Report is mis-spelled, (It

isn't), or not open.

I think this is erroring on the third line, not the
fourth. At this point, the report doesn't exist in memory
yet, so you cannot point to it. Then again perhaps I'm
confused by what you mean by Me.Reports!EnvTest.

When the procedure is reversed,
DoCmd.OpenReport stDocName, acPreview
Me.Reports!EnvTest.Report!txtAddr1 = Me.txtAddr1

No errors, but nothing displayed in the Reports control.

If the report has an empty record set, you won't get any
details and thus the report (depending upon how you set it
up) may appear blank.

Spent another 1 1/2 hours searching Microsoft KB, MSDN, and a few dozen MVP
sites.

Have You done anything similar before?
Populate an unbound control in an unbound Report with the data displayed in
an open Form.

I've used this quite a bit to pull date range information
into a report header, but again the key factor here is
that there is a data set for the report to deal with.
Put unbound text boxes in the report header and use
something like this for the Control Source:

=[Forms]![FormName]![txtAddr1]

I would guess you would be able to get this to work even
if the report had no data, as long as the fields you were
trying to stuff were in the report header and not in the
detail section. I think I've had reports with no data
print with the data range at the top and a message
stating "no data found for the specified period" or words
to that effect.

Thank You for educating me.

Happy to help. Sorry I'm do such a poor job of getting
the information across!

This is going to make quite a bit of sense a couple of
days after you get it working.

Good luck, and I'll check back to see if you post again.

Rob

Andy

Andy,

Given where you were starting from, I was anticipating a
report bound to a query. The VBA code would place the
correct address info into the right places on the form so
your query could easily get to them, and then the report
would be based on the query.

In the Query By Example editor, the fields would look like
this:

Address1: [Forms]![formName]![controlName]

and the data source would be that one-record "tickle"
table I mentioned earlier. This table would ensure the
query returns one record, and fields as defined above
would pull the appropriate values for the envelop.

As I said, this is merely one way to do it, and not likely
the one I would have choosen had I been doing it myself
(primarily because all of the vendor info would have been
in one table).

Hopefully this will get you going again.

Rob

-----Original Message-----
Rob:

Thank You again for Your replies.

I almost understand.

Take the ComboBox out of the equation.

Simply; if Your looking at a form, (not a sub-form), displaying information
in a control; how do You get that information to an Un- Bound Control in an
Un-Bound Report?

Would that be: Me.txtAddr1 = Forms![FormName]!txtAddr1
(Me.txtAddr1 being the name of the control on the Report)

Andy

Andy,

Assuming the new unbound text fields are called txtAddr1,
txtAddr2, txtAddr3, etc. the code in each button's On
Click event would look something like this:

stDocName = "rptEnvelop"
Me.txtAddr1 = {pointer to desired Addr1}
Me.txtAddr2 = {pointer to desired Addr2}
Me.txtAddr3 = {pointer to desired Addr3}
{etc.}
DoCmd.OpenReport stDocName, acPreview


Without knowing how you have the form set up, I can't tell
you what the {pointer to desired Addr} expressions

need
to

be - it all depends upon how you set up the form.

You indicated you are using a combo box to select the
vendor, but you didn't go into how you are displaying the
address information. Are you using a subform for each
table? The DLookup() function in txtboxes? A multi-
column list box? Or are you doing something else?

The particular method isn't really that important.

The
key

is to figure out how to point to it and plug the
appropriate expression into the code example above.

Rob


-----Original Message-----
Rob;

Thank You for Your replies.

Realized I'm not asking the question correctly.

The form is displaying:
AAA, Inc.
123 Any Street
Your Town, Your State, Your Zip

How do you get what is displayed in the form; displayed
in an Un-Bound
Report's Un-Bound Controls, without using a Qry?

Andy

message
I see, the same report kicked off by six different
buttons.

The same thing basically still applies, the only
difference is the you won't have to mess with the
OnFocus
or AfterUpdate events - you can simply have each command
button pull in the appropriate information, stuff it
into
the unbound text boxes and go from there. You can hide
these unbound text boxes behind the command

buttons,
by

the way.

The purpose of a single set of entry points for the
label
information is to minimize the complexity of your query.
This would let the query deal with only one set of
values
instead of having to pick among six.

How you go about populating these unbound fields depends
on how you have the combo boxes set up. If you

used
the

wizard to build the combo boxes, they may be set

up
as

multi-column lists with the key hidden. Or you might
have
set it up as a single column to show just the company
name. Either way, you could extend it to include
additional columns for the other address fields

and
use

a
column width of 0 to mask the values. The new

code
for

the command button could then read the extra

values
from

the current data in the combo box and plug it into the
unbound text boxes, and kick off the report.

There are any number of other approaches to this,
including doing everything at the query level. The
approach you take is largely a matter of style - this
just
happens to be the approach I would take given the
situation you describe.

Trying to make things easier in one place can greatly
complicate things in another, but I like your approach
of
making it as easy as possible on the end-user.

Rob


-----Original Message-----
Rob;

If the form bound to tblHats displays:
Hats, Inc.
Hats Address
Hats C/S/Zip
The cmdButton would open rptGenericEnvelope with that
information listed.

If the form bound to tblShirts displays:
Shirts, Inc.
Shirts Address
Shirts C/S/Zip
The cmdButton would open the SAME rptGenericEnvelope
with
that information
listed.

Thank You for replying.

Andy
message
I'm not sure I get what you are asking, but assuming
you
would be printing one envelop at a time, one

way
to

do
it
would be to add a set of unbound text boxes to the
form,
and have each of your combo boxes setup so that when
one
of them is selected or updated, the appropriate
address
information is copied into the unbound text boxes.
This
would require some coding.

Your query for the envelop report would then

pull
the

values from this set of textboxes, rather than
having to
deal with hunting through six tables. (You might
need/want to create a dummy table with a single
record,
whose sole purpose would be to ensure you got one
record
returned by the query.)

I think a better way to handle it would be to combine
all
six tables into one, and use an additional

field
to

indicate the type of vendor. Then each of your combo
boxes would be set up to show only the vendors

of
a

particular type (your code to allow entry for a new
vendor
could plug in this type automatically). Then you
could
perhaps use just a single unbound text field to hold
the
record ID of the last record touched, and the query
would
take it from there.

FWIW...
Rob

-----Original Message-----
Hi;

Want to use one report to print envelopes..

The main form contains 6 combo boxes.

Each Combo box displays only the name of a company
and
the contact we deal
with in the list.
Example: Shirt Mfgrs, Hat Mfgrs, Shoe Mfgrs. Etc.

When you dbl-click the combo box; a form opens where
you
can add an item to
the list including the name of the company, address,
phone and contact.
That form is bound to a distinct table,
(tblShirts/frmShirts,
tblHats/FrmHats).

Instead of creating 6 different reports bound

to
6

different qrys, would
like to create one report that can be used by all 6
forms.

For instance: On the form that opens with the dbl-
click
there would be a
cmdbutton; when clicked would populate the controls
of
the report with the
information currently displayed on the form.

If frmShirts, (bound to tblShirts), is open the
name of
the company and it's
address would be included in the report.
If frmHats, (bound to tblHats), is open the

name
of

the
company and it's
address would be included in the report.

The cmdbutton is easy. Populating the

controls
on

an
unbound report w/data
from 6 different tables is what I need to learn.

Any suggestions?

Andy




.



.



.



.

.

 

[John Spencer (MVP)]

Perhaps I misunderstand what you want, but can't you just set the control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is open.

Now if the form from which you are extracting the information changes then you
need more complex code in the on open event of the report to set the value of
the control.

If you are using later versions of Access (2002 and later, if I recall
correctly), reports have an OpenArgs parameter which you could use to pass the
value you want to put into the control or a reference to the source form and
control(s) as a string.

Another option is to declare a global variable in a general code module and
populate it in the calling code and then retrieve it in the reports on open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub

 

[Andy]

=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the displayed Form into
the text of a common Report?

Andy

 

[Rob]

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]![CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob

-----Original Message-----

=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the displayed Form into
the text of a common Report?

Andy

Perhaps I misunderstand what you want, but can't you

just set the
control's

source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is open.

Now if the form from which you are extracting the

information changes then
you

need more complex code in the on open event of the

report to set the value
of

the control.

If you are using later versions of Access (2002 and later, if I recall
correctly), reports have an OpenArgs parameter which

you could use to pass
the

value you want to put into the control or a reference

to the source form
and

control(s) as a string.

Another option is to declare a global variable in a

general code module
and

populate it in the calling code and then retrieve it in

the reports on
open

event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub

.

 

[Andy]

Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]![CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob

-----Original Message-----

=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the displayed Form into
the text of a common Report?

Andy

Perhaps I misunderstand what you want, but can't you

just set the
control's

source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is open.

Now if the form from which you are extracting the

information changes then
you

need more complex code in the on open event of the

report to set the value
of

the control.

If you are using later versions of Access (2002 and later, if I recall
correctly), reports have an OpenArgs parameter which

you could use to pass
the

value you want to put into the control or a reference

to the source form
and

control(s) as a string.

Another option is to declare a global variable in a

general code module
and

populate it in the calling code and then retrieve it in

the reports on
open

event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub

.

 

[Rob]

Andy,

Sorry for the delay in getting back to you. We had almost
eight inches of rain in about five hours yesterday
morning, and I'm dealing with a flooded basement. I hope
to get back to this later today.

Rob

-----Original Message-----
Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]! [CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob

-----Original Message-----
=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the displayed Form into
the text of a common Report?

Andy

Perhaps I misunderstand what you want, but can't you just set the
control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName"

is
open.

Now if the form from which you are extracting the information changes then
you
need more complex code in the on open event of the report to set the value
of
the control.

If you are using later versions of Access (2002 and later, if I recall
correctly), reports have an OpenArgs parameter which you could use to pass
the
value you want to put into the control or a reference to the source form
and
control(s) as a string.

Another option is to declare a global variable in a general code module
and
populate it in the calling code and then retrieve it

in
the reports on

open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer

in
a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub


.

.

 

[Andy]

Rob;

We lost our basement rec-room two years ago.

Know what You mean.

Andy

Andy,

Sorry for the delay in getting back to you. We had almost
eight inches of rain in about five hours yesterday
morning, and I'm dealing with a flooded basement. I hope
to get back to this later today.

Rob

-----Original Message-----
Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]! [CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob



-----Original Message-----
=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the
displayed Form into
the text of a common Report?

Andy

message
Perhaps I misunderstand what you want, but can't you
just set the
control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is
open.

Now if the form from which you are extracting the
information changes then
you
need more complex code in the on open event of the
report to set the value
of
the control.

If you are using later versions of Access (2002 and
later, if I recall
correctly), reports have an OpenArgs parameter which
you could use to pass
the
value you want to put into the control or a reference
to the source form
and
control(s) as a string.

Another option is to declare a global variable in a
general code module
and
populate it in the calling code and then retrieve it in
the reports on
open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in
a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub


.

.

 

[Rob]

Andy,

At long last, here we go:

Make a form with three or four unbound text boxes on it.
I named my form frmAddress and called the text boxes
addr1, addr2, addr3, and addr4.

Next, make a new report. Don't tie it to a data source,
and then add three or four unbound text boxes in the
Report Header section. It doesn't matter what you call
them, but set their Control Source property to:
=Forms!frmAddress!addr1
=Forms!frmAddress!addr2
=Forms!frmAddress!addr3
=Forms!frmAddress!addr4

Position them accordingly and if necessary play with the
Can Shrink properties to deal with cases where one of the
address lines is blank.

Next, on your Mfg forms, add a button to print the new
envelope report. You can use the Wizard to create the
shell for the code, then add something like this:

DoCmd.OpenForm "frmAddress", , , , , acHidden
Forms!frmAddress!addr1 = Forms!frmShirtMfg!Fname & " " &
Forms!frmShirtMfg!Lname
Forms!frmAddress!addr2 = Forms!frmShirtMfg!addr1
Forms!frmAddress!addr3 = Forms!frmShirtMfg!addr2
Forms!frmAddress!addr4 = Forms!frmShirtMfg!City & " " &
Forms!frmShirtMfg!St & " " & Forms!frmShirtMfg!ZIP
DoCmd.OpenReport "rptEnvelop", acPreview


Adjust accordingly for your address structure - your code
seemed to indicate you only needed three fields and that
you had City, State, and Zip combined already. I had four
address lines and had to combine fields to get the
expressions I wanted.

At any rate, this will open the new form in hidden mode,
so the user won't see it. Then it reads the values from
the visible form and stuffs them into the hidden one, and
then finally it opens the report which in turn pulls the
values from the hidden form.

There are a couple of minor loose ends here, like leaving
the frmAddress form open and reopening it after it's
already open, but Access seems to handle them without any
problems.

Good luck, and I'm sorry about the delay.

Rob

-----Original Message-----
Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]! [CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob

-----Original Message-----
=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the displayed Form into
the text of a common Report?

Andy

Perhaps I misunderstand what you want, but can't you just set the
control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName"

is
open.

Now if the form from which you are extracting the information changes then
you
need more complex code in the on open event of the report to set the value
of
the control.

If you are using later versions of Access (2002 and later, if I recall
correctly), reports have an OpenArgs parameter which you could use to pass
the
value you want to put into the control or a reference to the source form
and
control(s) as a string.

Another option is to declare a global variable in a general code module
and
populate it in the calling code and then retrieve it

in
the reports on

open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer

in
a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub


.

.

 

[Andy]

Rob;

Have been burning candle on 3 ends.

Is Your house back to normal?

Haven't had the extra engery to delve into the answer You've posted.

Want to Thank You in advance.

Andy

Andy,

At long last, here we go:

Make a form with three or four unbound text boxes on it.
I named my form frmAddress and called the text boxes
addr1, addr2, addr3, and addr4.

Next, make a new report. Don't tie it to a data source,
and then add three or four unbound text boxes in the
Report Header section. It doesn't matter what you call
them, but set their Control Source property to:
=Forms!frmAddress!addr1
=Forms!frmAddress!addr2
=Forms!frmAddress!addr3
=Forms!frmAddress!addr4

Position them accordingly and if necessary play with the
Can Shrink properties to deal with cases where one of the
address lines is blank.

Next, on your Mfg forms, add a button to print the new
envelope report. You can use the Wizard to create the
shell for the code, then add something like this:

DoCmd.OpenForm "frmAddress", , , , , acHidden
Forms!frmAddress!addr1 = Forms!frmShirtMfg!Fname & " " &
Forms!frmShirtMfg!Lname
Forms!frmAddress!addr2 = Forms!frmShirtMfg!addr1
Forms!frmAddress!addr3 = Forms!frmShirtMfg!addr2
Forms!frmAddress!addr4 = Forms!frmShirtMfg!City & " " &
Forms!frmShirtMfg!St & " " & Forms!frmShirtMfg!ZIP
DoCmd.OpenReport "rptEnvelop", acPreview


Adjust accordingly for your address structure - your code
seemed to indicate you only needed three fields and that
you had City, State, and Zip combined already. I had four
address lines and had to combine fields to get the
expressions I wanted.

At any rate, this will open the new form in hidden mode,
so the user won't see it. Then it reads the values from
the visible form and stuffs them into the hidden one, and
then finally it opens the report which in turn pulls the
values from the hidden form.

There are a couple of minor loose ends here, like leaving
the frmAddress form open and reopening it after it's
already open, but Access seems to handle them without any
problems.

Good luck, and I'm sorry about the delay.

Rob

-----Original Message-----
Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]! [CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob



-----Original Message-----
=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the
displayed Form into
the text of a common Report?

Andy

message
Perhaps I misunderstand what you want, but can't you
just set the
control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is
open.

Now if the form from which you are extracting the
information changes then
you
need more complex code in the on open event of the
report to set the value
of
the control.

If you are using later versions of Access (2002 and
later, if I recall
correctly), reports have an OpenArgs parameter which
you could use to pass
the
value you want to put into the control or a reference
to the source form
and
control(s) as a string.

Another option is to declare a global variable in a
general code module
and
populate it in the calling code and then retrieve it in
the reports on
open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in
a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub


.

.

 

[Andy]

Rob;

You did it!!!

Thank You.

Andy

Rob;

Have been burning candle on 3 ends.

Is Your house back to normal?

Haven't had the extra engery to delve into the answer You've posted.

Want to Thank You in advance.

Andy

Andy,

At long last, here we go:

Make a form with three or four unbound text boxes on it.
I named my form frmAddress and called the text boxes
addr1, addr2, addr3, and addr4.

Next, make a new report. Don't tie it to a data source,
and then add three or four unbound text boxes in the
Report Header section. It doesn't matter what you call
them, but set their Control Source property to:
=Forms!frmAddress!addr1
=Forms!frmAddress!addr2
=Forms!frmAddress!addr3
=Forms!frmAddress!addr4

Position them accordingly and if necessary play with the
Can Shrink properties to deal with cases where one of the
address lines is blank.

Next, on your Mfg forms, add a button to print the new
envelope report. You can use the Wizard to create the
shell for the code, then add something like this:

DoCmd.OpenForm "frmAddress", , , , , acHidden
Forms!frmAddress!addr1 = Forms!frmShirtMfg!Fname & " " &
Forms!frmShirtMfg!Lname
Forms!frmAddress!addr2 = Forms!frmShirtMfg!addr1
Forms!frmAddress!addr3 = Forms!frmShirtMfg!addr2
Forms!frmAddress!addr4 = Forms!frmShirtMfg!City & " " &
Forms!frmShirtMfg!St & " " & Forms!frmShirtMfg!ZIP
DoCmd.OpenReport "rptEnvelop", acPreview


Adjust accordingly for your address structure - your code
seemed to indicate you only needed three fields and that
you had City, State, and Zip combined already. I had four
address lines and had to combine fields to get the
expressions I wanted.

At any rate, this will open the new form in hidden mode,
so the user won't see it. Then it reads the values from
the visible form and stuffs them into the hidden one, and
then finally it opens the report which in turn pulls the
values from the hidden form.

There are a couple of minor loose ends here, like leaving
the frmAddress form open and reopening it after it's
already open, but Access seems to handle them without any
problems.

Good luck, and I'm sorry about the delay.

Rob

-----Original Message-----
Rob;

I've learned a lot Thank You.

Your "Fighting the aligators and we're here to drain the swamp."

frmShirtMfgr
lblOnlyShirtMfgrName: Shirt Mfgr Name
lblOnlyShirtMfgrStreet: Shirt Mfgr Street
lblOnlyShirtMfgrCityStZip: Shirt Mfgr CityStZip
Form Contains one CmdButton

cmdButton Button_Click
Function Public
What is the code to place here
That opens an Un-Bound Report
And places the Shirt Mfgr's Data, from the opened form
In the Report's txtControls?
End Function

Rob, Thank You again.

Andy

From the top:

1) because you have six different data sources (one for
each type of vendor) and you wanted to get by with only
one report, the first step was to create an easy mechanism
for the report to get at the desired data, without having
to hunt through six different data sources.

This is what the code under each command button was for -
to pull the values from the desired table and put it in
the spot where the report could get to it without a lot of
complexity. Hence each button would have something like
this, in somewhat simplified form because I don't know
what you are using for displaying the different vendor
types:

Dim stDocName As String
stDocName = "rptEnvelop"
Me.CommonAddr1 = [Forms]![Form1]![txtHatCompanyName]
Me.CommonAddr2 = [Forms]![Form1]![TxtHatAddr1]
Me.CommonAddr3 = [Forms]![Form1]![TxtHatAddr3]
Me.CommonAddr4 = [Forms]![Form1]![TxtHatCity] & state...
DoCmd.OpenReport stDocName, acPreview

Wait a minute, I see a big question mark looming here - do
you have six totally different forms, or one form with the
six different vendor types showing on it at the same time?
My original understanding is you had the latter. At any
rate...

2) Once the values you are looking for are in a
consistent spot, then the report can easily access them
one of two ways:
a) directly from the form - by using text boxes with the
control source settings =[Forms]![Form1]! [CommonAddr1]
If you do this, put these text boxes in the report
header so the report can show something even if
there is no data.
b) through a query - where the columns in the QBE grid
would look like Add1: [Forms]![Form1]![CommonAddr1]
The important factor here is the query must return a
single record in order for the report to have data to
chew on.


Now if you are using six different forms, you may need to
get creative and have the "print envelop" command button
open a special form for you to stuff the desired address
information & have the report or query reference this new
form.

Otherwise, well, at this point I don't think we want to
dive into messing with union queries or programatically
setting the control source for the text boxes on the
report at run time. There are other ways of handling this
issue, it's just a matter of trying to give you the
easiest one to implement.

As I originally understood your issue, you had a single
form with the six different types of vendors showing on it
(I was assuming by use of subforms or combo boxes - again
there are different ways...), and you wanted a button
under each to print the envelop for the current vendor
showing for a particular type. That's why I was
suggesting another set of unbound text fields on that
form, just to give the report mechanism a common spot to
look for the information. At the time that seemed like
the easiest means of getting you from point A to point B.

Do you have one form with all six vendors showing
simultaneously, or do you have six different forms showing
one at a time?

Rob



-----Original Message-----
=[Forms]![YourFormName]![TxtAddr1]

Do You need to enter the code listing all the forms?

EG:
Dim stDocName As String
stDocName = "rptEnvelop"
=[Forms]![Form1]![TxtAddr1]
=[Forms]![Form2]![TxtAddr1]
=[Forms]![Form3]![TxtAddr1]
=[Forms]![Form4]![TxtAddr1]
=[Forms]![Form5]![TxtAddr1]
' Upto last form?
DoCmd.OpenReport stDocName, acPreview

Or is there a different way to just get the text in the
displayed Form into
the text of a common Report?

Andy

message
Perhaps I misunderstand what you want, but can't you
just set the
control's
source to

=[Forms]![YourFormName]![TxtAddr1]

That should work as long as the form "YourFormName" is
open.

Now if the form from which you are extracting the
information changes then
you
need more complex code in the on open event of the
report to set the value
of
the control.

If you are using later versions of Access (2002 and
later, if I recall
correctly), reports have an OpenArgs parameter which
you could use to pass
the
value you want to put into the control or a reference
to the source form
and
control(s) as a string.

Another option is to declare a global variable in a
general code module
and
populate it in the calling code and then retrieve it in
the reports on
open
event. Something Like the following UNTESTED AIRCODE


After declaring a variable, for example gStrCustomer in
a code module

Option Compare Database
Option Explicit

Dim gStrCustomer as String


From the Calling form:
Private Sub Command0_Click()
Dim stDocName As String
stDocName = "EnvTest"
gStrCustomer = me.txtAddr1
DoCmd.OpenReport stDocName, acPreview
End Sub

In the report:
Private Sub Report_Open(Cancel As Integer)
Me.txtAddressControl = gStrCustomer
End Sub


.



.