Envelope: Print One Works:Data Entry Problem

[Guest]

See Printing One Envelope thread.
I am able to print one envelope using an unique field placed on the form and
the envelope report.
Problem: When I enter new records I am unable to enter the prefix in the
field on the form itself because of the parameter needed to print the
envelope of the record I am viewing.
I end up having to run a query cutting the parameter, entering the correct
prefixes and the pasting the parameter back.
Cumbersome. Is there a solution?
Help. Thanks. Joan

 

[John Vinson]

See Printing One Envelope thread.
I am able to print one envelope using an unique field placed on the form and
the envelope report.
Problem: When I enter new records I am unable to enter the prefix in the
field on the form itself because of the parameter needed to print the
envelope of the record I am viewing.
I end up having to run a query cutting the parameter, entering the correct
prefixes and the pasting the parameter back.
Cumbersome. Is there a solution?
Help. Thanks. Joan

What is the "prefix" and what is the "parameter"? It sounds like you
need two separate controls on the form; they shouldn't be interfering
at all.

The single-address report should be based on a Query referencing some
form control which uniquely identifies the record. The query criterion
would resemble

=[Forms]![yourformname]![uniquefieldname]

and you'ld have a button on the form to launch the report. I don't see
how the prefix gets into the mix at all!

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

 

[Guest]

I have two command buttons: The first contains the prefixes Mr. and Mrs.; Dr.
and Mrs.: The second command buttoncontains the prefixes: Ms.; Mr.;Dr.
Envelopes are addressed to Mr. and Mrs. John Brown or Mr. John Brown or Dr.
and Mrs. Brown or Ms. Jane Brown or Dr. Jane Brown.
The fields for these prefixes are Combo boxes with value fields.
The query criterion is: =Forms]![clients(myformname)]![ClientID].
The ClientID is the unique field that connects the form to the report
envelope.
Appears that I am doing something wrong.
Hope you can enlighten me. Await your reply. Thank you in advance.
Joan

See Printing One Envelope thread.
I am able to print one envelope using an unique field placed on the form and
the envelope report.
Problem: When I enter new records I am unable to enter the prefix in the
field on the form itself because of the parameter needed to print the
envelope of the record I am viewing.
I end up having to run a query cutting the parameter, entering the correct
prefixes and the pasting the parameter back.
Cumbersome. Is there a solution?
Help. Thanks. Joan

What is the "prefix" and what is the "parameter"? It sounds like you
need two separate controls on the form; they shouldn't be interfering
at all.

The single-address report should be based on a Query referencing some
form control which uniquely identifies the record. The query criterion
would resemble

=[Forms]![yourformname]![uniquefieldname]

and you'ld have a button on the form to launch the report. I don't see
how the prefix gets into the mix at all!

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

 

[John Vinson]

I have two command buttons: The first contains the prefixes Mr. and Mrs.; Dr.
and Mrs.: The second command buttoncontains the prefixes: Ms.; Mr.;Dr.
Envelopes are addressed to Mr. and Mrs. John Brown or Mr. John Brown or Dr.
and Mrs. Brown or Ms. Jane Brown or Dr. Jane Brown.
The fields for these prefixes are Combo boxes with value fields.
The query criterion is: =Forms]![clients(myformname)]![ClientID].
The ClientID is the unique field that connects the form to the report
envelope.
Appears that I am doing something wrong.
Hope you can enlighten me. Await your reply. Thank you in advance.
Joan

It sounds like you're assuming that the Form contains the data that
gets printed on the Report. It usually doesn't (well, it *can*, but
that would be out of the ordinary). The Table should contain a Prefix
field, which gets printed on the report; that field could well be
updated using code based on your command buttons, or your combo boxes.

You say "the fields... are combo boxes". Do you have Combos in your
*TABLE* design (Microsoft's abominable Lookup misfeature)? Or just on
the form? What is the Recordsource for the report, and the Control
Source of the textbox on the report which contains the prefix?


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

 

[Guest]

The fields that contain data on my form are from various tables. The form
itself is designed from a query.
The Record Source for the envelope report is designed using an envelope
query that contains all the fields used in the envelope report.
This includes the parameter in the unique CaseID field.
The combo boxes are in my main Cases table with the lists.
In form design mode I inserted the prefix fields onto my form.
Again, when I click on the fields I see the drop down entries but can't make
a selection.
I need to run the envelope query as described above.
The Control Source for the text box on the report is the Envelope query.
The command1 and command2 are as follows:
Command Button 1:
stDocName = "Envelope Father"
DoCmd.OpenReport stDocName, acPreview
Command Button 2:
stDocName = "Envelope Ms or Mr"
DoCmd.OpenReport stDocName, acPreview
The 1st line of the text box on the envelope of Command Button1 looks like:
=Trim([PrefixFather] & " " & "and" & " " & [PrefixMother] & " " &
[FirstName Father] & " " & [LastName Father]) All these fields are in my
Cases Table.
Thanks for your patience.
Joan

I have two command buttons: The first contains the prefixes Mr. and Mrs.; Dr.
and Mrs.: The second command buttoncontains the prefixes: Ms.; Mr.;Dr.
Envelopes are addressed to Mr. and Mrs. John Brown or Mr. John Brown or Dr.
and Mrs. Brown or Ms. Jane Brown or Dr. Jane Brown.
The fields for these prefixes are Combo boxes with value fields.
The query criterion is: =Forms]![clients(myformname)]![ClientID].
The ClientID is the unique field that connects the form to the report
envelope.
Appears that I am doing something wrong.
Hope you can enlighten me. Await your reply. Thank you in advance.
Joan

It sounds like you're assuming that the Form contains the data that
gets printed on the Report. It usually doesn't (well, it *can*, but
that would be out of the ordinary). The Table should contain a Prefix
field, which gets printed on the report; that field could well be
updated using code based on your command buttons, or your combo boxes.

You say "the fields... are combo boxes". Do you have Combos in your
*TABLE* design (Microsoft's abominable Lookup misfeature)? Or just on
the form? What is the Recordsource for the report, and the Control
Source of the textbox on the report which contains the prefix?


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

 

[Guest]

Please reply. Someone. Thanks.
Joan

The fields that contain data on my form are from various tables. The form
itself is designed from a query.
The Record Source for the envelope report is designed using an envelope
query that contains all the fields used in the envelope report.
This includes the parameter in the unique CaseID field.
The combo boxes are in my main Cases table with the lists.
In form design mode I inserted the prefix fields onto my form.
Again, when I click on the fields I see the drop down entries but can't make
a selection.
I need to run the envelope query as described above.
The Control Source for the text box on the report is the Envelope query.
The command1 and command2 are as follows:
Command Button 1:
stDocName = "Envelope Father"
DoCmd.OpenReport stDocName, acPreview
Command Button 2:
stDocName = "Envelope Ms or Mr"
DoCmd.OpenReport stDocName, acPreview
The 1st line of the text box on the envelope of Command Button1 looks like:
=Trim([PrefixFather] & " " & "and" & " " & [PrefixMother] & " " &
[FirstName Father] & " " & [LastName Father]) All these fields are in my
Cases Table.
Thanks for your patience.
Joan

I have two command buttons: The first contains the prefixes Mr. and Mrs.; Dr.
and Mrs.: The second command buttoncontains the prefixes: Ms.; Mr.;Dr.
Envelopes are addressed to Mr. and Mrs. John Brown or Mr. John Brown or Dr.
and Mrs. Brown or Ms. Jane Brown or Dr. Jane Brown.
The fields for these prefixes are Combo boxes with value fields.
The query criterion is: =Forms]![clients(myformname)]![ClientID].
The ClientID is the unique field that connects the form to the report
envelope.
Appears that I am doing something wrong.
Hope you can enlighten me. Await your reply. Thank you in advance.
Joan

It sounds like you're assuming that the Form contains the data that
gets printed on the Report. It usually doesn't (well, it *can*, but
that would be out of the ordinary). The Table should contain a Prefix
field, which gets printed on the report; that field could well be
updated using code based on your command buttons, or your combo boxes.

You say "the fields... are combo boxes". Do you have Combos in your
*TABLE* design (Microsoft's abominable Lookup misfeature)? Or just on
the form? What is the Recordsource for the report, and the Control
Source of the textbox on the report which contains the prefix?


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

 

[John Vinson]

In form design mode I inserted the prefix fields onto my form.
Again, when I click on the fields I see the drop down entries but can't make
a selection.

That seems to be the problem. I don't think it has anything to do with
your Report; it sounds like the Prefix combo boxes are not working
correctly.

Please open the Form in design view and post the Recordsource
property; open it by clicking the ... icon by the property in the
form's Properties window, going to SQL view, and post the SQL here. Do
the same for the RowSource property of the prefix combo box.

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

 

[Guest]

The following as you requested:
RecordSource Clients Form:
SELECT Clients.ClientID, Clients.[ClientCaseID#], Clients.Code_No,
Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, Clients.CodeDefFirm,
Clients.Defcslid, Clients.PrefixFather, Clients.[FirstName Father],
Clients.[LastName Father], Clients.PrefixMother, Clients.[FirstName Mother],
Clients.[LastName Mother], Clients.[Child First Name], Clients.[Child Last
Name], Clients.Address, Clients.Address1, Clients.City, Clients.State,
Clients.PostalCode, Clients.Email, Clients.[Home Phone], Clients.WorkPhone,
Clients.WorkExtension, Clients.WPhone, Clients.WPhone1, Clients.WorkPhone2,
Clients.WorkPhone3, Clients.MobilePhone, Clients.FaxNumber,
Clients.EmailName, Clients.ReferredBy, Clients.[Type of Case], Clients.[Date
Opened], Clients.[Date Closed], Clients.[Case Handler], Clients.[Case
Handler2], Clients.[Case Opened or Closed], Clients.[File Out],
Clients.Notes, Clients.Notes1, Clients.RequestedRecords1,
Clients.RequestedRecords, Clients.RecRecords, Clients.[Recd Records1],
Clients.[File Reviewed], Clients.RetainerLetter, Clients.[Amount Retainer],
Clients.[Amount Recevied], Clients.[Amount Used], Clients.RequestHearing,
Clients.HearingDate, Clients.RequestMed, Clients.MedDate, Clients.[PPT Date],
Clients.SettleAgrmt, Clients.SettleAgrmt1, Clients.SettleReached,
Clients.Settlefunds, Clients.Settlefunds1, Clients.RetainerRcdDate,
Clients.Twofiles, Clients.PrintFlag, Clients.Pulled, Clients.AttendMed,
Clients.Advisory, Clients.TimeMed, Clients.AttendDPH, Clients.TimeDPH,
Clients.AttendPPT, Clients.TimePPT, Clients.Probono, Clients.[Closed Letter],
Clients.[Hours Billed], Clients.[Amount Received], [School District].*,
[District Contacts].*, Schools.*, [Defense Firm].*, [Defense Attorney].*,
Status.*, Clients.RecRecords
FROM Status RIGHT JOIN ([Defense Firm] RIGHT JOIN ([Defense Attorney] RIGHT
JOIN (((Clients LEFT JOIN [District Contacts] ON Clients.SchDisCon =
[District Contacts].SchDisCon) LEFT JOIN [School District] ON Clients.Schcode
= [School District].Schcode) LEFT JOIN Schools ON Clients.SchoolCode =
Schools.SchoolCode) ON [Defense Attorney].Defcslid = Clients.Defcslid) ON
[Defense Firm].CodeDefFirm = Clients.CodeDefFirm) ON Status.Code_No =
Clients.Code_No;
RowSource of Combo Box with Value List:
“Mr.â€; “Dr.â€; “The Honorableâ€;
Shall await a reply. Thanks for all your assistance.
Joan

 

[Guest]

It Works. When you asked for the SQL the light dawned.
I was using the fields on the form (just as you thought) from the clients
table not the clients query.
I thank you for all your patience.
Now I need to create a string on the report envelope that contains all the
prefixes.
Mr. Mrs. Ms. Dr. The Honorable along with Mr. and Mrs.; Dr. and Mrs.
Maybe I just need 2 envelope reports unless I can create a field for the
word *and* that I can shrink.
I will enter this in the proper thread.
Again, thanks. Joan

The following as you requested:
RecordSource Clients Form:
SELECT Clients.ClientID, Clients.[ClientCaseID#], Clients.Code_No,
Clients.Schcode, Clients.SchDisCon, Clients.SchoolCode, Clients.CodeDefFirm,
Clients.Defcslid, Clients.PrefixFather, Clients.[FirstName Father],
Clients.[LastName Father], Clients.PrefixMother, Clients.[FirstName Mother],
Clients.[LastName Mother], Clients.[Child First Name], Clients.[Child Last
Name], Clients.Address, Clients.Address1, Clients.City, Clients.State,
Clients.PostalCode, Clients.Email, Clients.[Home Phone], Clients.WorkPhone,
Clients.WorkExtension, Clients.WPhone, Clients.WPhone1, Clients.WorkPhone2,
Clients.WorkPhone3, Clients.MobilePhone, Clients.FaxNumber,
Clients.EmailName, Clients.ReferredBy, Clients.[Type of Case], Clients.[Date
Opened], Clients.[Date Closed], Clients.[Case Handler], Clients.[Case
Handler2], Clients.[Case Opened or Closed], Clients.[File Out],
Clients.Notes, Clients.Notes1, Clients.RequestedRecords1,
Clients.RequestedRecords, Clients.RecRecords, Clients.[Recd Records1],
Clients.[File Reviewed], Clients.RetainerLetter, Clients.[Amount Retainer],
Clients.[Amount Recevied], Clients.[Amount Used], Clients.RequestHearing,
Clients.HearingDate, Clients.RequestMed, Clients.MedDate, Clients.[PPT Date],
Clients.SettleAgrmt, Clients.SettleAgrmt1, Clients.SettleReached,
Clients.Settlefunds, Clients.Settlefunds1, Clients.RetainerRcdDate,
Clients.Twofiles, Clients.PrintFlag, Clients.Pulled, Clients.AttendMed,
Clients.Advisory, Clients.TimeMed, Clients.AttendDPH, Clients.TimeDPH,
Clients.AttendPPT, Clients.TimePPT, Clients.Probono, Clients.[Closed Letter],
Clients.[Hours Billed], Clients.[Amount Received], [School District].*,
[District Contacts].*, Schools.*, [Defense Firm].*, [Defense Attorney].*,
Status.*, Clients.RecRecords
FROM Status RIGHT JOIN ([Defense Firm] RIGHT JOIN ([Defense Attorney] RIGHT
JOIN (((Clients LEFT JOIN [District Contacts] ON Clients.SchDisCon =
[District Contacts].SchDisCon) LEFT JOIN [School District] ON Clients.Schcode
= [School District].Schcode) LEFT JOIN Schools ON Clients.SchoolCode =
Schools.SchoolCode) ON [Defense Attorney].Defcslid = Clients.Defcslid) ON
[Defense Firm].CodeDefFirm = Clients.CodeDefFirm) ON Status.Code_No =
Clients.Code_No;
RowSource of Combo Box with Value List:
“Mr.â€; “Dr.â€; “The Honorableâ€;
Shall await a reply. Thanks for all your assistance.
Joan

That seems to be the problem. I don't think it has anything to do with
your Report; it sounds like the Prefix combo boxes are not working
correctly.

Please open the Form in design view and post the Recordsource
property; open it by clicking the ... icon by the property in the
form's Properties window, going to SQL view, and post the SQL here. Do
the same for the RowSource property of the prefix combo box.

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