VBA & Macro Problems

Use a Database Value in a Microsoft Access VBA Select Case Statement

Nimra MalikNimra Malik Nov 14, 2026 868 views

Question details

Select an Access report based on a model's database category while allowing users to add models without changing VBA code.

Use a Database Value in an Access VBA Select Case Statement
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.
Before you start

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.

Solution 1Recommended

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.

1
Create the category relationship

In Access, open Database Tools > Relationships. Link Categories.CategoryID to Models.CategoryID and enable referential integrity after checking the existing data.

2
Configure the model combo box

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.

3
Store report names

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.

4
Open the mapped report

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

5
Test the mapping

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.

Map categories to reports in Access tables
New reports still need to exist: Adding a category and its mapping does not create a report. Create the report in Access first, or map the category to an existing report.
Free Microsoft Office alternative

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.

Organize model and category lists in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesWork with Microsoft Word DOCX and PowerPoint PPTX filesUse a familiar interface for a smoother move between office suites
microsoft office alternative - wps office

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.