Access Printing Single Record to a Report

gballane
gballane used Ask the Experts™
on
I am working in access 2007.  I have created a form with a button to bring the data from the record to a report that has been created.

I have put the code below.  The problem I am having is that no matter what record I enter into the pop up box, it displays the FIRST record in the database in the report.

Thanks for the help.
Private Sub printlabelbutton_Click()
  DoCmd.OpenReport "ShippinlabelRPT", acViewPreview, , "InternalRepair_ID=" & Me.InternalRepair_ID
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
<no matter what record I enter into the pop up box>
What pop up box?

Place a code break on the line:
  DoCmd.OpenReport "ShippinlabelRPT", acViewPreview, , "InternalRepair_ID=" & Me.InternalRepair_ID
when the code stops on that line, hover your mouse over
Me.InternalRepair_ID
and see if it is picking up the correct value.
Post the value here.
Note : If InternalRepair_ID is text then:
   "InternalRepair_ID=" & Me.InternalRepair_ID
should be
   "InternalRepair_ID='" & Me.InternalRepair_ID &"'"


VBA data type-declaration characters:
Number (base 10): No type-declaration character needed (the number will automatically be converted to the type needed)
example: 1234, 67.77

Required data type-declaration characters:
String: Bracket with quotation marks or apostrophes (REQUIRED)
example: "This is a string" or 'this is a string'

date/time: Bracket with pound signs (REQUIRED)
example: #6/1/1947#, #June 17, 1999#, #3:30Pm#, #1/5/63 2:33#

Hexadecimal number: Precede with &h
example: &H10

0ctal number: Precede with &o
example: &O10

optional, explicitly specified data type-declaration characters:
Integer: Append with percent sign
example: 1234%

Long: Append with ampersand  
example: 67877768&

Currency: Append with at sign
example: 62.45@

Single: Append with exclamation point  
example: 123.777!, 67.00!

Double: Append with number sign
example: 677767.8888#
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!

Author

Commented:
The box that pops up when we click the print button is attached.

The value is always 1 when I put the stop in the code.
Capture56.JPG

Author

Commented:
I entered a value of 3, for example... and InternalRepair_ID is = 1.
If your have a parameter query as the report's record source and the parameter is getting InternalRepair_ID than you don't want
   "InternalRepair_ID=" & Me.InternalRepair_ID
in the openreport command.  It should Be:
  DoCmd.OpenReport "ShippinlabelRPT", acViewPreview

If you are getting InternalRepair_ID from the form's textbox Me.InternalRepair_ID in the open report command, then you don't want a parameter query.

Which do you want to use. a parameter query or pick up Me.InternalRepair_ID from the displayed record in the form?

Author

Commented:
I don't have a preference.... It can be the same record that is displayed in the form...

i've changed the code to the attached, but i'm still only getting the first record to be in the report.

Private Sub printlabelbutton_Click()
 
DoCmd.OpenReport "ShippinlabelRPT", acViewPreview
 
End Sub

Open in new window

Post the report's record source query.

Author

Commented:
Excuse my lack of experience with Access, but I'm getting better.... I'm not sure what you are asking me to post.  Can you provide me with exactly what you need me to post?
Post the highlighted part shown in the attached image. The image shows a table as the record source. I assume yours is a query. If it is a query name, then open the query in SQL view, then copy and past that. It will start with the word "Select" (second image).

or
Try posting your database so I and others can look at it. To remove private information and reduce the size of the database, do this:
1. Make a copy of your db.
2. If using Access 2007, convert the database to an mdb format.
3. Remove all tables, queries, forms, pages and reports that have nothing to do with the problem.
4. Delete all but 6-12 records in the remaining tables - use shift / Mouse select to select huge blocks of records, then delete.
5. Import all linked tables so that everything is in the one database
6. Remove any passwords.
7. Disable all Startup options.
8. Compact and repair
9. Move the database to a different folder to make sure it still runs showing the problem
10. Post explicit steps to recreate the issue.
11. Zip (optional) and attach to a message.
... Again, test the DB before posting to make sure that we can open the DB and easily see the issue.
Screen-Capture.gif
Screen-Capture.gif
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>
Open your report in design view
Press the F4 function key to bring up the property sheet.
Click the "All" tab.
Scroll to the top and copy the value in the "Record Source" property and paste it here.

(It sounds like your Reports recordsource is set up to only return that one record)

But I will let you continue with Nelson.
;-)

<Again, No points wanted>

Author

Commented:
Attached is the database.

Very simple to recreate this.

1. Open form: RepairOrdersForm on record 1, click "print label" button.... the report comes up and has information from record 1.
2. Change to the next record.... click the "print label" button.  the report comes up and it has information from the first record in the table.  It should have the information on the report that's that of the record you are on the form.

Hope that you can help me!  Thanks.

db.zip

Author

Commented:
Nelson is being VERY helpful.. Thanks for the help boaq.
So you understand the following discussion, let me explain that forms and reports have controls (textboxes, comboboxes, etc)  while tables and queries have fields.

Your problem was that the form RepairOrdersForm did not have a control (textbox) named InternalRepair_ID.  The textbox with the control source InternalRepair_ID was named Text45 (see image). When you refer to a control in code, you need to refer to the control's name as in the line:
  DoCmd.OpenReport "ShippinlabelRPT", acViewPreview, , "InternalRepair_ID=" & Me.InternalRepair_ID
In the line above, the part in quotation marks:
   "InternalRepair_ID="
refers to the table in the table, while the part after the ampersand:
   Me.InternalRepair_ID
refers to the control.

I changed the control name from Text45 (what Access automatically named the textbox when you created it) to InternalRepair_ID and it now works as it should.

One note of caution for the future.
If the record source of a control is an equation like:
=InternalRepair_ID + 2
then the name of the control cannot be a field in the equation. In this case the control cannot be named InternalRepair_ID (it could be ctlInternalRepair_ID).


Screen-Capture.gif
MGMOCt23.zip
correction:
refers to the field in the table, while the part after the ampersand:

Author

Commented:
When I click on PRINT LABEL, it brings up all of the labels.  I would like it to only show the one label of the record that was in the form... not all of them.  The purpose is because we are going to print these as mailing labels.  What am I missing?
There were several problems.

I had placed an apostrophe in the line
   DoCmd.OpenReport "ShippinlabelRPT", acViewPreview, , "InternalRepair_ID=" & Me.InternalRepair_ID
so
   "InternalRepair_ID=" & Me.InternalRepair_ID
was commented out and did not run.

The report "ShippinlabelRPT" was corrupted. I copied all the controls to a new report "ShippingLabelRPT".

The report did not have the field InternalRepair_ID listed in its record source. When a filter is applied to a form or report, the field(s) referred in the filter must be in the record source,
"InternalRepair_ID=" & Me.InternalRepair_ID
is a filter applied to the report.

It works now.  
MGMOCt23.zip

Author

Commented:
Great help!
Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson

Author

Commented:
I will be back!  This Access Database is a project we recently started at work.... we've made progress, but will be posting when we hit bumps in the road!

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