Export PDF

[ingarchsf]

N_modulo is a number field (1, 2, 3, ...) and Data_modulo is a date field (01/09/2014).



N_modulo is used only for selection, Data_modulo is used for the name of the PDF.

The problem is in If instruction: seems that doesn't recognize qncorso1 and qnmodulo1..

[June7]

Maybe because in the code there is letter "l" at the end instead of number "1".

qncorsol
qncorso1

qnmodulol
qnmodulo1

Have you run Debug > Compile?

[ingarchsf]

*_* wow.. a stupid mistake! I don't know why..

byw.. ok, I've changes l -> 1, and now the issue is:

Runtime error 3142: unexpected character after the end of the SQL statement

line:

Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

[ingarchsf]

ok, I've moved the three "if" before the "strSQL".. and the Runtime error changes.. now is 3075: parethesis in excess: ) in the expression of the query '(Cognome Like "**") AND (N_corso = 4) AND (N_modulo = )'.

I've entered only N_corso..

Private Sub ExportPDF_Click()
    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim sCriteria As String
    Dim strWhere As String                  
    Dim lngLen As Long                

    Set MyDB = CurrentDb
    strRptName = "Attestati_2014"
    
    If Not IsNull(Me.qcognome1) Then
        strWhere = strWhere & "(Cognome Like ""*" & Me.qcognome1 & "*"") AND "
    End If
    
    If Not IsNull(Me.qncorso1) Then
        strWhere = strWhere & "(N_corso = " & Me.qncorso1 & ") AND "
    End If
    
    If Not IsNull(Me.qnmodulo1) Then
        strWhere = strWhere & "(N_modulo = " & Me.qnmodulo1 & ") AND "
    End If
    
    'See if the string has more than 5 characters (a trailing " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then
        'Yep: there is something there, so remove the " AND " at the end.
        strWhere = "WHERE " & Left$(strWhere, lngLen)
    End If
    
     strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo],Selezione_corso_2014.[Nome]  From Selezione_corso_2014 " & strWhere & ";"
     strSQL = strSQL & " WHERE Cognome ='" & Me.qcognome1 & "' AND N_corso =" & Me.qncorso1 & " AND N_modulo =" & Me.qnmodulo1 & ""
    
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    With MyRS
        Do While Not MyRS.EOF
            sCriteria = "[Cognome]='" & ![Cognome] & "' and [Nome]='" & ![Nome] & "' and [Data_modulo] = #" & ![Data_modulo] & "#"
            DoCmd.OpenReport strRptName, acViewPreview, , sCriteria
            
            DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, "C:\Attestati\" & Format(![Cognome], ">") & Format(![Nome], "<") & "_" & Format(![Data_modulo], "YYYYMMDD") & ".pdf", False
            DoCmd.Close acReport, strRptName
            .MoveNext
        Loop
    End With
    MyRS.Close
    Set MyRS = Nothing
    MsgBox "Done"
End Sub

[June7]

When were the 3 "If" after strSQL???? Oh well, as long as in right place now.

Why is the string including the parameters if there are no inputs?
Are the input controls really null if nothing is entered?

An alternative If to handle both Null and empty string:

If Me.qcognome1 & "" <> "" Then

Step debug, follow the code as it executes, one line at a time.
What values are in the controls and variables as the code runs?
Does the code process as expected for the inputs?
Is the composed SQL statement correct? Can use a Debug.Print line to help view the string.
Review link at bottom of my post for debugging guidelines.

[ingarchsf]

I've tried, but it does exactly the same..

Runtime error 3142: unexpected character after the end of the SQL statement

line:

Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)

I've tried only with a value in one of the variables: I've put 1 in N_corso (qncorso1)
The other variables have as default value ""

[June7]

Why do you still have this line:

strSQL = strSQL & " WHERE Cognome ='" & Me.qcognome1 & "' AND N_corso =" & Me.qncorso1 & " AND N_modulo =" & Me.qnmodulo1 & ""

[ingarchsf]

ehm.. Because in the base code given by ssanfu there was that line, that selects th values from the boxes..
is the example there was only Cognome.. I added the other 2. If I insert all the three values, without if clauses, it works very well.. But if I insert only one value of the three, it gives error..

see posts 58 and 62

[June7]

But now you have the 3 If Then structures to conditionally build the WHERE clause. By keeping that old code the SQL could result in two WHERE clauses. The old line includes all 3 parameters even if there is no input. The flexibility of input is the reason for the conditional If Then structures. The old code line would then be eliminated. Can't have both in the same procedure - which is what you posted.

[ingarchsf]

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
disabling that line, WORKS!!! *_*

ok, it's almost perfect.. only one thing: at the beginning we started making a "search" button, following the first video example that you posted, but it was based on dynamic parameters, that can't be used now anymore.

I'm thinking: making a "search button" has the same logic, isn't it? the aim, as previously, is to search pages of the report, based on the usual 3 parameters, but now to only visualize them all together as Preview. I tried to modify the CODE, removing the operations about exporting PDF, but it opens only one page.

[June7]

A constructed WHERE clause could be used to set the form's Filter property. This is what Allen shows in his tutorial.

The difference is the WHERE word would not be used.

Also, constructed filter criteria (without the WHERE word) can be passed to report in OpenReport method.

[ingarchsf]

may I do something like this?

 DoCmd.OpenReport strRptName, acViewPreview, , WHERE ....

[June7]

The WHERE word is not used.

DoCmd.OpenReport strRptName, acViewPreview, , strCriteria

[ingarchsf]

I've tried this way:

    Dim MyDB As DAO.Database
    Dim MyRS As DAO.Recordset
    Dim strSQL As String
    Dim strRptName As String
    Dim sCriteria As String
    Dim strWhere As String                  
    Dim lngLen As Long                

    Set MyDB = CurrentDb
    strRptName = "Attestati_2014"
    
    If Not IsNull(Me.qcognome1) Then
        strWhere = strWhere & "(Cognome Like ""*" & Me.qcognome1 & "*"") AND "
    End If
    
    If Not IsNull(Me.qncorso1) Then
        strWhere = strWhere & "(N_corso = " & Me.qncorso1 & ") AND "
    End If
    
    If Not IsNull(Me.qnmodulo1) Then
        strWhere = strWhere & "(N_modulo = " & Me.qnmodulo1 & ") AND "
    End If
   
    lngLen = Len(strWhere) - 5
    If lngLen > 0 Then
        strWhere = "WHERE " & Left$(strWhere, lngLen)
    End If
    
     strSQL = "Select Selezione_corso_2014.[Cognome], Selezione_corso_2014.[Data_modulo],Selezione_corso_2014.[Nome]  From Selezione_corso_2014 " & strWhere & ";"
    
    Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    With MyRS
            sCriteria = "[Cognome]='" & ![Cognome] & "' and [N_corso]='" & ![N_corso] & "' and [N_modulo] = '" & ![N_modulo] & "'"
            DoCmd.OpenReport strRptName, acViewPreview, , sCriteria
    End With
    MyRS.Close
    Set MyRS = Nothing
    MsgBox "Done"

but the error is: 3075, too many parenthesys in (Cognome = ) AND (N_corso = 1) AND (N_modulo = )

[June7]

There are too many parens because the criteria are incomplete - there are no parameters.

See post 80. Did you step debug? Why is the strWhere including the criteria if the controls have no input? If the controls are truly Null, the code should not include those criteria.

You really need to learn debug techniques. Have you reviewed the link at bottom of my post?


For sCriteria, do not use apostrophe delimiters with number type field parameters. If N_corso and N_modulo are number type fields, remove the apostrophes. Date type fields need # delimiter for parameters.