All, using 2010. I am using a form with unbound fields to filter for a report to preview or export to excel. I want both options. But when I select fields to filter i get "two few parameters expect 3" when i click the preview button. If I fill 4 of the unbound fields; I get exected 4. It seems to go up the more fields i fill. I've never seen this. Here is the code:
Can anyone help please?
Code:
Private Sub cmdPrintPreview_Click()
On Error GoTo Err_cmdPrintPreview_Click
Dim strSQL As String, intCounter As Integer
Dim db As Database, rs As Recordset
Dim ctl As Control, strname As String, strnewquery As String
Dim strRptSel As String
Dim stMessage As String
Set db = CurrentDb
'Build SQL String
For Each ctl In Me.Form
If ctl.Tag = "input" Then
'strname = "me." & ctl.Name
If ctl.Value > "" Then
strSQL = strSQL & "[" & ctl.Name & "] " & " like " & Chr(34) & ctl.Value & Chr(34) & " And "
End If
End If
Next ctl
' Set the value of the parameter.
If Me.cboWEdateFrom & vbNullString <> "" And Me.cboWEdateTo & vbNullString <> "" Then
strSQL = strSQL & " ([Week Ending] BETWEEN " & Me.cboWEdateFrom & " And " & Me.cboWEdateTo & ") And "
End If
strnewquery = "Select qryQAReport.* FROM qryQAReport"
If strSQL <> "" Then
strSQL = Left(strSQL, (Len(strSQL) - 5))
strnewquery = strnewquery & " WHERE " & strSQL & ";"
End If
Debug.Print strnewquery
' Create the recordset
Set rs = db.OpenRecordset(strnewquery)
If rs.RecordCount <> 0 Then
DoCmd.OpenReport "rptQAReport", acViewPreview, , strSQL
'DoCmd.OutputTo acOutputQuery, "qryQAReportQuery", acFormatXLS, True
DoCmd.Close acForm, "frmReportbuilder"
Else
MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
Exit Sub
End If
Exit_cmdPrintPreview_Click:
Exit Sub
Err_cmdPrintPreview_Click:
Select Case Err.Number
Case 2501 'OpenQuery action was cancelled
Resume Exit_cmdPrintPreview_Click
Case Else
MsgBox Err.Description
Resume Exit_cmdPrintPreview_Click
Resume
End Select
End Sub