re.MoveNext not moving

[Jim Jackson]

I have set up a module to run when the main data form is opened, to check for
"Next Steps" dates which are within two days of the current date. I set up a
table and an append query which pastes qualified data into the table. The
routine calls that table for its purposes of setting up Tasks in Outlook.

All works well except that each task is always for the first record, the
correct number according to the EOF statement, but not for each individual
record. The code is thus:

Private Sub Form_Open(Cancel As Integer)

Dim rs As DAO.Recordset

Dim Customer_Name As String, strNext_Steps As String, strDueDate As String
Dim strStartDate As String
Stop
On Error GoTo Err_cmdCreateTask_Click

strSubject = Me.Customer_Name

Dim objOl As Outlook.Application
Dim objItem As Outlook.TaskItem
Dim blnOlRunning As Boolean
Dim txtDueDate As Date
Dim txtStartDate As Date

On Error Resume Next

blnOlRunning = True

Set objOl = GetObject(, "Outlook.Application")

If Err <> 0 Then
Set objOl = CreateObject("Outlook.Application")
blnOlRunning = False
Err.Clear
End If

On Error GoTo 0

Set rs = DBEngine(0)(0).OpenRecordset("Alerts")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF

Set objItem = objOl.CreateItem(olTaskItem)
With objItem

.Subject = Me.Customer_Name
.Body = Me.Next_Steps

.DueDate = Me.[next steps date]
.StartDate = Me.[next steps date]
.Save
End With

If blnOlRunning = True Then

objItem.Display
objItem.Assign

objOl.Quit
End If
rs.MoveNext
Loop
rs.Close

Exit_cmdCreateTask_Click:
Set objItem = Nothing
Set objOl = Nothing
Exit Sub

Err_cmdCreateTask_Click:
Select Case Err

Case 0

Case Else
MsgBox Err.Description
Resume Exit_cmdCreateTask_Click
End Select
End Sub

Thank you for any help.

Jim

 

[Ken Snell \(MVP\)]

Put your code in the form's Load event, not the Open event. Often, the form
has not "loaded" its data during the Open event, so trying to manipulate the
data in the Open event usually is not successful.

 

[Jim Jackson]

That makes sense.

Thanks.

Jim

Put your code in the form's Load event, not the Open event. Often, the form
has not "loaded" its data during the Open event, so trying to manipulate the
data in the Open event usually is not successful.

--

Ken Snell
<MS ACCESS MVP>

I have set up a module to run when the main data form is opened, to check
for
"Next Steps" dates which are within two days of the current date. I set
up a
table and an append query which pastes qualified data into the table. The
routine calls that table for its purposes of setting up Tasks in Outlook.

All works well except that each task is always for the first record, the
correct number according to the EOF statement, but not for each individual
record. The code is thus:

Private Sub Form_Open(Cancel As Integer)

Dim rs As DAO.Recordset

Dim Customer_Name As String, strNext_Steps As String, strDueDate As String
Dim strStartDate As String
Stop
On Error GoTo Err_cmdCreateTask_Click

strSubject = Me.Customer_Name

Dim objOl As Outlook.Application
Dim objItem As Outlook.TaskItem
Dim blnOlRunning As Boolean
Dim txtDueDate As Date
Dim txtStartDate As Date

On Error Resume Next

blnOlRunning = True

Set objOl = GetObject(, "Outlook.Application")

If Err <> 0 Then
Set objOl = CreateObject("Outlook.Application")
blnOlRunning = False
Err.Clear
End If

On Error GoTo 0

Set rs = DBEngine(0)(0).OpenRecordset("Alerts")

rs.MoveLast
rs.MoveFirst
Do While Not rs.EOF

Set objItem = objOl.CreateItem(olTaskItem)
With objItem

.Subject = Me.Customer_Name
.Body = Me.Next_Steps

.DueDate = Me.[next steps date]
.StartDate = Me.[next steps date]
.Save
End With

If blnOlRunning = True Then

objItem.Display
objItem.Assign

objOl.Quit
End If
rs.MoveNext
Loop
rs.Close

Exit_cmdCreateTask_Click:
Set objItem = Nothing
Set objOl = Nothing
Exit Sub

Err_cmdCreateTask_Click:
Select Case Err

Case 0

Case Else
MsgBox Err.Description
Resume Exit_cmdCreateTask_Click
End Select
End Sub

Thank you for any help.

Jim