p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   VB Databases Basics (http://p2p.wrox.com/forumdisplay.php?f=82)
-   -   Export Access Paramter Query to Excel (http://p2p.wrox.com/showthread.php?t=38109)

jcharlton February 6th, 2006 10:45 PM

Export Access Paramter Query to Excel
 
Hello,

I have used Access for quite a while, but I am new to Visual Basic. Right now, I am trying to Automate my database by exporting the results of a parameter query to Excel. The criteria for the parameter query is based on a combo box I have loaded onto the form. How do I execute the parameter query (based on my combo box) and have the data automatically exported to an Excel template I have already created? I would like to automate this as much as possible with the combo box and parameter query. Thanks.

Justin

mmcdonal February 10th, 2006 08:58 AM

Hi,

   Please post in Microsoft Office > Access VBA for better results.

You can put the parameter directly in the query in design view. Enter the following sort of code in the Criteria of the field you want to pass the parameters to:

[Forms]![frmMyForm].[cboMyComboBox]

Then use the button to open the form or report based on this query, and the query will go get the parameter from the combo box itself.

The downside is that this version of the query can only be used for this one button. If you want to pass the parameter to one version fo the query for all uses, then you would do this:

Dim stDocName As String
Dim inComboValue As Integer ' assumes PK is integer is being passed
Dim stLink As String

If IsNull(Me.MyComboBox) or Me.MyComboBox = "" Then
   'error handling
   Else
   inComboValue = Me.MyComboBox
End If

stLink = "[MyPK] = " & inComboValue
stDocName = "frmMyForm" 'or "rptMyReport"

DoCmd.Open... stDocName, , , stLink ' in the WHERE section

HTH


mmcdonal


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

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