Printing Labels of Current record on a Form

Chrisjack001
Chrisjack001 used Ask the Experts™
on
I have a form called “Scheduling-Edit” that displays schedules of patients. I want to be able to print patient labels on the current record displayed using the following fields.
Patient Name (Bold face & Left Justified)
DOB (Left Justified)
Date of Appointment (Right Justified)
I want only the current record displayed to be printed on the label and it should be a full page, Avery address label 5160.
How can I accomplish this goal with the (Print Labels) button on the form
Attached is copy of my database with the form. Thanks in advance

Invoice-7-7-2001-91611.accdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Is is a single label printer using or do you want to print to a A4 3x10 labels page?

Author

Commented:
I  want to print to a A4  3x10 labels page

Avery address labels   5160

3 Accross
10 Down
ste5anSenior Developer

Commented:
1. Then you need to create a new report. Use Report Design on the Create tab. Don't use the wizard.
2. Remove the page header and footer.
3. GoTo the Page Setup tab in the Report Design Tools.
4. Run the Page Setup.
5. Setup your normal A4 printing (margins) for your Avery 3x10 sheet under Print Options and Page.
6. Now switch to the Columns page and set the Number of Columns to 3.
7. Set the Row and Column Spacing according to your Avery sheet.
8. Press OK
9. Design your report as normal.
10. Test your report by printing on normal paper.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
1. Access has a Labels wizard to help you create the labels.
(On the "Create" tab, click labels)

2. The code to print the current form record would look something like this:

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenReport "YourReport", acViewPreview, , "YourID=" & YourID

'Use this is if the ID is text
'DoCmd.OpenReport "YourReport", acViewPreview, , "YourID=" & "'" & YourID & "'"

I am sure with your experience here you can get this going...

JeffCoachman


Author

Commented:
Where does this code go. Is it in the click event of the "Print Label" button I have on the form. Should this come after creating a report first and following the steps by ste5an. I have never done this label creation in access so I'm little bit lost. Thanks for your help

Author

Commented:
I dont know if I am doing this the right way but I have created a query called Labels and I have been able to design a report from that query called "rptLabels". This query is pulling all the scheduling data which is not what I want to accomplish. I want to connect my report to my "Print Labels" button to print only the current record on the whole page. 3 x 10 = 30 labels. How can I accomplish this. Attached is a copy of my database with the report and query created. I dont know if thats even the right way to do it. Never done this label thing. Maybe it involves some VB and SQL. Thanks for your help
Invoice-7-7-2001-91611.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
First pleas just do post your database.
Please provide the *explicit* steps to see the issue.

What I posted is a very simple example of how to print a report of only the current form record.

Again, my presumption here (based on your question history here) was that you would first try this on a simple DB, then adapt it to work in your specific situation.

Can you try this first, then report back?

Author

Commented:
I have attached a copy of my database with the "Scheduling Edit" form that has the "Print Labels" button with your code in the click event. I wanted the DoCmd code to link 2 different fields because their is not really a unique ID. I wanted to use both the patient Name and the Date Of Appointment to make sure that no other record by this patient is printed. This 1 record should fill the whole page which means 30 labels
Invoice-7-7-2001-91611.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Can you compile the code in your DB and run the compact /repair utility then re-post the DB?

When I click the button, I get this message:
untitled.JPG

Author

Commented:
I have re-posted the database. Thanks
Demo.accdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
If your goal here is to simply print the current form record, then you can pull the report controls controlsources from the form directly.

See the attached modified sample
(also noting the "save record" command in the button code)

Again, you have a lot of "oddities" in the design here, this workaround only adds to the complexity...
Just an FYI and a disclaimer...
Demo.accdb

Author

Commented:
Hi boag2000, that is exactly what I need but I would want that record to be populated on the whole sheet. That means it should be 3 accross and 10 down making it a total of 30. How can I accomplish that goal. Thanks for your patience and help.

Author

Commented:
Hi boag2000,
                    Upon further review by checking other records that have a patient with multiple appointments example "Josephine Andrews", I noticed the report pulls up 2 records instead of just the one that was on 5-25-11 which should be the only one pulled and populated on the whole sheet but it also pulled the record on 6-1-11. How can I add the date (DateOfAppt) to to code behind the print label button?
MIS Liason
Most Valuable Expert 2012
Commented:
<I wanted the DoCmd code to link 2 different fields because their is not really a unique ID. I wanted to use both the patient Name and the Date Of Appointment to make sure that no other record by this patient is printed. This 1 record should fill the whole page which means 30 labels >

AFAICT, ShcedulingID is the Unique key
The source for the Form is the table called "Scheduling" it has a primary key of Scheduling ID.
The Scheduling ID can be included in the record source for the form.
So it seems to me that there IS a unique ID...

So you add the Scheduling Id to form's recordsource, then add it in as a control onto the form, ...and set the visibility of the control to: No.

You can then make a table with 30 (the number of Labels needed)  records
Then add it to the Label query with NO Join.
This will create a query with 30 duplicate records. (Perfect for your Label purpose here)

So see the attached newer modified sample.

(A final note some labels will have a DOB, yet this DOB does not appear on the form...?
...but this is perhaps best as the subject for a new question)

JeffCoachman


Demo.accdb

Author

Commented:
Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial