Jan's Working with Databases

Step-by-Step: Create Form Manually

---

What you will learn:

to create a blank form to add multiple controls from Field List to resize multiple controls - drag to resize and position control(s) to move control without moving label to add non-adjacent controls from Field List to align controls and labels to resize control with Property Sheet to check alignment with ruler to resize form to add a title control to Form Header

---

Start with:  , resource files, worldtravel-Lastname-Firstname.accdb from folder databases project4 as updated in the previous lesson

Create Blank Form

1. In the Create ribbon tab, click on the button Form Design.

   
   

   A blank form opens in Design View. Only the Detail section is included automatically.
   You must tell Access which table to use as the Record Source.
2. If necessary, click on the button Property Sheet to open the Property Sheet pane.
3. If necessary, click on the All tab.
4. Drop the list of tables and queries for the property Record Source and click on Clients.
   
   
5. Click on the button Add Existing Fields .
   The pane changes to show the fields from the Clients table.
6. If necessary, click the link Show all tables at the top.
   The bottom of the pane changes to show tables that are related to the Clients table and then lists other tables at the bottom.

   For the form results to make sense, stick to related tables or (even better) create a query first and make sure it gives the results you want. Then use that query as the record source. Otherwise, your form results will not make any sense!

   
    

---

Add Multiple Controls from Field List: Column 1 (Name Fields)

The easiest way to manually add a field to a form is to drag and drop it from the Field List. The type of control added depends on the field's data type. Later you can change the type of control, if you need to.

Alternate method: Add the type of control you want using the buttons in the Controls gallery. Then change the Record Source property in the Property Sheet.
Some types of controls won't accept some data types.

1. Select adjacent fields:
   Click on the first field in the Field List, ClientID. Hold the SHIFT key down and click on the field Title.

   All fields between the two your clicked are selected.
   These are the fields having to do with the client's name.
    

2. Drag the fields to the blank form.
   The pointer changes to the Drag shape .

3. Drop at the top left, below the 'e' in the word Detail.
   Whoops. The controls are overlapping the labels!

   Drop point: Access puts the top left corner of the control at the point where you dropped, and puts the label to the left. So you must allow for the label when you drop, without being able to see exactly where the label will land. Awkward!
    
   When there is not enough room for the whole label on the form at the drop location, the label starts at the form's edge and extends underneath the control. You need to move the controls and leave the labels alone but dragging with the Move handle will only move that particular control. You cannot move just the controls as a group. Instead, let's back up and try to drop again in a better location.

   Problem: Font is different from illustration
   You applied a theme to the form that you edited yourself. Themes apply to all objects in the database!
   Solution: Change the theme/autoformat back to the default Office theme.
   

4. Undo.
   

5. If necessary, select the fields again in the Field List.
   

6. Drag and drop the fields below the 1" mark on the horizontal ruler.
   Ah! Better.
   Access is trying to be smart and helpful. It has lined up all of the labels and controls. It has left some space between the end of the widest label (MiddleName) and its control. There is just a little bit of vertical space between the controls - one row of grid dots.
   
   However, Access used the default width for all of the controls, one inch. Some of the other fields will need more space and some of these do not really need this much width. Too much space is probably better than not enough!

7. Switch to Form View
8. Navigate through all 16 of the records.
   Are any of the controls too narrow for the data?
   Yes, the Title control is not wide enough in the record for Luis Perez.
    
   How to see data that is out of view: Even when the control is too small to show all of the data, the data is NOT lost. It's still there. Memo/Long Text fields will get scroll bars but other control types do not. You can see the hidden data in Form View by putting your cursor in the field and using the arrow keys. You can also drag inside the field and the data will scroll into view as you drag. Try it!
   
   Evaluate: Which is more important: a neat agreement of control sizes and alignments or being able to see all of the data in a control? Sometimes you will choose neatness but most of the time you will want to choose being able to see the data!
    
   Evaluate: Do you like to see controls that are just the right size for their maximum data? Would you rather have most of the controls the same size even if many of them are much wider than necessary?
   For example, you could make the Prefix field just wide enough for 5 characters because that is the maximum size allowed for that field in the underlying table Clients. ClientID could be that narrow, too, if you think that you won't have more than 99,999 clients (5 characters). But the fields for parts of a name must be much wider than 5 characters.

---

Resize Multiple Controls: Drag

You can resize your selected controls together. The labels will not resize unless they, too, are selected.

1. 

   Switch back to Form Design View.
   The controls are still selected.
   

2. Move your mouse pointer over a handle on the right edge of any control until it changes to the Resize Horizontal shape .
   

3. Drag to the right to ruler at 2.5" and drop.
   All of the selected controls resize together but their labels stay the same.
    

4. Switch to Form View and check the Title field again for all the records.
   If it is still not wide enough, resize the control again until the control is just wide enough.

5. Save the form as Clients.
   It can be a good idea to save your work along the way using different names, of course. Access only remembers the last 20 actions for the Undo list. It can be hard to get back to a previous arrangement if you change your mind. You just have to decide which version is the one you want to use and delete the rest once you get it just right.

---

Add Fields: Column 2 (Address Info)

1. Switch to Form Design View.  

2. Select the next 7 fields in the Field List, from OrganizationName through Country. These are the parts of a complete address.
   

3. Drag the selected fields and drop below the 4" point on the horizontal ruler and level with the top of the first column of controls.

   

   A second column of controls and labels appears, neatly lined up to the grid. Two rows of grid dots should show at the top.

4. Switch to Form View and navigate through the records to see if controls are wide enough for the data.

   Several controls have data is either too long or barely fits. Future data might be longer. You can make these wider but taller can help, too.

---

Resize and Reposition Controls in Column 2

You will make room for longer organization names and for longer addresses.

1. Switch to Form Design View.
   
2. If necessary, select the controls in the second column.
3. Widen all of the selected controls to the 5.25" mark.
4. Click in a blank area to deselect the controls.
5. Select just the OrganizationName control and drag it wider, to the 6" mark.
   
6. Select the controls City, State, Region, Postalcode, and Country.
   
7. Move with arrow keys: Press the Down arrow key to move the selection down until the bottom of the Country control aligns with the bottom of the first column.
   Now you have some extra space in the column.
8. Select the Address control.
9. Move your mouse pointer over the handle in the middle of the bottom edge of the Address field.
   
10. Drag down until the bottom edge leaves one row of dots above the City control.
    
11. Switch back to Form View and check how the records display now, especially the OrganizationName for record #5 and some of the longer addresses.
    Better!

      

12. Save the form. 
    [Clients]

---

Add Controls: Column 3 (Phone and Email Info)

1. Switch to Form Design View.
   

2. Select the phone number fields and the email field from the Field List.
   

3. Drag and drop the fields onto the form beside the Address control at the 6.75" line.
   Yes - that is outside the form's width.

   Access enlarges the form to hold the fields that you dropped. So helpful!

4. If necessary, drag the inserted controls to adjust the position.
   You want to see one line of dots between the HomePhone control and the bottom of the OrganizationName control and 6 or 7 dots between the left end of the HomePhone label and the right end of the Address control.

   

5. If your form is not 8" wide, drag the right edge of the form to the 8" mark on the ruler.

6. Switch to Form View and navigate the records again to check the display of data in the new controls.

   Phone numbers from some countries fit in the controls but others are too long to fit.

   The EmailAddress control is not large enough for most of the addresses. 

---

Move Control without Label (Column 3)

The phone number fields are going to be fine for most numbers, so for now you will leave these controls as they are.

Several email addresses, however, are far too wide for this control. You must enlarge the control. Making it taller would allow the email address to wrap, but that makes the address hard to read and copy. You will rearrange the control and its label so that the form itself will not get any wider.

1. Switch to Form Design View.  
2. Select the EmailAddress control.
   
3. Drag with the Move handle at the upper left of the control.
   

4. Drop the control below its label lined up at the left edge.
   If necessary, you can use Align Left after selecting both the label and the control.

5. Resize the control EmailAddress so that it is as wide as the whole column of labels and controls, but do not enlarge the form itself.

   Problem: Control and label overlap after aligning.
   The control was not moved far enough down to clear the label when Access lined them up.
   Solution: Use Undo right away in such a situation! Then move the control to a better location and align again.

   Problem: Form enlarged when you resized object
   If you drag a control past the form's edge, Access will automatically enlarge the form.
   Solution 1: Use Undo immediately.
   Solution 2: You can drag the edges of the form to resize it. If any objects are in the space you are trying to remove, you will have to move or resize them before you can resize the form. Access will not automatically resize them for you.
   
6. Switch back to Form View and navigate through the records to see if the email addresses now fit in the control.
   Hurrah! They do.
   It is still possible to have an email address that is too wide to fit this control, but it is not as likely now.
   

7. Save the form. 
   [Clients]

---

Add Controls: Non-Adjacent Fields in Field List

You have several fields left in the Field List that are not on the form yet. You need to add these one at a time and position them carefully to keep the form from getting wider than 8". That's the widest that will print on most printers in Portrait orientation.

None of the records in any table have a photograph attached yet. You will handle that in a later lesson.

1. Switch to Form Design View.
   
2. Select non-adjacent fields: Hold the CTRL key down and click on the fields Birthdate, Nationality, and Hobbies in the Field List to select these non-adjacent fields.
    
3. Drag the fields and drop them at the bottom of the first column, but further to the right than the existing controls and with a wide blank space at the top.
   

4. Open the Property Sheet and change the Width for the multiple-selection to 1.5".

   

5. Save the form. 
   [Clients]

---

Align Controls and Labels

It is easy to miss the exact spot you meant to drop on.

1. Drag across part of the three new controls and the Title control to select them.

   
   

2. Right click on the selection and choose Align > Left.
   All selected controls now line up.
3. Similarly, align the labels.
   
4. Select the new controls again.
5. Press the Up arrow key to move the selection up until there is only one row of dots between the Birthdate and Title controls.
    

---

Resize with Property Sheet; Check Alignment with Ruler

You will move and resize some controls and then add the Photograph field, which is an Attachment data type.

1. Drag the fields HealthIssues and Notes and drop them at the bottom of the second column.
   These are both Memo/Long Text type fields. Access allows a lot more space since Memo fields can hold a lot of text.
    
2. Resize HealthIssues and Notes to fit underneath column 2.
   

3. Drag the field Photograph and drop it at the bottom of the third column. This is an Attachment type of field.
   Access creates a very small square control.

   Apparently it expects you to show the attached file as an icon most of the time. But this control needs to show a photograph. It needs to be bigger.

4. Move the control underneath the label
    
5. Open the Property Sheet for the Photograph control.
   (Right click on the control and click on Properties.)
6. Change the Width and Height to 2".
    

7. Drag the last remaining field, DateUpdated, from the Field List and drop it at the bottom of the form at the left, even with the bottom of the Photograph control.

   

8. Check alignment: Move your mouse over the vertical ruler at the left until the pointer changes to a horizontal arrow . Hold the left mouse button down. A line appears across the form. Move the pointer to put the line across the bottom of the DateUpdated control. It should run across the Photograph control's bottom edge also. When you release the mouse button, any controls/labels that the line touched will be selected. Just click on the form background to deselect.

   Fix any alignment problems.

   The DateUpdated field records when the data for this record was last checked and updated. Only a few of the records have a date in this field yet. Knowing when records were last updated can be very helpful. Having a plan for regularly updating records is even more helpful!

9. Save the form. 
   [Clients]
   

---

Resize Form

Now that all of the fields are on the form, you can see that you do not need all the space that the default blank form came with. You can resize the form yourself.

Point of Confusion: Form Size and Scroll Bars
In Form View, Access will show scroll bars when part of the form is out of view, even if the hidden area is blank.
Don't design forms with unused areas at the right and bottom that generate scrollbars at small window sizes. You cannot control the size of the window your users decide to work in!

1. Move your mouse pointer over the bottom right corner of the form.
   The pointer changes to a new Resize shape for resizing a form instead of a control.
   

2. Drag upwards until the form is just wide enough and tall enough to hold its controls.

   

   Controls prevent resizing of form: You cannot drag a form's edge over any control, even if it is just a decorative line. The controls will not resize or move when the form's edge bumps into them. You will first have to move or resize any controls that are in the way and then resize the form again. This is especially a problem with lines, which are hard to see in Design View, especially if they are at the very top or bottom of a section.

3. Switch to Form View and verify that your data is showing nicely.
   You do not have any photos in the records yet.
   

4. Save the form. 
   [Clients]

---

Form Header: Title

Now that all of the fields are on the form, it may be that your columns are not lined up on the same grid row. Your form also needs a title to make it clear what it is all about. You could put a title label in the Detail section but it is more usual to put it in the Form Header.

Your final result does not have to be exactly like the illustration.

Access has some buttons for three often-added objects: Logo, Title, Date and Time.

1. In Design View, click the button Title on the ribbon tab
   Form Design Tools: Design.
   Access shows the Form Header and Footer sections and uses the table name as the form's title.

   

   Access 2010, 2013, 2016: There is a small, blank Logo control to the left of the Clients label. The Clients label extends past the 6.5" mark.

2. Move the mouse pointer over the bottom edge of the Form Footer until it changes to the Resize Row shape .

3. Drag upwards until the bottom edge meets the Form Footer section bar.
   The Form Footer is now hidden.

   Alternate method: In the Property Sheet for the Form Footer, change the Height to 0.
   

4. Inspect the form, looking especially at how the columns line up with each other.

5. Adjust the HealthIssues and Notes controls to allow them to take up the space to the bottom of the form.
   You will need to move Notes down and then make both controls taller.

6. If necessary, move controls and align again so that:

   • Labels and controls in each column are aligned at the left with each other except for EmailAddress and Photograph.
   • The vertical spacing between controls is the same throughout the form except for DateUpdated.
   • There is at least one row of dots between a label or control and the edge of the form or its neighboring labels or controls.
     
   • The form is just large enough to hold the controls.
7. Save again.
   [Clients]