Running multiple queries using one set of parameters

[Schnadi]

I have approximatley 10 select queries set up which are run on a monthly basis. The common factor between these queries is that they all use the same parameters (start date and end date)

What i would like to do is run all 10 of these queries and only have to enter the parameters once. I have looked into a few ways of doing this and firstly settled on using two text boxes on a form which i then referenced in the query criteria. This however isn't very user friendly because if i wanted to run one of the queries on an ad hoc basis i'd be given something similar to [Forms]![frmPJ_DQ]![txtStartDate] when requesting for the parameters to be input.

I then decided on the below solution but still no luck as i cannot display the results of the queries on screen. It would be fine if they were action queries but as they are all select queries i have a slight problem!

Private Sub btnRunDQ_Click()

Dim cmd As ADODB.Command
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter
Dim dtStartDate As Date
Dim dtEndDate As Date

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdStoredProc

dtStartDate = txtStartDate
dtEndDate = txtEndDate
Set prm1 = cmd.CreateParameter("Enter Start Date", adDate, adParamInput, , dtStartDate)
Set prm2 = cmd.CreateParameter("Enter Day After End Date", adDate, adParamInput, , dtEndDate)
cmd.Parameters.Append prm1
cmd.Parameters.Append prm2

cmd.CommandText = "PJDQ_Dummy"
cmd.Execute
cmd.CommandText = "PJDQ_NotKnown"
cmd.Execute
cmd.CommandText = "PJDQ_NowLeft"
cmd.Execute
cmd.CommandText = "PJDQ_NotWithinEpDates"
cmd.Execute

End Sub

Can anyone be of help here please?

Thanks,
Paul

 

[AlexCuse]

You could build the queries in VBA. This would allow you to capture your two parameters once, and use the same values in each query.

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page

 

[Schnadi]

Hi Alex,

Thanks for the reply. That's something I've thought about but i'd like to avoid hard coding the SQL within the VBA if at all possible. Many of the people i work with are scared of getting their hands dirty with vba so i'm looking for another solution if there is one?!

Thanks,
Paul

 

[ClulessChris]

Build the queries as normal but have a vba procedure to call them all

sub CallAll(dtStartDate, dtEndDate)
    Call qry1(dtStartDate, dtEndDate)
    Call qry2(dtStartDate, dtEndDate)
    Call qry3(dtStartDate, dtEndDate)
    Call qry4(dtStartDate, dtEndDate)
    Call qry5(dtStartDate, dtEndDate)
    Call qry6(dtStartDate, dtEndDate)
    Call qry7(dtStartDate, dtEndDate)

end sub

I don't often code in access and therefore am rusty on the syntax to call query but the principal seems sound.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Schnadi]

Could you give a bit more detail please chris - coding isn't my forte unfortunately :-(

Thanks

 

[ClulessChris]

Try

sub CallAll(dtStartDate, dtEndDate)
    DoCmd.qry1 dtStartDate, dtEndDate
    DoCmd.qry2 dtStartDate, dtEndDate
    DoCmd.qry3 dtStartDate, dtEndDate
    DoCmd.qry4 dtStartDate, dtEndDate
    DoCmd.qry5 dtStartDate, dtEndDate
    DoCmd.qry6 dtStartDate, dtEndDate
    DoCmd.qry7 dtStartDate, dtEndDate

end sub

Where qry# is the name of your queries.
You would perhaps have a button assigned to the CallAll procedure (or some other method to call this routine).

good luck.

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[ClulessChris]

If you want to avoid having to click through all the querys called use DoCmd.SetWarnings False before opening the queries and DoCmd.SetWarnings True after they've all been called.

Never knock on Death's door: ring the bell and run away! Death really hates that!