Excel Form Help Needed, New to VBA

[Memanoth]

Hi All

I'm hoping I can get some help . I'm new to VBA and this is really my first time using it.

I have designed a form in excel that has 3 textboxes. The textboxes are specifically named WQZN, StartDate and EndDate.

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I currently have a button setup so that when it is clicked a macro will run which as a result will open an access query macro and update about 7 tables. At the moment the macro runs fine, however I have to enter a lot of information when it runs as the criteria in the query is setup as “Between [Start Date] And [End Date]” and “Like “QZ” & [WQZ] & “*””.

<o></o>
The data that gets entered for all of these parameters is exactly the same, so what I’m looking to do is create a form in excel (already done) on which the access queries will draw the data from the specific textboxes and run the query upon click of the command button on the bottom of the form.

<o></o>
The query outputs the data into an access table which in turn is linked to PivotReports. I don’t need it to return any data to me, all I need is for the criteria to be updated depending on what is entered into the excel form.

<o></o>
I hope that makes sense, I have tried searching for a solution but can’t seem to put anything together, so any help would be great.

 

[dk]

Hi,

It's certainly possible to run a query with parameters from VBA but I'm not sure if it's possible to run an Access macro from Excel VBA and then pass the parameters to the Access macro.

You might be better off running the queries directly from within Excel VBA (and therefore bypassing the Access macro) as this will give you much more control of the process. Here is an example of how to run an Access query from within Excel where the query has two parameters. It is not trivial but should hopefully make some sense. The code needs a reference to the Microsoft ActiveX Data Objects 2.x Library (go to Tools, References from within the VB Editor to do this).

Sub QueryWithParamsExample()

    Dim oConn As ADODB.Connection
    Dim oCmd As ADODB.Command
    Dim oParam As ADODB.Parameter



    'This opens an ADO connection to your Access file
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Provider = "Microsoft.Jet.OLEDB.4.0"
    oConn.ConnectionString = "C:\some folder\your file.mdb"
    oConn.Open



    'The command object is used to represent a query
    Set oCmd = CreateObject("ADODB.Command")
    oCmd.CommandText = "Your_Query"


    'The parameter object represents a parameter (surprise!).  There needs to be one
    'parameter object created for each parameter in your query.
    Set oParam = CreateObject("ADODB.Parameter")

    oParam.Name = "NEW_VALUE"
    oParam.Type = adDate

    'This line is important - it's where you specify the parameter value.
    'It can refer to a control on a userform
    oParam.Value = "20-SEP-1975"


    oCmd.Parameters.Append oParam
    Set oParam = Nothing

    'This associates the command object with your actual database
    Set oCmd.ActiveConnection = oConn

    'And finally run the query
    oCmd.Execute

    'Clean up.  This should generally be performed once you have finished with the database
    Set oCmd = Nothing
    oConn.Close
    Set oConn = Nothing



End Sub

HTH
DK