Run SQL using 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

 

[Tim Ferguson]

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.

jetFieldList = ""

If chkFullName.Value = True Then
if Len(jetFieldList) >0 Then jetFieldList = jetFieldList & ", "
jetFieldList = jetFieldList & "FullName"
End If

If chkAgeAtRegistration.Value = True Then
if Len(jetFieldList) >0 Then jetFieldList = jetFieldList & ", "
jetFieldList = jetFieldList & "AgeAtRegistration"
End If

If chkPostCode.Value = True Then
if Len(jetFieldList) >0 Then jetFieldList = jetFieldList & ", "
jetFieldList = jetFieldList & "PostCode"
End If

jetSQL = "SELECT " & jetFieldList & vbNewLine & _
"FROM MyTable " & vbNewLine & _
"WHERE " & jetCriterion & vbNewLine & _
"ORDER BY " & etc & etc


Hope that helps


Tim F

 

[John Nurick]

One way is along these lines:

1) Create a select query (let's call it qryXXX) that gets data from your
table.

2) Set up a form with a checkbox for each field that you want the user
to be able to select, and a button to click when the selection is ready.

3) In the button's Click event procedure, use something like this air
code:

Dim strSQL As String

strSQL = "SELECT"
If ckField1.Value = True Then
strSQL = strSQL & " Field1,"
End If
If ckField2.Value = True Then
strSQL = strSQL & " Field2,"
End If
'and so on for all the fields
...
If ckFieldN.value = True Then 'last field
strSQL = strSQL & " FieldN,"
End If

'Get rid of superfluous comma
strSQL = Left(strSQL, Len(strSQL) - 1)

'Optionally add WHERE and ORDER BY
'clauses here

'Tidymindedly add terminating ;
strSQL = strSQL & ";"

'assign strSQL to the saved query
DBEngine(0)(0).QueryDefs("qryXXX").SQL = strSQL

open it
DoCmd.OpenQuery "qryXXX", acViewNormal