Is there a way to specify what fields to show in a query from a form?

[offie]

I have a huge amount of data and fields.


I want to be able to search through this a have the user specify what what fields that they want to see in the resulting query.
Is there a way to remotely press that "Show" button or something similar?

Thanks!

[JoeM]

What you can do is to use your form and VBA to build the SQL code of your query.

If you go to any query, and change the view to SQL View, this returns the SQL code of the query. This is what you would need to build with VBA. Once you build the SQL code in a String in VBA, you can assign that SQL code to an existing query like this:

CurrentDb.QueryDefs("QueryName").SQL = mySQLString

where "QueryName" is the name of the existing query you are applying the SQL code to
and mySQLString is the SQL code you are building in VBA.

You can then open the query and view your results.

[JoeM]

Here is a real simplified example of what that might look like: http://www.mrexcel.com/forum/microso...ml#post3126422

You would just want to build the SQL string on the fly, deciding on which fields to add to your string based on your Form selections.

[offie]

I get what you are saying but how do can you choose the fields in the user interface?

Would I put something like [Forms]![Name]![Field] into the sql? as the Where for the query, but how would this be any different than just a regular query, it would still show all the fields in the query wouldn't it?

[JoeM]

I would create the Form and have each field listed out with a check box next to it, and have them select the ones that they wish to display.
So, they would make the selection, then click a command button.

Here is a simple example with three fields they would choose from, to give you an idea of how to do this:

Private Sub Command1_Click()

    Dim strSQL As String
    strSQL = "SELECT "
    
'   Check first box
    If Check1 = -1 Then
        strSQL = strSQL & "[Field1],"
    End If
    
'   Check second box
    If Check2 = -1 Then
        strSQL = strSQL & "[Field2],"
    End If
    
'   Check third box
    If Check3 = -1 Then
        strSQL = strSQL & "[Field3],"
    End If
    
'   Check to make sure that they have made at least one selection
    If Right(strSQL, 1) <> "," Then
        MsgBox "You have not selected to show any fields!", vbOKOnly, "TRY AGAIN!"
        Exit Sub
'   If they have, drop the final comma and add From clause
    Else
        strSQL = Left(strSQL, Len(strSQL) - 1) & " FROM [Table1];"
    End If
    
'   Do view code before applying, uncomment line below
''  MsgBox strSQL
    
'   Assign SQL code to Query
    CurrentDb.QueryDefs("Query1").SQL = strSQL
    
'   Open query to view results
    DoCmd.OpenQuery "Query1", acViewNormal, acEdit
 
End Sub

[offie]

Sorry it's taken me so long, lots of other stuff to do, but I do have a question.

I am getting an error at
' Assign SQL code to Query
CurrentDb.QueryDefs("Query1").SQL = strSQL

I just made up a name, gave me the error, so then I made a blank query with that name, still doesn't work.
I am obvioulsy doing something wrong...

Thanks!

[offie]

Nevermind, got it to work, sorry
Thanks again!