User Defined Show/No Show Query Results

[lauriphi]

I have a parameter query built and am curious if anyone knows of a good way to allow the user to define which fields are displayed in the query results.

i tried looking for other threads discussing this without any luck.. not sure if it was my search terms or what. thanks so much for any help..

 

[Jon K]

Take a look at this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=77061
.

 

[lauriphi]

thanks jon..

i looked at the code and such from your example and given my newbie status am having a slight bit of trouble understanding the various elements of the code and getting it implemented in to my database.

perhaps if you could just explain the various stages of the code and how they would apply in any database that would be just excellent.. again, many thanks.

 

[lauriphi]

i have attached an abbreviated version of my database that i am looking to edit in case that helps at all...

 

[Jon K]

To let the user determine which fields to display in the results of the Commodity query, each time a new query SQL statement must be built and placed in the query.

I have added a form in your database. The code is in the On Click event of the command button.

As explained by the remarks in the code, the code loops through the list box and build a query SQL statement to display the selected fields. And if a component is selected in the combo box, a criterion is also added to the SQL statement.

The code then replaces the SQL statement of the Commodity query with the newly built SQL statement and runs the query.
.

 

[lauriphi]

wow, cant thank you enough...

and just one other question, how easy would it be to change the component pulldown menu to a text entry where the user is able to enter in a portion of the component name and it will look up based on the text they enter...

thanks again so much

 

[Jon K]

Assuming the text box name is txtComponent, you can change the IF.......End If like this:-

   If Not IsNull(Me.txtComponent) Then
      SQL = SQL & " where [Component] Like ""*" & Me.txtComponent & "*"""
   End If

.

 

[lauriphi]

couldnt i write in code portion #1 to replace portion #2 that is currently there so that when the user clicked the form button the query data would be exported to excel?

#1

DoCmd.TransferSpreadsheet acExport, "Microsoft Excel", "Commodity", "c:/query_data.xls", True
Application.FollowHyperlink "c:/query_data.xls"

#2

DoCmd.OpenQuery "Commodity"

been having a bit of difficulty there.. curiuous if you had any input. thanks!

 

[Jon K]

DoCmd.TransferSpreadsheet acExport, "Microsoft Excel", "Commodity", "c:/query_data.xls", True
Application.FollowHyperlink "c:/query_data.xls"

Instead of putting "Microsoft Excel" there, you need to specify a SpreadsheetType, or just leave it blank for Access to use the default. The file folder is "C:\".

DoCmd.TransferSpreadsheet acExport, , "Commodity", "c:\query_data.xls", True
Application.FollowHyperlink "c:\query_data.xls"


In some version of Windows, you may need to delete the previous xls file first before exporting the query results to avoid a Run-time error 3190: "Too many fields defined" when the number of fields selected in the query is larger than the number of data columns in the previous xls file.

On Error Resume Next
Kill "c:\query_data.xls"
On Error GoTo 0

DoCmd.TransferSpreadsheet acExport, , "Commodity", "c:\query_data.xls", True
Application.FollowHyperlink "c:\query_data.xls"
.

 

[lauriphi]

incredible... i really cant thank you enough for all of your help.

i took yoru thoughts and built form that allows you to query with multiple parameters.. nothing too fancy. and the way that i have it structured right now i get an error that tells me that the SQL query is structured incorrectly. and i see what the problem is, i just dont know exactly how to fix it..

Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   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 [Main Table]"
   
   ' add criteria for Supplier if a supplier has been entered
   If Not IsNull(Me.txtSupplier) Then
      SQL = SQL & " where [Name] Like ""*" & Me.txtSupplier & "*"""
   End If
   
   ' add criteria for HSE if a value has been selected.
   If Not IsNull(Me.cboHSE) Then
      SQL = SQL & " and [HSE]=""" & Me.cboHSE & """"
   End If
   
   ' add criteria for Cost if a value has been selected.
   If Not IsNull(Me.cboCost) Then
      SQL = SQL & " and [Cost]=""" & Me.cboCost & """"
   End If
   
   ' add criteria for Schedule if a value has been selected.
   If Not IsNull(Me.cboSchedule) Then
      SQL = SQL & " and [Schedule]=""" & Me.cboSchedule & """"
   End If
   
   ' add criteria for Supplier Health if a value has been selected.
   If Not IsNull(Me.cboHealth) Then
      SQL = SQL & " and [Supplier Health]=""" & Me.cboHealth & """"
   End If
   
   ' add criteria for Component if a component has been entered.
   If Not IsNull(Me.txtComponent) Then
      SQL = SQL & " and [Component] Like ""*" & Me.txtComponent & "*"""
   End If

   ' add criteria for PO if a # has been entered.
   If Not IsNull(Me.txtPO) Then
      SQL = SQL & " and [PO] Like ""*" & Me.txtPO & "*"""
   End If
   
   ' add criteria for Project if a project is selected
   If Not IsNull(Me.cboProject) Then
      SQL = SQL & " and [Project]=""" & Me.cboProject & """"
   End If
   
   ' add criteria for Selected Supplier if a supplier is selected
   If Not IsNull(Me.cboSelected) Then
      SQL = SQL & " and [SelSupp]=""" & Me.cboSelected & """"
   End If
   
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("Query")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' delete previous file that had been written
   On Error Resume Next
   Kill "c:\query_data.xls"
   On Error GoTo 0
   
   ' run query
   DoCmd.TransferSpreadsheet acExport, , "Query", "c:\query_data.xls", True
   Application.FollowHyperlink "c:\query_data.xls"
End Sub

by making the statement SQL = & " and [...] ...." the query gets screwed up if the first text input is left blank and then the "where" portion of the code is missing.. do you have any thoughts on how to restructure and avoid that problem?

 

[Jon K]

The code

Private Sub cmdRunQuery_Click()
   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim sWhere 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 [Main Table]"
   
   ' build criteria for Supplier if a supplier has been entered
   If Not IsNull(Me.txtSupplier) Then
      sWhere = " and [Name] Like ""*" & Me.txtSupplier & "*"""
   End If
   
   ' add criteria for HSE if a value has been selected.
   If Not IsNull(Me.cboHSE) Then
      sWhere = sWhere & " and [HSE]=""" & Me.cboHSE & """"
   End If
   
   ' add criteria for Cost if a value has been selected.
   If Not IsNull(Me.cboCost) Then
      sWhere = sWhere & " and [Cost]=""" & Me.cboCost & """"
   End If
   
   ' add criteria for Schedule if a value has been selected.
   If Not IsNull(Me.cboSchedule) Then
      sWhere = sWhere & " and [Schedule]=""" & Me.cboSchedule & """"
   End If
   
   ' add criteria for Supplier Health if a value has been selected.
   If Not IsNull(Me.cboHealth) Then
      sWhere = sWhere & " and [Supplier Health]=""" & Me.cboHealth & """"
   End If
   
   ' add criteria for Component if a component has been entered.
   If Not IsNull(Me.txtComponent) Then
      sWhere = sWhere & " and [Component] Like ""*" & Me.txtComponent & "*"""
   End If

   ' add criteria for PO if a # has been entered.
   If Not IsNull(Me.txtPO) Then
      sWhere = sWhere & " and [PO] Like ""*" & Me.txtPO & "*"""
   End If
   
   ' add criteria for Project if a project is selected
   If Not IsNull(Me.cboProject) Then
      sWhere = sWhere & " and [Project]=""" & Me.cboProject & """"
   End If
   
   ' add criteria for Selected Supplier if a supplier is selected
   If Not IsNull(Me.cboSelected) Then
      sWhere = sWhere & " and [SelSupp]=""" & Me.cboSelected & """"
   End If
   
   ' if length of criteria > 0, add criteria in SQL statement
   If Len(sWhere) > 0 Then
      SQL = SQL & " where " & Mid(sWhere, 5)
   End If
   
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("Query")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' delete previous file that had been written
   On Error Resume Next
   Kill "c:\query_data.xls"
   On Error GoTo 0
   
   ' run query
   DoCmd.TransferSpreadsheet acExport, , "Query", "c:\query_data.xls", True
   Application.FollowHyperlink "c:\query_data.xls"
End Sub

Alternatively, you can use the dynamic QueryDef method published in this Microsoft Article.
http://support.microsoft.com/default.aspx?scid=kb;en-us;210242

   If Me.lstFieldList.ItemsSelected.Count = 0 Then
      MsgBox "Select some field names first."
      Exit Sub
   End If
   
   Dim qDef As Object
   Dim SQL As String
   Dim sWhere As Variant
   Dim vItem As Variant
   
   sWhere = Null
   
   ' 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 [Main Table]"
   
   ' build criteria string
   sWhere = " and [Name] Like ""*" + Me.txtSupplier + "*"""
      
   sWhere = sWhere & " and [HSE]=""" + Me.cboHSE + """"
      
   sWhere = sWhere & " and [Cost]=""" + Me.cboCost + """"
      
   sWhere = sWhere & " and [Schedule]=""" + Me.cboSchedule + """"
      
   sWhere = sWhere & " and [Supplier Health]=""" + Me.cboHealth + """"
      
   sWhere = sWhere & " and [Component] Like ""*" + Me.txtComponent + "*"""
   
   sWhere = sWhere & " and [PO] Like ""*" + Me.txtPO + "*"""
      
   sWhere = sWhere & " and [Project]=""" + Me.cboProject + """"
      
   sWhere = sWhere & " and [SelSupp]=""" + Me.cboSelected + """"
      
   ' add criteria in SQL statement
   SQL = SQL & " where " + Mid(sWhere, 5)
      
   ' save query with new SQL statement
   Set qDef = CurrentDb.QueryDefs("Query")
   qDef.SQL = SQL
 
   Set qDef = Nothing
   
   ' delete previous file that had been written
   On Error Resume Next
   Kill "c:\query_data.xls"
   On Error GoTo 0
   
   ' run query
   DoCmd.TransferSpreadsheet acExport, , "Query", "c:\query_data.xls", True
   Application.FollowHyperlink "c:\query_data.xls"

The code is comparatively shorter as the method doesn't need to test the combo boxes and text boxes for non-Null values.
.