Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    47

    Unanswered: VBA code for filtering a set of records.

    Hi,

    I have a table (TblDiscount) which stores discount conditions for a commodity. The structure of TblPrice is:

    Commodity - String
    Pieces - Integer
    Discount - Integer

    I have a form to set discounts for a Commodity, based on volumes.

    Can someone help with a VBA code to 'filter' out only those Commodities so that I can set the discounts based on Pieces?

    Rgds,

  2. #2
    Join Date
    Mar 2004
    Posts
    118
    you want a filer that filters for the entries in a combo box?


    What i have is 2 combo boxes filtering a list box...
    You can add as many boxes as you want, you just need to replicate the code.

    Code:
    Public Function BuildFilter() As String
    
    'create a filter string to add onto strSQL after the ware clause
    Dim strSubSystemFilter As String
    Dim strItemTypeFilter As String
    
        If Not IsNull(Me.cbosubsystem) Then
            'pass the string in the filter box into a new string
            strSubSystemFilter = "[Sub System] =" & Me.cbosubsystem
        Else
            'if there is nothing set string to nothing
            strSubSystemFilter = ""
        End If
            
        If Not IsNull(Me.cboitemcode) Then
            strItemTypeFilter = "[Item Type Code] ='" & Me.cboitemcode & "'"
        Else
            strItemTypeFilter = ""
        End If
        
        
        If strSubSystemFilter = "" Then
            'if there is nothing in the string the buildfilter remains unchanged
            BuildFilter = BuildFilter
        Else
            If BuildFilter = "" Then
                'if the buildfilter is empty, set string as buildfilter
                BuildFilter = strSubSystemFilter
            Else
                'if the build filter is not empty, add the string to the buildfilter
                BuildFilter = BuildFilter & " AND " & strSubSystemFilter
            End If
        End If
        
        If strItemTypeFilter = "" Then
            BuildFilter = BuildFilter
        Else
            If BuildFilter = "" Then
                BuildFilter = strItemTypeFilter
            Else
                BuildFilter = BuildFilter & " AND " & strItemTypeFilter
            End If
        End If
            
    End Function
    
    Private Function CheckList(strControlName As String)
    
    'function to revert back to the previous buildfilter if the filter returns no records
    
    Me.List2.SetFocus
    Me.List2.Requery
    
    'count the number of rows in the list and if 0 then execute the statement
    If Me.List2.ListCount = 0 Then
    
        MsgBox "The filter you selected did not find any maching records."
        Me.Controls(strControlName) = Null
        'refresh list with new filter
        List2.RowSource = strSQL & " WHERE " & BuildFilter & ";"
        List2.Requery
             
    End If
    
    
    End Function
    
    
    Private Sub CboSubSystem_change()
    
    'at this stage the buildfilter is added to strSQL
    
        If Not IsNull(Me.cbosubsystem) Then
        
            'if the filter box is not empty add buildfilter
        
            BuildFilter
                    
            MsgBox strSQL & " WHERE " & BuildFilter
            'populate the list with updated SQL
            Me.List2.RowSource = strSQL & " WHERE " & BuildFilter & ";"
            Me.List2.Requery
        
        Else
            
            'if the filter box is empty force an update of buildfilter
            'this is to ensure that the filter updates
            'after the user decides to null one of the filters
            
            BuildFilter
            
            MsgBox strSQL & " WHERE " & BuildFilter
            Me.List2.RowSource = strSQL & " WHERE " & BuildFilter & ";"
            Me.List2.Requery
            
        End If
        
    CheckList Me.cbosubsystem.Name
    
    End Sub
    
    Private Sub cboitemcode_change()
    
        If Not IsNull(Me.cboitemcode) Then
        
            BuildFilter
            
            MsgBox strSQL & " WHERE " & BuildFilter
            Me.List2.RowSource = strSQL & " WHERE " & BuildFilter & ";"
            Me.List2.Requery
            
        Else
            
            BuildFilter
            
            MsgBox strSQL & " WHERE " & BuildFilter
            Me.List2.RowSource = strSQL & " WHERE " & BuildFilter & ";"
            Me.List2.Requery
            
        End If
        
    CheckList Me.cboitemcode.Name
    
    
    End Sub
    It's a bit messy I havent used it, so youd need to tidy it up.
    Last edited by YevSnow; 04-16-04 at 03:56.

  3. #3
    Join Date
    Feb 2004
    Location
    CT,USA
    Posts
    250

    Re: VBA code for filtering a set of records.

    Originally posted by Kashizzz
    Hi,

    I have a table (TblDiscount) which stores discount conditions for a commodity. The structure of TblPrice is:

    Commodity - String
    Pieces - Integer
    Discount - Integer

    I have a form to set discounts for a Commodity, based on volumes.

    Can someone help with a VBA code to 'filter' out only those Commodities so that I can set the discounts based on Pieces?

    Rgds,
    How many are you doing each time? Are you trying to fill a table that you will use later for calculation? Can you give an example or two?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •