Question Exporting a single record to PDF & Excel File

[babui386]

Hi,

I am trying to Export a single record from my customer table and using below codes -

Private Sub lblPDF_Click()
Me.Refresh
Dim myPath As String
Dim stDocName As String
Dim theFileName As String
stDocName = "rptCustomerMaster" (is my Report File name)
>>>DoCmd.OpenReport stDocName, acPreview, , "CustId = " & Nz(Me.CustID, 0)
myPath = "C:\..."
theFileName = "CustID " & CustID & ".pdf"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, theFileName, True
End Sub

Having errors on the highlighted code, could somebody guide me how to resolve it, or is there any support available to export record into PDF file?

 

[marlan]

Hi,
I don't know why you get an error (can CustID be ambiguous ? how about "Me.CustID = " & Nz(Me.CustID, 0)? )
In any case, I think you need to manipulate the report's RecordSource Property, and put the WHERE clouse there. I find best practice is to have a query be my RecordSource, and modify it's SQL Property:

FirstSQL = CurrenDB.QueryDefs(MyQueryName).SQL 
CurrenDB.QueryDefs(MyQueryName).SQL = FirstSQL & MyWhereClouse
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, theFileName, True
CurrenDB.QueryDefs(MyQueryName).SQL = FirstSQL

 

[babui386]

Hi Marlan,

Thanks for replying to my thread...

Just tried with the first bit of change but no luck -

in rptCustomerReport - Recordsource of sqls are -

SELECT tblCustomerMaster.CustID, tblCustomerMaster.CustomerName, tblCustomerMaster.MainBuyerName, tblCustomerMaster.AssistantBuyerName, tblCustomerMaster.Telephone, tblCustomerMaster.Fax, tblCustomerMaster.CompanyLogo, tblCustomerMaster.AccountSince, tblCustomerMaster.InvoiceAddress, tblCustomerMaster.PostalCode, tblCustomerMaster.City, tblCustomerMaster.Country, tblCustomerMaster.Email, tblCustomerMaster.Website FROM tblCustomerMaster;

As you have just mentioned to change the WHERE CLAUSE, what did you actually mean?

 

[marlan]

Hi,
You can Use a query as the record source for a form or report. Just open your current recordSouce using the Query Builder, and save it in a usefull name. You can also filter the data using this Query Builder (but you dont want to do that now, because you want to aplly the filter on all your data, using VBA code.
The query builder cerates an SQL statment behind the scense. this statment is saved in the SQL property of the Query. You can modify, or add a WHERE clouse in code, in the way shown above.
(If you don't know this, you had better learn some SQL, it is REALY helpfull when you work with MS Access or other Data-bases)

 

[Pat Hartman]

Modify the report's RecordSource query to include a where clause:

Where CustID = Forms!yourform!CustID

Then get rid of the DoCmd.OpenReport and just use the outputto which will print directly to pdf.

I generally give my users the option to preview or print or print to pdf and depending on which option they choose, I create the requested output.

 

[babui386]

Hi Pat,

Thanks for the response.

Have update the SQL to


WHERE (((tblCustomerMaster.[CustID])=[Forms]![frmCustomerMaster]![CustID]));

And VB as follows -


Private Sub lblPDF_Click()
Me.Refresh
Dim myPath As String
Dim stDocName As String
Dim theFileName As String
stDocName = "rptCustomerMaster"
myPath = "C:\..."
theFileName = "CustID " & CustID & ".pdf"
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, theFileName, True
End Sub


It worked fine, but pulling the whole record, where I wanted only the record I am viewing and would like to export that record to PDF.

Any suggestion would be highly appreciated.

Regards

Babui386

 

[babui386]

Hi,

just to let you know, it works fine, I have just edited page layout and working fine now.

Many thanks for your help.