Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from 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] ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page