Use a Database Value in a Microsoft Access VBA Select Case Statement
Question details
Select an Access report based on a model's database category while allowing users to add models without changing VBA code.

- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- An Access form lets users choose a model and open a report associated with its main category.
- Observed behavior
- The desired setup uses stored category information to select reports and accommodates new models or categories with minimal code maintenance.
Identify the model combo box, its bound value, and the existing report names. Check whether each model has one main category and whether each category should open one report.
Map categories to reports in Access tables
Use a data-driven report mapping when users need to add categories without editing Select Case branches.
Store each main category in a separate table and give each model a numeric category foreign key. Store one report name per category, either in the category table or in a related mapping table.
The example below assumes a Models table with ModelID and CategoryID, and a Categories table with CategoryID and ReportName. Replace the table, field, and control names with those in your database.
In Access, open Database Tools > Relationships. Link Categories.CategoryID to Models.CategoryID and enable referential integrity after checking the existing data.
Open the form in Design View and set the combo box Row Source to a query that returns ModelID and the model name. Set Bound Column to 1 so the combo box value is the numeric ModelID.
Open the Categories table and enter the exact name of an existing Access report in ReportName for each category. New models can then reference an existing category; new categories need their own report mapping.
In the report button's On Click event, open the VBA editor and use code such as: If IsNull(Me.cboModel) Then Exit Sub Dim categoryId As Variant Dim reportName As String categoryId = DLookup("CategoryID", "Models", "ModelID=" & CLng(Me.cboModel.Value)) If IsNull(categoryId) Then MsgBox "No category found for this model." Exit Sub End If reportName = Nz(DLookup("ReportName", "Categories", "CategoryID=" & CLng(categoryId)), "") If reportName = "" Then MsgBox "No report is mapped to this category." Exit Sub End If DoCmd.OpenReport reportName, acViewPreview
Switch to Form View, select a model in the combo box, and click the report button. Add a test model or category and confirm that its category maps to an existing report.

Use the combo box category column in Select Case
Use Select Case when the report choices are fixed and new models will use existing categories.
Manage related office files with WPS Office
WPS Office is a free, lightweight alternative for working with documents, spreadsheets, and presentations in a familiar interface. Its Microsoft Office format compatibility helps you move everyday files between suites. It does not replace Microsoft Access or run an Access database application.

Frequently Asked Questions
How do I read a category from an Access combo box in VBA?
Include the category in the combo box Row Source, then use the zero-based Column property. For example, Me.cboModel.Column(1) reads the second column.
Can I add a new category without changing Select Case?
A fixed Select Case generally needs a new Case branch for a category with different report handling. Instead, store the category-to-report mapping in a table and have VBA read the report name from that table.
Why should the main category be stored in a separate table?
A separate Categories table lets multiple models reference the same category through a numeric foreign key. You can manage the category and its report mapping in one place rather than repeating those values for every model.
What should I do if no report opens for a model?
Check that the model has a valid CategoryID, that the category has a ReportName value, and that the named report exists in the Access Navigation Pane. Also confirm that the combo box returns the expected bound ModelID.




