Full Version: Print report on active record from form
UtterAccess Forums > Microsoft® Access > Access Forms
likajoho
I want to have a command button on a form that will print a report filtered for the active record only.
know I've done something like this before, but it was a long time ago, and I don't even know where to begin to find this information. I've hunted around in the properties, but the one that sets the report to active record only is alluding me, so maybe I'm thinking of something similar.
Any help or suggestion would be appreciated.
Thanks,
Linda
Jack Cowley
DoCmd.OpenReport "NameOfReportHere", , , "[PrimaryKey] = " & Me.NameOfControlOnFormWithPrimaryKey
hange PrimaryKey, above, to the name of the primary key of the record source for your form and report....
hth,
Jack
likajoho
Jack,
Is this a VBA thing? If not, where do you put it?
Clueless
Jack Cowley
Clueless -
It is a 'VBA' thing so in your command buttons property sheet click on the Events tab and then select the On Click event. In the On Click event select [Event Procedure] from the list by clicking the down arrow at the right side of the field. Next click the button with 3 dots and the code editor will open and the cursor will be flashing between two lines of code. Put your 'code' there.
hth,
Jack
likajoho
Jack,
o I include the quotation marks?
Thanks,
Linda
Jack Cowley
Linda -
es. I assume your primary key is an autonumber so it is a Number data type and the quotation marks in the code are for a number. Of your primary key is TEXT (it shouldn't be) then this is the syntax:
CODE
DoCmd.OpenReport "NameOfReport", , , "[PrimaryKey] = '" & Me.NameOfControlWithPrimaryKey & "'"

hth,
Jack
likajoho
Jack, I typed in:
DoCmd.OpenReport stDocName, , , "[ContactID] = " & Me.ContactID acPreview

Below:
stDocName = "rpt_qryNeedAddressLtr_email"
Odidn't tell you this is a subform. In a work around I figure out before you sent me this answer (macro to open report filtered as a where statement) I found that the main form ContactID had to be used to get it to work.
So, do I need to change that (I got a syntax error)?
Thanks,
Linda
Jack Cowley
Linda -
This should work as long as you have a control in your subform with the ContactID in it. If it is a subform and ContactID is the Foreign Key in the subform then it should work.
CODE
DoCmd.OpenReport "rpt_qryNeedAddressLtr_email" , acViewPreview, , "[ContactID] = " & Me.ContactID

What view is your subform (Datasheet, Continuous form, Single form) and do you have a command button to preview the report?
Jack
likajoho
Jack the subform is in single form view because there are so many fields.
Yes I'm making a command button to preview the report and need the macro or vba for that.
BTW, you'll get a kick out of this. I tried duplicating my work around for a different letter and couldn't get it to work. I did everything from clearing the macro in vba and deleting the command button and recreating it and the macro several times. After checking "everything" I could think of, I had pretty much decided it was a glitch. But then I checked the query. I had forgotten to add the ContactID to the query as I had done with the other letter query.
So much to remember! No wonder I'm such a flop at this. Memory is not my strong point.
Anyway, I'll try your solution and see if it doesn't work much better than going the other route.
Thanks, Jack.
Linda
Jack Cowley
Linda -
ime to forget that you ever heard the word 'macro' and start using code. It is not as hard as you might think and it is so much more powerful that you will wonder why you every bothered with macros in the first place...
Ounderstand about the missing field in a query or form and the agony you go through before you finally stumble on to the problem. I'm glad you found the missing field and are now back in business. The code I suggested should do the trick, but if you have a problem be sure and let us know.
I doubt that you are a flop at what you are doing or that memory is a problem for you... Access is big and powerful with a fairly steep learning curve so keep that in mind when you forget to add a field to a query...
Jack
likajoho
Thanks, Jack.
TW, got it working. It's a lot cleaner/concise than a macro I noticed (when in VBA window). I'm still not ready to jump into VBA with both feet, but I guess this is a start. Little by little though. You made it easy and painless. But I'm not ready for lines and lines of code yet.
Again, thanks for all of your help.
Linda
Jack Cowley
Linda -
You are welcome!
You ARE ready to jump into code with both feet! Be brave, as I assume you are your lighthouse avatar because you are a beacon in a landlocked State.... Go for the gusto, as there are lots and lots of members here that are ready and willing to answer your VBA questions!!!
Jack
likajoho
Jack etal,
Can you help me add an additional condition to my statement?
Odiscovered quite by accident that the statement I used previously may be wrong when used on a record with more than one transaction. It filled some fields with information from the last transaction recorded, rather than the active transaction.
To recap, I'm printing a letter (report) directly from a subform using a command button. Both the form and the subform share ContactID which I used in the statement you recommended that I use in place of a macro. The statement is executed by an event procedure upon click. The statement is:
DoCmd.OpenReport "rpt_qryMiscDonorLtr", acViewPreview, , "[ContactID] = " & Me.ContactID
My subform is for transactions. The letter/report takes the amount of a contribution, the name of the donor, and other information to complete the letter.
How do I get the letter/report to take the transaction information from the current active transaction?
I have added the TrsID to the underlying query for the letter/report.
I tried just adding an additional condition by following the previous condition with "AND...." Nice try, but it didn't work.
Now, that was just a guess. What do I really do?
Thanks,
Linda
Jack Cowley
Linda -
How do I get the letter/report to take the transaction information from the current active transaction?"
You can have unbound text boxes in your report with code similar to this in their Control Sources:
=[Forms]![NameOfMainForm]![NameOfTransactionSubform].Form.NameOfControlWithAmoun
tOfContribution
Do the same for DonorName and any other information you need in the report.
Let me know if this helps..
Jack
PS. I work on Sunday so I will be gone all day, just in case you have questions and don't get a response...
likajoho
A workaround, huh? I thought VBA was sooo great! frown.gif (Probably just more complicated than I could handle, right?)
Actually, I found a workaround of my own. I modified the query and used some fields I had used for other letters to identify which transaction to pull from. I should have thought of that sooner.
Thanks, anyway. Looks like my non-VBA answer was the best one this time. frown.gif
And thank you for your prompt response.
Why is it I think better after I have posted a question than before?
Linda
Jack Cowley
Linda -
osting a response to UA always causes a great increase in brain chemstry. Consequently the answer comes to you and you are good to go!
I am glad you found a solution and you are so right that VBA is not all that it is cracked up to be. You just proved it!!! Again...
Well done!
Jack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.