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
