Applying VBA in the Real World

After you close and save the table, you need to open that table and type in the value of at least one field. That's because when you bind a dialog box to that table later, it works only if the table already contains one record. For example, Figure 9-3 shows one record that we typed into the SettingsTable table. The blank record beneath the filled record isn't an actual record in the table. That empty record appears only as a placeholder for any new record that you want to add to the table in Datasheet view.

Figure 9-3:

One table record stores dialog box settings.

S SettingsTable _ n x

RepartName - LabelsTp^typ

S SettingsTable _ n x

RepartName - LabelsTp^typ

1*1

0

| Record: m lofl ►

►I h: I ,

You can see an example of using the SettingsTable values in a dialog box a little later in this chapter. For now, in the next section, you master how to create a dialog box in the first place.

Setting form properties

Creating a dialog box in Access is similar to creating any other form. You don't even need any VBA code to create the box. Rather, you just create a form and set its form properties so that the form looks and acts like a dialog box. Here's how:

1. Click the Create tab, and then click the Form Design command in the Forms group.

This step creates a new, blank form in Design view.

2. If the property sheet isn't visible, click the (Form Design Tools) Design tab and click Property Sheet in the Tools group, or press F4.

3. In the property sheet, make sure that Form is selected in the Selection Type drop-down list, and then click the All tab (see Figure 9-4).

4. Set the properties as indicated in Table 9-3.

5. Save the form by clicking the Save button on the Quick Access toolbar.

Figure 9-4:

Setting form properties for a dialog box.

Figure 9-4:

Setting form properties for a dialog box.

Table 9-3 Properties to Make a Form into a Dialog Box

Property

Setting

Reason

Record Source

SettingsTable

This is the table the form is bound to.

Default View

Single Form

Make it look like a dialog box.

Allow Form View

Yes

Make it look like a dialog box.

Allow Datasheet View

No

Dialog boxes have no such view.

Allow PivotTable View

No

Dialog boxes have no such view.

Allow PivotChart View

No

Dialog boxes have no such view.

Allow Layout View

No

User doesn't need to see this view.

Allow Edits

Yes

User needs to change data on the form.

Allow Deletions

No

Underlying table (if any) must contain only one record.

Allow Additions

No

Underlying table (if any) must contain only one record.

(continued)

Table 9-3 (continued)

Property

Setting

Reason

Allow Filters

No

Underlying table (if any) has only one record.

Data Entry

No

Underlying table (if any) must contain only one record.

Scroll Bars

Neither

Dialog boxes don't have scroll bars.

Record Selectors

No

Dialog boxes don't have record selectors.

Navigation Buttons

No

Dialog boxes don't have navigation buttons.

Dividing Lines

No

Dialog boxes don't need them.

Pop Up

Yes

Keep dialog box on top of other open windows.

Modal

Yes

Disable other open windows until user responds to dialog box.

Border Style

Dialog

Make it look like a dialog box border.

Control Box

Yes

Need to make Close button visible.

Min Max Buttons

None

Dialog box can't be minimized or maximized.

Close Button

Yes

Dialog boxes have a Close button, which acts like a Cancel button.

Cycle

Current Record

Only one record is in underlying settings table.

To color your dialog box, click the Detail section in Design view, click its Back Color property, and choose a color. For example, for a slightly off-white color, set the Back Color property of the Detail section to 16316664.

To color your dialog box, click the Detail section in Design view, click its Back Color property, and choose a color. For example, for a slightly off-white color, set the Back Color property of the Detail section to 16316664.

Adding controls to the dialog box

The form properties that you change to control the appearance and behavior of a form don't affect how you add controls to the form. You can still use all the standard techniques that you use in Access to create a form for scrolling through records. For example, to add a bound control to the form, click a control type in the Controls group on the (Form Design Tools) Design tab. Then drag the underlying field's name from the Field List to the form. To add an unbound control to the form, click a control type in the Toolbox and then click the form's Design grid.

If the Control Wizard opens after you drop a control on the form, you can step through the wizard as you normally would. If you're planning to attach custom code to the control later and don't want the wizard to create the control, just click the wizard's Cancel button. Then you can assign a name, a caption, and some events to the control by using the control's property sheet.

For example, the top half of Figure 9-5 shows in Design view a sample dialog box with four main controls: ReportName, LabelsToSkip, CancelBttn, and PrintBttn. In that example, the controls ReportName and LabelsToSkip are bound to fields in the SettingsTable described earlier in this section. Thus, the dialog box remembers the settings in those controls from one session to the next. The lower half of Figure 9-5 shows the same form open in Form view.

pä] Unfarí^1 "SkipLabels dialog BÖÄ _ =1 x

pä] Unfarí^1 "SkipLabels dialog BÖÄ _ =1 x

* l>tail

1

JJrii

t which label r<

port?| ReportName

1

S

tip how uidiiy 1.

-, btils? LdbuhTuSkip

Cancel

Pn it

¡H SkipLabefs_

Print which label report? ftvery8462 Labels

Skip ho\or many labels? | 10

Figure 9-5:

Controls on a form (dialog box).

In the sample form shown in Figure 9-5, the CancelBttn and PrintBttn controls aren't bound to any table field. Instead, each just has some custom code tied to its On Click event. For example, the On Click event procedure forCancelBttnisDoCmd.Close acForm, Me.Name, acSaveNo, which closes the form without saving any changes or printing.

The On Click event procedure for PrintBttn can execute any VBA code or macro. For instance, to call the SkipLabels procedure described in Chapter 8, have that procedure execute the statement

Call SkipLabels ([ReportName].Value,[LabelsToSkip].Value)

Doing so prints whatever report name appears in the ReportName control, skipping the number of labels specified in the LabelsToSkip control. The procedure also closes the dialog box. The following code shows the On Click event procedure for both controls in the class module for the sample form:

Private Sub CancelBttn_Click()

'Close the SkipLabels form without doing anything. DoCmd.Close acForm, Me.Name, acSaveNo

End Sub

Private Sub PrintBttn_Click()

'Print the specified labels, skipping specified blanks. Call SkipLabels([ReportName].Value, [LabelsToSkipj.Value)

'Then close the SkipLabels form. DoCmd.Close acForm, Me.Name, acSaveNo

End Sub

We help you create a much fancier SkipLabels dialog box in the sections that follow. For now, you should be able to see how it works. The controls named ReportName and LabelsToSkip on the form serve as data to pass to the SkipLabels() Sub procedure. Clicking the Print button on the form calls the SkipLabels routine using the syntax

Call SkipLabels([ReportName].Value,[LabelsToSkip].Value)

When SkipLabels runs, it prints whatever report name appears in the ReportName control on the form and also skips however many labels are specified in the LabelsToSkip control on the form.

Was this article helpful?

0 0

Post a comment