Article Index
Microsoft Access Tutorial
Queries
Creating A Database
Tables
Creating Tables
Queries
Forms In Microsoft Access
Reports In Microsoft Access
Data Access Pages
Controlling The User's Input
Subdatasheets
Designing Forms
Exploring And Analyzing Data
Tables Looks
Design And Exploration
Forms Design Propertie
Controlling Data Output
Microsoft Access Controls
Design And Improvements
Importing And Exporting Data
Macros
Switchboards

One of the most important features of a database system is to avoid typing repeated data. Another one is to give calculation processes to the software. You can use expressions and calculated fields to ask Microsoft Access to take care of that side of headache for you.

Text Display:

Many techniques are available to manipulate the display of text of your form or report. You can combine portions of a name to display a full name by adding the first name, the middle name, and the last name. The same process can be applied to display an address.

Start Microsoft Access and open the Employees3 database.

Double-click the frmEmployee form to open it.

When you have finished viewing it, switch to Design View. Make sure you have the toolbox handy.

From the Toolbox, click the Text Box and drop one just above the Notes box (I left enough space in that area for the labels and boxes we are going to use).

Click the new label, click it again and edit it to read Full Name. After you have typed, press Enter. While the label still has focus, press and hold Shift, then click the Work Phone label to select them both. Right-click any of the selected labels and choose Size -> To Widest. Format the label and the text box to appear like their respective counterpart on the form.

Right-click the Unbound text box and choose Properties. Click the All tab. Give the name FullName to the text box. In the Control Source field, type =[FirstName] & ' ' & [MI] & ' ' & [LastName]. Close the Properties dialog.

Switch to Form View to view the result. As you move through different records, you find out that the form can display names in full format. But there is a problem. If a full name has an MI, it displays fine; but a name that doesn't have an MI displays an empty space, which is annoying and unprofessional. To fix this, we will use the Immediate If function, called IIf. When you have finished, switch to Design View.

Right-click the FullName text box and choose Properties. Right-click the Source Code field and choose Zoom... Edit the content to read: =IIf(IsNull([MI]),[First Name] & ' ' & [LastName], [FirstName] & ' ' & [MI] & ' ' & [LastName]). In simple terms, this code means: "Hi, text box, if a person doesn't have a middle initial name, ignore it and just display her first then last name, otherwise (if she does have a middle initial name) display them all".

In the Tool Box, click another Text Box and drop that one on the right of the full name's.

Edit the new label to read User Name. Format both items to be in harmony with the other items on the form.

Right-click the new Unbound box and choose Properties.

Name the text box UserName and press Enter. On the right of the Control Source field, click the Build/ellipsis button.

Different companies use different naming conventions to assign user names to their employees. Here, we will combine an employee last name plus the first letter of his first name. In the Expression Builder dialog, click the = button. Make sure the frmEmployees is selected in the left list.

In the middle list, double-click LastName. Click the & button. In the left list, double-click Functions to expand its folder, then click Built-In Functions. In the middle list, click Text. In the right list, double-click Left. In the top expression, click <<stringexpr>> to highlight it. In the left list, click frmEmployees. In the middle list, double-click FirstName. In the top expression, click <<n>> and type 1. Click OK.

Back in the Text Box Properties dialog, convert the user name to lowercase by typing < in the Format field.

Close the Properties dialog and switch to Form View to view the result. When you have finished, switch back to Form View and get the Properties dialog.

Again, most companies use the user name as the e-mail name for an employee and append it the company domain to give her an e-mail address in the form of " This e-mail address is being protected from spam bots, you need JavaScript enabled to view it ". I will use the same process here to show you how you can append static text to an expression. First, change the name of the text box from UserName to EMailAddress.

Right-click in the Control Source field and choose Zoom... Append the domain name to complete an e-mail address as follow:

=[LastName] & Left([FirstName],1) & "@functionx.com".

Close the Properties and switch to Form View.

Calculated Expressions:

You can defer the traditional calculations to Microsoft Access to reduce typing mistakes. Once you set your expressions clearly, the software will take care of the rest. You can type most expressions manually, otherwise, the Expression Builder that we have used earlier can assist you.

Open the Videos5 database.

Double-click the frmVideos form to open it.

Switch to Design View.

From the Toolbox, click the Text Box and click on the right of Qty to add a text box to the form. Change the text of the label to Total. Format both the label and the text box to harmonize with the other items on the form.

Right-click the text box and choose Properties. From the All tab, change its name to Total. In the Control Source field, click the build button. In the Expression builder dialog, click the = button. Make sure frmVideos is selected in the left list. In the middle list, double-click VideoPrice. Click the * button. In the middle list, double-click Quantity. The expression should now read

= [VideoPrice] * [Quantity]. Click OK. In the Properties dialog, set the text box Format to currency. Close the Text Box Properties.

Switch to Form View. After playing with the form, close it and close the Videos5 database.

Subforms:

A subform is a child of a form that allows you to instantaneously see or access data related to the parent table. You can create a subform along with its parent table using the form wizard. You can also use of of the related table as a subform. In Microsoft Access 2000, you can also create a table when using the New Object:AutoForm feature. Using the Silver National Bank database, we will make sure the user can view and even process bank transactions from the subform.

Open the Silver National Bank3 database.

Click the Forms object or tab. Click the New button. Choose Design View and base the form tblTransactions.

Right-click on the form and choose Form Header/Footer. In the Header section, place 6 labels as:

#, Date, Type, Deposit, W/D Amt, Charge.

Drag TransactionNumber, TransactionDate, TransactionTypeID, DepositAmount, WithdrawalAmount, ServiceCharge to the Detail section. Every time you drop a text box, delete its label, place the text box to the very top under its corresponding header label.

Save the form as sbfAccounts.

Access the subform's Properties. Set the Navigation Buttons field to No.

To perform some calculations about an account's transactions, we need to regularly update a customer's routine transactions: deposits, withdrawals, service charges, etc.

Click the Text Box on the Toolbox, click to the left of the Footer section, delete the label of the new Text Box, resize the Text Box to roughly 2 divisions (the user will not see/use it). On the Properties dialog, change the name of the text box to TotalDeposits, and set its Control Source to =Sum([DepositAmount]). Drop another Text Box to the right of the TotalDeposits. Delete its label, shrink or narrow it, change its name to TotalWithdrawals, and its Control Source to =Sum([WithdrawalAmount]).

Add one more Text Box to the right of the existing text boxes in the Footer section. Delete its label and shrink it, change its name to TotalServiceCharges, and set its Control Source to =Sum([ServiceCharge]).

Add one last Text Box on the right of the Footer section. Change the name of its label to lblBalance and the Caption of its label to Balance:. Change the name of the Text Box to Balance, and its Control source to:

=nz([TotalDeposits])-nz([TotalWithdrawals])-nz([TotalServiceCharges])

Select the first three text boxes (TotalDeposits, TotalWithdrawals, and TotalServiceCharges). On the Properties dialog, set their Visible field to No. Select all the text boxes in the Footer section and set their Format to Currency.

Before leaving, drag AccountID from the Field List to the Header section of the form (you can drop it anywhere) and set its Visible field to No. Save and close the subform.

In Design View, create a form based on the tblAccounts. Save the form as frmAccounts.

Open the frmAccounts2 form in Design View. On the Toolbox, click the Page Break control and click in the Detail section far left but right on 2 1/4.

Make sure the Control Wizard on the Toolbox is pressed. On the Toolbox, click the Subform/Subreport control. Click on the form under the page break. In the first page of the Subform/Subreport Wizard, click the Forms combo box and choose sbfAccounts1. Click Next, Next, and Finish. Switch to Form View.

On the main menu, click Window -> Size to Fit Form. Close the form.

Open the frmAccounts3 form.

Click Control Wizard button on the Toolbox to deselect it. On the form, scroll down completely to see the Footer section.

On the Toolbox, click Command Button. Click under the footer bar at 1 inch of the horizontal ruler. On the Properties dialog, change the button's name to DetailsBtn and its Caption to &Details. Drop another button on the right of the first button, change its name to TransactionsBtn and its Caption to &Transactions.

Right-click the Details button and choose Build Event... In the Choose Builder dialog, click Code Builder and click OK. Implement the procedure as follows:

Private Sub DetailBtn_Click()
Me.GoToPage 1
End Sub

In Microsoft Access, right-click the other button, choose Build Event... and double-click Code Builder. Implement the procedure as follows:

Private Sub TransactionsBtn_Click()
Me.GoToPage 2
End Sub

Close Visual Basic. Switch the form to Form View. If necessary, on the main menu, click Window -> Size to Fit Form. Click the buttons in the footer section and make sure they allow you to access the top and bottom section of the form. Save the form.

Close the Silver National Bank3 database.

Multiple Calculations on a Form:

Sometimes the tricky thing about calculations is to remember all the elements involved to produce the desired or right result. That's what happens when you dealing with restaurant, dry cleaning stores, etc. The calculation by itself might be a big deal and usually you don't need to get Visual Basic involved.

Open the Georgetown Cleaning Services5 database.

Open the frmCleaningOrders form in Design View. Set the DepositDate's Default Value to =Date(). That will display the current date for the user on the form. Set the Default Value of the RetrieveDate to =[DepositDate]+2 (this cleaning store takes two days to clean clothes). That will automatically display the retrieve date but still give the user the option of changing the retrieve date.

On the Toolbox, click the Text Box and clickright to the PantsPrice text box. Delete the label of the new text box. On the Properties dialog, change the name of the new text box to ttlPantsPrice. Set its Control Source to =Pants*PantsPrice. Set its Format to Currency, and its font color to brown.

Repeat the same technique to every one of the items on the form.

Open the frmCleaningOrders1 form. Switch the form to Design View.

From the Toolbox, click the Text Box and click in the Footer section. Change the label's name to lblTotal and its Caption to Total:. Change the Text Box's name to Total. Set its Control Source to:

=nz([ttlPantsPrice])+nz([ttlMenSuits2PiecePrice])
+nz([ttlMenSuits3PiecePrice])+nz([ttlSweaterPrice]) +nz([ttlShirtRegularPrice])+nz([ttlShirtSilkPrice]) +nz([ttlShirtWoolPrice])+nz([ttlTiePrice])
+nz([ttlWomenSuitPrice])+nz([ttlDressesPrice])
+nz([ttlSkirtBeltPrice])+nz([ttlSkirtHookPrice]) +nz([ttlCoatPrice])+nz([ttlJacketPrice])
+nz([ttlBlousePrice])+nz([ttlBeltPrice])

Switch the form to Form View. The Total box should now work and display the price the customer owes for the cleaning.