We help IT Professionals succeed at work.

VBA code in Access 2003

marian68
marian68 asked
on
Hi guys,
I am building a command button on my form for printing the current record.
Can anyone give me this VBA code to insert it on click event?
Thank you
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013

Commented:
You should create a report rather than printing a form.

The code for printing the current record in a report with the same recordsource as your form is:

Docmd.OpenReport "rptYourReportName",,,"[IDField] = " & Me.IDField
Database Architect / Application Developer
Top Expert 2007
Commented:
Try this:


DoCmd.PrintOut acSelection

mx
Most Valuable Expert 2012
Top Expert 2013

Commented:
You can easily create a report by copy/pasting (and rearranging if desired) the controls from your form to a report in design view, and setting the Recordsource propert of the report to the same recordsource as your form.

Commented:
I suppose you first need to build a report that prints the form's current record.  Start with that.

Then use:

Docmd.OpenReport (ReportName, acViewNormal, "YourColumn = " & [Forms]![YourForm]!YourFieldName)

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
>> "on my form for printing the current record."

Author

Commented:
Hi guys,

Sorry for delays. Last Friday I had to leave my computer.
For DatabaseMx: Your code works.
For mbizup: I created a report as you suggested me, I created a command button on my form and I placed you code on click event but I am receiving the message:
"Compile error: Method or date member not found
and the code text ".IDField" is highlighted.
Thanks a lot
Most Valuable Expert 2012
Top Expert 2013
Commented:
That '.IDField' is just for example purposes.

You need to replace it with the actual name of the primary key field as seen on your form (and report).

For Example, if your form is based on an Employee table, you might need to replace that with EmployeeID, like this:

Docmd.OpenReport "rptYourReportName",,,"[EmployeeID] = " & Me.EmployeeID

Open in new window


(You need to substitute the actual field name that you are using in your database...)

Author

Commented:
I replaced the IDfield with my primary key field but I am receiving the message:
'Run-time error "438". Object doesn't support this property or method"
and all code line is highlighted.
Thank you
Most Valuable Expert 2012
Top Expert 2013

Commented:
Can you post a sample copy of your database (remove any sensitive data)?
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"For DatabaseMx: Your code works."
ok ... soooo ?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
btw ... there is *nothing* wrong with printing a Form for a quick 'report'

mx

Author

Commented:
Thank  you DatabaseMx for your answer. Yes. It works. I will increase the value of my question and I'll give you 250 points because this was the initial question.
But following your colleague's advice I built a report and I will build 2 buttons: 1 for printing the form and 2nd for printing the report corresponding to the current record in the form and afterwards the user will choose one of them.
If for printing the form your code worked with no problem, for printing the report I can not figure out why the above code doesn't work.
My code is:
"DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & Me.APPLICATION"
 where the field "APPLICATION" is a text field and primary key in my table source.
Thanks a lot
Most Valuable Expert 2012
Top Expert 2013

Commented:
For text, try this:


"DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & chr(34) & Me.APPLICATION & chr(34)

Open in new window

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
APPLICATION is a text field

DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & Chr(34) &  Me.APPLICATION & Chr(34)

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Note ... no double quote at the beginning of DoCmd ...

mx
Most Valuable Expert 2012
Top Expert 2013

Commented:
Drop the " at the beginning.

It should be:

DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & chr(34) & Me.APPLICATION & chr(34) 

Open in new window

Most Valuable Expert 2012
Top Expert 2013

Commented:
That leading " was brought in from the post here where the author enclosed the entire statement in quotes: http:#a37238836.  I got the end quote, but missed the first.

(Copy/paste mistake)

Author

Commented:
It didn't work.
Instead I found the code which works well;
Private Sub Command16_Click()
Dim strReport As String
Dim strWhere As String

strReport = "Mainreport"
strWhere = "[APPLICATION] = """ & Me![APPLICATION] & """"

DoCmd.OpenReport strReport, , , strWhere


End Sub

Thank a lot guys

Author

Commented:
Thank  you
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
"It didn't work."

if this

DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & Chr(34) &  Me.APPLICATION & Chr(34)
                                ^                 ^       ^                           ^
exactly as shown does not work, then something else is going on.  Note there are only total of 4 double quotes.

mx
Most Valuable Expert 2012
Top Expert 2013

Commented:
Just for kicks, try this:

DoCmd.OpenReport "Mainreport", , , "[APPLICATION] = " & chr(34) & Me!APPLICATION & chr(34)  

(It's the syntactical equivalent to what you have posted)

Author

Commented:
For DatabaseMX: your last code doesn't work.
For mbizup: your last code works.
Thanks a lot

Most Valuable Expert 2012
Top Expert 2013

Commented:
'Application' is a special word.

It didn't work following "Me." because it is a Property of the form (among other things).

That's why the brackets are needed - but a better solution in the long run if you are not too far along with it might to rename that field
Most Valuable Expert 2012
Top Expert 2013

Commented:
It would help to at least name your TextBox 'txtApplication' (which is not a reserved word) to indicate that it is a textbox, and take a look at these standard naming conventions:

http://www.xoc.net/standards/rvbanc.asp

Standards specific to Access are midway down the page,

Author

Commented:
Thank you