Full Version: Print a single record report
UtterAccess Forums > MicrosoftŪ Access > Access Forms
chadallen_
Hello,
I am wanting to print a single record report using a command button on a form. I have found this code that I believe is for that but I don't quite understand it and don't know where to put it. I also don't know what to change in the code to make it my own, does that make sense?
THere is the code:
******************** Code Start ************************
Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
strWhere = "[RunID]=" & me!RunID
DoCmd.OpenReport strDocName, acPreview, , strWhere
'******************** Code End ************************
Thank you for your help.
-Chad
ScottGem
strWhere is the key here. The code is setting a filter to restrict what records are used by the report. RunID is your primary key field. Its setting str where to a expression that says primary key = the current primary key vaule from the form. thereby restricting the report. The code goes in the button that kicks off the report.
chadallen_
Thanks for your quick response.
My primary key is OrderID so would I write it as: strWhere = "[OrderID]="&me!OrderID ? I am not sure what the &me! means. Also, are the quotes in the right place?
I am still not sure where the code goes. If I double-click on the button in the design view where do I enter the code. do I go to where it says On Click and enter it there somewhere or what? I am still new at this so you may have to dumb it down for me.
Thanks.
fkegley
Yes, it will go in the On Click. Double-click on the button, click the Event page, the On Click row, the ... at the right end of the row, the Code Builder menu choice.

The code window should then open up and you should be in the right place to type in the code that you found and that Scott has modified for you:

Dim strDocName As String
Dim strWhere As String
strDocName = "rptSomeReport"
strWhere = "[OrderID]=" & me!OrderID
DoCmd.OpenReport strDocName, acPreview, , strWhere

HAs Scott has already pointed out, strWhere is the name given to the Filter string to use for the report. The "[OrderID] =" portion tells Access which field is to be used in the filter, the & is a concatenation that joins the two phrases on either side into one phrase, the me!OrderID tells Access which value to get from the form that is executing this code. In this case it is getting the value of the OrderID control on the form. The code assumes that OrderID is a numeric value.

Edited by: fkegley on Mon Jan 16 14:14:54 EST 2006.
Edited by: fkegley on Mon Jan 16 14:17:33 EST 2006.
kapeller
Hi!!!!!!
Take a look at this method. It does exactly what you want.
Cheers!!!!!!!!!!!!!!!
Lou
chadallen_
After I did that I get a run-time error '3075' that says: extra ) in query expression '([OrderID]=)'
dont have a ) in that anywhere. Do you know what that means?
Thanks
Chad
fkegley
Well, it's coming from somewhere, can you post the relevant code?
Edited by: fkegley on Mon Jan 16 16:13:51 EST 2006.
ScottGem
Post your exact code. Also you need to be using the exact names for the fields ant the control on your form.
The Me qualifier just indicates the current form. Also, the code assumes that OrderID is a numeric datatype.
chadallen_
Also, when I click on the debug button it highlights this line:
DoCmd.OpenReport strDocName, acPreview, , strWhere
chadallen_
Here is everything I think that you need.
Private Sub Print_Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "rptBeef"
strWhere = "[OrderID]=" & Me!OrderID
DoCmd.OpenReport strDocName, acPreview, , strWhere
On Error GoTo Err_Print_Click
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.PrintOut acSelection
Exit_Print_Click:
Exit Sub
Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub
Thanks again.
chadallen_
I have another question while I am at it. Do you know any good books that I could read to teach myself about access, visual basic, etc.?
fkegley
I think this line
oCmd.OpenReport strDocName, acPreview, , strWhere
needs to be this
DoCmd.OpenReport strDocName, acPreview, , , strWhere
chadallen_
Now it gives me a different error that says Compile error: Wrong number of arguments or invalid property assignment. Then when I click ok it highlights in yellow Private Sub Print_Click() and it highlights in gray .OpenReport.
Thanks.
fkegley
I told you the wrong thing, change the line back to this:
oCmd.OpenReport strDocName, acPreview, , strWhere
Has it was before I helped you. There's something else wrong.
chadallen_
ok, does it look like I put the code in the correct place?
fkegley
Yes, I don't understand why it's not working. Have you checked the spelling of the report name?
chadallen_
Yeah, it is spelled correctly. Is there anything else I need to do other than just adding that code?
chadallen_
Also, OrderID is an automated Number if that matters any.
chadallen_
I tried something else that may be helpful. on the line: DoCmd.OpenReport strDocName, acPreview, , strWhere I took out the second comma making it look like this: DoCmd.OpenReport strDocName, acPreview, strWhere . It brought up the report, which it hasn't done yet but gave me an error that says: The comand or action 'SelectRecord' isn't available now. *You may be in a read-only database or an unconverted database from an earlier version of Microsoft Access. *The type of object the action applies to isn't currently selected or isn't in the active view. Use only those commands and macro actions that are currently available for this database.
don't know what any of that means though.
Also, when I checked to see if it would print it was going to print all of the records instead of just the one.
Chad
fkegley
Yes, I think you took out one too many commas that time, what has happened is that the parameters to the Open Report command are not in the right place. They are what is known as positional parameters, which means that the correct number of commas must be present for them to be interpreted correctly.
fkegley
I just developed a report that printed the record on the screen. Here are the code lines that matter:
Dim stDocName As String
stDocName = "Employees"
DoCmd.OpenReport stDocName, acPreview, , "[EmployeeID] = " & Me.EmployeeID
chadallen_
ok, now it seems to be working right except I still get that error but if I click ok it will print just the one record. Now you said before that the only code lines that matter are those three. Do I need to erase the others?
fkegley
You can keep them. They are harmless.
chadallen_
Do you know about the error? Also, my records aren't saving right, do I need to start a new discussion to work on that problem?
fkegley
I would need to see the code again. By this point, I have forgotten what you have and haven't done and what I have and haven't told you. If you prepare a sample database, no confidential data, but enclose the malfing form with some sample data, zip and post it, I'll take a look at it.
It will be better if you start a new discussion regarding the save problem that you are having. The more eyes that look at a problem the better for you.
chadallen_
Ok, I will do that. I wont be able to until later tonight or tomorrow. So, I will do that then. Thanks again for your help.
had
sarahegan
Hi Chad - dumb question but, do you have OrderID as a field in the source for your report? Ie, whatever the report is based on (a query or table) would need that field in order for the code to work.
ther than that it looks okay to me... (except maybe you should put your on error at the top of your code if you want to catch any/all errors that occur) ... ?
~Sarah
chadallen_
Attached is what I think you (fkegley) are needing.
lso, Sarah, I'm not sure that I know what you are are saying. ARe you saying that I need OrderID actually on the report? If yes, I did try that but it is still doing the same.
thanks again.
Chad
fkegley
I found the problem. I have "rem" out the offending code lines. It is attached. You should import the form from the one I am sending you to the "real" one, as the real one has security on it that I bypassed by importing the objects to the database that I am sending you.
chadallen_
Thank you very much!
fkegley
You're welcome. I am glad I was able to help.
chadallen_
I tried to import it but I am getting an error. Can you think of something that I could be doing wrong. I first tried to just open the file, and then I tried open the "real" database and clicked on file and import from there and I got the same error. I am at my in-laws right now and not at my computer or I would tell you the error I am getting.
orry.
Chad
fkegley
I know what the trouble is, you are using Access 97, which I didn't notice. I sent you a database created using Access 2003. Access 97 cannot read Access 2003's files. I have converted to Access 97 and attached to this post.
chadallen_
Awesome, thank you.
had
fkegley
Chad, you're welcome. I am glad I was able to help. Sorry I didn't notice the version conflict.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.