Anyone know why this code is failing ?

[Guest]

This has me stuck. I'm getting "Automation error Unspecified Error" on the
second rs.open and it triggers the form_undo event. This function is run from
the form_load event. Any clues?

On Error GoTo ER
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngWhen As Long, lngMins As Long
strSQL = "SELECT COUNT(*) As Num FROM tblNotEmailed WHERE Agent = '" &
NTUserID & "'"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
NumNotEmailed = rs.Fields("Num").Value
If NumNotEmailed = 0 Then
Me!butNotEmailed.Visible = False
Me.TimerInterval = 0
Else
rs.Close
strSQL = "SELECT MIN(When) As MW FROM tblNotEmailed WHERE Agent = '" &
NTUserID & "'"
rs.Open strSQL, CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly
lngWhen = rs.Fields("MW").Value
lngMins = DateDiff("n", lngWhen, Now())
If lngMins < 30 Then
Me.TimerInterval = 600
Else
If lngMins < 45 Then
Me.TimerInterval = 300
Else
Me.TimerInterval = 150
End If
End If
Me!butNotEmailed.Visible = True
End If
rs.Close
Set rs = Nothing

 

[Douglas J. Steele]

"When" is a reserved word, and so shouldn't be used as a field name.

If you cannot (or will not) change the name of the field, try putting square
brackets around it:

strSQL = "SELECT MIN([When]) As MW FROM tblNotEmailed WHERE Agent = '" &
NTUserID & "'"

For a good discussion of names to avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html