Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009
    Posts
    3

    Unanswered: Improving VBA code involving multiple toggle buttons

    I have been using Access for a few months now, and I am getting more involved with using VBA in place of macros. I have a database with a table the contains information on my companies computers. Information like the network name, what processor/memory, etc. I made a form (frmHardware) to filter the results form a query (qryHWList, that contains some information about the systems and linked information) using combo boxes and toggle buttons.

    Each toggle button sits above the corresponding combo box, which lists the possible choices for that part. There are two command buttons, one "AND Filter" and one "OR Filter". The code I have written works just fine, but I am thinking there is a better way to loop through and get the states of each toggle button to build the subform filter.

    Code:
    Option Compare Database
    Option Explicit
    Public Sub Filter_Builder(OpFnt As String, iTrim As Integer)
        Dim HWQry As String
        Dim HWFilter As String
        Dim HWTrim As String
        HWFilter = ""
        HWQry = "qryHWList"
        HWTrim = ""
            If Me.tglMemory = True Then
                HWFilter = HWFilter & HWQry & ".Mem = '" & cboMemSize & "' " & OpFnt & " "
            Else
                HWFilter = HWFilter
            End If
            If Me.tglHD = True Then
                HWFilter = HWFilter & HWQry & ".HD = '" & cboHDSize & "' " & OpFnt & " "
            Else
                HWFilter = HWFilter
            End If
            If Me.tglCPU = True Then
                HWFilter = HWFilter & HWQry & ".CPU = '" & cboCPUType & "' " & OpFnt & " "
            Else
                HWFilter = HWFilter
            End If
            If Me.tglOS = True Then
                HWFilter = HWFilter & HWQry & ".OS = '" & cboOS & "' " & OpFnt & " "
            Else
                HWFilter = HWFilter
            End If
        HWTrim = HWFilter
        HWFilter = Left(HWTrim, Len(HWTrim) - iTrim)
        Forms![frmHardware]![frmHardwareSub].Form.Filter = HWFilter
        Forms![frmHardware]![frmHardwareSub].Form.FilterOn = True
    End Sub
    Private Sub cmdAND_Click()
        Dim OpFnt As String
        Dim iTrim As Integer
        OpFnt = "AND"
        iTrim = 5
        Call Filter_Builder(OpFnt, iTrim)
    End Sub
    Private Sub cmdOR_Click()
        Dim OpFnt As String
        Dim iTrim As Integer
        OpFnt = "OR"
        iTrim = 4
        Call Filter_Builder(OpFnt, iTrim)
    End Sub
    Not being terribly experienced with VBA, or coding in general, does anyone see a way to do what I need to without having to resort to an If statement for each toggle?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Nothing wrong with your code, but you could drop the Else parts as HWFilter = HWFilter does nothing.

    Have a nice day!

  3. #3
    Join Date
    Feb 2009
    Posts
    3
    Thanks.

    I've now run into another problem. I have added a button to print a report based on the filter for the form. When I use this code:

    Code:
    Private Sub cmdOpenReport_Click()
        DoCmd.OpenReport "rptHardware", A_PREVIEW, , Forms![frmHardware]![frmHardwareSub].Form.Filter
    End Sub
    It returns all the results from the qry, when it should only return the filtered results. I have tried to do it both in the Filter and Where conditions, but both result in the same thing.

    EDIT: It now suddenly works. Nothing was changed, it just works.
    Last edited by fusionpit; 04-10-09 at 15:58.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I love it when problems solve themselves
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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