I can't seem to get a recordset passed back with the following code in a large Access application. The same exact ADO format works in other sub procedures in the same Access application. All my references are set.


public Sub CreateDate()
Dim dbConn as ADODB.Connection
Dim rstStaffing as ADODB.Recordset
Dim strSql as string
Dim curMonth as Integer
Dim curYear as Integer
Dim prevYear as Integer
Dim strMonthYear as string
Dim strPrevMonthYear as string
Dim strPrevMonthNextYear as string
Dim remainMonths as Integer
Dim fld as string

curMonth = Month(date)
curYear = Year(date)
'prevYear = (Year(date) - 1)
'Append a zero to the single digit months

If curMonth < 10 then
strMonthYear = Trim(("0") & (curMonth) & ("-") & (curYear)) ' ex 05-2001
strPrevMonthYear = Trim(("0") & (curMonth - 1) & ("-") & (curYear))
strPrevMonthNextYear = Trim(("0") & (curMonth - 1) & ("-") & (curYear + 1))
else
strMonthYear = Trim((curMonth) & ("-") & (curYear))
strPrevMonthYear = Trim((curMonth - 1) & ("-") & (curYear))
strPrevMonthNextYear = Trim((curMonth - 1) & ("-") & (curYear + 1))
End If

strSql = "Select tblStaffing.TimePeriod"
strSql = strSql & " from tblStaffing"
strSql = strSql & " WHERE tblStaffing.ProjectID=418 And tblStaffing.PersonName='Hunt Ernest' And tblStaffing.TimePeriod ='" & strPrevMonthYear & "'"



set dbConn = CurrentProject.Connection
set rstStaffing = new ADODB.Recordset
rstStaffing.ActiveConnection = dbConn
rstStaffing.CursorType = adOpenDynamic
rstStaffing.LockType = adLockPessimistic
rstStaffing.Open strSql


If rstStaffing.RecordCount > 0 then
rstStaffing!TimePeriod = strPrevMonthNextYear
rstStaffing.Update
End If


End Sub




Debug.Print rstStaffing.RecordCount produces a -1

Thanks