Can someone explain this code to me?

[accessma]

I was hoping that someone could explain what this code is doing? It looks like it is filtering but my question is if any of the If statements are true, does it filter it so it will not be visible in a form? The form is supposed to show all active records but I think that some are failing some sort of test and they are not being selected. Thanks

Private Function BuildPredicate() As String
    Dim dbCurr As Database
    Dim rsProjList As Recordset
    
    Dim strSQL As String
    Dim strWhere As String
    Dim strGroupBy As String
    Dim strInValues As String
    
    'Initialize to ""
    strWhere = ""
    strSQL = "SELECT Project.ProjID FROM Project INNER JOIN (EmpProj INNER JOIN " & _
    "[Employee] ON EmpProj.EmployeeID = Employee.EmployeeID) ON Project.ProjID = " & _
         "EmpProj.ProjID GROUP BY Project.ProjID, "
   
   
   If Not IsNothing(Me.cmbContact) Then
        '..build the predicate
       strWhere = "([EmpProj].[EmployeeID] = " & Me.cmbContact & ") AND ([EmpProj].[DateOff] IS NULL) AND "
       strGroupBy = "[EmpProj].[EmployeeID], [EmpProj].[DateOff], "
   End If
    
    If Not IsNothing(Me.cmbDepartment) Then
        strWhere = strWhere & "[Employee].[DepartmentID] = " & Me.cmbDepartment & " AND "
       strGroupBy = strGroupBy & "[Employee].[DepartmentID], "
   End If
   
    If Not IsNothing(Me.cmbMGHGoalSelect) Then
        '..build the predicate
       strWhere = strWhere & "[MGHGoal] = " & Me.cmbMGHGoalSelect & " AND "
       strGroupBy = strGroupBy & "MGHGoal, "
   End If
   
   If Not IsNothing(Me.cmbDivisionGoal) Then
        '..build the predicate
       strWhere = strWhere & "([DivisionGoal] = " & Me.cmbDivisionGoal & ") AND "
       strGroupBy = strGroupBy & "DivisionGoal, "
   End If
    
    If Not IsNothing(Me.cmbPrimaryClient) Then
        '..build the predicate
       strWhere = strWhere & "([PrimaryClient]='" & Me.cmbPrimaryClient.Value & "') AND "
        strGroupBy = strGroupBy & "PrimaryClient, "
   End If
   
       If Not IsNothing(Me.cmbPrimaryClientDept) Then
        '..build the predicate
       strWhere = strWhere & "([PrimaryClientDept]='" & Me.cmbPrimaryClientDept.Value & "') AND "
        strGroupBy = strGroupBy & "PrimaryClientDept, "
   End If
   
    If Not IsNothing(Me.cmbLevelOfEffort) Then
        '..build the predicate
       strWhere = strWhere & "([LevelOfEffort] = " & Me.cmbLevelOfEffort & ") AND "
       strGroupBy = strGroupBy & "LevelOfEffort, "
   End If
   
    '..build the predicate
    strWhere = strWhere & "([projecttype] = " & Me.fraProjectType & ") AND "
    strGroupBy = strGroupBy & "projecttype, "

  
    If Me.chkIncludeCompleted = False Then
    '..build the predicate
       strWhere = strWhere & "([Completed] = False) AND "
       strGroupBy = strGroupBy & "Completed, "
    Else
    strWhere = strWhere & "([Completed] = False" & ") OR "
       strGroupBy = strGroupBy & "Completed, "
    strWhere = strWhere & "([Completed] = True" & ") AND "
       strGroupBy = strGroupBy & "Completed, "

    End If
   
    'Check to see that filter was built
    '
    strWhere = Left(strWhere, Len(strWhere) - 5)
    strGroupBy = Left(strGroupBy, Len(strGroupBy) - 2)
    strSQL = strSQL & strGroupBy & " HAVING " & strWhere
    '
    Set dbCurr = CurrentDb()
    Set rsProjList = dbCurr.OpenRecordset(strSQL, dbOpenSnapshot)
    '
    With rsProjList
        If .RecordCount = 0 Then
        MsgBox "There are no projects for the " & _
            "contact criteria you specified.", _
            vbInformation, gstrAppTitle
        DoCmd.Close acForm, Me.Name
        Exit Function
        Else
            .MoveFirst
            Do Until .EOF
              strInValues = strInValues & ![ProjID] & ", "
               .MoveNext
           Loop
       End If
    End With
    '
    strInValues = Left(strInValues, Len(strInValues) - 2)
    strSQL = "SELECT * FROM Project WHERE Project.ProjID In(" & strInValues & ")"
    DoCmd.OpenForm "frmProject"
    Forms![frmProject].RecordSource = strSQL
    '
    BuildPredicate = strWhere
    '
End Function

 

[Zaeed]

Yep, it filters the form.

Each of the If statements check if a control has a value in it, and if so adds that to an SQL statement. This statement then extracts all the project ID's that your criteria match. The final step is using those project ID's to extract all project information with matching ID's and filling a form.

 

[missinglinq]

Since this is an Access forum, I assume you're doing this in Access. IsNothing() is, I believe, a Visual Basic function, not an Access VBA function. You might try using IsNull() instead.

 

[accessma]

Well I thought that was the case. So I made an entry in every field in the table it pulls from and got no more records loaded to the form than before I filled in the blanks. I suppose if I commented out all the if statements that might work. What is your take on that. Also might try is null as well. The goal is to show all records in the table with no restrictions.

Yes I am doing this in Access. I inherited this from someone else so it learn as you go.

 

[accessma]

I tried commenting out the If statements and changing the IsNothing to IsNull with no luck. Going to post the FE and BE to see if anyone sees something. Hope so, I gotta have it fixed by Friday morning. All help greatly appreciated. The form the code is in is called frmSelectProject. The table is called Project.