Query from form's combo box w/ field list!

[octick]

I have a combo box(s) that is pulling the 'Field List' for 'Row Source Type' from table [INPUT].
The field list is never the same and this is why I need the individual to select the 'fields' in the combo box(s).

Once the user has selected the fields in the combo box(s), I would like a query that shows the data from table[INPUT]. Instead all I get is the correct number of lines, but all are full with the 'Field Names' not the actual data. What do I need to write in SQL to get back the actual data rather than 'Field Names'.

Thanks ahead of time.

Aron

 

[Jon K]

You can use a multi-select list box and each time build a new SQL statement for the query before running it.


I have attached a sample database (which contains the Employees table from the NorthWind sample.) You can open the form, select some fields in the list box and click on the command button to run the query "qryEmployees".

The code used is in the On Click event of the command button.

Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As [b]Object[/b]
   Dim SQL As String
   Dim vItem As Variant
   
   ' loop through selected field names
   For Each vItem In Me.lstFieldList.ItemsSelected
      SQL = SQL & ",[" & Me.lstFieldList.ItemData(vItem) & "]"
   Next vItem
   
   ' build new SQL statement
   SQL = "Select " & Mid(SQL, 2) & " from [Employees]"
   
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryEmployees")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryEmployees"

End Sub

Note
The Multi Select property of the list box has been set to Extended.

In the code qDef is declared as an object instead of DAO.QueryDef so that the code can be used even in ADO without referencing DAO.
.

 

[octick]

Great!!!! but, 1 more question

That is some incredible sql writing. I couldn't even duplicate instead I had to import into your example.

However, it doesn't complete answer my problem.

I need seperate combo boxes because I need to give each single choice of that combo box a specific name.

Combo box #1s choice might be 'Customer' from the Field List, but it will be renamed 'Object' when Query is ran.
Combo box #2's choice might be 'Net Sales' from the Field List, but it will be renamed 'Value' when Query is ran.

I have attached a sample database including the revised form and query from your example.

Thanks once again ahead of time. I need to take a SQL class badly.

 

[Jon K]

I have added a query "qryResults" in the database and modified your code as follows:-

Private Sub Command16_Click()
   If IsNull(Me.Combo4) And IsNull(Me.Combo6) And _
      IsNull(Me.Combo8) And IsNull(Me.Combo10) And _
      IsNull(Me.Combo12) And IsNull(Me.Combo14) Then
      
      MsgBox "You must select from at least one box."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim SelFields As Variant
   
   SelFields = Null
   SelFields = ",[" + Me.Combo4 + "] as [Object]"
   SelFields = SelFields & ",[" + Me.Combo6 + "] as [Value]"
   SelFields = SelFields & ",[" + Me.Combo8 + "] as [Unique 1]"
   SelFields = SelFields & ",[" + Me.Combo10 + "] as [Unique 2]"
   SelFields = SelFields & ",[" + Me.Combo12 + "] as [Unique 3]"
   SelFields = SelFields & ",[" + Me.Combo14 + "] as [Unique 4]"
       
   SQL = "Select " & Mid(SelFields, 2) & " from [Data]"
       
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("qryResults")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' run query
   DoCmd.OpenQuery "qryResults"

End Sub

The user must make a selection in at least one of the combo boxes. (If some combo boxes must be selected, you can modify the code to ensure that they are selected.)


The field selected in the first combo box is renamed as "Object", the field in the second combo box as "Value" etc. in the query results.

Hope I have read your question right this time.
.

 

[octick]

You the man!

What can I say!

Damn impressive! Thanks so much. You make me want to go out and learn everything possible.

Thanks once again.