select column name base on user's choice

[cdolphin88]

Hi,

I want to build a query base on user's choice...

I'll have a form where the user will choose which of the columns he
wants to show.

Then I need to build the query based on his choice. Is that possible?

How should I do?

Eg:

User chose item, location, then my query will be

SELECT item, location
FROM Table1;


Cheers!

Claudi

 

[Guest]

You will have to use VBA to create an SQL statement.
Create a List Box control. Make the table or query they will be using the
Row Source of the List box. Make the Row Source Type Field List. Make the
Multi Select property Extened.

Now, the list box will present a list of all the field in the row source and
the user can select which of those they want to see.

To create the SQL, you will need to loop through the ItemsSelected
collection of the list box and add each selected field to the SQL. Now, I
don't know how you intend to present the results, but the issue you will have
is that many methods will not accept an SQL string - They require the name of
a query, So included is the code that will allow you to save the query and
use it in an OpenQuery method

Dim varItem As Variant
Dim ctl As Control
Dim strSQL As String

Set ctl = Me.MyListBox
With ctl
For Each varItem In .ItemsSelected
strSQL = strSQL & .ItemData(varItem) & "', "
Next varItem
End With

'Remove the Last Comma
strSQL = Left(strSQL,Len(strSQL) -1)
'Add the other stuff
strSQL = "SELECT " & strSQL & " FROM MyTable;"

'Save the Query
Currentdb.QueryDefs("MyQueryName").SQL = strSQL

'Show the Results
Docmd.OpenQuery "MYQueryName"

 

[Guest]

Claudi:

You can amend the SQL property of the query before opening it. First create
any query, e.g.

SELECT *
FROM Table1;

and save it as MyQuery say.

Then in your form you could have a multi-select list box, lstFields, which
lists all the fields and a button with the following code in its Click event
procedure:

Dim dbs As DAO.Database, qdf As DAO.QueryDef
Dim varItem As Variant
Dim strFieldList As String
Dim strSQL as String
Dim ctrl As Control

Set ctrl = Me.lstFields

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strFieldList = strFieldList & "," & ctrl.ItemData(varItem)
Next varItem

' remove leading comma
strFieldList = Mid(strFieldList, 2)

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

Set dbs = CurrentDb
Set qdf = dbs,QueryDefs("MyQuery")

qdf.SQL = strSQL
DoCmd.OpenQuery "MyQuery"
Else
MsgBox "No Fields Selected", vbInformation, "Warning"
End If

The query should open showing only those columns selected in the list box.

Ken Sheridan
Stafford,

 

[cdolphin88]

Hi,

But this will only store the query in a string, it won't store
physically in the access.. because after building the query, I need
this query to design the report... I mean the report needs to be based
on this new query....
Is that possible?

Cheers!

Claudi




Ken Sheridan escreveu:

 

[Guest]

Claudi:

Yes its possible, but tricky as it means you have to amend the report
definition at runtime so that its controls are bound to the selected columns.
In fact you are going to have to amend the report definition anyway, so you
might as well just leave the query as SELECT * etc. so it returns all
columns. You then have a number of options as regards amending the report
definition:

1. Have controls bound to every column and hide the unwanted ones,
adjusting the position of the visible columns.

2. Have controls bound to every column and delete the unwanted ones,
adjusting the position of the visible columns.

3. Have no bound controls in the report and add those for the selected
columns at runtime.

Of these the first has the advantage that you can do it when the report
runs, whereas the other two would require you to open it invisibly in design
view first and amend the design, save it, run the report, open it on design
view again and amend the design back to the default, and then save it again.
The following is some sample code which I wrote once for someone who wanted
to show only non-zero value controls in a tabular report. In this case she
wanted it done on a row by row basis, so the code went in the detail
section's Format event procedure, whereas in your case it could go in the
report header's Format event procedure. The principle is much the same, but
in your case you'd test for the column being selected in the list box on the
form rather than its value being zero. You'd need to experiment with the
values for the Left property settings and the value of the conADJUST
constant, which determines how far a control moves left when reuired.

Const conADJUST = 1227

' make all controls visible by default
Field1.Visible = True
Field1_Label.Visible = True
Field2.Visible = True
Field2_Label.Visible = True
Field3.Visible = True
Field3_Label.Visible = True
Field4.Visible = True
Field4_Label.Visible = True
Field5.Visible = True
Field5_Label.Visible = True

' set default Left values for controls
Field1.Left = 60
Field1_Label.Left = 60
Field2.Left = 1287
Field2_Label.Left = 1287
Field3.Left = 2514
Field3_Label.Left = 2514
Field4.Left = 3741
Field4_Label.Left = 3741
Field5.Left = 4868
Field5_Label.Left = 4968

' adjust controls leftwards to omit zero values
If Field1 = 0 Then
Field1.Visible = False
Field1_Label.Visible = False
Field2.Left = Field2.Left - conADJUST
Field2_Label.Left = Field2_Label.Left - conADJUST
Field3.Left = Field3.Left - conADJUST
Field3_Label.Left = Field3_Label.Left - conADJUST
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field2 = 0 Then
Field2.Visible = False
Field2_Label.Visible = False
Field3.Left = Field3.Left - conADJUST
Field3_Label.Left = Field3_Label.Left - conADJUST
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field3 = 0 Then
Field3.Visible = False
Field3_Label.Visible = False
Field4.Left = Field4.Left - conADJUST
Field4_Label.Left = Field4_Label.Left - conADJUST
Field5.Left = Field5.Left - conADJUST
Field5_Label.Left = Field5_Label.Left - conADJUST
End If

If Field5 = 0 Then
Field5.Visible = False
Field5_Label.Visible = False
End If

Ken Sheridan
Stafford, England