Access Cookbook - Ken Getz [212]
Figure 10-19. frmReceiveMail displays a special message when there is no new mail
This trick is accomplished by setting the form's AllowAdditions property to No, adding a watermark picture to the form, and adding an opaque rectangle that hides the watermark when there are records in the form's recordset. When there are no records in a form's recordset and you have set AllowAdditions to No, Access hides all of the form's controls—including the unbound Rectangle control—and prominently displays the form's watermark, if there is one.
This method uses the Access username to track mail senders and recipients. To use it in production, you'll need to activate Access security (otherwise, everyone is signed on as the Admin user at all times). To activate security, simply use Security Change Password to assign a password to the Admin user. Then you can select Users from the Security menu and create as many new users as you like. Security was discussed in more detail in the Solution in Recipe 10.1.
To test this solution with multiple users, you'll need to have several machines available on a network. Make a copy of 10-04fe.MDB for each computer, and use File → Get External Data Link Tables to link the same copy of tblMessage to each one. Log in as a different user at each computer, and you'll be able to send messages back and forth.
You can adjust the performance impact of this technique by changing the TimerInterval property of frmReceiveMail. This property measures the number of milliseconds between each execution of the OnTimer event. In the sample database, the TimerInterval property is set to 10000 milliseconds, or 10 seconds; its highest possible value is 65535, or just over a minute. If you want a longer delay, you can add a static integer variable to acbCheckMail and increment it more than once before you check for new mail.
See Also
For more on working with Outlook programmatically, see Recipe 12.8 in Chapter 12.
10.5. Programmatically Track Users and Groups
Problem
As the database administrator, you want to be able to track users and their groups within your workgroup. How can you gather the information you need?
Solution
Using Data Access Objects (DAO), you can retrieve all the information you need about users' names and groups. Once you have that information, you can use it in creating your applications.
The sample form frmUserGroups in 10-05.MDB fills tables with the information you need and presents it to you in a list box. To test it, open and run frmUserGroups. Figure 10-20 shows the form in use for a sample workgroup.
Figure 10-20. frmUserGroups shows users and groups for a sample workgroup
To gather this information in your own applications, follow these steps:
Create the tables you'll need to hold the information. Either import the three tables from 10-05.MDB, or use the information in Table 10-13 to create your own.
Table 10-13. Table layouts for gathering user/group information
Table name
Field name
Field type
Primary key?
tblGroups
Group
Text
No
GroupID
Counter
Yes
tblUserGroups
UserID
Number (Long Integer)
Yes
GroupID
Number (Long Integer)
Yes
tblUsers
UserName
Text
No
UserID
Counter
Yes
If you created your own tables in Step 1, you'll need to add an index to tblGroups. In the Indexes properties sheet (available by choosing View → Indexes when tblGroups is open in design mode), add a row as described in Table 10-14 for the index properties. Table 10-14 also shows the primary key row that should already exist in the Indexes properties sheet.
Table 10-14. Index settings for tblGroups
Index name
Field name
Sort order
Group
Group
Ascending
PrimaryKey
GroupID
Ascending
Either import the module basListUsers from 10-05.MDB, or enter the following code into a global module. This is the code you'll use to fill the three tables you just created:
Public Sub acbListUsers( )
' Create tables containing all
' the users and groups in the