Chapter 16: Exporting query data to Excel
This is an excellent chapter!
I'm trying to develop a form that has a list box that shows 5 or 6 queries from which Users can select, to export data to Excel. I don't want to list ALL the queries in the database (but this chapter also has an excellent way to do that).
Problem: On page 544, it says "If you don't want Users to be able to choose every Query in the database, the [u]names</u> of the object can always be hard-coded into the Row Source property for the list box." I have tried modifying various code in VBA to include a single specific query, or in the Form's property sheet -- nothing works. Can anyone help?
There are 2 examples of VBA code in the book -- both are used in the Form's OnLoad Event.
'Example 1 -- setting the list data for a List Box control on form load
Private Sub Form_Load()
Dim qdQueryName As QueryDef
'Clear the list if it is already filled
lstExport.RowSource = "" '(lstExport is the name of the List Box)
lstExport.RowSourceType = "Value List"
'Add all of the Query names to the List Box
For Each qdQueryName In Application.CurrentDb.QueryDefs
If (InStr(1, qdQueryName.Name, "~") = 0) Then
'Example 2 -- setting the list data for a List Box control on form load from a SQL statement
Private Sub Form_LoadOther()
'Create the SQL Statement
Dim strSQL As String
strSQL = _
"SELECT MSysObjects.Name " & _
"FROM MSysObjects " & _
"WHERE (((MSysObjects.Name) Not Like ""~*"") AND ((MSysObjects.Type)=5));"
'Set the Row Sourse with the SQL Statement
'lstExport is the name of the List Box.
Me.lstExport.RowSourceType = "Table/Query"
Me.lstExport.RowSource = strSQL
How can I specifically list several query names in the RowSource property to fill the list box? Thanks for any suggestions.