Print current record on form to report? (1 Viewer)

Holly_Associated · Mar 21, 2013

Good Afternoon All,

I'm hoping you can help. I have a DB in Access 2007 which I'm having trouble with. I have a form, "Sub_Det_Frm", on which I would like to place a button to print the current record, (Primary Key is an autonumber "Employee Number") to a report "Sub_DetForm_Rpt".
OK... hopefully that made sense. I have no knowledge of VBA but can follow instructions. I generally use forums to help but this is the first time I've actually posted!

I've used the instructions from, oops I can't show you the link! It's h(double t)p(colon)//allenbrowne(dot)com/casu-15(dot)html but I'm getting this error:
The expression On Click you entered as the property setting produced the following error: A problem occurred while Employee Details was communicating with the OLE server or ActiveX Control.
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

Can anyone tell me where I'm going wrong or help with an alternative?

:banghead: Holly

boblarson · Mar 21, 2013

Use this method (the sample shows how to do it with a form but you can change the code to use the same method with reports).

http://www.baldyweb.com/wherecondition.htm

with reports:
DoCmd.OpenReport "ReportName", acViewPreview, WhereCondition:="FieldName = " & Me.ControlName

Pay attention to what he says about text and dates too.

Holly_Associated · Mar 21, 2013

Hi Bob,

Where do I type the DoCmd...
Is it still in the button, on click, event procedure, then build, then delete the other stuff and insert this in between "Private Sub cmdPrint_Click()"
and "End Sub"?

boblarson · Mar 21, 2013

Yes, it goes inside those.

Code:

Private Sub cmdPrint_Click()
   DoCmd.OpenReport "ReportNameHere", acViewPreview, WhereCondition:="[FieldNameHere]=" & Me.ControlOnYourFormHere
End Sub

Holly_Associated · Mar 21, 2013

Hi Bob,

I've done the following:
Private Sub cmdPrint_Click()
DoCmd.OpenReport "Sub_DetForm_Rpt", acViewPreview, WhereCondition:="[Employee Number]=" & Me.ControlOnSub_Det_Frm
End Sub
...and am getting exactly the same error? Do I have to amend the properties for the button?
The form is from a query and I've put the "Employee Number" field into the query but it still says the same thing.
Please help!

boblarson · Mar 21, 2013

Holly_Associated said:
WhereCondition:="[Employee Number]=" & Me.ControlOnSub_Det_Frm

Is the text box that has Employee Number on your form really named ControlOnSub_Det_Frm? You need to use the name of the text box that is currently displaying the Employee Number field.

Holly_Associated · Mar 21, 2013

Oh dear, silly me... I didn't have the Employee Number showing on the form. I've now added it and when I look at text box, other and name it is "EmpNo" without the quotes, so I've changed it to:
Private Sub cmdPrint_Click()
DoCmd.OpenReport "Sub_DetForm_Rpt", acViewPreview, WhereCondition:="[Employee Number]=" & Me.EmpNo
End Sub
And I'm still getting the same error message?
Thank you for taking the time to read this and try figure it out Bob. When I set out to add this button I thought it would be a little simpler than this.

boblarson · Mar 21, 2013

Do you happen to still have something in the event PROPERTY for that click instead of it reading [Event Procedure]?

Should be like this:

Holly_Associated · Mar 21, 2013

It looks exactly like that with [Event Procedure] in the box. When I click the drop down I can see my macros and when I click the 3dots (build?) I can see the code in a new screen.
The code seems to be saving. I edit it and then close the DB and open it and it's still there when I click build again.
I need to learn VBA! I've got by up until now on basic stuff but this weird language is becoming a must I think!

boblarson · Mar 21, 2013

Try going to the VBA window and selecting from the very top menu

DEBUG > COMPILE

and see if it takes you to an error. Sometimes other errors can cause problems that seem like they are on the one you are working on even if they aren't.

Holly_Associated · Mar 21, 2013

OK, I pressed Compile and nothing happened and now the option is greyed out. I've closed the DB and opened it again the and the option is still grey.

boblarson · Mar 21, 2013

Holly_Associated said:
OK, I pressed Compile and nothing happened and now the option is greyed out. I've closed the DB and opened it again the and the option is still grey.

Okay, can you upload a copy of the database here (using fake data) so I can take a look? If you do, make sure you run Compact and Repair first and then ZIP the file (you can do so by right-clicking on the file and selecting SEND TO > COMPRESSED FOLDER.

Holly_Associated · Mar 22, 2013

Hi Bob,
I've made a copy of the DB, removed all confidential records, added 4 test records, compacted and repaired and then zipped the file and it is still 7.9mb, exceeding the 2mb allowance for uploading. Is there any way I can make it smaller or shall I delete forms / reports which are not linked to adding this button. I'm not sure if you need to see the whole thing or not?

Holly_Associated · Mar 22, 2013

So...
I thought of a workaround so that I wouldn't have to use code.
I followed the instructions on Microsoft online help...
I created a query and a little form for the user to enter the employee number. In my query I used the builder to reference the text box on my little form so I didn't make a typo and it doesn't open it? I get the standard popup with my criteria line on it.
Maybe I was never meant to be able to print a single record in the report style I want?...

Holly_Associated · Mar 22, 2013

Hi All,
I've removed my little form and will just use the access standard one.

boblarson · Mar 22, 2013

I noticed you deleted this thread. That is not something to do unless you have posted and NOBODY has answered you. If someone has answered and worked on things, the thread stays. You can post that you have solved your issue, or chosen to move on, but do not delete the thread.

I have undeleted it (I can do that as a moderator).

imtiazali266 · Jan 23, 2020

i have also the same problem

AccessBlaster · Jan 23, 2020

imtiazali266 said:
i have also the same problem

We know it's being address here >> https://access-programmers.co.uk/forums/showthread.php?t=309067

You are not helping yourself by opening old threads.

Print current record on form to report? (1 Viewer)

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Smeghead

Attachments

Registered User.

Smeghead

Registered User.

Smeghead

Registered User.

Registered User.

Attachments

Registered User.

Smeghead

Registered User.

Registered User.

Similar threads

Users who are viewing this thread