Form entered Parameters to Pass-thru query

[jhansiblu]

I have three pass through queries set up in my Access database that are processed on a MSSQL server. All three queries function properly and process the data as expected. My problem is that the parameters (FromDate and ToDate) are being entered via three different forms--one form for each query. I would like for one form to execute all three pass through queries. How can I make this work?

Here is my code: (basically the smae for each form with different query identified)

Private Sub cmdRunqptRenewalRate_Click()
Dim db As DAO.Database
Dim strFromDate As String
Dim strToDate As String
Dim qd As DAO.QueryDef
Dim strSQL As String

' Ensure date-string inputs exist.
' NOTE: The code requires that both dates
' are supplied.
With Me!txtFromDate
If IsNull(.Value) Then
MsgBox "Please specify the first PaidThru date for the report."
.SetFocus
Exit Sub
Else
strFromDate = Format(.Value, "YYYY-MM-DD")
End If
End With
With Me!txtToDate
If IsNull(.Value) Then
MsgBox "Please specify the last PaidThru date for the report."
.SetFocus
Exit Sub
Else
strToDate = Format(.Value, "YYYY-MM-DD")
End If
End With
' Build the SQL query.
strSQL = _
"exec sp_NBAA_RenewRate '" & strFromDate & _
"','" & strToDate & "'"
Set db = CurrentDb()
Set qd = CurrentDb.QueryDefs("qry_update1_UD_Renewals")
qd.SQL = strSQL
qd.Connect = "ODBC;DSN=IMIS_NBAA_Prod;Database=DB_name;Uid=XXX;Pwd=****;"
qd.ODBCTimeout = 300
qd.ReturnsRecords = False
DoCmd.OpenQuery "qry_update1_UD_Renewals"
'DoCmd.RunSQL strSQL

 

[pbaldy]

One way would be to have a form with 3 buttons on it, one that processed each query. Or if you want to run all 3 with one button, you can just have the relevant code under one button. IOW, test the form controls and then set each query's parameters and run it, in turn.