Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Search | Today's Posts | Mark Forums Read
BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3
This is the forum to discuss the Wrox book Access 2007 VBA Programmer's Reference by Teresa Hennig, Rob Cooper, Geoffrey L. Griffith, Armen Stein; ISBN: 9780470047033
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Access 2007 VBA Programmer's Reference ISBN: 978-0-470-04703-3 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 9th, 2007, 04:23 PM
Registered User
Join Date: Oct 2007
Location: Milwaukee, WI, USA.
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
            Me.lstExport.AddItem qdQueryName.Name
        End If

End Sub

'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

End Sub

How can I specifically list several query names in the RowSource property to fill the list box? Thanks for any suggestions.

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Exporting data to Excel lrickett Beginning VB 6 3 February 10th, 2011 01:27 AM
help exporting data to excel MarkGT Classic ASP Basics 5 April 29th, 2008 06:13 PM
Exporting Query Data From Access to Excel JimInSouthernCal Access VBA 3 December 21st, 2007 08:34 AM
Query data and exporting data from one sheet to an testman Excel VBA 2 April 28th, 2005 02:40 PM
Exporting Query Results to Excel File redrobot5050 Access VBA 2 October 7th, 2004 05:26 PM

All times are GMT -4. The time now is 05:05 PM.

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.