none
How to set Filter to Data From a Recordset with vba RRS feed

  • Question

  • Hello 

    I found this code on line and it works and adds the records to the template. However it is not filter records based on my main form.  I have done lots of reading but am not sure how to change the record set to a filter to the subforms query. Any help would be appreciated. 

    Function Print3Year()


    Dim lngColumn As Long
    Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

    blnEXCEL = False
    ' Replace True with False if you do not want the first row of
    ' the worksheet to be a header row (the names of the fields
    ' from the recordset)
    blnHeaderRow = True

    ' Establish an EXCEL application object
    On Error Resume Next
    Set xlx = GetObject(, "Excel.Application")
    If Err.Number <> 0 Then
          Set xlx = CreateObject("Excel.Application")
          blnEXCEL = True
    End If
    Err.Clear
    On Error GoTo 0

    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    xlx.Visible = True

    ' Replace C:\Filename.xls with the actual path and filename
    ' of the EXCEL file into which you will write the data
    Set xlw = xlx.Workbooks.Open("O:\CHI-DET-MIN_HUB\Tech Group\Regional Property Performance Module\Forms\AFS Review Sheet.xltm")

    ' Replace WorksheetName with the actual name of the worksheet
    ' in the EXCEL file
    ' (note that the worksheet must already be in the EXCEL file)
    Set xls = xlw.Worksheets("P&LThreeYear ")
    ' Replace A1 with the cell reference into which the first data value
    ' is to be written
    Set xlc = xls.Range("A1") ' this is the first cell into which data go

    Set dbs = CurrentDb()

    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    Set rst = dbs.OpenRecordset("q_P&L Three YearDetroit", dbOpenDynaset, dbReadOnly)
    'Create filtered recordset

    'Begin transfer
    If rst.EOF = False And rst.BOF = False Then
          rst.MoveFirst

          If blnHeaderRow = True Then
                For lngColumn = 0 To rst.Fields.Count - 1
                      xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
                Next lngColumn
                Set xlc = xlc.Offset(1, 0)
          End If

          ' write data to worksheet
          Do While rst.EOF = False
                For lngColumn = 0 To rst.Fields.Count - 1
                      xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
                Next lngColumn
                rst.MoveNext
                Set xlc = xlc.Offset(1, 0)
          Loop
    End If

    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing
    ' Close the EXCEL file while saving the file, and clean up the EXCEL objects
    Set xlc = Nothing
    Set xls = Nothing
    xlw.Close True   ' close the EXCEL file and save the new data
    Set xlw = Nothing
    If blnEXCEL = True Then xlx.Quit
    Set xlx = Nothing
    End Function

    Tuesday, March 7, 2017 10:52 PM

All replies

  • Hi Lavenderchan,

    According to your description,I think that you want to filter your recordset before inserting it to template,right?

    You could using Recordset.Filter to filter the recordset.

    The code is like this

    Set rst = dbs.OpenRecordset("Select T.CITY,T.COST From TestTable T", dbOpenDynaset, dbReadOnly)
    
    rst.Filter = "Cost>8000 Or Cost<3000"
    
    Set rst = rst.OpenRecordset

    Refer to:Recordset.Filter

    Best regards,

    Edward


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 8, 2017 2:51 AM
  • So how do I set rst.filter to equal the textbox that filters the main form.  The filter is not carrying over to the query.  I've looked for examples but nothing is working.

    Thanks,

    Keri

    Tuesday, March 14, 2017 8:49 PM
  • >>but am not sure how to change the record set to a filter to the subforms query
    >>but am not sure how to change the record set to a filter to the subforms query

    Do you want to filter subform by the textbox in main form? If so, I suggest you try below code:

    Private Sub Command23_Click()
    Me.UserNote.SetFocus
    Debug.Print Me.UserNote.Text
    f = Me.UserNote.Text
    Me.tblUserSubform.SetFocus
    Me.tblUserSubform.Form.Filter = "[UserNote] = '" & f & "'"
    Me.tblUserSubform.Form.FilterOn = True
    Me.tblUserSubform.Requery
    End Sub

    If it did not work for, it would be helpful if you could share us a screen shot about your Access form and the expected result you want. A demo database would be much helpful. You could share it through OneDrive.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, March 15, 2017 5:50 AM