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.

Suppose that a user has just finished entering the name, 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.

Address Book Form

Figure 6-8:

Sample address book form and place order button.

Address Book Form

Figure 6-8:

Sample address book form and place order button.

ContactID

PlaceOrder button

ContactID

PlaceOrder button

Orders Main Form

ContactID

Figure 6-9:

Sample order main form.

Orders Main Form

ContactID

Figure 6-9:

Sample order main form.

Payment method

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

1. Open the order form ready to add a new record.

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

3. Move cursor to convenient control on order form.

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

To start this programming endeavor, open Address Book Form in Design view, click the Place Order button, click the Event tab in the Properties sheet, click the Build button in the Properties sheet, and choose Code Builder. As always, you'll be taken to the class module for the form. The cursor will be in a new Sub procedure whose name reflects the button and On Click event, as follows:

Private Sub PlaceOrder_Click() End Sub

Next, you need to convert the plain-English steps that the procedure needs to take into actual VBA code. 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 record. DoCmd.Close acForm, "Address Book Form", acSaveYes

End Sub

0 -1

Responses

  • allison
    How to add a record to access table with vba?
    7 years ago

Post a comment