Passing 2 parameters to stored query?
- From: "EJ Hansen" <EJHansen05@xxxxxxxxx>
- Date: 28 Sep 2006 10:58:42 -0700
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
.
- Prev by Date: Re: Creating dbf table using ADO Recordset open method
- Next by Date: Re: Creating dbf table using ADO Recordset open method
- Previous by thread: Item cannot be found in the collection corresponding to the requested name or ordinal.
- Index(es):
Relevant Pages
|
