Access Cookbook - Ken Getz [211]
Add a command button to the form, with the properties shown in Table 10-12.
Table 10-12. Properties for cmdReceive
Property
Value
Name
cmdReceive
Caption
&Mark as Read
OnClick
=acbReceiveMail( )
Save this form as frmSendMail.
Discussion
This technique works by passing messages back and forth through tblMessage. The sending form is unbound, because when you send a message, you don't want to have to flip through all the previous messages. The acbSendMail function just takes whatever you type into the form and puts it into this table. It also uses the CurrentUser function to put your name into the From field of the table, and the Now function to time-stamp the message. The acbSendMail function is shown here:
Public Function acbSendMail( ) As Integer
' Take the message and user from the
' frmMailSend form and send it to the mail
' backend
On Error GoTo HandleErr
Dim db As DAO.Database
Dim rstMail As DAO.Recordset
Dim frmMail As Form
Set db = CurrentDb( )
Set rstMail = db.OpenRecordset( _
"tblMessage", dbOpenDynaset, dbAppendOnly)
Set frmMail = Forms("frmSendMail")
rstMail.AddNew
rstMail("From") = CurrentUser( )
rstMail("To") = frmMail.cboTo
rstMail("DateSent") = Now
rstMail("Message") = frmMail.txtMessage
rstMail.Update
frmMail.cboTo = Null
frmMail.txtMessage = Null
ExitHere:
On Error Resume Next
rstMail.Close
Err.Clear
Exit Function
HandleErr:
MsgBox Err & ": " & Err.Description, , "acbSendMail( )"
Resume ExitHere
End Function
Opening the recordset with the dbAppendOnly flag accelerates the process of adding a new record because it avoids reading in the existing records that the send function doesn't care about.
The cboTo combo box uses a list-filling function to fill the combo box with a list of current users in the workgroup. List-filling functions were discussed in the Solution in Recipe 7.5. This particular function fills its list using security data access objects to iterate through the collection of users in the workgroup. We defer discussion of this topic to the Solution in Recipe 10.5.
The Receive Mail form is based on a query that finds all messages directed to the current user that have nothing in their DateReceived fields. By default, new records added from elsewhere on a network do not show up on an already-opened form; you must explicitly requery the form for this to happen. The acbCheckMail function automatically performs this requery at load time and once every 10 seconds to check for new mail. The acbCheckMail function is shown here:
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
After the form is requeried, acbCheckMail checks for new mail by looking at the RecordsetClone property of the form. This property returns an exact duplicate of the form's underlying recordset. If there are any records to be shown, this RecordsetClone will not be at its EOF, so the function changes the form's caption and, if it is currently minimized, restores the form to its full size. The function calls the Windows API function IsIconic (declared in the declarations section of basMail) to determine if the form is minimized.
We have used the form's Picture property, a rectangle, and the form's AllowAdditions property to add one more effect to the form: when the form's recordset is empty, all the controls on the form disappear and a bitmap reading