Software optimize

Save room without sacrificing functionality in an Access form

When an Access memo field contains a lot of data, it can be difficult to accommodate that control on a form. Here's an easy trick that lets users decide when to display the full contents of a memo (or text) field.
You often need to display large amounts of text in an Access form -- well, it's common to need to store lots of text. Whether users need to view all that data all the time is unlikely.The good news is that with a little planning, your form can provide quick access to large amounts of data in a memo or text field while remaining neat and trim. The key is to let users decide when they need to view all the content. In short, let the user double-click the memo control or a command button to display all the data only when they need it. That way, the user has access to the data and the control consumes a reasonable area on the form.

Using Northwind, let's work through a quick example:

  1. Open the Employees form in Design view.
  2. Click the Code button to launch the form's module.
  3. Enter the Notes_DblClick() subprocedure shown below.
  4. Return to Access and open the form in Form view.
  5. Double-click the Notes control.

Private Sub Notes_DblClick(Cancel As Integer)
  'Display contents of Notes field in a Zoom box.
  DoCmd.RunCommand acCmdZoomBox
End Sub

A quick double-click of the Notes control displays the entire entry for the current record's Notes field. (The Notes control is on the form's Personal Info tab.)

Now, this method isn't intuitive to the user -- you'll have to train them to double-click the control. If you'd like something more obvious, you can add the same code to a command button. With the right caption, most users will understand that clicking the button will display the full contents of the corresponding control.

Using the Employees form, let's work through an example that uses a special zooming command button:

  1. With the Employees form in Design view, click the Personal Info tab.
  2. Insert a command button to the right of the Notes control and resize the new command button so that it's fairly small.
  3. Double-click the command button to open its Properties window.
  4. Name the button cmdZoom.
  5. Enter a + (plus sign) for the control's Caption. (Or use something more descriptive, like the phrase "Zoom In."
  6. Click the Code button to launch the form's module.
  7. Enter the cmdZoom_Click subprocedure shown below.
  8. Return to Access and open the Employees form in Form view.
  9. Click the Personal Info tab.
  10. Click the + Command button to display the contents of the Notes control in a zoom box.

Private Sub cmdZoom_Click()
  'Display contents of Notes field in a Zoom box.
  Me.Notes.SetFocus
  DoCmd.RunCommand acCmdZoomBox
End Sub

Notice that the SetFocus method in the command button's Click event procedure references the Notes control on the Employees form (Me.Notes). When applying this technique to your forms, be sure to substitute the Notes reference with the name of the memo or text field you want to zoom. Both solutions are simple to implement, and it won't take long for users to get the hang of viewing voluminous data.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

3 comments
k2harrell
k2harrell

I have a team lead who wants all the entry forms in our database to be viewable on her monitor like it was on 1 page of paper, so all the fields are 'squished' down to the bare minimum, which I hate when I'm trying to read some of the memo fields. I'm making changes to our DB to do this! Love it!!

alan
alan

I have been using this bit of code for sometime but sometimes lost the data in the control because it is hi-lited by default (as it is in the image in this post). So I added the following to un-hi-lite the contents of the zoomed control. Application.SetOption "Behavior Entering Field", 1 Hope that helps to round out the tip. Alan

chuck
chuck

Works great, many thanks. Chuck