Results 1 to 4 of 4
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

    Help Cleaning Up My Code


    Hello all. I have this set of code and I had to do different sql queries per what is selected on the form. Most of these queries only have one change to the WHERE or ORDER BY. Is there a simpler way to write this so there isn't so much to look through? Any advice or resources is welcome. Thank you.

    Code:
    Dim lngPK As LongDim strWhere As Variant
    
    
    Private Sub ActiveRecords_AfterUpdate()
    lngPK = Me.EquipmentID
    
    
        If Me.cboFilterBy = 1 Then
            If Me.ActiveRecords = True Then
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE Equipment.Inactive = False" & _
                " ORDER BY Equipment.EquipmentID"
            Else
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " ORDER BY Equipment.EquipmentID"
            End If
        End If
        
        If Me.cboFilterBy = 2 Then
            If Me.ActiveRecords = True Then
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 1 AND Equipment.Inactive = False" & _
                " ORDER BY Equipment.EquipNumber"
            Else
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 1" & _
                " ORDER BY Equipment.EquipNumber"
            End If
        End If
        
        If Me.cboFilterBy = 3 Then
            If Me.ActiveRecords = True Then
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 2 AND Equipment.Inactive = False" & _
                " ORDER BY Equipment.EquipNumber"
            Else
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 2" & _
                " ORDER BY Equipment.EquipNumber"
            End If
        End If
        
        If Me.cboFilterBy = 4 Then
            If Me.ActiveRecords = True Then
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 3 AND Equipment.Inactive = False" & _
                " ORDER BY Equipment.EquipNumber"
            Else
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE EquipmentType.EquipmentTypeID = 3" & _
                " ORDER BY Equipment.EquipNumber"
            End If
        End If
        
        If Me.cboFilterBy = 5 Then
            If Me.ActiveRecords = True Then
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " WHERE Equipment.Inactive = False" & _
                " ORDER BY Equipment.EquipNumber"
            Else
                Me.RecordSource = "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive" & _
                " FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID" & _
                " ORDER BY Equipment.EquipNumber"
            End If
        End If
        
    'From http://www.baldyweb.com/Requery.htm
         With Me.RecordsetClone
              .FindFirst "EquipmentID= " & lngPK
              If .NoMatch Then
                   MsgBox "Record not found!", vbCritical
              Else
                   Me.Bookmark = .Bookmark
              End If
         End With
    
    
    Me.txtFilter.SetFocus
        
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If the only differences are in the WHERE or ORDER BY clauses, why change the RecordSource property?

    Build the criteria and set the Filter and OrderBy properties. Review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    My $0.02........

    Consider setting the form record source to
    Code:
    "SELECT Equipment.EquipmentID, Equipment.EquipNumber, Equipment.EquipmentType_ID, EquipmentType.EquipType, Equipment.Inactive  FROM EquipmentType INNER JOIN Equipment ON EquipmentType.EquipmentTypeID = Equipment.EquipmentType_ID"
    Then try the modified code:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub ActiveRecords_AfterUpdate()
        Dim iFilterBy As Integer
        '    lngPK = Me.EquipmentID
    
        iFilterBy = Me.cboFilterBy
    
        Select Case iFilterBy
            Case 1
                If Me.ActiveRecords = True Then
                    Me.Filter = "Equipment.Inactive = False"
                    Me.FilterOn = True
                Else
                    Me.Filter = ""
                    Me.FilterOn = False
                End If
    
                'set order
                '            Me.OrderBy = "Equipment.EquipmentID"
                '            Me.OrderByOn = True
    
            Case 2
                If Me.ActiveRecords = True Then
                    Me.Filter = "EquipmentType.EquipmentTypeID = 1 AND Equipment.Inactive = False"
                    Me.FilterOn = True
                Else
                    Me.Filter = "EquipmentType.EquipmentTypeID = 1"
                    Me.FilterOn = True
                End If
    
                'set order
                '            Me.OrderBy = "Equipment.EquipNumber"
                '            Me.OrderByOn = True
    
            Case 3
                If Me.ActiveRecords = True Then
                    Me.Filter = "EquipmentType.EquipmentTypeID = 2 AND Equipment.Inactive = False"
                    Me.FilterOn = True
                Else
                    Me.Filter = "EquipmentType.EquipmentTypeID = 2"
                    Me.FilterOn = True
                End If
    
                'set order
                '            Me.OrderBy = "Equipment.EquipNumber"
                '            Me.OrderByOn = True
    
            Case 4
                If Me.ActiveRecords = True Then
                    Me.Filter = "EquipmentType.EquipmentTypeID = 3 AND Equipment.Inactive = False"
                    Me.FilterOn = True
                Else
                    Me.Filter = "EquipmentType.EquipmentTypeID = 3"
                    Me.FilterOn = True
                End If
    
    
                'set order
                '            Me.OrderBy = "Equipment.EquipNumber"
                '            Me.OrderByOn = True
    
            Case 5
                If Me.ActiveRecords = True Then
                    Me.Filter = "Equipment.Inactive = False"
                    Me.FilterOn = True
                Else
                    Me.Filter = ""
                    Me.FilterOn = False
                End If
    
                'set order
                '            Me.OrderBy = "Equipment.EquipNumber"
                '            Me.OrderByOn = True
    
        End Select
    
        'all of the cases had the same ORDER BY, so I moved it to after the SELECT CASE
        'set order
        Me.OrderBy = "Equipment.EquipNumber"
        Me.OrderByOn = True
    
    
        'From http://www.baldyweb.com/Requery.htm
        '            With Me.RecordsetClone
        '                .FindFirst "EquipmentID= " & lngPK
        '                If .NoMatch Then
        '                    MsgBox "Record not found!", vbCritical
        '                Else
        '                    Me.Bookmark = .Bookmark
        '                End If
        '            End With
    
        Me.txtFilter.SetFocus
    
    End Sub

  4. #4
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Quote Originally Posted by June7 View Post
    If the only differences are in the WHERE or ORDER BY clauses, why change the RecordSource property?

    Build the criteria and set the Filter and OrderBy properties. Review http://www.allenbrowne.com/ser-62.html
    Yep. That's exactly what I was looking for. Crazy enough, I'm doing that in another form. Guess I just couldn't see the forest through the trees here. Thank you!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help data cleaning
    By baronqueefington in forum Queries
    Replies: 1
    Last Post: 01-05-2016, 04:20 PM
  2. Cleaning up Queries
    By shifty in forum Queries
    Replies: 1
    Last Post: 04-26-2015, 06:00 PM
  3. Cleaning up a spreadsheet
    By tonydepo in forum Import/Export Data
    Replies: 16
    Last Post: 11-13-2012, 04:27 PM
  4. Cleaning up the alphabet
    By ducecoop in forum Access
    Replies: 4
    Last Post: 10-28-2010, 08:33 AM
  5. Cleaning Data
    By Sck in forum Queries
    Replies: 1
    Last Post: 07-22-2010, 12:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums