Linking Lists across Forms

Working with list controls (such as ListBox and ComboBox controls) isn't always a matter of controlling the Row Source Type and Row Source properties of the control. In some cases, it's just getting the darn control to show what's in the underlying table or query — or worse yet, getting it to accept a value that should be acceptable to the control, but isn't. These types of problems happen a lot when two or more forms are involved in the scenario.

We suppose that a typical example is a user who is trying to type in a new order, perhaps coming in over the phone. The user might be sitting there looking only at the Orders form shown on the left side of Figure 10-12. To start typing an order, she can choose an existing customer from the CustID combo box (labeled "Customer:") on the Orders form, or she can click the New Customer button (NewCustBttn) to enter name and address info for a new customer.

Disclaimer: All the names and e-mail addresses shown in these figures are fictional, and any resemblance to real people or e-mail addresses is purely coincidental.

If your user clicks the New Customer button, the NewCust form (also shown in Figure 10-12) opens at a blank record, ready to type in a new customer's info. Then the user types in the info and clicks the Done - Fill Order button (named DoneBttn). At that point, the NewCust form closes, and the user is returned to the Orders form. That moment — when the NewCust form closes and the focus returns to the Orders form — is where most troubles begin. The problem has to do with when a combo box or list box gets its data from an underlying table or query, which (in general) is only once — when the form opens.

Figure 10-12:

Sample open Orders and NewCust forms.

Figure 10-12:

Sample open Orders and NewCust forms.

Updating a combo box or a list box

A typical combo box or list box gets the values that it shows in its list only once, right after the form opens. For example, the CustID control in the Orders form pictured earlier gets its list of customers from a field in a table named Customers. It gets that list when the Orders form opens. When a user adds a new record to the Customers table via the NewCust form, the Orders table knows nothing of the new record. The drop-down list in the CustID control just continues to show the same names it did before the NewCust form added a new record to the Customers table.

The solution to the problem is the Requery method, which every list box and combo box control exposes to VBA. As its name implies, the Requery method forces a list box to update its list or a combo box to update its dropdown list immediately. The syntax for using the method is controlName.Requery where controlName is the name of the combo box or list box that needs updating. When you need to update a control on a form (other than the one in which the code is running), you need a complete identifier at the start of the name, specifying the name of the form on which the control resides. For example, the following line updates the CustID control in the Orders form from code that's in the class module for the NewCust form (or any other form):

Forms![Orders]![CustID].Requery

The preceding statement says "Update the control named CustID on the open form named Orders."

Go back to the sample Orders and NewCust forms shown in Figure 10-12. First, clarify that the Orders form there is bound to a table named Orders. The CustID control on the Orders form is bound to the CustID control in the Orders table, which is a Long Integer. The CustID control on the NewCust form is bound to the CustID control in the Customers table, where it's defined as an AutoNumber field and Primary key (ensuring that every new customer automatically gets a unique, unchangeable CustID value the moment a new record is added). Figure 10-13 shows the structures of the Orders and Customers tables.

Tables underlying Orders and NewCust forms.

When you look at the CustID combo box in Figure 10-12, it doesn't look like it's bound to an Integer field in a table because the control displays text. However, the actual value in that CustID control is an integer; the integer is just hidden from view. Read more on that in the upcoming section "Using hidden values in combo and list boxes."

Suppose that you have a form like the Orders form that has a button to add a new record through some other form. The first thing you need to do is get the button to open the appropriate form pointing at a new, blank record. Tackle that problem first.

Opening a form to enter a new record

Suppose that you have the Orders form open in Design view and you need to get that New Customer button to open the NewCust form poised to accept a new record. You can do that with a macro, or you can assign the following procedure to the New Customer button's (NewCustBttn) On Click event property:

Private Sub NewCustBttn_Click()

'Open NewCust form at new, blank record (asFormAdd). DoCmd.OpenForm "NewCust", acNormal, , , acFormAdd End Sub

That's it for the Orders form's role in all of this, so you close and save that form. When the user clicks the New Customer button on the Orders form (in Form view), the NewCust form opens. Presumably, the user then types in the new customer's information, clicks the Done button, and returns to the Orders form. That's where the CustID control on the Orders form gets out of sync.

When the NewCust form closes and saves the new record, the CustID control on the Orders form doesn't know about the new record. Hence, its drop-down list is out of sync. Somehow you have to get the NewCust form to tell the Orders form, "Hey, update your CustID control" before the form closes.

To solve the problem, write some code that updates the CustID control on the Orders form every time the NewCust form adds a new record to the Customers table. As it turns out, anytime a form adds a new record to its underlying table or query, that form's After Insert event occurs. Thus, a guaranteed way to ensure that the Orders form's CustID combo box is up-to-date is to requery that control every time the NewCust form's After Insert event occurs.

To make that happen, do the following:

1. First make sure to open the NewCust form (not the Orders form) in Design view.

2. Make sure Form is selected in the property sheet (so that you're setting Form properties).

3. Click the Event tab in the property sheet.

4. Click the Build button next to the After Insert event property.

5. Choose Code Builder.

7. Type the VBA statement needed to requery the control on the Orders form:

Forms![Orders]![CustID].Requery

The entire Form_AfterInsert procedure in the NewCust form's class module looks like this:

Private Sub Form_AfterInsert()

'Update CustID combo on open Orders form. Forms![Orders]![CustID].Requery End Sub

The problem is now solved because every time the user adds a customer to the Customers table from the NewCust form, the CustID control on the Orders form is automatically requeried to include that new record. You could leave it at that. However, in solving that problem, you created a new problem, as described next.

Seeing whether a form is open

VBA can requery a control only on a form that's open. If a form is closed, you have no way to (and no reason to) requery any of its controls because any list controls on the form are created (and hence up-to-date) the moment the form opens. If VBA code tries to requery a control on a form that's closed, the procedure crashes, and an error message appears on-screen. Not good.

To get around the problem of the Form_AfterInsert() procedure crashing when the Orders form isn't open, put the statement that updates the control inside an If...End If block. Make the condition of the If statement CurrentProject.AllForms("FormName"').IsLoaded in your code but substitute FormName with the name of the form that needs to be open. For example, the following modified Form_AfterInsert() procedure requeries the Orders form's CustID control only if the Orders form is open when the procedure executes:

Private Sub Form_AfterInsert()

'If the Orders form is open

(loaded...)

If CurrentProject.AllForms(

"Orders").IsLoaded Then

'...update CustID combo

on open Orders form.

Forms![Orders]![CustID].

Requery

End If

End Sub

If the Orders form is closed when the preceding procedure is executed, the procedure does absolutely nothing. That's good because as we mention, there's no need to requery a control on a closed form.

Getting forms in sync

Requerying the CustID control on the Orders form keeps the combo box's drop-down list up-to-date with the current contents of the Customers table at all times. However, it doesn't change the value that's displayed in that control. In other words, requerying a ComboBox control fixes the combo box's hidden drop-down list, but it doesn't change which option on that menu is now selected and visible in the control. You can always add some code to take care of that.

A perfect example is when the user adds a new customer via the NewCust form and returns to the Orders form. Ideally, you want the Orders form to already show a new, blank order form with the new customer already chosen as the one placing the order. From a VBA perspective, when the user closes the NewCust form, it makes sense to add a new, blank record to the Orders form and set the CustID control on the Orders form to the new customer's CustID value. In other words, when the user clicks the Done - Fill Order button, you want VBA to

1 Copy the new customer's CustID to a variable for holding

1 Close the NewCust form, saving the new customer's record

1 Make sure you're at new, blank record in Orders form

1 Copy the new customer's CustID into Orders form's CustID control

1 On the Orders form, put the cursor in whatever control the user is most likely to resume typing the order

Making those steps happen whenever someone clicks the DoneBttn button in the NewCust form requires the procedure in Listing 10-8 in the NewCust form's class module.

Listing 10-8: Updating a Control on a Separate Form

Private Sub DoneBttn_Click()

'Do these steps only if Orders form is open. If CurrentProject.AllForms("Orders").IsLoaded Then

'Copy the new customer's CustID to a variable. Dim NewCustID As Long NewCustID = MelCustID.Value

'Close the NewCust form. DoCmd.Close acForm, "NewCust"

'Make sure were at new, blank record in Orders form

(continued)

Listing 10-8 (continued)

DoCmd.GoToRecord acDataForm, "Orders", acNewRec

'Copy new CustID into Orders form's CustID control Forms![Orders]!CustID.Value = NewCustID

'Move cursor to PaymentMethod control in Orders form.

Forms![Orders]![PaymentMethod].SetFocus End If End Sub

You might notice that none of the statements in the preceding procedure requeries the CustID control on the Orders form. That's because you already wrote a Form_AfterInsert() procedure to ensure that anytime any record gets added to Customers via the NewCust form, code immediately updates the CustID control on the Orders form. When VBA executes the statement DoCmd.Close acForm, "NewCust", it saves the current record (because when you close a form, the current record is saved automatically). Right after the form inserts the new record into the Customers table, the Form_AfterInsert() procedure runs, updating the CustID combo box on the Orders form.

In other words, by the time execution reaches the first statement under the

DoCmd.Close acForm, "NewCust" statement, the Form_AfterInsert() event has already occurred and updated the CustID control on the Orders form to include the new customer's record.

0 0

Post a comment