filter query from form to export to excel

Delindan · Jan 24, 2012

I'm trying to filter a query to export to excel. I don't know what I'm doing so I've been trying to copy some examples I've seen. I have a text box on my form that captures user input to use in the routine. In the routine I am really not sure where to put the filter information or how to apply it to my query. I am going to paste the code. I have a randome line in there after I defined my record set which obviously does nothing. Again, not sure how to apply. Any help would be appreciated!

Option Compare Database

Private Sub cmdStartExport___Click()

Dim DB As Database
Dim xlApp As New Excel.Application
Dim RSBudget As Recordset
Dim WB As Workbook
Dim strFolder As String
Dim strFilename As String
Dim strSheetName As String
Dim introw As Long
Dim strCC As String
Dim strPosition As String
Dim strExportTemplate As String
Dim strStart As String
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strSQL As String

strStart = txtStart
strFolder = Trim(txtFolder)
If Right(strFolder, 1) <> "\" Then
strFolder = strFolder & "\"
End If

strFilename = Trim(txtFileName)
If Right(strFilename, 5) <> ".xlsx" Then
strFilename = strFilename & ".xlsx"
End If
strFilename = strFolder & strFilename

strExportTemplate = strFolder & "New Export Template.xlsx"

With xlApp
.Visible = False
Set WB = .Workbooks.Open(strExportTemplate)
.Workbooks(1).SaveAs (strFilename)
End With

txtCurrProfile = Null
DoEvents

strStart = txtStart

Set DB = CurrentDb
Set qdf = DB.QueryDefs("Summary of fx and oh")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RSBudget = qdf.OpenRecordset

strSQL = "Select * From [Summary of fx and oh] Where [Job Type] = Forms![Dollar Export with fx and oh]![txtJobType]"

xlApp.Worksheets(1).Cells(1, 2) = strStart
RSBudget.MoveFirst
strCC = RSBudget("Act Cost Center") & ""
strPosition = RSBudget("Position Number") & ""
introw = 4
Do Until RSBudget.EOF
txtCurrProfile = "Exporting Position " & strPosition & " ..."
DoEvents
With xlApp
.Cells(introw, 1) = RSBudget("Position Number")
.Cells(introw, 2) = RSBudget("Job Title")
.Cells(introw, 3) = RSBudget("Taleo Number")
.Cells(introw, 4) = RSBudget("Staffing Status")
.Cells(introw, 5) = RSBudget("Act Cost Center")
.Cells(introw, 6) = RSBudget("Job Type")
.Cells(introw, 7) = RSBudget("RLT Member")
.Cells(introw, 8) = RSBudget("Business Need")
.Cells(introw, 13) = Nz(RSBudget("B1"), 0)
.Cells(introw, 14) = Nz(RSBudget("M1"), 0)
.Cells(introw, 16) = Nz(RSBudget("F1a"), 0)
.Cells(introw, 17) = Nz(RSBudget("F1b"), 0)
.Cells(introw, 15).Formula = "=(+" & .Cells(introw, 16) & "+" & .Cells(introw, 17) & ")"
.Cells(introw, 18) = Nz(RSBudget("B2"), 0)
.Cells(introw, 19) = Nz(RSBudget("M2"), 0)
.Cells(introw, 21) = Nz(RSBudget("F2a"), 0)
.Cells(introw, 22) = Nz(RSBudget("F2b"), 0)
.Cells(introw, 20).Formula = "=(+" & .Cells(introw, 21) & "+" & .Cells(introw, 22) & ")"
.Cells(introw, 23) = Nz(RSBudget("B3"), 0)
.Cells(introw, 24) = Nz(RSBudget("M3"), 0)
.Cells(introw, 26) = Nz(RSBudget("F3a"), 0)
.Cells(introw, 27) = Nz(RSBudget("F3b"), 0)
.Cells(introw, 25).Formula = "=(+" & .Cells(introw, 26) & "+" & .Cells(introw, 27) & ")"
.Cells(introw, 28).Formula = "=(+" & .Cells(introw, 13) & "+" & .Cells(introw, 18) & "+" & .Cells(introw, 23) & ")"
.Cells(introw, 29).Formula = "=(+" & .Cells(introw, 14) & "+" & .Cells(introw, 19) & "+" & .Cells(introw, 24) & ")"
.Cells(introw, 30).Formula = "=(+" & .Cells(introw, 15) & "+" & .Cells(introw, 20) & "+" & .Cells(introw, 25) & ")"
.Cells(introw, 31) = Nz(RSBudget("B4"), 0)
.Cells(introw, 32) = Nz(RSBudget("M4"), 0)
.Cells(introw, 34) = Nz(RSBudget("F4a"), 0)
.Cells(introw, 35) = Nz(RSBudget("F4b"), 0)
.Cells(introw, 33).Formula = "=(+" & .Cells(introw, 34) & "+" & .Cells(introw, 35) & ")"
.Cells(introw, 36) = Nz(RSBudget("B5"), 0)
.Cells(introw, 37) = Nz(RSBudget("M5"), 0)
.Cells(introw, 39) = Nz(RSBudget("F5a"), 0)
.Cells(introw, 40) = Nz(RSBudget("F5b"), 0)
.Cells(introw, 38).Formula = "=(+" & .Cells(introw, 39) & "+" & .Cells(introw, 40) & ")"
.Cells(introw, 41) = Nz(RSBudget("B6"), 0)
.Cells(introw, 42) = Nz(RSBudget("M6"), 0)
.Cells(introw, 44) = Nz(RSBudget("F6a"), 0)
.Cells(introw, 45) = Nz(RSBudget("F6b"), 0)
.Cells(introw, 43).Formula = "=(+" & .Cells(introw, 44) & "+" & .Cells(introw, 45) & ")"
.Cells(introw, 46).Formula = "=(+" & .Cells(introw, 31) & "+" & .Cells(introw, 36) & "+" & .Cells(introw, 41) & ")"
.Cells(introw, 47).Formula = "=(+" & .Cells(introw, 32) & "+" & .Cells(introw, 37) & "+" & .Cells(introw, 42) & ")"
.Cells(introw, 48).Formula = "=(+" & .Cells(introw, 33) & "+" & .Cells(introw, 38) & "+" & .Cells(introw, 43) & ")"
.Cells(introw, 49) = Nz(RSBudget("B7"), 0)
.Cells(introw, 50) = Nz(RSBudget("M7"), 0)
.Cells(introw, 52) = Nz(RSBudget("F7a"), 0)
.Cells(introw, 53) = Nz(RSBudget("F7b"), 0)
.Cells(introw, 51).Formula = "=(+" & .Cells(introw, 52) & "+" & .Cells(introw, 53) & ")"
.Cells(introw, 54) = Nz(RSBudget("B8"), 0)
.Cells(introw, 55) = Nz(RSBudget("M8"), 0)
.Cells(introw, 57) = Nz(RSBudget("F8a"), 0)
.Cells(introw, 58) = Nz(RSBudget("F8b"), 0)
.Cells(introw, 56).Formula = "=(+" & .Cells(introw, 57) & "+" & .Cells(introw, 58) & ")"
.Cells(introw, 59) = Nz(RSBudget("B9"), 0)
.Cells(introw, 60) = Nz(RSBudget("M9"), 0)
.Cells(introw, 62) = Nz(RSBudget("F9a"), 0)
.Cells(introw, 63) = Nz(RSBudget("F9b"), 0)
.Cells(introw, 61).Formula = "=(+" & .Cells(introw, 62) & "+" & .Cells(introw, 63) & ")"
.Cells(introw, 64).Formula = "=(+" & .Cells(introw, 49) & "+" & .Cells(introw, 54) & "+" & .Cells(introw, 59) & ")"
.Cells(introw, 65).Formula = "=(+" & .Cells(introw, 50) & "+" & .Cells(introw, 55) & "+" & .Cells(introw, 60) & ")"
.Cells(introw, 66).Formula = "=(+" & .Cells(introw, 51) & "+" & .Cells(introw, 56) & "+" & .Cells(introw, 61) & ")"
.Cells(introw, 67) = Nz(RSBudget("B10"), 0)
.Cells(introw, 68) = Nz(RSBudget("M10"), 0)
.Cells(introw, 70) = Nz(RSBudget("F10a"), 0)
.Cells(introw, 71) = Nz(RSBudget("F10b"), 0)
.Cells(introw, 69).Formula = "=(+" & .Cells(introw, 70) & "+" & .Cells(introw, 71) & ")"
.Cells(introw, 72) = Nz(RSBudget("B11"), 0)
.Cells(introw, 73) = Nz(RSBudget("M11"), 0)
.Cells(introw, 75) = Nz(RSBudget("F11a"), 0)
.Cells(introw, 76) = Nz(RSBudget("F11b"), 0)
.Cells(introw, 74).Formula = "=(+" & .Cells(introw, 75) & "+" & .Cells(introw, 76) & ")"
.Cells(introw, 77) = Nz(RSBudget("B12"), 0)
.Cells(introw, 78) = Nz(RSBudget("M12"), 0)
.Cells(introw, 80) = Nz(RSBudget("F12a"), 0)
.Cells(introw, 81) = Nz(RSBudget("F12b"), 0)
.Cells(introw, 79).Formula = "=(+" & .Cells(introw, 80) & "+" & .Cells(introw, 81) & ")"
.Cells(introw, 82).Formula = "=(+" & .Cells(introw, 67) & "+" & .Cells(introw, 72) & "+" & .Cells(introw, 77) & ")"
.Cells(introw, 83).Formula = "=(+" & .Cells(introw, 68) & "+" & .Cells(introw, 73) & "+" & .Cells(introw, 78) & ")"
.Cells(introw, 84).Formula = "=(+" & .Cells(introw, 69) & "+" & .Cells(introw, 74) & "+" & .Cells(introw, 79) & ")"
.Cells(introw, 85).Formula = "=(+" & .Cells(introw, 28) & "+" & .Cells(introw, 46) & "+" & .Cells(introw, 64) & "+" & .Cells(introw, 82) & ")"
.Cells(introw, 86).Formula = "=(+" & .Cells(introw, 29) & "+" & .Cells(introw, 47) & "+" & .Cells(introw, 65) & "+" & .Cells(introw, 83) & ")"
.Cells(introw, 87).Formula = "=(+" & .Cells(introw, 30) & "+" & .Cells(introw, 48) & "+" & .Cells(introw, 66) & "+" & .Cells(introw, 84) & ")"
.Cells(introw, 88).Formula = "=(+" & .Cells(introw, 86) & "+" & .Cells(introw, 87) & ")"
If Len(strCC) <> 0 Then
blnCC = CC(strCC, introw, WB, xlApp)
End If
End With
introw = introw + 1
RSBudget.MoveNext
If RSBudget.EOF Then Exit Do
strCC = RSBudget("Act Cost Center") & ""
strPosition = RSBudget("Position Number") & ""
Loop
introw = introw + 2
With xlApp
.Cells(introw, 1) = "Totals:"
.Cells(introw, 6).Formula = "=SUBTOTAL(3, F4:" & .Cells((introw - 2), 11).Address(False, False) & ")"
.Cells(introw, 13).Formula = "=SUBTOTAL(9, M4:" & .Cells((introw - 2), 11).Address(False, False) & ")"
.Cells(introw, 13).Select
.Selection.Copy
.Range("N" & introw & ":CJ" & introw).Select
.ActiveSheet.Paste
.Range("M4:CJ" & introw).Select
.Selection.NumberFormat = "0;[Red]0"
.Application.Goto Reference:="R4C2"
.ActiveWindow.FreezePanes = True
.Workbooks(1).Save
.Workbooks(1).Close
End With

xlApp.Quit
RSBudget.Close

DB.Close

Set xlApp = Nothing
Set RSSpecialist = Nothing
Set DB = Nothing

txtCurrProfile = "Done!"
DoEvents

End Sub

Private Function CC(strCC As String, introw As Long, WB As Workbook, xlApp As Excel.Application) As Boolean

Dim DB As Database
Dim RSCCinfo As Recordset

CC = False

Set DB = CurrentDb

Set RSCCinfo = DB.OpenRecordset("Cost Center Information", dbOpenSnapshot)
RSCCinfo.MoveFirst
Do While Not RSCCinfo.EOF
If Trim(RSCCinfo("Sap#")) = strCC Then
xlApp.Worksheets(1).Cells(introw, 9) = RSCCinfo("Region")
xlApp.Worksheets(1).Cells(introw, 10) = RSCCinfo("Country")
xlApp.Worksheets(1).Cells(introw, 11) = RSCCinfo("Division")
End If

RSCCinfo.MoveNext
If RSCCinfo.EOF Then Exit Do
Loop

CC = True

End Function

MazeWorX · Jan 25, 2012

try untested

Code:

Private Sub cmdStartExport_Click()
    Dim DB As DAO.Database[red]'Specify DAO or ADO
    Dim xlApp As New Excel.Application
    Dim RSBudget As DAO.Recordset [red]'Specify DAO or ADO
    Dim WB As Workbook
    Dim strFolder As String
    Dim strFilename As String
    [red]'Dim strSheetName As String '**************This is not being used[/red]
    Dim introw As Long
    Dim strCC As String
    Dim strPosition As String
    Dim strExportTemplate As String
    Dim strStart As String
    [red]'Dim qdf As DAO.QueryDef'**************This is not being used[/red]
    [red]'Dim prm As DAO.Parameter'**************This is not being used[/red]
    Dim strSQL As String

    Set DB = CurrentDb
    strStart = Me.txtStart [red]'reference form controls with 'Me'[/red]

    strFolder = Trim(Me.txtFolder)
    If Right(strFolder, 1) <> "\" Then
        strFolder = strFolder & "\"
    End If

    strFilename = Trim(Me.txtFileName)
    If Right(strFilename, 5) <> ".xlsx" Then
        strFilename = strFilename & ".xlsx"
    End If
    strFilename = strFolder & strFilename

    strExportTemplate = strFolder & "New Export Template.xlsx"

    With xlApp
        .Visible = False
        Set WB = .Workbooks.Open(strExportTemplate)
        .Workbooks(1).SaveAs (strFilename)
    End With

    Me.txtCurrProfile = Null [red]'if this is text then you can set it to =""(empty string)[/red]
    DoEvents
[red]
    '************************************
    'strStart = Me.txtStart ' duplicate
    'Set qdf = DB.QueryDefs("Summary of fx and oh")
    'For Each prm In qdf.Parameters
    'prm.Value = Eval(prm.Name)
    'Next prm
    'Set RSBudget = qdf.OpenRecordset
    '************************************remove [/red]

    [blue]'Changed
    strSQL = "Select * From [Summary of fx and oh] Where [Job Type] = '" & Me.txtJobType & "';"
    Set RSBudget = DB.OpenRecordset(strSQL)[/blue]
[red]'You could create a query using the form as criteria then open the recordset in the same manner
'Set RSBudget = DB.OpenRecordset("MyQueryName")[/red]

    xlApp.Worksheets(1).Cells(1, 2) = strStart
    RSBudget.MoveFirst
    strCC = RSBudget("Act Cost Center") & ""
    strPosition = RSBudget("Position Number") & ""
    introw = 4
    Do Until RSBudget.EOF
        Me.txtCurrProfile = "Exporting Position " & strPosition & " ..."
        DoEvents
        With xlApp
            .Cells(introw, 1) = RSBudget("Position Number")
            .Cells(introw, 2) = RSBudget("Job Title")
            .Cells(introw, 3) = RSBudget("Taleo Number")
            .Cells(introw, 4) = RSBudget("Staffing Status")
            .Cells(introw, 5) = RSBudget("Act Cost Center")
            .Cells(introw, 6) = RSBudget("Job Type")
            .Cells(introw, 7) = RSBudget("RLT Member")
            .Cells(introw, 8) = RSBudget("Business Need")
            .Cells(introw, 13) = Nz(RSBudget("B1"), 0)
            .Cells(introw, 14) = Nz(RSBudget("M1"), 0)
            .Cells(introw, 16) = Nz(RSBudget("F1a"), 0)
            .Cells(introw, 17) = Nz(RSBudget("F1b"), 0)
            .Cells(introw, 15).Formula = "=(+" & .Cells(introw, 16) & "+" & .Cells(introw, 17) & ")"
            .Cells(introw, 18) = Nz(RSBudget("B2"), 0)
            .Cells(introw, 19) = Nz(RSBudget("M2"), 0)
            .Cells(introw, 21) = Nz(RSBudget("F2a"), 0)
            .Cells(introw, 22) = Nz(RSBudget("F2b"), 0)
            .Cells(introw, 20).Formula = "=(+" & .Cells(introw, 21) & "+" & .Cells(introw, 22) & ")"
            .Cells(introw, 23) = Nz(RSBudget("B3"), 0)
            .Cells(introw, 24) = Nz(RSBudget("M3"), 0)
            .Cells(introw, 26) = Nz(RSBudget("F3a"), 0)
            .Cells(introw, 27) = Nz(RSBudget("F3b"), 0)
            .Cells(introw, 25).Formula = "=(+" & .Cells(introw, 26) & "+" & .Cells(introw, 27) & ")"
            .Cells(introw, 28).Formula = "=(+" & .Cells(introw, 13) & "+" & .Cells(introw, 18) & "+" & .Cells(introw, 23) & ")"
            .Cells(introw, 29).Formula = "=(+" & .Cells(introw, 14) & "+" & .Cells(introw, 19) & "+" & .Cells(introw, 24) & ")"
            .Cells(introw, 30).Formula = "=(+" & .Cells(introw, 15) & "+" & .Cells(introw, 20) & "+" & .Cells(introw, 25) & ")"
            .Cells(introw, 31) = Nz(RSBudget("B4"), 0)
            .Cells(introw, 32) = Nz(RSBudget("M4"), 0)
            .Cells(introw, 34) = Nz(RSBudget("F4a"), 0)
            .Cells(introw, 35) = Nz(RSBudget("F4b"), 0)
            .Cells(introw, 33).Formula = "=(+" & .Cells(introw, 34) & "+" & .Cells(introw, 35) & ")"
            .Cells(introw, 36) = Nz(RSBudget("B5"), 0)
            .Cells(introw, 37) = Nz(RSBudget("M5"), 0)
            .Cells(introw, 39) = Nz(RSBudget("F5a"), 0)
            .Cells(introw, 40) = Nz(RSBudget("F5b"), 0)
            .Cells(introw, 38).Formula = "=(+" & .Cells(introw, 39) & "+" & .Cells(introw, 40) & ")"
            .Cells(introw, 41) = Nz(RSBudget("B6"), 0)
            .Cells(introw, 42) = Nz(RSBudget("M6"), 0)
            .Cells(introw, 44) = Nz(RSBudget("F6a"), 0)
            .Cells(introw, 45) = Nz(RSBudget("F6b"), 0)
            .Cells(introw, 43).Formula = "=(+" & .Cells(introw, 44) & "+" & .Cells(introw, 45) & ")"
            .Cells(introw, 46).Formula = "=(+" & .Cells(introw, 31) & "+" & .Cells(introw, 36) & "+" & .Cells(introw, 41) & ")"
            .Cells(introw, 47).Formula = "=(+" & .Cells(introw, 32) & "+" & .Cells(introw, 37) & "+" & .Cells(introw, 42) & ")"
            .Cells(introw, 48).Formula = "=(+" & .Cells(introw, 33) & "+" & .Cells(introw, 38) & "+" & .Cells(introw, 43) & ")"
            .Cells(introw, 49) = Nz(RSBudget("B7"), 0)
            .Cells(introw, 50) = Nz(RSBudget("M7"), 0)
            .Cells(introw, 52) = Nz(RSBudget("F7a"), 0)
            .Cells(introw, 53) = Nz(RSBudget("F7b"), 0)
            .Cells(introw, 51).Formula = "=(+" & .Cells(introw, 52) & "+" & .Cells(introw, 53) & ")"
            .Cells(introw, 54) = Nz(RSBudget("B8"), 0)
            .Cells(introw, 55) = Nz(RSBudget("M8"), 0)
            .Cells(introw, 57) = Nz(RSBudget("F8a"), 0)
            .Cells(introw, 58) = Nz(RSBudget("F8b"), 0)
            .Cells(introw, 56).Formula = "=(+" & .Cells(introw, 57) & "+" & .Cells(introw, 58) & ")"
            .Cells(introw, 59) = Nz(RSBudget("B9"), 0)
            .Cells(introw, 60) = Nz(RSBudget("M9"), 0)
            .Cells(introw, 62) = Nz(RSBudget("F9a"), 0)
            .Cells(introw, 63) = Nz(RSBudget("F9b"), 0)
            .Cells(introw, 61).Formula = "=(+" & .Cells(introw, 62) & "+" & .Cells(introw, 63) & ")"
            .Cells(introw, 64).Formula = "=(+" & .Cells(introw, 49) & "+" & .Cells(introw, 54) & "+" & .Cells(introw, 59) & ")"
            .Cells(introw, 65).Formula = "=(+" & .Cells(introw, 50) & "+" & .Cells(introw, 55) & "+" & .Cells(introw, 60) & ")"
            .Cells(introw, 66).Formula = "=(+" & .Cells(introw, 51) & "+" & .Cells(introw, 56) & "+" & .Cells(introw, 61) & ")"
            .Cells(introw, 67) = Nz(RSBudget("B10"), 0)
            .Cells(introw, 68) = Nz(RSBudget("M10"), 0)
            .Cells(introw, 70) = Nz(RSBudget("F10a"), 0)
            .Cells(introw, 71) = Nz(RSBudget("F10b"), 0)
            .Cells(introw, 69).Formula = "=(+" & .Cells(introw, 70) & "+" & .Cells(introw, 71) & ")"
            .Cells(introw, 72) = Nz(RSBudget("B11"), 0)
            .Cells(introw, 73) = Nz(RSBudget("M11"), 0)
            .Cells(introw, 75) = Nz(RSBudget("F11a"), 0)
            .Cells(introw, 76) = Nz(RSBudget("F11b"), 0)
            .Cells(introw, 74).Formula = "=(+" & .Cells(introw, 75) & "+" & .Cells(introw, 76) & ")"
            .Cells(introw, 77) = Nz(RSBudget("B12"), 0)
            .Cells(introw, 78) = Nz(RSBudget("M12"), 0)
            .Cells(introw, 80) = Nz(RSBudget("F12a"), 0)
            .Cells(introw, 81) = Nz(RSBudget("F12b"), 0)
            .Cells(introw, 79).Formula = "=(+" & .Cells(introw, 80) & "+" & .Cells(introw, 81) & ")"
            .Cells(introw, 82).Formula = "=(+" & .Cells(introw, 67) & "+" & .Cells(introw, 72) & "+" & .Cells(introw, 77) & ")"
            .Cells(introw, 83).Formula = "=(+" & .Cells(introw, 68) & "+" & .Cells(introw, 73) & "+" & .Cells(introw, 78) & ")"
            .Cells(introw, 84).Formula = "=(+" & .Cells(introw, 69) & "+" & .Cells(introw, 74) & "+" & .Cells(introw, 79) & ")"
            .Cells(introw, 85).Formula = "=(+" & .Cells(introw, 28) & "+" & .Cells(introw, 46) & "+" & .Cells(introw, 64) & "+" & .Cells(introw, 82) & ")"
            .Cells(introw, 86).Formula = "=(+" & .Cells(introw, 29) & "+" & .Cells(introw, 47) & "+" & .Cells(introw, 65) & "+" & .Cells(introw, 83) & ")"
            .Cells(introw, 87).Formula = "=(+" & .Cells(introw, 30) & "+" & .Cells(introw, 48) & "+" & .Cells(introw, 66) & "+" & .Cells(introw, 84) & ")"
            .Cells(introw, 88).Formula = "=(+" & .Cells(introw, 86) & "+" & .Cells(introw, 87) & ")"
            If Len(strCC) <> 0 Then
                blnCC = CC(strCC, introw, WB, xlApp)
            End If
        End With
        introw = introw + 1
        RSBudget.MoveNext
        If RSBudget.EOF Then Exit Do
        strCC = RSBudget("Act Cost Center") & ""
        strPosition = RSBudget("Position Number") & ""
    Loop
    introw = introw + 2
    With xlApp
        .Cells(introw, 1) = "Totals:"
        .Cells(introw, 6).Formula = "=SUBTOTAL(3, F4:" & .Cells((introw - 2), 11).Address(False, False) & ")"
        .Cells(introw, 13).Formula = "=SUBTOTAL(9, M4:" & .Cells((introw - 2), 11).Address(False, False) & ")"
        .Cells(introw, 13).Select
        .Selection.Copy
        .Range("N" & introw & ":CJ" & introw).Select
        .ActiveSheet.Paste
        .Range("M4:CJ" & introw).Select
        .Selection.NumberFormat = "0;[Red]0"
        .Application.Goto Reference:="R4C2"
        .ActiveWindow.FreezePanes = True
        .Workbooks(1).Save
        .Workbooks(1).Close
    End With

    xlApp.Quit
    RSBudget.Close
    DB.Close
    Set xlApp = Nothing
    Set RSSpecialist = Nothing
    Set DB = Nothing
    Me.txtCurrProfile = "Done!"
    DoEvents

End Sub

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

Delindan · Jan 25, 2012

Thanks...I tried this but without the dao.querydef and dao.parameter the date input used in the query doesn't work. I tried this:

Set DB = CurrentDb
Set qdf = DB.QueryDefs("Select * From [Summary of fx and oh] Where [Job Type] = '" & Me.txtJobtype & "';")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RSBudget = qdf.OpenRecordset

But of course it didn't like it. run time error 3265...item not in collection. Any ideas?

thanks again

Delindan · Jan 26, 2012

I figured out how to do it!! Here's what I have:

Set DB = CurrentDb
Set qdfnew = DB.CreateQueryDef("", "Select * From [Summary of fx and oh] Where [Job Type] = Forms![Dollar Export with fx and oh]![txtJobType]")
For Each prm In qdfnew.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set RSBudget = qdfnew.OpenRecordset

Only one problem...when there are either no records that fit the criterea it bombs because there are no records. What is a good way of dealing with that? Also, if I want to give the user the option (which they may or may not choose to do) of putting a filter on a couple of fields, how would I do that?

Thanks!

MazeWorX · Jan 27, 2012

check the recordset count

RSBudget.MoveLast
RSBudget.MoveFirst

If RSBudget.RecordCount = 0 Then
MsgBox "No records found"
Exit Sub
Else
'Execute code
End If

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

Delindan · Jan 27, 2012

That works great! Now, one more scenario...how would you filter on multiple values? For example, if they want to run the report for new and transfer positions. It would be nice if they could input New, Transfer in the text box but how would you translate that onto your filter line?

Thanks!

MazeWorX · Jan 27, 2012

use the AND operator to include 2 or more criteria as well you could get all records with that meet 2 criteria with OR , or a combination of both depending on the scenario

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

filter query from form to export to excel

Delindan

MIS

MazeWorX

IS-IT--Management

Delindan

MIS

Delindan

MIS

MazeWorX

IS-IT--Management

Delindan

MIS

MazeWorX

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor