By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Pass 2 parameters to stored query

P: n/a
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").Valu e 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

Sep 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Your code should be something like:-

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

Set rst = comm.Execute

Set comm = Nothing

' And so on ...
--

Terry Kreft
"Mayhem05" <EJ********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...
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").Valu e 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

Sep 29 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.