VBA coding help & Form Linking

[BlueIshDan]

If you would like to take a stab at coding your own query generation, here is an example of my function used in my access project that does exactly what you are designing:

Public Function GenerateQuery() As String

    Dim sql As String
    Dim var_Field As Variant
    Dim var_Show As Variant
    Dim msg As String
    Dim where_clause As String
    Dim field_count As Integer
    
    field_count = 0
    
    For Each var_Show In show_list
        If Not var_Show Is Nothing Then
            If Not IsNull(var_Show.Value) Then
                If var_Show.Value = -1 Then
                    If sql = "" Then: sql = "SELECT "
                    sql = sql & show_list(var_Show.Tag).ControlTipText & " AS '" & Left(label_list(var_Show.Tag).Caption, Len(label_list(var_Show.Tag).Caption) - 1) & "',"
                    current_fields(field_count) = CInt(var_Show.Tag)
                    field_count = field_count + 1
                End If
            End If
        End If
    Next

    If field_count > 0 Then
        If sql <> "" Then
            sql = Mid(sql, 1, Len(sql) - 1)
        Else: sql = "SELECT *"
        End If
        
        sql = sql & " FROM QueryBuilderMergedData"
        
        For Each var_Field In filter_list
            var_Field.SetFocus
            If Not IsNull(var_Field.Value) Then
                If var_Field.Value <> "" Then
                    If where_clause = "" Then: where_clause = " WHERE"
                    If like_list(var_Field.Tag).Value = -1 Then
                        where_clause = where_clause & " CStr(" & show_list(var_Field.Tag).ControlTipText & ") LIKE '*" & var_Field.Value & "*' AND"
                    Else
                        where_clause = where_clause & " CStr(" & show_list(var_Field.Tag).ControlTipText & ") = '" & var_Field.Value & "' AND"
                    End If
                End If
            End If
        Next
        
        If where_clause <> "" Then
            where_clause = Mid(where_clause, 1, Len(where_clause) - 3)
            sql = sql & where_clause
        End If
        
        While field_count <= 9
            current_fields(field_count) = "10"
            field_count = field_count + 1
        Wend
        
         GenerateQuery = sql
         
    End If
    
End Function

 

[Cronk]

The art in debugging code that is not doing what you want is to find what is actually the value of the variable, in your case strWhere

You can set a break point by putting the cursor on the End Function line by pressing F9 (that's function 9). Click your search button and the code will stop running at the break point.

You can see what is in the variable by typing in the Immediate window at the bottom of the code window (press Ctrl +G to bring it up) and type
? strWhere

You will then be able to see the SQL that you are feeding to the subform data source. I don't see any "WHERE" being included in the SQL but there might be other problems.

You can paste the SQL into a query window and let Access show you any error.