VBA and ADO

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

What is CurrentProject?

currentProject.Connection is the internal connection string to the Access 2000 DB which is currently open and is being run in Access 2000 module in VBA

Well it seem the select returned no rows ... could it be because of the two spaces in ...PersonName='Hunt Ernest'



Try to print the generated SQL string and execute it with some other tool.

Jenda

Another posibility. Your timeperiod calculations are incorrect. They will give incorrect results at least for January and October.

For January: strPrevMonthYear = 00-2001

For October: strPrevMonthYear = 9-2001

You need to do something like :

curMonth = Month(date)
curYear = Year(date)
prevMonth = curMonth - 1
If prevMonth = 0 then
prevMonth = 12
prevMonthsYear = curYear - 1
else
prevMonthsYear = curYear
End If

strMonthYear = curMonth & "-" & curYear
strPrevMonthYear = prevMonth & "-" & prevMonthsYear
strPrevMonthNextYear = prevMonth & "-" & (prevMonthsYear + 1)

If curMonth < 10 then strMonthYear = "0" & strMonthYear
If prevMonth < 10 then
strPrevMonthYear = "0" & strPrevMonthYear
strPrevMonthNextYear = "0" & strPrevMonthNextYear
End If




But anyway ... are you really sure that if you run THE EXACTLY SAME SQL via something else, the query returns some rows?

Jenda