Code errors on converting 97 to 2003 see pasted code (1 Viewer)

I have converted a 97 db to 2003.

The code below fails where I have commented with 3 ??? in front are the relevent bits.

I have changed bits of code as annotated.

I wonder if someone can help, I am happy to Send full db if required via Email it is making me MAD

Thanks in anticipation

Robbie


Function AddDates(pDaysToAdd As Integer)
On Error GoTo Err_Add_Dates_Click
'This procedure will add dates to the "tblDutyDate" table.

Dim Criteria1, StartDate, EndDate, AddDate As String
Dim DayNum As Single ' To allow for the Friday value of 1.5
Dim intI, intQ As Integer
'Declare an array to hold day values
Dim DayValues(1 To 7) As Single
Dim dbs As database

'??? Changed what was rsRoster as RecordSet to see below
Dim rsDay, rsDate, rsRoster As DAO.Recordset


Set dbs = DBEngine.Workspaces(0).Databases(0)

'Fill the array with the daily values
Set rsDay = dbs.OpenRecordset("tblDayValues", dbOpenSnapshot) ' Open table.

For intI = 1 To 7
Criteria1 = "ID = " & intI
rsDay.FindFirst Criteria1
If Not rsDay.NoMatch Then
DayValues(intI) = rsDay.[Value]
Else
DayValues(intI) = 1
End If
Next intI
rsDay.Close

'Get the last date in the database table
If IsNull(DMax("DutyDate", "[tblDutyDate]")) Then 'tblDutyDate is empty
StartDate = Now - 62
Else
StartDate = DMax("DutyDate", "[tblDutyDate]")
End If

If Not pDaysToAdd > 0 Then
' "Don't need to add days prior to Now"
Exit Function
End If

EndDate = Format((StartDate + pDaysToAdd), "dd/mm/yyyy")

'???old code in 97
'Set rsRoster = dbs.OpenRecordset("tblRoster", dbOpenRecordSet) ' Open table.
'Set rsDate = dbs.OpenRecordset("tblDutyDate", dbOpenRecordSet) ' Open table.

'???my changes that don't work
Set rsRoster = dbs.OpenRecordset("tblRoster", dbOpenDynaset) ' Open table.
Set rsDate = dbs.OpenRecordset("tblDutyDate", dbOpenDynaset) ' Open table.

Do Until rsRoster.EOF ' Until end of file.
'Add the required days to the database
AddDate = Format((StartDate + 1), "dd/mm/yyyy")


'Do this until all dates are added
intI = 0
Do Until intI >= pDaysToAdd
DayNum = WeekDay(AddDate)
intQ = 1
'Do this until an entry has been made for each post in a Roster
'Do Until intQ > rsRoster.Qty old

'??? method or data member not found ie Qty
Do Until intQ > rsRoster.Qty
rsDate.AddNew ' Create new record.
rsDate("DutyDate") = AddDate ' Set Date
rsDate("DutyID") = rsRoster.DutyID ' Set Roster ID
rsDate("Value") = DayValues(DayNum) ' Set days value
rsDate("Post") = intQ ' Set post number
rsDate.Update
'More than one person reqd for the duty
intQ = intQ + 1
Loop
' Increment dates
AddDate = DateValue(AddDate) + 1
intI = intI + 1
Loop
rsRoster.MoveNext 'Move onto the next roster
Loop

rsDate.Close ' Close table.
rsRoster.Close
Exit_Add_Dates_Click:
Exit Function

Err_Add_Dates_Click:
MsgBox Error$
Resume Exit_Add_Dates_Click

End Function

Try changing:
Dim rsDay, rsDate, rsRoster As DAO.Recordset

to:
Dim rsDay As DAO.Recordset
Dim rsDate As DAO.Recordset
Dim rsRoster As DAO.Recordset

Likewize you should also change the "Dim ..... as string" line

Change
Do Until intQ > rsRoster.Qty
to
Do Until intQ > rsRoster!Qty

And it cannot hurt to change
Dim dbs As database
to
Dim dbs As dao.database

I am sure that will do the trick....

Thank you that has cured almost all the errors, I am now working on the others and will return here if I fail.

I made your suggested changes throughout all the code.

Thanks again

Robbie