Report Export.Pdf with form Button

[mba_110]

Here i came with another query,

I am trying to export the report based on combo selection of

cboEmpID
cboCatagory
cboReportName

After all this are selected on frmExtIndividualRpts than only this command button should work and export the report based on the same name of report saved in

[tblReports]![ReportLocation]

Since i have more than 100 reports, i can't mention all this names in coding hence i have made a table called tblReports which has same report name saved in field [Reportlocation] i just need to call that field to export it as per the selection of category and Reportname selected on form.

I have manage to code the below, but not sure what is wrong with it, also its given me a message of "Data type mismatch"

[RID] is Number field and PK
[ReportName] is text field (Same data is saved as in cboReportName)

[ReportLocation] is the field which is having report real name cboReportName is just for non programming user's just not to confuse them self with short names of report.

Private Sub btnExportToPDF_Click()
On Error GoTo errhandlers:
If IsNull(cboEmpId_RC) Or IsNull(cboReportCatagory) Or IsNull(cboReportName) = "" Then
MsgBox "Please Select all required fields to proceed further for export. !"
Exit Sub
End If

If Not IsNull(cboReportName) Then

Me.cboReportName.Value = DLookup("[ReportName]", "[tblReports]", "[RID]='" & [cboReportName] & "'")

DoCmd.OutputTo acOutputReport, "tblReports", "[ReportLocation]", acFormatPDF
End If
Exit Sub
errhandlers:
MsgBox Err.Description, , Err
End Sub

 

[Minty]

You really need to standardise the way you refer to objects in your code.
Whenever you are referring to the value of a control on a form you should prefix it with Me.YourControlName .

Edit : This can't evaluate - what does the bit in red mean?

If IsNull(cboEmpId_RC) Or IsNull(cboReportCatagory) Or IsNull(cboReportName) [COLOR="Red"]= ""[/COLOR] Then

This code here makes no sense

If Not IsNull(cboReportName) Then

Me.cboReportName.Value = DLookup("[ReportName]", "[tblReports]", "[RID]='" & [cboReportName] & "'")

To me this reads as - you have selected a value in cboReportName you are then immediately replacing it with another value? which probably isn't in the combobox list

Why not bring the RID in with the combobox values as a hidden column and simply refer to it?

 

[mba_110]

i have added the [RID] to cboReportName record source query but i unable to achieve it let me try again and not possible then i will upload the strip version of data base, also i want you to know that I already have [ReportCatagory] and [ReportType] in it

with criteria of [Forms]![frmExtIndividualRpts]![cboCatagory] on ReportCatagory

and "Individual" on ReportType

So, can i use this or need to make new query?

 

[Minty]

If you want my honest advice I think you should slow down your coding and make one thing work at a time.

Looking at only the first bit of your code. Format it correctly, debug it and make sure it is working;

Private Sub btnExportToPDF_Click()
    On Error GoTo errhandlers:

    Dim sReportName     As String
    Dim sReportPath     As String


    If IsNull(Me.cboEmpId_RC) Or IsNull(Me.cboReportCatagory) Or IsNull(Me.cboReportName) Then
        MsgBox "Please Select all required fields to proceed further for export. !"
        Exit Sub
    End If

    MsgBox "Selected values are: " & Me.cboEmpId_RC & " : " & Me.cboReportCatagory & " : " & Me.cboReportName & " ."

  [COLOR="Green"]  ' If Not IsNull(cboReportName) Then  'This is completely redundent - you have already checked it above.
    
    'Here you need to change your cboReportName to bring in your 
    'actual reportname as a hidden second column in the rowsource
[/COLOR]
    sReportName = Me.cboReportName.Column(1)
    sReportPath = "c:\yoursavelocation\whateverYoucallit.pdf"

    DoCmd.OutputTo acOutputReport, sReportName, sReportPath, acFormatPDF

    Exit Sub
errhandlers:
    MsgBox Err.Description, , Err
End Sub

Notice how it's much easier to see what is happening if you add some simple string variables and assign them. Call them things that make it obvious what they contain.

Whilst making it work add message boxes to "See" the values you are actually working with. Simply comment them out once it is working.

 

[mba_110]

I am sorry, again i think i am doing some stupid things but nothing is happening if i put the following may be i am wrong some where?

Private Sub btnExportToPDF_Click()
On Error GoTo errhandlers:
    Dim sReportName     As String
    Dim sReportPath     As String

    If IsNull(Me.cboEmpId_RC) Or IsNull(Me.cboCatagory) Or IsNull(Me.cboReportName) Then
        MsgBox "Please select all * required fields to proceed further for export. !"
      End If
      Exit Sub
      

    If Not IsNull(cboReportName) Then
    
    ReportLocation = Me.cboReportName.Value

    sReportName = Me.cboReportName.Column(4)
    sReportPath = "C:\Users\Am\ Documents\Employee Management Database\TempReport.pdf"

    DoCmd.OutputTo acOutputReport, sReportName, sReportPath, acFormatPDF
    End If
Exit Sub

errhandlers:
MsgBox Err.Description, , Err
End Sub

I used the same existing query and added the column (4) for [RID] since its filtering the report name for Requery based on form's cboCatagory selection.

Please don't mind i can understand that i am struggling with my codes and format. sorry again to bother you.

 

[Minty]

Okay - so as suggested - slow down and see and understand what you are doing with each part of your code - I would also get into the habit of putting in comments at each part so you can see later what and why you where doing. I'll go through what you have so far - it's not criticism, just observations and hopefully getting you to examine your code in a more objective fashion.

First - you are still checking for

[I]If Not IsNull(cboReportName) Then [/I]

which isn't required. You have already checked it's not null in the first part of your code.

Next you are setting a value for this variable ReportLocation here

[I]ReportLocation = Me.cboReportName.Value[/I]

but you aren't using it anywhere, and you haven't declared it. What do you think you are setting here?

Finally - and this is a technique you should get used to - and I mentioned before - use Debug.Print or message boxes to check the values Access is actually using. So add a

Debug.Print "Column 4 Value: " & sReportName

after you have set it.

In the VBA code editor window make sure you have the immediate window open (Press ctrl + G), run your code and look at the value returned in the window.

I suspect that your column(4) should be Column(3) as in comboboxes the column numbers start at 0. But you can at least prove it to yourself. Whist you are doing that also add

Debug.Print  Me.cboReportName.Value ;  Me.cboReportName

to your code and see what you get. They are (Should be!)the same.

 

[mba_110]

I am sorry but i am struct here, please give me final solution i just want to complete it to have an example for export reports.

here the part i am missing in the code is how to tell export report based on cboReportName saved as actual report name in tblReports, [ReportLocation] field which is actual report name by which the report is saved in DB.

Private Sub btnExportToPDF_Click()
On Error GoTo errhandlers:
    Dim sReportName As String
    Dim sReportPath As String
    Dim Filename    As String
    

    If IsNull(Me.cboEmpId_RC) Or IsNull(Me.cboCatagory) Or IsNull(Me.cboReportName) Then
        MsgBox "Please select all * required fields to proceed further for export. !"
      End If
      Exit Sub
      
    Debug.Print Me.cboReportName.Value; Me.cboReportName

    sReportName = Me.cboReportName.Column(3)
    Filename = Me.txtRptEmpName & "_ID " & Me.cboEmpId_RC
    sReportPath = "C:\Users\Am\Documents\Employee Management Database\TempReport.pdf"
    DoCmd.OutputTo acOutputReport, sReportName, sReportPath, acFormatPDF
    MsgBox "Employee Report Save, Sucessfully in .Pdf format", vbInformation, "Saved Confirmed"
   
Exit Sub

errhandlers:
MsgBox Err.Description, , Err
End Sub

Also when i run combo control source query the field [RID] is not showing any number, is that also a problem ?

If this type of conditions was not their than i would finish it must early.

I hope you won't mind give me the final correction.

 

[Minty]

In your code above you are creating a variable called Filename, but don't appear to be using it anywhere?

I'm confused by your process here - maybe you could upload a sample database so we can see what isn't working.

 

[mba_110]

I have attached the DB, Thanks for your help.

 

[Minty]

Have a look now - I can't make the actual report open as the tables aren't there - but look carefully at how its been changed.

 

[mba_110]

Thank you thank you agian and i was just making something very silly i forgot to include the column of Reportlocation in cboReportName.

Thanks again my friend. now i will do the reset button coding.

I will come back in same post with my code error.