Hi All, Using Access 2003 front end with Sql Server backend. I Have a form users can use to export a query to an excel spreadsheet . On the form are several unbound boxes: division, year, to month and from month. My query is a pass thru query. I am trying to let them choose the division and time frame of the query to output instead of outputting all the records in the query. I used this code before but running reports. I am trying to modify it to use a query instead. This is what I have so far. I am having difficulty referencing division and the year. I tried running to see what I needed but I can't get pass the error. Can someone guide me with this code. Thank you
Code:
Private Sub cmdExport_Click()
On Error GoTo Err_cmdExport_Click
Dim strSQL As String, intCounter As Integer
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.
Select Case Me.Module
Case SP
strnewquery = "[qrySPReports_test_passthru]"
Case LTL
strnewquery = "[qryLTLReports_test_passthru]"
Case DTF
strnewquery = "[qryDTFReports_test_passthru]"
If Me.cboFROM & vbNullString <> "" And Me.cboTO & vbNullString <> "" Then
strSQL = strSQL & " ([MONTHPROCESSED] BETWEEN " & Me.cboFROM & " And " & Me.cboTO & ") And "
End If
strnewquery = "Select qrySPReports_test_passthru.* FROM qrySPReports_test_passthru"
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.OpenQuery "[qrySPReports_test_passthru]", acViewNormal, strSQL
DoCmd.Close acForm, "frmREPORTBUILDER"
Else
MsgBox "There are no records that match your criteria! Please select new criteria.", vbInformation
Exit Sub
End If
DoCmd.Minimize
stDocName = "[qrySPReports_test_passthru]"
DoCmd.OpenQuery "[qrySPReports_test_passthru]", acViewNormal, strSQL
DoCmd.Close acForm, "frmREPORTBUILDER"
stDocName = "[qrySPReports_test_passthru]"
DoCmd.OpenQuery stDocName, acPreview
DoCmd.OutputTo acOutputQuery, [qrySPReports_test_passthru], acFormatXLS
DoCmd.Close "[qrySPReports_test_passthru]"
Exit_cmdExport_Click:
Exit Sub
Err_cmdExport_Click:
Select Case Err.Number
Case 2501 'OpenQuery action was cancelled
Resume Exit_cmdExport_Click
Case Else
MsgBox Err.Description
Resume Exit_cmdExport_Click
Resume
End Select
End Sub