Code for messaging not working 1

[mguidry]

Below is a bit of code from the basMail module from the Access Cookbook, 2nd Ed (example 10-04, I think). I'm using Access 2k, and I can't get it to work right. I'm opening frmReceiveMail with and AutoExec macro at startup of my database. The form is not re-opening from minimized when new messages are available - not showing "New Mail!" caption either. Any ideas?

P.S. I've enabled DAO references already.

Function acbCheckMail() As Integer

    ' Check for new mail, and if there is any,
    ' restore the received mail form
    
    On Error GoTo HandleErr
    
    Dim rstClone As DAO.Recordset
    Dim frmMail As Form
    
    Set frmMail = Forms("frmReceiveMail")
    frmMail.Requery
    
    Set rstClone = frmMail.RecordsetClone
    If Not rstClone.EOF Then
        rstClone.MoveFirst
        frmMail.Caption = "New Mail!"
        If IsIconic(frmMail.Hwnd) Then
            frmMail.SetFocus
            DoCmd.Restore
        End If
    Else
        frmMail.Caption = "No mail"
    End If
    
ExitHere:
    Exit Function
    
HandleErr:
    Select Case Err.Number
        Case 3021       ' no current record, do nothing
        Case Else
            MsgBox Err & ": " & Err.Description, , "acbCheckMail()"
    End Select
    Resume ExitHere
End Function

 

[faeryfyrre]

Your problem is that Access2k uses ADODB Recordsets to drive it's forms.

You can't assign an ADODB Recordsetclone to a DAO recordset object.

modify your variable declaration to

Dim rstClone As ADODB.Recordset

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[mguidry]

When I try that, I get an Error 13: Type mismatch.

 

[PHV]

On which event is acbCheckMail launched ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[faeryfyrre]

When run as you originally posted the code, did you get any errors? Or did the code just not do what you expect?

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[mguidry]

First, to PHV: The event runs on load and on timer interval (10000, or ten seconds).

To Alec: I don't get any errors when the code runs as is. It just doesn't do as I expect. Form doesn't restore, and caption doesn't change from "No Mail" to "New Mail!" like it says it should.

 

[PHV]

Perhaps a DoEvents between the .Requery and .RecordsetClone ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[faeryfyrre]

If the Form is not restoring to it's previous size then i would suggest that the code doesn't reach the docmd.restore line in your code.

Have you traced through the code to see what it does line by line? Does it ever reach the restore line?

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[mguidry]

I have traced through the code, and it doesn't give me errors, but it seems to "pass over" the lines of code that involve the recordsetclone function. I'm not very VBA savvy, so I didn't know if that was the norm or not.

If you guys have time, I have another question that's driving me nuts at thread181-945430

I appreciate your help.

 

[faeryfyrre]

I'm assuming you set breakpoints on every line of code in the body of the function. So the code "passes over" the Recordsetclone portion of code?

Try modifying the code slightly

    Set rstClone = frmMail.RecordsetClone
    [COLOR=red]rstClone.MoveFirst[/color]
    If Not rstClone.EOF Then
        frmMail.Caption = "New Mail!"
        If IsIconic(frmMail.Hwnd) Then
            frmMail.SetFocus
            DoCmd.Restore
        End If
    Else
        frmMail.Caption = "No mail"
    End If

Alec Doughty
Doughty Consulting P/L

"Life's a competition. Play hard, but play fair"

 

[mguidry]

Wow. How simple was that? It works now. One stinking line of code out of place, and the whole thing goes buggy. Thanks a bunch!