Previously we created a form with the wizard but there are other ways to create forms. To create a form in Design view, without the help of a wizard, choose Insert/Form and select one of the options from the list that appears:
In this case we want to create a form based on the TrackDetails table in Design view. On selecting this option we see the following display:
There is an empty form with only the Detail band showing, a list of the fields in the TrackDetails table and the Toolbox.
To change the Grid properties choose Edit/Select Form or press Ctrl-R and then click the Properties button in the toolbar. Scroll down to the GridX and GridY properties on the Format page/tab and set the value to 5 (in metric). This will give you a finer grid for aligning the controls.
To add a footer and a header to the form choose View/Form Header and Footer.
To add items to the header and footer such as labels or images click on the relevant icon in the Toolbox and draw out the space that it will occupy. Select a label and format it from the toolbar in the normal way.
To add fields to the form select the Text Box icon in the Toolbox and then drag the field identifiers from the Field List Box. The field and its default caption will be dropped on the form with the top left corner of the text box at the position of the mouse.
If a field was defined with the Lookup wizard then it would appear automatically on a form generated by the Form Design Wizard as a combo box. This is not the case with a form in Design mode, here a combo box or list box must be explicitly chosen from the Toolbox and then the field set up by the combo box wizard.
You can add other items from the Toolbox such as images or bound or unbound objects. Note that unbound images will not vary as users scan through the records, only bound items included in the table will change with each record.
To move the caption and the text box apart you will have to drag the left border of the text box. To resize the caption or the text box drag the left or right borders. To move a field and its caption move the mouse over the item until you see the hand cursor and then drag it. Note that you can resize multiple selections at the same time.
After adding a number of fields to the form you can edit their position and size so they are aligned in the way you want. To select a column of fields click above them in the ruler (or use Shift-Click); to select a row of fields click in the vertical ruler. To align the selected items use the options on the Format menu, Align, Size, Horizontal and Vertical.
You may want to change one of the fields to a combo box or a List box to make it easier to acquire data for the fields. You might have done this earlier when you set up the table that underlies the form by setting the data type for the field to Lookup Wizard, which allows you to choose data from a list entered by yourself or from a field in an existing table.
It may be, however, that you overlooked the use of a lookup and you now want to make use of one and fortunately you still can. You can also create a lookup in a combo box or a list box from a data source on another form. In the following example we will make the CDID field into a combo box so we can select from it rather than having to type it in.
To change a plain text field to a combo box or list box do the following:
The procedure for creating a list box is almost identical to that for creating a combo box.
You can now delete the original text field for CDID and adjust the size and position of the new combo box control. Replace the label 'SerialNumber' with 'CDID'. In Design view this gives us the following:
An alternative approach is to choose Format/Change To/Combo Box and then use the Properties box to define the row source for the combo or list box.
In Datasheet view the form now has a list available for choosing the serial numbers of CDs. This is not the most useful example as a serial number like this is difficult to deal with.
The SerialNumber field in the CDs table is unique because it is the primary key of the table. As a result each entry in the list of serial numbers is unique and no duplicates appear. In some circumstances a field converted to a combo box may not be a primary key and may have duplicates that you don't want to appear in the list - they will make it long and will take time to skip over.
Using combo boxes has the added benefit of locating an item as the user types. The first letter will filter out all items not beginning with that letter, the second will eliminate all those not including this letter, and so on so the user will quickly find the item they want.
It may be that there is a point where the number of items being read into the combo boxes exceeds the desire of the user to scroll up and down a list or even of memory to support the list on screen. Lengthy lists in the combo boxes would certainly make it difficult to run a database form a web site as it would take a long time to compile the lists.
To remove the multiple occurrence of items proceed as follows:
Switch to Design view for the form, right click the combo box you just created and select Properties. Choose the data tab and click in the Row Source item. A Build button appears (the dieresis), which you can click to create a query for the row source.
Click on the Builder button to view the query as defined so far:
We need to modify the SQL for this query. Choose View/SQL or use the left hand button in the toolbar to switch to SQL view. Type the word 'DISTINCT' into the SQL as follows:
Save the changes to the SQL and update the property. This makes sure that the artists selected for the combo box are distinct or unique.
It would be better still to have taken the list of artists from a separate table where each artist was recorded only once anyway but we haven't created this table yet.
There are properties for the form as a whole, for each section of the form and for each control on the form. To see the properties for the form right click in the small square in the top left corner, between the rulers (or use Edit/Select Form or Ctrl-R). Properties allow you to fine tune the behaviour, format and appearance of sections of a form. Rather than go through each property now we will introduce them as we need them. Most of them are self-explanatory and generally, if there is something you want to do on a form, such as making something read only, this will be done through properties.
The CDs table includes a field to record whether or not a CD is currently available or has been withdrawn or deleted. The Form Design Toolbox includes three controls for capturing Yes/No data, the Toggle Button, the Option Button and the Check Box. Rather than accept a text box control for a Yes/No field type you can insert one of these alternative graphical controls instead. Delete the original control and then add one of these three alternatives. Edit the Label for the control to set it to what you want.
Tab order is the order in which fields are accessed on a form when the Tab key is pressed. The Tab key is very useful during data entry so it is important that the tab order should be kept in sequence on a form. The tab order of the controls on a form may be changed during the design process but it can be customised at the end to ensure easy data entry. To view and change the Tab order choose View/Tab Order.
As well as using colour and other formatting the appearance of a form can be enhanced by adding lines and rectangles to emphasise zones of a form.
This button in the Toolbox opens a list of ActiveX controls installed on the computer. One particularly useful control is the Calendar control that comes with Windows. Where you have a date field in a table and on a form you can use the Calendar ActiveX control so that the user can choose a date rather than type one in.
To add an ActiveX control click on the More Controls button in the bottom right corner of the Toolbox and choose 'Calendar Control n.n' from the list of available controls. Drag out a rectangle on the form with the mouse pointer - you will need to make room for it. Alternatively, choose Insert/ActiveX, which opens a dialogue box:
Now right click the calendar and choose Properties. Click the Data tab and choose the field that you want to associate with the calendar control - this should, of course, be a field of type Date/Time.
Close the Properties box and switch to Data view on the form. As you scroll through the records you will see the date on the calendar change. If you click on a different day and then click in another field the date field will be changed to the new value in the calendar.
Smart Tags are a feature of Office and are available in Word and Excel as well as Access. In Access you can add a Smart Tag to a label, a text box, a combo box or a list box to examine the value of the text in the control and to take action accordingly. Access has some pre-defined smart tags including:
To add a smart tag to a hyperlink field that contains an email address proceed as follows:
In form Design view right click the hyperlink/email field and choose Properties.
Choose the Data tab and click in the Smart Tags field. Click on the dieresis to build the smart tag. Choose the Person Name option and note the actions associated with this tag: Send Mail, Schedule a Meeting, Open Contact, Contact Details. These options will appear in a menu on the form.
Click OK and close the Properties box for the hyperlink.
Now when an email address appears in a hyperlink control there will be a small triangle in the lower left corner of the text area and an Information button to click to open the menu of options.
It would probably be a good idea to have separate fields for websites and emails so that the smart tag applies only to those containing an email.
After wading through the manual procedures for setting up a form you probably think that it would be better in future to use a wizard and then fine-tune the form produced automatically. You are probably right. In future when you want to add a form choose the wizard.