Building the User Interface

Now that the tables have been created, it’s time to move on to creating the empty forms for the user interface. You can then write the code in class modules and standard modules, and finish off by adding code to each form to call the class modules and standard modules. The example in this chapter uses ADO to connect to the ProjectTrackerDb database, but you can alternatively or additionally add an explicit link to the separate database so you can see the tables from within your user interface project.

Try It Out-Building the frmProjects and frmContacts Forms

Now let’s create the separate database file that will store the user interface objects:

1. Select the Office Button New. From the list of available templates, select “Blank Database” and specify ProjectTrackerUI for the file name. Click the Create button.

2. Create a new form called frmProjects. First, open the form in design view. From the ToolBox, double-click the tab control and size it appropriately on the form. Select the tab control and right-click, so you can insert a third tab if one is not already present by using the Insert Page option. You add additional tabs/pages as illustrated in Figure 13-13.

   
   Figure 13-13

3. Next, drag and drop the numerous other controls onto the form and page 1 of the tab, as shown in Figure 13-14.

Figure 13-14

4. The following table will help ensure that you have all the correct objects on the form. Rename the controls and change other properties for each object as shown in the table.

Open table as spreadsheet

Default Name in Figure 13-14	Name	Type of Control	Other Properties to Set from the Properties Dialog
Form1	frmProjects	Form	Record Selectors = No; Navigation Buttons = No; Dividing Lines = No; Pop up = Yes; Caption = "Project Tracker"
Text5	txtProjectId	Text Box	Set corresponding label caption to "Project ID:"
Text7	txtProjectTitle	Text Box&	Set corresponding label caption to "Project Title:"
Text9	txtProjectDesc	Text Box	Set corresponding label caption to "Project Description:"
Combo25	cboPriority	Combo Box	Set corresponding label caption to "Priority:"
Text11	txtReferenceNum	Text Box	Set corresponding label caption to "Reference/Matter #:"
Text13	txtMoneyBudget	Text Box	Set corresponding label caption to "Money Budget:"
Text15	txtMoneyToDate	Text Box	Set corresponding label caption to "MoneyToDate:"
Text17	txtHoursBudget	Text Box	Set corresponding label caption to "Hours Budget:"
Text21	txtHoursToDate	Text Box	Set corresponding label caption to "Hours to Date:"
Text23	txtDateDue	Text Box	Set corresponding label caption to "Date Due:"
Combo27	cboStatus	Combo Box	Set corresponding label caption to "Status:"
Toggle31	togShowUnclosed	Toggle Button	Caption = "Show Unclosed"
Toggle32	togShowAll	Toggle Button	Caption = "Show All"
Command33	cmdMoveFirst	Command Button	Caption = "Move First"; Picture = select Go to First 1 from list
Command34	cmdMovePrevious	Command Button	Caption = "Move Previous"; Picture = select Go to Previous 1 from list
Command35	cmdMoveNext	Command Button	Caption = "Move Next"; Picture = select Go to Next 1 from list
Command36	cmdMoveLast	Command Button	Caption = "Move Last"; Picture = select Go to Last 1 from list
Command37	cmdAddNew	Command Button	Caption = "Add New"
Command38	cmdSave	Command Button	Caption = "Save Changes"
Command39	cmdDelete	Command Button	Caption = "Delete"
Page1	pgComments	Page of Tab	Control Caption = "Comments/Tasks"
List41	lstComments	List Box	Set corresponding label caption to "Comments/Tasks:"
Command47	cmdDeleteComment	Command Button	Caption = "Delete Comment"
Command48	cmAddComment	Command Button	Caption = "Add Comment"
Text45	txtAddComment	Text Box	Set corresponding label caption to "Comment/Task Text:"
Label66	lblRecordNum	Label	Caption = "Record # of #"

5. Next, select Page 2 of the tab control and drag the additional controls onto that tab, as shown in Figure 13-15.

Figure 13-15

6. The following table will help ensure that you have all the correct objects on Page 2 of the tab control on frmProjects. Rename the controls and change other properties for each object as shown in the table.

Open table as spreadsheet

Default Name on Figure 13-15	Name	Type of Control	Other Properties to Set from the Properties Dialog
Page2	pgContacts	Page of Tab Control	Caption = "Contacts"
List49	lstContacts	List Box	Set corresponding label caption to "Related Contacts:"
Command53	cmdViewContact	Command Button	Caption = "View Details for Selected Contact"
Command54	cmdManageContacts	Command Button	Caption = "Add/Manage Contacts"
Command59	cmdDeleteContact	Command Button	Caption = "Delete Selected Contact"
Command60	cmdEmailContact	Command Button	Caption = "Email Selected Contact"; Picture = select Mailbox from list of pictures

7. Next, select Page 3 of the tab control and drag the additional controls onto that tab, as shown in Figure 13-16.

Figure 13-16

8. The following table will help ensure you have all the correct objects on Page 3 of the tab control on frmProjects. Again, rename the controls and change other properties for each object as shown in the table that follows.

   Open table as spreadsheet

   Default Name on Figure 13-16	Name	Type of Control	Other Properties to Set from the Properties Dialog
   Page3	pgFileAttachments	Page of Tab Control	Caption = "File Attachments"
   List55	lstFileAttachments	List Box	Set corresponding label caption to "Related File Attachments:"
   Command61	cmdRemoveAttachment	Command Button	Caption = "Remove File Attachment"
   Command64	cmdAddAttachment	Command Button	Caption = "Add File Attachment"
   Text57	txtFileDesc	Text Box	Set corresponding label caption to "File Description:"
   Text 62	txtFileName	Text Box	Set corresponding label caption to "File Name:"
   Command65	cmdOpenFile	Command Button	Caption = "Open Selected File"; Picture = select Preview Document from list
   Command67	cmdFileBrowse	Command Button	Caption = "File Browse"; Picture = select Open Folder from list

9. Verify that each control on frmProjects has the correct name and other property settings as listed in the prior tables. If any of these are misspelled, later code referencing them will produce an error you will need to fix. The form should now appear similar to those shown in Figures 13-17 to 13-19.

   
   Figure 13-17

   
   Figure 13-18

   
   Figure 13-19

10. Make sure to save all your changes to frmProjects.

11. Next, create a new form called frmContacts. Drag and drop the controls onto the form, as shown in Figure 13-20.

    
    Figure 13-20

12. The following table will help ensure that you have all the correct objects on the form. Rename the controls and change other properties for each object as shown in the table.

Open table as spreadsheet

Default Name on Figure 13-20	Name	Type of Control	Other Properties to Set from the Properties Dialog
Form2	frmContacts	Form	Record Selectors = No; Navigation Buttons = No; Dividing Lines = No; Pop up = Yes; Caption = "Contacts"
Text0	txtLName	Text Box	Set corresponding label caption to "Last Name:"
Text2	txtFName	Text Box	Set corresponding label caption to "First Name:"
Text4	txtMName	Text Box	Set corresponding label caption to "Middle Name:"
Text6	txtCompany	Text Box	Set corresponding label caption to "Company:"
Text8	txtAddress1	Text Box	Set corresponding label caption to "Address1:"
Text10	txtAddress2	Text Box	Set corresponding label caption to "Address2:"
Text12	txtCity	Text Box	Set corresponding label caption to "City:"
Text14	txtRegion	Text Box	Set corresponding label caption to "State/Region:"
Text16	txtPostalCode	Text Box	Set corresponding label caption to "Postal Code:"
Text18	txtWorkPhone	Text Box	Set corresponding label caption to "Work Phone:"
Text20	txtHomePhone	Text Box	Set corresponding label caption to "Home Phone:"
Text22	txtCellPhone	Text Box	Set corresponding label caption to "Cell Phone:"
Text24	txtEmail	Text Box	Set corresponding label caption to "E-mail:"
Command26	cmdAddToProject	Command Button	Caption = "Add Current Contact to Project"
Command33	cmdMoveFirst	Command Button	Caption = "Move First"; Picture = select Go to First 1 from list
Command34	cmdMovePrevious	Command Button	Caption = "Move Previous"; Picture = select Go to Previous 1 from list
Command35	cmdMoveNext	Command Button	Caption = "Move Next"; Picture = select Go to Next 1 from list
Command36	cmdMoveLast	Command Button	Caption = "Move Last"; Picture = select Go to Last 1 from list
Command37	cmdAddNew	Command Button	Caption = "Add New"
Command38	cmdSave	Command Button	Caption = "Save Changes"
Command39	cmdDelete	Command Button	Caption = "Delete"
Label27	lblRecordNum	Label	Caption = "Record # of #"

13. After setting all the properties, frmContacts should now appear as shown in Figure 13-21.

Figure 13-21

14. Save all the changes for frmContacts.

15. A Database Window showing how the ProjectTrackerUI database should appear at this point is illustrated in Figure 13-22.

Figure 13-22

How It Works

The frmProjects form will be used to display project records. You added various controls to this form, including several text boxes and other controls that will be used to display data and accept input from a user. The primary data for the frmProjects form will come from the tblProjects table. Additional data for the tabs on the tab control will come from the tblProjectsComments, tblProjectsContacts, and tblProjectsFileAttachments tables.

The frmContacts form will be used to display contact records for all contacts or for a selected contact. You also added various controls to this form that will be used to display the contact data and accept input from a user. The data for the frmContacts form will come from the tblContacts table.