No result when using CurrentDb.QueryDefs.OpenRecordset?

[jase2006]

When I run the query straight off it shows rows of results, but for some reason when it to the
"If Not rstAssignments.EOF Then" It doesn't pass, which means it's empty? All the fields on the frmReportParam2 form is filled in (hence result when running the query straight off) But it should return some result. Any ideas? Thanks in advance

If ([Forms]![frmReportParam2]![cboBU]) <> "" Then
Set qryException = CurrentDb.QueryDefs("qryExceptionReportWithBU")
Else
Set qryException = CurrentDb.QueryDefs("qryExceptionReport")

End If
'add parameter to query - start date

With qryException

.Parameters(0).Value = DateValue(Forms!frmReportParam2!txtDate)
lngWeeks = CLng(Forms!frmReportParam2!cboTimePeriod)
.Parameters(1).Value = lngWeeks
.Parameters(2).Value = (Forms!frmReportParam2!cboEmploymentTypeT)
.Parameters(3).Value = (Forms!frmReportParam2!cboEmploymentTypeA)
.Parameters(4).Value = (Forms!frmReportParam2!cboEmploymentTypeP)

If ([Forms]![frmReportParam2]![cboBU]) <> "" Then
.Parameters(5).Value = (Forms!frmReportParam2!cboBU)
'.Parameters(5).Value = ""
End If


End With

'make recordset from exception report query
Set rstAssignments = qryException.OpenRecordset(dbOpenSnapshot)

If Not rstAssignments.EOF Then
strRES_RT = rstAssignments("RES_RT").Value
strRES_PC = rstAssignments("RES_PC").Value
sglHours = rstAssignments("RES_WORK_HOURS").Value
End If

With rstAssignments
If Not .EOF Then
.MoveFirst
Do
bah bah bah

End If

 

[mp9]

Try sticking a rstAssignments.MoveFirst before your If Not rstAssignments.EOF line

 

[jase2006]

I try that but got the "3021" run time error with the "No Current Record" message

 

[JoeAtWork]

It seems your recordset is opening fine, it's just that the parameters are causing no records to be returned. I would probably step through the code to make sure the parameters are being set to what I think they should be.

 

[jase2006]

Thanks, I'll look into that.