recordset not looping to all records

[Guest]

I have set up the following sub routine to run in the onclick event of a
button on my main database form. It is a routine that when finished will open
all the current activities, complete them, and then assign new activities for
the next step in our system. I am having the following problem. The code
works great until it comes to changing the records that I have opened in
dbsSQL (see section marked in code marked as this portion not working
properly). Without this section in the code, the routine will select all of
the records, loop through them, add the new records, and then quit. However,
when I add this portion to the code something causes the loop to fail or
quit, etc. Testing has found that it does open the first record, but the
ending message box says that only one record was complete. Subsequent
varifacation in tblactivity shows that no new records were created nor were
and new records added.

I have also tried changing how the record set is opened. dbopendynaset,
dynamic, table. When changing those items I get a run time error #91. When
I debug the line that says "rst.SQL close" is highlighted and it tells me
that the variable has not been intialized. Can anyone help me correct this
problem so that I can open all of these records and change the values of
their fields. As well if anyone notes anything in the code that might help
speed things up, that would be great as well.

Private Sub cmdTodayVisitComplete_Click()
On Error GoTo Exit_Here

Dim rstSQL As DAO.Recordset
Dim dbsSQL As DAO.Database
Dim rstNew As DAO.Recordset
Dim dbsNew As DAO.Database
Dim dbsUpdate As Database
Dim strSql As String
Dim strFilter As String
Dim strMessage As String
Dim intCount, intUser As Integer
Dim strAddress, strNames, strMLSNum, strActivityID As String
Dim strAll As String
Dim dteToday As Date
Dim strFormOpen, strMSWord, strSQLUpdate As String

'Sets the SQL string to open the current activities that need to be
completed strSql = "SELECT tblActivity.SystemStep,
tblActivity.SystemNumber, tblActivity.Complete, tblActivity.DateDue,
qryExpired.Address, tblActivity.MLSListNumber, tblActivity.ActivityID,
tblActivity.AgentComplete, tblActivity.DateComplete " & _
"FROM tblTAX RIGHT JOIN (tblMLS RIGHT JOIN (qryExpired INNER JOIN
tblActivity ON qryExpired.MLSListNumber = tblActivity.MLSListNumber) ON
tblMLS.MLSListNumber = tblActivity.MLSListNumber) ON
tblTAX.TAXPropertyIdentificationNumber = tblMLS.MLSPropertyIDNumber " & _
"WHERE (((tblActivity.SystemStep)=1) AND ((tblActivity.SystemNumber)=1)
AND ((tblActivity.Complete)=0) AND ((tblActivity.DateDue)=Date()));"


'Defines who the user is that will be completing the task
If fOSUserName = "cspiper" Then
intUser = 1
ElseIf fOSUserName = "rpiper" Then
intUser = 2
End If

'Defines the date variable for the new activity
dteToday = Date + 1
dteToday = dteCorrect(dteToday) 'dtecorrect is function that changes
the date from either saturday to friday, or from sunday to monday

'Creates the recordset to use when completing the activities
Set dbsSQL = CurrentDb()
Set rstSQL = dbsSQL.OpenRecordset(strSql)

'Creates the recordset to use when creating the new activities
Set dbsNew = CurrentDb()
Set rstNew = dbsNew.OpenRecordset("tblActivity", dbOpenDynaset,
dbAppendOnly)

Set dbUpdate = CurrentDb

'Initializes the counter variable for use on the status bar and in the
ending message box
intCount = 0

'Starts the loop command for each activity
If Not (rstSQL.EOF And rstSQL.BOF) Then
rstSQL.MoveFirst
Do Until rstSQL.EOF

'Start Loop
intCount = intCount + 1
SysCmd acSysCmdSetStatus, "Completing record " & intCount

'Set All Variables
strAddress = rstSQL.Fields("Address").Value
strMLSNum = rstSQL.Fields("MLSListNumber").Value
'strNames = rstSQL.Fields("Owner Name").Value
strActivityID = rstSQL.Fields("ActivityID").Value

'Define MS Word text
'strMSWord = strMSWord & vbCrLf & strNames & " - " & strAddress

'Print MLS Sheet
'strFormOpen = "[MLSListNumber]= " & strMLSNum
'DoCmd.OpenForm "frmProperty"
'**** Insert the print command here ****
'DoCmd.Close acForm, "frmProperty", acSaveNo

'*************************************
'********This portion is not working**********
'*************************************
'Complete Activity in activity record
rstSQL.Fields("Complete").Value = -1
rstSQL.Fields("AgentComplete").Value = intUser
rstSQL.Fields("DateComplete").Value = Now()

'I have also tried the following
strSQLUpdate = "Update tblAcivity" & _
" Set Complete = -1 And AgentComplete = " & intUser & " And
DateComplete = " & Now() & _
'" Where ActivityID = " & strActivityID

'dbUpdate.Execute strSQLUpdate

'*************************************
'********End portion is not working**********
'*************************************

'Create New Activity in tblActivity

rstNew.AddNew
rstNew.Fields("DateStart").Value = dteToday
rstNew.Fields("DateDue").Value = dteToday
rstNew.Fields("DateEntered").Value = Now
rstNew.Fields("MLSListNumber").Value = strMLSNum
rstNew.Fields("ActivityNote").Value = "The second step of
the expireds system" & _
", send letter 2."
rstNew.Fields("AgentAssign").Value = 3
rstNew.Fields("ActivityType").Value = "Letter"
rstNew.Fields("SystemNumber").Value = 1
rstNew.Fields("SystemStep").Value = 2
rstNew.Update

'Exit Loop and Move to next
rstSQL.MoveNext
Loop
End If

Exit_Here:

SysCmd acSysCmdClearStatus

rstSQL.Close
Set rstSQL = Nothing
dbsSQL.Close
Set dbsSQL = Nothing
rstNew.Close
Set rstNew = Nothing
dbsNew.Close
Set dbsNew = Nothing

DoCmd.Requery

MsgBox intCount & " activities completed"

End Sub

 

[CP1234]

Thank you to all of those of you who took the time to
read the terrible post previous this. Through additional
work and reading, I have diagnosed the problem and
created a working solution.

The problem that I was facing was that the query I was
calling in my sql string was not updatable. Therefore
when I went to update it, an error would occur. Since I
had created an error handler that said to quit on error,
nothing else happened.