A simple problem with MoveFirst

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In the following code, a table is populated with times and dates relating to
a particular employee. What I then want to do is populate 2 variables (one
with the 1st date and one with the last):

'Append the data relating to this user
DoCmd.SetWarnings False
DoCmd.OpenQuery "qAppDataToBeEmailedIndividual"
DoCmd.SetWarnings True

'Populate the from and to date variables
tbDataToBeEmailedIndividual.MoveFirst
FirstDate = tbDataToBeEmailedIndividual!error_date.Value
tbDataToBeEmailedIndividual.MoveLast
LastDate = tbDataToBeEmailedIndividual!error_date.Value

However, when the code gets to 'FirstDate =' I get the following error:

Run-time error '-2147217885 (80040e23)':

Record is deleted

The 1st record hasn't been deleted ... it's just been populated by a query!!

Any ideas?

Thanks
 
I don't see where you are creating tbDataToBeEmailedIndividual. Seems to be
some code missing.
 
At the end of the previous running of the code, the data from
tbDataToBeEmailedIndividual is deleted. On the next run,
qAppDataToBeEmailedIndividual is run to append the latest data to the table.
Then, I try to move to the first record and pick up the 'error_date' value.
 
I believe Duane's trying to figure out what tbDataToBeEmailedIndividual is.
If it's a recordset, where are you opening it? How you opened it is
important in terms of knowing whether it's going to see changes.
 
Here's everything as it is stands:

Sub EmailData()

Dim tbDataToBeEmailed As ADODB.Recordset
Dim tbUser As ADODB.Recordset
Dim qHoursIDNameEmail As ADODB.Recordset
Dim tbDataToBeEmailedIndividual As ADODB.Recordset
Dim nameCount As Integer
Dim totalNameCount As Integer
Dim i As Integer
Dim DisplayMsg As Boolean
Dim Counter As Long
Dim tbLinks As ADODB.Recordset
Dim FirstDate As Date
Dim LastDate As Date

Set cnCurrent = CurrentProject.Connection
Set tbDataToBeEmailed = New ADODB.Recordset
Set tbUser = New ADODB.Recordset
Set tbLinks = New ADODB.Recordset
Set qHoursIDNameEmail = New ADODB.Recordset
Set tbDataToBeEmailedIndividual = New ADODB.Recordset
DisplayMsg = True
nameCount = 1

tbUser.Open "tbUser", cnCurrent, adOpenKeyset, adLockOptimistic, adCmdTable
tbDataToBeEmailed.Open "tbDataToBeEmailed", cnCurrent, adOpenKeyset,
adLockOptimistic, adCmdTable
qHoursIDNameEmail.Open "qHoursIDNameEmail", cnCurrent, adOpenKeyset,
adLockOptimistic, adCmdTable
tbDataToBeEmailedIndividual.Open "tbDataToBeEmailedIndividual", cnCurrent,
adOpenKeyset, adLockOptimistic, adCmdTable
tbLinks.Open "tbLinks", cnCurrent, adOpenKeyset, adLockOptimistic, adCmdTable

RetValue = MsgBox("Do you want to send out the incomplete ACIS hours to all
required users?", vbYesNo)

If RetValue = vbNo Then Exit Sub

'Delete data from tables
cnCurrent.Execute "DELETE * from tbUser"
cnCurrent.Execute "DELETE * from tbDataToBeEmailed"
cnCurrent.Execute "DELETE * from tbDataToBeEmailedIndividual"

'Build dataset of employees to be emailed
cnCurrent.Execute "INSERT INTO tbDataToBeEmailed ( [Clock No], [First Name],
Surname, Email, error_date, missing_hour ) " & _
"SELECT tbEmployees.[Clock No], tbEmployees.[First Name],
tbEmployees.Surname, tbEmployees.Email, " & _
"tbExceptionReport.error_date,
tbExceptionReport.missing_hour " & _
"FROM tbEmployees INNER JOIN tbExceptionReport ON
tbEmployees.[Clock No] = tbExceptionReport.auth_id " & _
"WHERE (((tbExceptionReport.DoNotEmail) = False)) " & _
"ORDER BY tbEmployees.[Clock No],
tbExceptionReport.error_date"

'Move to the first record of qHoursIDNameEmail
qHoursIDNameEmail.MoveFirst

For i = 1 To qHoursIDNameEmail.RecordCount

'Loop while not at the last record
While Not qHoursIDNameEmail.EOF

'Assign value to variable
totalNameCount = qHoursIDNameEmail.RecordCount

'Append the ID for only the one user in tbUser
tbUser.AddNew
tbUser!ClockNo.Value = qHoursIDNameEmail![Clock No].Value
tbUser!Email.Value = qHoursIDNameEmail!Email.Value
tbUser!firstname.Value = qHoursIDNameEmail![First Name].Value
tbUser.Update

'Append the data relating to this user
cnCurrent.Execute "qAppDataToBeEmailedIndividual", dbFailOnError

'Populate the from and to date variables
tbDataToBeEmailedIndividual.MoveFirst
FirstDate = tbDataToBeEmailedIndividual!error_date.Value
tbDataToBeEmailedIndividual.MoveLast
LastDate = tbDataToBeEmailedIndividual!error_date.Value

'Cancel email message
On Error GoTo errCancelEmail

'Can send email with no attachment, detailing the from and to dates
Call NotesMailSend(tbUser!Email.Value, "ACIS Time Recording", "*** THIS
IS AN AUTOMATED EMAIL SENT ON BEHALF OF DSI AND WILLIAM BUCK CONSULTING (VIC)
PTY LTD ***" & vbNewLine & vbNewLine & _
"Dear " & tbUser!firstname.Value & vbNewLine &
vbNewLine & _
"You have not entered time into the ACIS
time-recording system on some dates between " & FirstDate & " to " & LastDate
& ". " & _
"It would be greatly appreciated if you could
complete all outstanding timesheet data entries " & _
"by " & tbLinks!ReturnByDate.Value & " in order
that these hours can be included in DSI's ACIS return." & vbNewLine &
vbNewLine & _
"ACIS revenue is very important to DSI and your
cooperation would be very much appreciated. If you are " & _
"experiencing difficulties using the ACIS
system, please let us know by return email (please include " & _
"your telephone number) and we will endeavour to
provide you with follow-up assistance." & vbNewLine & vbNewLine & _
"If you would like to better understand ACIS,
please contact Brian O'Meara via email " & _
"(Brian.O'(e-mail address removed)) to
arrange additional training." & vbNewLine & vbNewLine & _
"Your support of DSI is greatly appreciated.")

'This allows an attachment but will not send automatically
'DoCmd.SendObject acTable, "tbDataToBeEmailedIndividual", acFormatXLS,
tbUser!Email.Value, , , "ACIS Time Recording", _
"*** THIS IS AN AUTOMATED EMAIL SENT ON BEHALF
OF DSI AND WILLIAM BUCK CONSULTING (VIC) PTY LTD ***" & vbNewLine & vbNewLine
& _
"Dear " & tbUser!firstname.Value & vbNewLine &
vbNewLine & _
"Please find attached a listing of the dates on
which you have not entered time into the ACIS time-recording " & _
"system. It would be greatly appreciated if you
could complete all outstanding timesheet data entries " & _
"by " & tbLinks!ReturnByDate.Value & " in order
that these hours can be included in DSI's ACIS return." & vbNewLine &
vbNewLine & _
"ACIS revenue is very important to DSI and your
cooperation would be very much appreciated. If you are " & _
"experiencing difficulties using the ACIS
system, please let us know by return email (please include " & _
"your telephone number) and we will endeavour to
provide you with follow-up assistance." & vbNewLine & vbNewLine & _
"If you would like to better understand ACIS,
please contact Brian O'Meara via email " & _
"(Brian.O'(e-mail address removed)) to
arrange additional training." & vbNewLine & vbNewLine & _
"Your support of DSI is greatly appreciated." &
vbNewLine & vbNewLine & _
"Kind regards,", True

cnCurrent.Execute "DELETE * from tbUser"
cnCurrent.Execute "DELETE * from tbDataToBeEmailedIndividual"
qHoursIDNameEmail.MoveNext
nameCount = nameCount + 1
i = i + 1

Wend
Next

tbUser.Close
tbDataToBeEmailed.Close
tbDataToBeEmailedIndividual.Close
qHoursIDNameEmail.Close

Set tbUser = Nothing
Set tbDataToBeEmailed = Nothing
Set tbDataToBeEmailedIndividual = Nothing
Set qHoursIDNameEmail = Nothing

Exit Sub

errCancelEmail:

Dim ErrorNumber
ErrorNumber = Err.Number

Select Case ErrorNumber
Case 287
MsgBox "Send email cancelled by user", vbOKOnly
Case 2501
MsgBox "Send email cancelled by user", vbOKOnly
Case 2295
MsgBox "Unknown recipient - process stopped", vbOKOnly
Case 3265
MsgBox "Invalid email name - Process stopped", vbOKOnly
Case Else
MsgBox Err.Number & " " & Err.Description, vbOKOnly
End Select

End Sub
 
Try a different CursorType for tbDataToBeEMailedIndividual (perhaps
adOpenDynamic).

The definition of a keyset cursor (what you currently have) is "Like a
dynamic cursor, except that you can't see records that other users add,
although records that other users delete are inaccessible from your
Recordset. Data changes by other users are still visible." While you may be
the only user, the fact that you're using qAppDataToBeEmailedIndividual to
update the table (rather than using the recordset) means that from ADO's
perspective, you're another user.

I don't see why you're opening the recordset at the beginning of the
routine, and making all sorts of changes to the underlying table without
using the recordset. Open it when you need it, not before.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


TommoUK said:
Here's everything as it is stands:

Sub EmailData()

Dim tbDataToBeEmailed As ADODB.Recordset
Dim tbUser As ADODB.Recordset
Dim qHoursIDNameEmail As ADODB.Recordset
Dim tbDataToBeEmailedIndividual As ADODB.Recordset
Dim nameCount As Integer
Dim totalNameCount As Integer
Dim i As Integer
Dim DisplayMsg As Boolean
Dim Counter As Long
Dim tbLinks As ADODB.Recordset
Dim FirstDate As Date
Dim LastDate As Date

Set cnCurrent = CurrentProject.Connection
Set tbDataToBeEmailed = New ADODB.Recordset
Set tbUser = New ADODB.Recordset
Set tbLinks = New ADODB.Recordset
Set qHoursIDNameEmail = New ADODB.Recordset
Set tbDataToBeEmailedIndividual = New ADODB.Recordset
DisplayMsg = True
nameCount = 1

tbUser.Open "tbUser", cnCurrent, adOpenKeyset, adLockOptimistic,
adCmdTable
tbDataToBeEmailed.Open "tbDataToBeEmailed", cnCurrent, adOpenKeyset,
adLockOptimistic, adCmdTable
qHoursIDNameEmail.Open "qHoursIDNameEmail", cnCurrent, adOpenKeyset,
adLockOptimistic, adCmdTable
tbDataToBeEmailedIndividual.Open "tbDataToBeEmailedIndividual", cnCurrent,
adOpenKeyset, adLockOptimistic, adCmdTable
tbLinks.Open "tbLinks", cnCurrent, adOpenKeyset, adLockOptimistic,
adCmdTable

RetValue = MsgBox("Do you want to send out the incomplete ACIS hours to
all
required users?", vbYesNo)

If RetValue = vbNo Then Exit Sub

'Delete data from tables
cnCurrent.Execute "DELETE * from tbUser"
cnCurrent.Execute "DELETE * from tbDataToBeEmailed"
cnCurrent.Execute "DELETE * from tbDataToBeEmailedIndividual"

'Build dataset of employees to be emailed
cnCurrent.Execute "INSERT INTO tbDataToBeEmailed ( [Clock No], [First
Name],
Surname, Email, error_date, missing_hour ) " & _
"SELECT tbEmployees.[Clock No], tbEmployees.[First Name],
tbEmployees.Surname, tbEmployees.Email, " & _
"tbExceptionReport.error_date,
tbExceptionReport.missing_hour " & _
"FROM tbEmployees INNER JOIN tbExceptionReport ON
tbEmployees.[Clock No] = tbExceptionReport.auth_id " & _
"WHERE (((tbExceptionReport.DoNotEmail) = False)) " & _
"ORDER BY tbEmployees.[Clock No],
tbExceptionReport.error_date"

'Move to the first record of qHoursIDNameEmail
qHoursIDNameEmail.MoveFirst

For i = 1 To qHoursIDNameEmail.RecordCount

'Loop while not at the last record
While Not qHoursIDNameEmail.EOF

'Assign value to variable
totalNameCount = qHoursIDNameEmail.RecordCount

'Append the ID for only the one user in tbUser
tbUser.AddNew
tbUser!ClockNo.Value = qHoursIDNameEmail![Clock No].Value
tbUser!Email.Value = qHoursIDNameEmail!Email.Value
tbUser!firstname.Value = qHoursIDNameEmail![First Name].Value
tbUser.Update

'Append the data relating to this user
cnCurrent.Execute "qAppDataToBeEmailedIndividual", dbFailOnError

'Populate the from and to date variables
tbDataToBeEmailedIndividual.MoveFirst
FirstDate = tbDataToBeEmailedIndividual!error_date.Value
tbDataToBeEmailedIndividual.MoveLast
LastDate = tbDataToBeEmailedIndividual!error_date.Value

'Cancel email message
On Error GoTo errCancelEmail

'Can send email with no attachment, detailing the from and to dates
Call NotesMailSend(tbUser!Email.Value, "ACIS Time Recording", "*** THIS
IS AN AUTOMATED EMAIL SENT ON BEHALF OF DSI AND WILLIAM BUCK CONSULTING
(VIC)
PTY LTD ***" & vbNewLine & vbNewLine & _
"Dear " & tbUser!firstname.Value & vbNewLine &
vbNewLine & _
"You have not entered time into the ACIS
time-recording system on some dates between " & FirstDate & " to " &
LastDate
& ". " & _
"It would be greatly appreciated if you could
complete all outstanding timesheet data entries " & _
"by " & tbLinks!ReturnByDate.Value & " in order
that these hours can be included in DSI's ACIS return." & vbNewLine &
vbNewLine & _
"ACIS revenue is very important to DSI and your
cooperation would be very much appreciated. If you are " & _
"experiencing difficulties using the ACIS
system, please let us know by return email (please include " & _
"your telephone number) and we will endeavour
to
provide you with follow-up assistance." & vbNewLine & vbNewLine & _
"If you would like to better understand ACIS,
please contact Brian O'Meara via email " & _
"(Brian.O'(e-mail address removed)) to
arrange additional training." & vbNewLine & vbNewLine & _
"Your support of DSI is greatly appreciated.")

'This allows an attachment but will not send automatically
'DoCmd.SendObject acTable, "tbDataToBeEmailedIndividual", acFormatXLS,
tbUser!Email.Value, , , "ACIS Time Recording", _
"*** THIS IS AN AUTOMATED EMAIL SENT ON BEHALF
OF DSI AND WILLIAM BUCK CONSULTING (VIC) PTY LTD ***" & vbNewLine &
vbNewLine
& _
"Dear " & tbUser!firstname.Value & vbNewLine &
vbNewLine & _
"Please find attached a listing of the dates on
which you have not entered time into the ACIS time-recording " & _
"system. It would be greatly appreciated if you
could complete all outstanding timesheet data entries " & _
"by " & tbLinks!ReturnByDate.Value & " in order
that these hours can be included in DSI's ACIS return." & vbNewLine &
vbNewLine & _
"ACIS revenue is very important to DSI and your
cooperation would be very much appreciated. If you are " & _
"experiencing difficulties using the ACIS
system, please let us know by return email (please include " & _
"your telephone number) and we will endeavour
to
provide you with follow-up assistance." & vbNewLine & vbNewLine & _
"If you would like to better understand ACIS,
please contact Brian O'Meara via email " & _
"(Brian.O'(e-mail address removed)) to
arrange additional training." & vbNewLine & vbNewLine & _
"Your support of DSI is greatly appreciated." &
vbNewLine & vbNewLine & _
"Kind regards,", True

cnCurrent.Execute "DELETE * from tbUser"
cnCurrent.Execute "DELETE * from tbDataToBeEmailedIndividual"
qHoursIDNameEmail.MoveNext
nameCount = nameCount + 1
i = i + 1

Wend
Next

tbUser.Close
tbDataToBeEmailed.Close
tbDataToBeEmailedIndividual.Close
qHoursIDNameEmail.Close

Set tbUser = Nothing
Set tbDataToBeEmailed = Nothing
Set tbDataToBeEmailedIndividual = Nothing
Set qHoursIDNameEmail = Nothing

Exit Sub

errCancelEmail:

Dim ErrorNumber
ErrorNumber = Err.Number

Select Case ErrorNumber
Case 287
MsgBox "Send email cancelled by user", vbOKOnly
Case 2501
MsgBox "Send email cancelled by user", vbOKOnly
Case 2295
MsgBox "Unknown recipient - process stopped", vbOKOnly
Case 3265
MsgBox "Invalid email name - Process stopped", vbOKOnly
Case Else
MsgBox Err.Number & " " & Err.Description, vbOKOnly
End Select

End Sub

Douglas J. Steele said:
I believe Duane's trying to figure out what tbDataToBeEmailedIndividual
is.
If it's a recordset, where are you opening it? How you opened it is
important in terms of knowing whether it's going to see changes.
Click to expand...
Click to expand...
 
Back
Top