Re: Query will not pull records in VBA

You should post to an Access VBA group to be sure, but I think in order
for this to work, you are going to have to add a PARAMETERS clause to
your saved query. See the Jet SQL Reference section in Access online
help.

EJ Hansen wrote:
I have some VBA code that I am using from a button in Acess 2003 that
I want to call a stored query with parameters from the form that I
have the button on. The code I have is not pulling in the records,
but it does work when I just run the query after populating the
fields on the form.

I also am getting a "Compile error: Method or datamember not found"
which may be related to the fact that I'm not getting any records in
my recordset. Pointer is on last field on INSERT command near end of
my code.

Query has 3 tables it pulls from. SQL from Access looks like:

SELECT TblSupport.SptState, TblSupport.SptType,
TblSptPAXdate.StartDate, TblSptPAXdate.EndDate, TblSptPAXdate.PAX,
TblUnitPull.UIC, [forms]![FrmReportsByDate]![txtdatefrom] AS [Date
From], [forms]![FrmReportsByDate]![txtDateTo] AS [Date To]
FROM TblUnitPull INNER JOIN (TblSupport INNER JOIN TblSptPAXdate ON
TblSupport.[Line#] = TblSptPAXdate.[Line#]) ON TblUnitPull.UnitID =
TblSupport.UnitID
WHERE
(((TblSptPAXdate.StartDate)<=[forms]![FrmReportsByDate]![txtdatefrom])
AND ((TblSptPAXdate.EndDate)>=[forms]![FrmReportsByDate]![txtDateTo]
Or (TblSptPAXdate.EndDate) Is Null) AND ((TblSptPAXdate.PAX)>0)) OR
(((TblSptPAXdate.StartDate)>=[forms]![FrmReportsByDate]![txtdatefrom])
AND ((TblSptPAXdate.EndDate)<=[forms]![FrmReportsByDate]![txtDateTo])
AND ((TblSptPAXdate.PAX)>0)) OR
(((TblSptPAXdate.StartDate)<=[forms]![FrmReportsByDate]![txtdatefrom])
AND ((TblSptPAXdate.EndDate) Between
[forms]![FrmReportsByDate]![txtDateFrom] And
[forms]![FrmReportsByDate]![TxtDateTo] Or (TblSptPAXdate.EndDate) Is
Null) AND ((TblSptPAXdate.PAX)>0)) OR (((TblSptPAXdate.StartDate)
Between [forms]![FrmReportsByDate]![txtdatefrom] And
[forms]![FrmReportsByDate]![txtDateTo]) AND
((TblSptPAXdate.EndDate)>=[forms]![FrmReportsByDate]![txtDateTo] Or
(TblSptPAXdate.EndDate) Is Null) AND ((TblSptPAXdate.PAX)>0))
ORDER BY TblSptPAXdate.StartDate;

Here is the code for the button:

Dim CalcDate As Date
Dim loCommand As New ADODB.Command
Dim rsPaxData As New ADODB.Recordset

With loCommand
.CommandType = adCmdStoredProc
.CommandText = "QrySupportRpt"
.Parameters.Append .CreateParameter( _
"StartDate", adDate, adParamInput, , Me.txtdatefrom.Value)
.Parameters.Append .CreateParameter( _
"EndDate", adDate, adParamInput, , Me.txtDateTo.Value)
End With

'rsPaxData record set to QrySupportRpt
Set rsPaxData = New ADODB.Recordset
rsPaxData.Open loCommand, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic


DoCmd.SetWarnings False
' cboTblPaxReport.SetFocus

' Delete records from table
strsql = "Delete * from TblPaxReport;"
CurrentDb.Execute strsql, dbFailOnError

CalcDate = StartDate


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

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

' Run report
'Docmd.OpenReport

' DoCmd.CloseQuery stPAXquery

' Clean up
rsPaxData.Close
Set rsPaxData = Nothing
Set loCommand = Nothing

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.


.