CODEWITHSUNDEEP
ms-accessvba
MAW74656
MAW74656

Reputation: 3549

Access vba modify query def to change columns displayed?

I'm using the CurrentDb.QueryDefs object to alter the sql in my query. This works well, until I change the select clause. I want the query to now show only the fields named in my new select clause. Instead, the column headers are still showing, but there are no values. I'm displaying the query in a subform.

How can I force the subform/query to only show the specified columns that can change on a button click?

REASON: This is an advanced search form where checkboxes represent each field, and the user can remove and add fields each time they search.

Upvotes: 0

Views: 5799

Answers (1)

downwitch
downwitch

Reputation: 1372

You can't requery, you have to refresh the subform's source object:

MySubformControl.SourceObject = ""
MySubformControl.SourceObject = "Query.MyQuery"

For testing, I created a table Table1 with fields Field1,...,Field4, then a form with 4 check boxes and a subform, then a query Query1 which the fields from Table1. Here is the code behind the form (I let Access name all my objects, so the subform is called Child8):

Private Sub Check0_AfterUpdate()
    Rewrite_Query
End Sub

Private Sub Check2_AfterUpdate()
    Rewrite_Query
End Sub

Private Sub Check4_AfterUpdate()
    Rewrite_Query
End Sub

Private Sub Check6_AfterUpdate()
    Rewrite_Query
End Sub

Private Sub Rewrite_Query()

    Dim qdf     As QueryDef
    Dim strSQL  As String

    Set qdf = CurrentDb.QueryDefs("Query1")
    If Check0.Value = True Then
        If Len(strSQL) > 0 Then strSQL = strSQL & ","
        strSQL = strSQL & "Field1"
    End If

    If Check2.Value = True Then
        If Len(strSQL) > 0 Then strSQL = strSQL & ","
        strSQL = strSQL & "Field2"
    End If

    If Check4.Value = True Then
        If Len(strSQL) > 0 Then strSQL = strSQL & ","
        strSQL = strSQL & "Field3"
    End If

    If Check6.Value = True Then
        If Len(strSQL) > 0 Then strSQL = strSQL & ","
        strSQL = strSQL & "Field4"
    End If

    strSQL = "SELECT " & strSQL & " FROM Table1"

    qdf.SQL = strSQL
    qdf.Close
    Set qdf = Nothing

    Child8.SourceObject = ""
    Child8.SourceObject = "Query.Query1"

End Sub

Upvotes: 2

Related Questions