Lotsa code; need help with efficiency!!

[usmcgrunt]

Ok, I have an unbound popup form with 3 option controls and 2 combo boxes. These controls are used to filter the records that are displayed in the main form once the user clicks a button control.



Here are descriptions of each control

[Option Control #1]OpnFilter:
'Div' value = 1
'Sub' value = 2

[Option Control #2]OpnStatus:
'All' value = 1
'Active' value = 2
'Inactive' value = 3

[Option Control #3]OpnSemester:
'Fall' value = 1
'Spring' value = 2
'Both' value = 3

[Combobox #1]cboDivSelect
value can be a number between 0 and 9

[Combobox #2]cboSubSelect
value can be an number between 0 and 64

Now here is the kicker. The code you are about to see works but man does it seem inefficient. For the life of me I cannot figure out how to decrease the amount of code an accomplish the same task. This is where I am look for some help.

I tried to use a SELECT CASE statement. Unfortunately, you can only have 1 test expression. Next I though that I might be able to sum of all the control's and then use the resulting number in a SELECT CASE statement. Alas, that did not filter the records properly.

This code is in the FORM_OPEN event of the main form. Hold on to your hats.

' ******************************************************************************
' Filters the form frmLessonCard based on filter criteria in form frmLCFilter.
' ******************************************************************************

Private Sub Form_Open(Cancel As Integer)
    
On Error GoTo ErrorHandler
        
    'DoCmd.MoveSize Parameters ([Right], [Down], [Width], [Height])
    DoCmd.MoveSize 2# * 1440, 1# * 1440, 9.9 * 1440, 7.9 * 1440
    
     'User selects all Divisions and all lesson cards
    If Forms!frmLCFilter.cboDivSelect = 0 And Forms!frmLCFilter.opnStatus = 1 Then
        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter;"
        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter ORDER BY qryLCFilter.LNumber;"
    Else
        'User selects all Divisions and only the ACTIVE Lesson Cards
        If Forms!frmLCFilter.cboDivSelect = 0 And Forms!frmLCFilter.opnStatus = 2 Then
            Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE Inactive = False"
            Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE Inactive = False ORDER BY LNumber;"
        Else
            'User selects all Divisions and only the INACTIVE Lesson Cards
            If Forms!frmLCFilter.cboDivSelect = 0 And Forms!frmLCFilter.opnStatus = 3 Then
                Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE Inactive = True"
                Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE Inactive = True ORDER BY LNumber;"
            Else
                'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for the FALL SEMESTER
                If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 1 And Forms!frmLCFilter.opnSemester = 1 Then
                    Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " And OFECSemester = 1"
                    Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " And OFECSemester = 1 ORDER BY LNumber;"
                Else
                    'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for the FALL SEMESTER
                    If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 2 And Forms!frmLCFilter.opnSemester = 1 Then
                        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False And OFECSemester = 1"
                        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False And OFECSemester = 1 ORDER BY LNumber;"
                    Else
                        'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for the FALL SEMESTER
                        If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 3 And Forms!frmLCFilter.opnSemester = 1 Then
                            Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True And OFECSemester = 1"
                            Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True And OFECSemester = 1 ORDER BY LNumber;"
                        Else
                            'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for the SPRING SEMESTER
                            If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 1 And Forms!frmLCFilter.opnSemester = 2 Then
                                Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " And OFECSemester = 2"
                                Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " And OFECSemester = 2 ORDER BY LNumber;"
                            Else
                                'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for the SPRING SEMESTER
                                If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 2 And Forms!frmLCFilter.opnSemester = 2 Then
                                    Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False And OFECSemester = 2"
                                    Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False And OFECSemester = 2 ORDER BY LNumber;"
                                Else
                                    'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for the SPRING SEMESTER
                                    If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 3 And Forms!frmLCFilter.opnSemester = 2 Then
                                        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True And OFECSemester = 2"
                                        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True And OFECSemester = 2 ORDER BY LNumber;"
                                    Else
                                        'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for ALL SEMESTERS
                                        If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 1 And Forms!frmLCFilter.opnSemester = 3 Then
                                            Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & ""
                                            Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " ORDER BY LNumber;"
                                        Else
                                            'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for ALL SEMESTERS
                                            If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 2 And Forms!frmLCFilter.opnSemester = 3 Then
                                                Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False"
                                                Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False ORDER BY LNumber;"
                                            Else
                                                'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for ALL SEMESTERS
                                                If Forms!frmLCFilter.cboDivSelect = 6 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 3 And Forms!frmLCFilter.opnSemester = 3 Then
                                                    Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True"
                                                    Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True ORDER BY LNumber;"
                                                Else
                                                    'User selects all Sub-Courses within a specific Division and all Lesson Cards
                                                    If Forms!frmLCFilter.cboDivSelect > 0 And IsNull(Forms!frmLCFilter.cboSubSelect) And Forms!frmLCFilter.opnStatus = 1 Then
                                                        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & ""
                                                        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " ORDER BY LNumber;"
                                                    Else
                                                        'User selects all Sub-Courses within a specific Division and only the ACTIVE Lesson Cards
                                                        If Forms!frmLCFilter.cboDivSelect > 0 And IsNull(Forms!frmLCFilter.cboSubSelect) And Forms!frmLCFilter.opnStatus = 2 Then
                                                            Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = False"
                                                            Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = False ORDER BY LNumber;"
                                                        Else
                                                            'User selects all Sub-Courses within a specific Division and only the INACTIVE Lesson Cards
                                                            If Forms!frmLCFilter.cboDivSelect > 0 And IsNull(Forms!frmLCFilter.cboSubSelect) And Forms!frmLCFilter.opnStatus = 3 Then
                                                                Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = True"
                                                                Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = True ORDER BY LNumber;"
                                                            Else
                                                                'User selects all Sub-Courses within a specific Division and all Lesson Cards
                                                                If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect = 0 And Forms!frmLCFilter.opnStatus = 1 Then
                                                                    Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & ""
                                                                    Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " ORDER BY LNumber;"
                                                                Else
                                                                    'User selects all Sub-Courses within a specific Division and only the ACTIVE Lesson Cards
                                                                    If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect = 0 And Forms!frmLCFilter.opnStatus = 2 Then
                                                                        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = False"
                                                                        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = False ORDER BY LNumber;"
                                                                    Else
                                                                        'User selects all Sub-Courses within a specific Division and only the INACTIVE Lesson Cards
                                                                        If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect = 0 And Forms!frmLCFilter.opnStatus = 3 Then
                                                                            Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = True"
                                                                            Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & Forms!frmLCFilter.cboDivSelect & " AND Inactive = True ORDER BY LNumber;"
                                                                        Else
                                                                            'User select a specific Sub-course within a specific Division and all Lesson Cards
                                                                            If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 1 Then
                                                                                Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & ""
                                                                                Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " ORDER BY LNumber;"
                                                                            Else
                                                                                'User select a specific Sub-course within a specific Division and only the ACTIVE Lesson Cards
                                                                                If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 2 Then
                                                                                    Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False"
                                                                                    Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = False ORDER BY LNumber;"
                                                                                Else
                                                                                    'User select a specific Sub-course within a specific Division and only the INACTIVE Lesson Cards
                                                                                    If Forms!frmLCFilter.cboDivSelect > 0 And Forms!frmLCFilter.cboSubSelect > 0 And Forms!frmLCFilter.opnStatus = 3 Then
                                                                                        Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True"
                                                                                        Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & Forms!frmLCFilter.cboSubSelect & " AND Inactive = True ORDER BY LNumber;"
                                                                                    Else
                                                                                        'User selects all OFEC lesson cards
                                                                                        If Me.LDivision = 6 Then
                                                                                            Me.OFECSemester.Visible = True
                                                                                            Me.NonOFECObjLabel.Visible = False
                                                                                            Me.sfrmLCOFECStandardsLabel.Visible = True
                                                                                            Me.sfrmLCOFECStandards.Visible = True
                                                                                            Me.MiscOFECEdObjLabel.Visible = True
                                                                                            Me.MiscOFECEdObj.Visible = True
                                                                                            Me.sfrmLCLearnOutLabel.Visible = False
                                                                                            Me.sfrmLCLearnOut.Visible = False
                                                                                            Me.sfrmLCEdObjLabel.Visible = False
                                                                                            Me.sfrmLCEdObj.Visible = False
                                                                                        Else
                                                                                            Me.OFECSemester.Visible = False
                                                                                            Me.NonOFECObjLabel.Visible = True
                                                                                            Me.sfrmLCOFECStandardsLabel.Visible = False
                                                                                            Me.sfrmLCOFECStandards.Visible = False
                                                                                            Me.MiscOFECEdObjLabel.Visible = False
                                                                                            Me.MiscOFECEdObj.Visible = False
                                                                                            Me.sfrmLCLearnOutLabel.Visible = True
                                                                                            Me.sfrmLCLearnOut.Visible = True
                                                                                            Me.sfrmLCEdObjLabel.Visible = True
                                                                                            Me.sfrmLCEdObj.Visible = True
                                                                                        End If
                                                                                    End If
                                                                                End If
                                                                            End If
                                                                        End If
                                                                    End If
                                                                End If
                                                            End If
                                                        End If
                                                    End If
                                                End If
                                            End If
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                End If
            End If
        End If
    End If

Exit_ErrorHandler:
    Exit Sub

ErrorHandler:
    Select Case Err.Number
        Case 2501
            'The open form action was cancelled - ignore it - Do Nothing
        Case 2450
            'The form frmHiddenLogon is not open - ignore it - Do Nothing
        Case Else
            Call ErrorLog(Err.Number, Err.Description, Me.Name)
            Resume Exit_ErrorHandler
    End Select

End Sub

So if you have made it this far tell me what you think.

Thanks, Sean

[rpeare]

Is it running extremely slowly? I don't think I'd set things up as you have but I'm curious why you want to pare it down unless it's causing some problems with load times etc.

[usmcgrunt]

Is it running extremely slowly? I don't think I'd set things up as you have but I'm curious why you want to pare it down unless it's causing some problems with load times etc.

rpeare, no its running just fine. Really I'm just trying to get better at writing code and figured there must be a better and shorter way to write this.

Sean

[rpeare]

Well the only thing that you seem to be changing is your WHERE clause of your SQL statement so you could generate only the WHERE statement in your loops and attach it to the basic SELECT * FROM X query that runs both your combo box and your record source but in reality if it doesn't slow your application down and it functions as you want it to there's no reason to change it.

I'd worry about efficiency when it's affects the performance of your database.

[ssanfu]

Interesting bit of code. You might be able to use nested Select Case statements, but I don't see any way to reduce much code.

So, I have two suggestions.
1) Every time you have to get a value from a form, it takes more time - it is better (more efficient) to use a variable to store the value, then use the variable to do the comparisons. Especially with the number of form reads in the code.

2) Use the If...ElseIf...Else...End If statement variation. There is only one If statement rather than 21 (?) nested If statements.

Option Compare Database
Option Explicit
' ******************************************************************************
' Filters the form frmLessonCard based on filter criteria in form frmLCFilter.
' ******************************************************************************

Private Sub Form_Open(Cancel As Integer)
   On Error GoTo ErrorHandler

   Dim vDivSel As Integer
   Dim vSubSel As Integer
   Dim vStatus As Integer
   Dim vSemstr As Integer

   'put form data into variables
   vDivSel = Forms!frmLCFilter.cboDivSelect
   vSubSel = Forms!frmLCFilter.cboSubSelect
   vStatus = Forms!frmLCFilter.opnStatus
   vSemstr = Forms!frmLCFilter.opnSemester

   'DoCmd.MoveSize Parameters ([Right], [Down], [Width], [Height])
   DoCmd.MoveSize 2# * 1440, 1# * 1440, 9.9 * 1440, 7.9 * 1440

   'User selects all Divisions and all lesson cards
   If vDivSel = 0 And vStatus = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter;"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter ORDER BY qryLCFilter.LNumber;"
      'User selects all Divisions and only the ACTIVE Lesson Cards
   ElseIf vDivSel = 0 And vStatus = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE Inactive = False"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE Inactive = False ORDER BY LNumber;"
      'User selects all Divisions and only the INACTIVE Lesson Cards
   ElseIf vDivSel = 0 And vStatus = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE Inactive = True"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE Inactive = True ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for the FALL SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 1 And vSemstr = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " And OFECSemester = 1"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " And OFECSemester = 1 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for the FALL SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 2 And vSemstr = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False And OFECSemester = 1"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False And OFECSemester = 1 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for the FALL SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 3 And vSemstr = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True And OFECSemester = 1"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True And OFECSemester = 1 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for the SPRING SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 1 And vSemstr = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " And OFECSemester = 2"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " And OFECSemester = 2 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for the SPRING SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 2 And vSemstr = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False And OFECSemester = 2"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False And OFECSemester = 2 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for the SPRING SEMESTER
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 3 And vSemstr = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True And OFECSemester = 2"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True And OFECSemester = 2 ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and all Lesson Cards for ALL SEMESTERS
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 1 And vSemstr = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & ""
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the ACTIVE Lesson Cards for ALL SEMESTERS
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 2 And vSemstr = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False ORDER BY LNumber;"
      'User select a specific Sub-course within the MOS Training Division and only the INACTIVE Lesson Cards for ALL SEMESTERS
   ElseIf vDivSel = 6 And vSubSel > 0 And vStatus = 3 And vSemstr = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and all Lesson Cards
   ElseIf vDivSel > 0 And IsNull(vSubSel) And vStatus = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & ""
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and only the ACTIVE Lesson Cards
   ElseIf vDivSel > 0 And IsNull(vSubSel) And vStatus = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = False"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = False ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and only the INACTIVE Lesson Cards
   ElseIf vDivSel > 0 And IsNull(vSubSel) And vStatus = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = True"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = True ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and all Lesson Cards
   ElseIf vDivSel > 0 And vSubSel = 0 And vStatus = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & ""
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and only the ACTIVE Lesson Cards
   ElseIf vDivSel > 0 And vSubSel = 0 And vStatus = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = False"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = False ORDER BY LNumber;"
      'User selects all Sub-Courses within a specific Division and only the INACTIVE Lesson Cards
   ElseIf vDivSel > 0 And vSubSel = 0 And vStatus = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = True"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LDivision = " & vDivSel & " AND Inactive = True ORDER BY LNumber;"
      'User select a specific Sub-course within a specific Division and all Lesson Cards
   ElseIf vDivSel > 0 And vSubSel > 0 And vStatus = 1 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & ""
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " ORDER BY LNumber;"
      'User select a specific Sub-course within a specific Division and only the ACTIVE Lesson Cards
   ElseIf vDivSel > 0 And vSubSel > 0 And vStatus = 2 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = False ORDER BY LNumber;"
      'User select a specific Sub-course within a specific Division and only the INACTIVE Lesson Cards
   ElseIf vDivSel > 0 And vSubSel > 0 And vStatus = 3 Then
      Me.RecordSource = "SELECT qryLCFilter.* FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True"
      Me.lblLessonList.RowSource = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE LVolume = " & vSubSel & " AND Inactive = True ORDER BY LNumber;"
      'User selects all OFEC lesson cards
   Else
      If Me.LDivision = 6 Then
         Me.OFECSemester.Visible = True
         Me.NonOFECObjLabel.Visible = False
         Me.sfrmLCOFECStandardsLabel.Visible = True
         Me.sfrmLCOFECStandards.Visible = True
         Me.MiscOFECEdObjLabel.Visible = True
         Me.MiscOFECEdObj.Visible = True
         Me.sfrmLCLearnOutLabel.Visible = False
         Me.sfrmLCLearnOut.Visible = False
         Me.sfrmLCEdObjLabel.Visible = False
         Me.sfrmLCEdObj.Visible = False
      Else
         Me.OFECSemester.Visible = False
         Me.NonOFECObjLabel.Visible = True
         Me.sfrmLCOFECStandardsLabel.Visible = False
         Me.sfrmLCOFECStandards.Visible = False
         Me.MiscOFECEdObjLabel.Visible = False
         Me.MiscOFECEdObj.Visible = False
         Me.sfrmLCLearnOutLabel.Visible = True
         Me.sfrmLCLearnOut.Visible = True
         Me.sfrmLCEdObjLabel.Visible = True
         Me.sfrmLCEdObj.Visible = True
      End If
   End If

Exit_ErrorHandler:
   Exit Sub

ErrorHandler:
   Select Case Err.Number
      Case 2501
         'The open form action was cancelled - ignore it - Do Nothing
      Case 2450
         'The form frmHiddenLogon is not open - ignore it - Do Nothing
      Case Else
         Call ErrorLog(Err.Number, Err.Description, Me.Name)
         Resume Exit_ErrorHandler
   End Select

End Sub

I don't know if you will be able to see any change in execution speed, but it's worth a try.....

[michaelplogue]

As a suggestion to help trim down the code:

First, don't assign the record source and row source within each and every If/Else statement.

Create some string variables and use them to build your SQL statement.

 
Dim mySqlRecSel as String
Dim mySqlRecWhr as String
Dim mySqlRowSel as String
Dim mySqlRowWhr as String
Dim mySqlRowSrt as String
 
mySqlRecSel = "SELECT qryLCFilter.* FROM qryLCFilter WHERE "
mySqlRowSel = "SELECT qryLCFilter.LID, qryLCFilter.LNumber, qryLCFilter.LTitle FROM qryLCFilter WHERE "
mySqlRowSrt = " ORDER BY LNumber"

Then, in each of your if/else statements, you just assign the value for your "where" clause.

 
If whatever.......
mySqlRecWhr = "LDivision = " & vDivSel & " AND Inactive = False"
mySqlRowWhr = "LDivision = " & vDivSel & " AND Inactive = True"
Else 
etc, etc
ElseIf
etc, etc 
EndIf

Then, at the end of all your if/else statements, you then assign the rowsource and record source

 
Me.RecordSource = mySqlRecSel & mySqlRecWhr
Me.RowSource = mySqlRowSel & mySqlRowWhr & mySqlRowSrt

In short, use variables when building SQL strings, then tie everything together at the end.


You could probably use Select Case for these by doing the same thing - use variables to build the parts of your strings.