Adding a related record to another table

One of the most common uses of opening forms from VBA is to allow the user to easily enter a record of information with some data already provided. For example, Figure 6-8 shows a sample form named Address Book Form. It displays records from a table of names and addresses, where each customer has a unique ContactID number.

ContactID

Figure 6-8:

Sample address book form and Place Order button.

m Address Book

Address Book

QuickFind

Type First Name Company Address 1 Address 2 City Country Phone

QuickFind

Type First Name Company Address 1 Address 2 City Country Phone

Customer

1-ContactID: 1

Tori

Last Name IPines

Arbor Classics

345 Pacific Coast Hwy

Suite 3232

Del Mar

|CA vj| |98765

USA

v State/Province ZlP/Postal Code

(618)555-4949

Fax 1 (618)555-4343

|Tor [email protected] bore lass ics. con

' Tax Exempt Customer

|www, arbordassics. con

Exempt ID J

New Contact

PlaceOrder button

Suppose that a user has just finished entering the name and address and other information for a new customer and now wants to switch over to an order form and enter a new order for that customer. When the order form opens, you want it to have already created a new record for the order, put the current customer's ContactID value into that order form, and position the cursor to where the user is most likely to type next, such as the Payment Method control, as shown in Figure 6-9.

To make these tasks work, you need to tie some code to the Place Order button's On Click event procedure. That code needs to perform these steps:

1. Open the order form so that it's ready to add a new record.

2. Copy the customer's ContactID to the ContactID control on the order form.

3. Move the cursor to a convenient control on the order form.

4. Close the address book form and save its record.

Figure 6-9:

Sample order form.

Payment Method

To start this programming endeavor, open Address Book Form in Design view, click the PlaceOrder button, click the Event tab in the property sheet, click the Build button in the property sheet, and choose Code Builder. As always, you're taken to the class module for the form. The cursor is in a new Sub procedure whose name reflects the button and the On Click event, as shown here:

Private Sub PlaceOrder_Click()

End Sub

ContactID

Orders Main Form

ContactID

Orders Main Form

Figure 6-9:

Sample order form.

Payment Method

Next, you convert into VBA code the plain-English steps that the procedure needs to take. The complete procedure, as it appears in the VBA Editor Code window, is shown in Listing 6-3.

Listing 6-3: Form_Address Book Form

Private Sub PlaceOrder_Click()

'Open the order form ready to add a new record. DoCmd.OpenForm "Orders Main Form", acNormal, , , acFormAdd

'Copy customer's ContactID to ContactID control on order form. Forms![Orders Main Form]!ContactID.Value = Me![ContactID].Value

'Move cursor to convenient field in order form. Forms![Orders Main Form]![Payment Method].SetFocus

'Close the address book form and save its changes. DoCmd.Close acForm, "Address Book Form", acSaveYes

End Sub

Was this article helpful?

0 0

Post a comment