'wizard-like' query field selection

[stellJess]

Hello Everyone,

i may be posting this in the wrong forum. if so, let me know.

is there a way to allow the user to select some or all fields in a query like the wizards we use in Access. something like this:

FIELDS TO SELECT FIELDS CHOSEN

field1 > field2

field4 >> field3

field5 < field6

field7 <<

and then be able to run the query based on the FIELDS CHOSEN?

in advance, thanks.
stellJess

 

[smedvid]

Sure... In terms of queries, you need to know if this is just a temporary query that will display in a form or a query that needs to be saved.

You can use a couple of approaches. If the query needs to be saved, u can use QueryDef to Create a new query.

If just trying to load a list in a form, u can modify the datasource.

The key is building the SQL Select Statement. In the above design, you can simply loop thtough the selected items and build the SQL.

htewh,

Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[stellJess]

Hi Steve,

thanks for your reply. i do not need to save the query results. although, i have created and saved the query but need help with the wizard-like funtionality. i do not know how to make this happen.

any help is appreciatedm, thanks.

stellJess

 

[smedvid]

Ok... First u will need some Medium coding ability.

The approach I would use is to create a form with List Box of Field Names in the Table (or Query) that is being used in the result set. If the list is stable, just hard code the list. Turn the multi-select option on within the list box properties so users can select more than one entry. Then, simply loop through list box to form the Fields list.
---- Psuedo Code
Dim lcSQL as String
'Check if At least One item Selected
If Me.lstBoxName.ItemsSelected.Count = 0
msgbox(&quot;Select Field(s)&quot
Exit Sub (or Function)
Endif
'Loop through List Box.
Dim intICnt As Integer
Dim lcFields as String
For intICnt = 0 To Me.lstBoxName.ListCount - 1
If Me.lstBoxName.Selected(intICnt) Then
lcFields = lcFields & Me.lstBoxName.Column(10, intICnt) & &quot;,&quot;
End if
Next intICnt
lcFields = Mid(lcFields,1,LEN(lcfields)-1) 'remove last comma.

lcSQL = &quot;SELECT &quot; & lcfields & &quot; &quot;
lcSQL = lcSQL & &quot;FROM TABLENAME &quot;
lcSQL = lcSQL & &quot;WHERE ... ?&quot;

'Create Saved Query in DB
lcQueryName = &quot;qry_FundsTransfer_Dump_&quot; & Year(ldTemp) & &quot;_&quot; & Month(ldTemp) & &quot;_&quot; & Day(ldTemp)
For Each lc_Query In ThisDB.QueryDefs
If Trim(lc_Query.Name) = Trim(lcQueryName) Then
'Delete Existing Query if Found!
ThisDB.QueryDefs.Delete (lcQueryName)
ThisDB.QueryDefs.Refresh
End If
Next lc_Query

Set lc_Query = ThisDB.CreateQueryDef(lcQueryName, lcSQL)
ThisDB.QueryDefs.Refresh

Done... U now have Query in database?

htwh..


Steve Medvid
&quot;IT Consultant & Web Master&quot;
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[stellJess]

Steve,

thanks, i'll try it and keep you posted. i may need your help again.

stellJess