RE: Passing 2 parameters to stored query?

Hi:
Here is a bit of VB6 code that I use frequently to deal with parameters:
Set cat = New ADOX.Catalog
cat.ActiveConnection = Connection

Set cmd = cat.Procedures("QCreditSummaryQTD-YTD").Command

***Error condition - tell the user and quit procedure
If (cmd Is Nothing) Then
MsgBox "An error has occurred trying to open the Records for the
Provider Medical Summary!", vbCritical + vbOKOnly, "Medical Report Error"
MedicalReports = False
Set cat = Nothing
Set cmd = Nothing
Exit Function
End If

cmd.Parameters("pardtStart").Value = dtQstart
cmd.Parameters("pardtEnd").Value = dtQend
Set rsList = cmd.Execute()

--
Uwe


"EJ Hansen" wrote:

I have an Access 2003 database that I need to write some VBA code for
to populate a table. The table is based on a query I have built in
Access queries. Right now I have 2 parameters that are passed to the
query from a form (DateFrom and DateTo). When I open the form and
populate the variables (DateFrom and DateTo) then open the query it
works fine.

My problem is that I need to do this from VBA coding and pass the 2
parameters to the query that way and populate my recordset. How do I
go about doing this? When I step through it, I find that the recordset
is not populating.

Here is some sample code that I have been trying to use.

Dim comm As ADODB.Command
Dim pmStartDate As ADODB.Parameter
Dim pmEndDate As ADODB.Parameter
Dim rst As ADODB.Recordset

Set comm = New ADODB.Command
Set comm.ActiveConnection = CurrentProject.Connection
comm.CommandText = "qrySupportRptTest"
comm.CommandType = adCmdStoredProc

Set pmStartDate = comm.CreateParameter("StartDate", adDBDate,
adParamInput, , Me.txtdatefrom)
comm.Parameters.Append pmStartDate

Set pmEndDate = comm.CreateParameter("EndDate", adDBDate,
adParamInput, , Me.txtDateTo)
comm.Parameters.Append pmEndDate

comm.Execute

Set comm = Nothing

Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = CurrentProject.Connection
.Source = "select * from QrySupportRptTest where DateFrom = #"
& pmStartDate & "#"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.CursorLocation = adUseClient
.Open
End With


'On Error Resume Next

' Loop for record inside loop for date
' This keeps the dates grouped

If rst.RecordCount <> 0 Then
Do Until CalcDate = Me.txtDateTo
rst.MoveFirst
Do Until rst.EOF
If CalcDate >=
rst.Fields("QrySupportRptTest.StartDate").Value And CalcDate <=
rst.Fields("QrySupportRptTest.EndDate").Value Or CalcDate >=
rst.Fields("QrySupportRptTest.StartDate").Value And
rsPaxData.Fields("QrySupportRptTest.EndDate").Value Is Null Then
DoCmd.Execute "Insert into TblPaxReport (SptState,
SptType, CalcDate, PAX, UIC) values (" &
rst.Fields("QrySupportRptTest.SptState") & "," &
rst.Fields("QrySupportRptTest.SptType") & "," & CalcDate & "," &
rst.Fields("QrySupportRptTest.PAX") & "," &
rst.Fields("QrySupportRptTest.UIC") & ");"
End If
rst.MoveNext
Loop
CalcDate = DateAdd("d", 1, CalcDate)
Loop
End If

' Run report
'Docmd.OpenReport

' DoCmd.CloseQuery stPAXquery

' Clean up
rst.Close
Set rst = Nothing
Set comm = Nothing
Set pmStartDate = Nothing
Set pmEndDate = Nothing


.