Integrating Excel and Access

Using Data

Because many times combo boxes and list boxes are filled in by data from tables or queries, instead of hardcoded values, you sometimes might want to change the underlying query for a control. A good example of this is if you have a form asking you to choose a store location. When the form first opens, all locations are available. Let's assume that there are combo boxes for state and location. It wouldn't be uncommon for a client to ask you to show only locations for a particular state when a user selects state. It also might not be a stretch for the client to ask that the combo box for location not be activated until the user chooses a state. Here are the steps you would follow to accomplish these two tasks.

First, using the form's OnOpen event (or OnCurrent event if this is a data entry form), set the Enabled property of the Location combo box to FALSE. Next, put code in the AfterUpdate event of the State combo box to change the underlying query of the Location combo box by changing the Row Source property via VBA. You can see what is currently in the Row Source property by looking at the Data tab of the combo box properties. Example 11-3 includes code for both of these events.

Example 11-3. Code to restrict locations to particular states

Private Sub Form_Open(Cancel As Integer) Me.LocationCombo.Enabled = False End Sub   Private Sub StateCombo_AfterUpdate() Me.LocationCombo.Enabled = True Me.LocationCombo.RowSource = "SELECT [tbl_Locations].[Location] " & _ "FROM [tbl_Locations] ...