Run a SQL query via VBA based on which fields the user selects

[dan2bhm via AccessMonster.com]

I've been fumbling around with this for entirely too long, and I need some
assistance.

What I'm trying to do is to set up a form where the user can select from a
particular set of fields (which obviously exist in one of my tables).

After selecting the fields, I'd like to execute an SQL query via VBA. I only
want the query to display the fields that have been selected.

Does anyone know how to do this?? I'm in definate need of one of you gurus.

Thanks

(After selecting the fields I'd eventually like them to be able to set up
some sorting options and maybe specific criteria as to which records to
display.) - not too concerned with this though - I just really need help with
the above problem

 

[Klatuu]

Not that hard to do. I don't know how you are planning to select the fields.
There are a bunch of ways to do that. My favorite would be to use a
MultiSelect List Box with a rowsource type of Field List and the rowsource of
the table you want to search on. What you will get will be a list of all the
fields in the table. The user just has to click the ones they want, and all
you have to do is spin throught the ItemsSelected collection and build an SQL
string.

Dim strSQL As String
Dim varItm As Variant

'Put all the selected fields in the SQL string
With Me.lstFields
For Each varItm .ItemsSelected
strSQL = strSQL & .ItemData(varItm) & ", "
Next varItm
End With

'Add the select and the table name and take off the last comma

strSQL = Left(strSQL, Len(strSQL) -2)
strSQL = "SELECT " & strSQL & " FROM MyTableName;"

Easy as that.