Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Error 3061

I have a module as an event to a form. It is designed to collect multiple customer_id from user and then run a report which outputs a PDF file for each customer_id.
I'm getting the error 3061 on the sql string. Here is the code:
Anybody know a solution? I searched all over for this and still no solution.

Sub Command21_Click()

'------------------------------------------
'On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
'----------------------------------------------
'glbVarName As String
Dim rs As DAO.Recordset
Dim sql As String
Dim strPath As String
TempVars.Add "strvndCode", Null 'Me.cboProdCode.Value
TempVars.Add "StartDate", Me.StartDate.Value
TempVars.Add "EndDate", Me.EndDate.Value
'-----------------------------------------------
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.
strDelim = """" 'Delimiter appropriate to field type. See note 1.
strDoc = "test pdf"
'MsgBox strDoc
'Loop through the ItemsSelected in the list box.
With Me.NamesList 'LstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
' strWhere = strWhere & strDelim & .ItemData(varItem) & strDelim & ","
strWhere = strWhere & .ItemData(varItem) & ","
'Build up the description from the text in the visible column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) & """, "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen > 0 Then
strWhere = "[Customer_ID] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
strPath = "C:Access"
sql = "SELECT DISTINCT TempTablePDF.Customer_id FROM TempTablePDF where '" & strWhere & "' and invoice_date between '#startdate#' and '#enddate#';"[/color]Set rs = CurrentDb().OpenRecordset(sql, dbOpenSnapshot)
Do While Not rs.EOF
strVndCode = rs!Customer_id
TempVars.Add "strvndCode", strVndCode
OpenArgs:=strDescrip

DoCmd.OutputTo acOutputReport, strDoc, acFormatPDF, strPath & strVndCode & ".pdf"
rs.MoveNext
Loop

Set rs = Nothing
End Sub
Sign In or Register to comment.