Access VBA Macro Programming

This is a standard SQL select statement. Notice that first name and last name are

concatenated together using a space in the middle, and also notice the use of square

brackets because the field names have a space in the middle. You can test this out by

creating a normal query in Access. It should return a two-column result of ID number

and the concatenated name of the employee.

 Row Source Type This property must be set to Table/Query.

 Bound Column This property is set to 1, indicating that the first column in the

combo box will be bound to the underlying table. This is the hidden column holding

the ID number. When the user edits an employee name or adds a new record, the ID

number in the first column is written back to the Employee ID field in the Orders table.

 Limit To List This property must be set to Yes so that users are restricted to the

employees within the database and do not start inventing their own!

If you now view your form in Form View mode, you will see that the combo box is

automatically populated with the full name of the employee, although this is made up of two

separate fields from the Employees table.

If you click the drop-down, you will see a list of full names of employees, but what you do

not see is that each employee has a hidden ID number within the list.

Try amending an employee on a record. Move to the next record to save the change and

then move back to your original record. You will see that your change has taken effect and a

different employee is showing on that record.

This methodology has the advantage that when new employees are added to the Employees

table, they will automatically appear in the drop-down together with their relevant ID number.

This preserves the integrity of the database and ensures that any queries using the relationship

between orders and employees will still return the correct results.

Subforms

So far we have designed a user interface based on the user browsing through a table and

looking at a single record each time. This works well on the Orders table, since each record

forms a single meaningful chunk of data. However, you are only looking at a particular order

and cannot see any details as to what products and quantities were included in the order.

Even though several rows of products may exist, effectively you are only looking at the

order header record. This is where a subform can be used to display the multiple detail

associated with a single record in the parent form. To do this, create a new blank form and

call itsubfrmOrderDetails. It is a good idea to distinguish subforms from parent forms by

using a simple naming convention.

Change the Record Source property for the form to Order Details. Place a text box on the

form by clicking the Text box icon in the Controls group of the Design ribbon and dragging it

102 Microsoft Access 2010 VBA Macro Programming