Others

Validate a Combo Box Selection Before Opening a Form in Access

Khadija KhanKhadija Khan Nov 13, 2026 868 views

Question details

Validate a person selected in a combo box before opening the related Access form.

Validate a Combo Box Selection Before Opening a Form in Access
Product
Microsoft Access
Device & OS
not provided
Scenario
A user selects or types a person's name in a combo box and opens a form for that person's record.
Observed behavior
The process fails when the typed name is not included in the combo box query.
Before you start

Identify the combo box, its row-source query, the unique person ID field, and the form to open. The examples use cboPatient, qrySelectablePatients, PatientID, and frmPatient; replace them with your actual names.

Solution 1Recommended

Validate the combo box and open the form by PatientID

Use this approach to reject names outside the list and open the selected record using a unique numeric key.

Configure the combo box to store PatientID while displaying identifying details such as the person's name. Names can be duplicated, so they should not be used as the record identifier.

The example assumes qrySelectablePatients contains the currently eligible people and is the combo box's row source.

1
Configure the row source

Open the form in Design View, select cboPatient, and open its Property Sheet. On the Data tab, set Row Source to qrySelectablePatients, Bound Column to 1, and Limit To List to Yes. Make PatientID the first column in the query.

2
Display useful identifying details

On the Property Sheet's Format tab, set Column Count to match the query and set Column Widths to hide the first ID column while showing names and any other identifying fields. For example, use 0;1.5;1.5 for three columns.

3
Handle names outside the list

On the Event tab, set On Not In List to [Event Procedure] and select the build button. Add this code to show a message, undo the invalid entry, and suppress the default message: Private Sub cboPatient_NotInList(NewData As String, Response As Integer) MsgBox "Please select a person from the list.", vbExclamation Response = acDataErrContinue Me!cboPatient.Undo End Sub

4
Validate and open the record

In the button's On Click event procedure, check for an empty selection and confirm the ID is still in the row-source query before opening the form: Private Sub cmdOpenPatient_Click() Dim patientId As Long If IsNull(Me!cboPatient.Value) Then MsgBox "Please select a person from the list.", vbExclamation Exit Sub End If patientId = CLng(Me!cboPatient.Value) If DCount("*", "qrySelectablePatients", "[PatientID]=" & patientId) = 0 Then MsgBox "That person is no longer available in the list.", vbExclamation Me!cboPatient = Null Me!cboPatient.Requery Exit Sub End If DoCmd.OpenForm "frmPatient", , , "[PatientID]=" & patientId End Sub

5
Test the form

Open the form in Form View. Try an empty selection, a name outside the list, and a valid selection; confirm that only the valid selection opens frmPatient at the matching record.

Validate the combo box and open the form by PatientID
Match the validation query: If the combo box uses additional filters, apply the same filters in qrySelectablePatients so the DCount check validates against the same set of people. The destination form must also include PatientID in its record source.
Free Microsoft Office alternative

Use WPS Office for Everyday Office Documents

WPS Office is a free, lightweight alternative for working with documents, spreadsheets, and presentations. Its familiar interface and Microsoft Office format compatibility make it easy to move everyday files, but it does not fix or replace Access forms and VBA.

Work with Office documents alongside your Access database tasksOpen and edit Microsoft Word, Excel, and PowerPoint formatsUse a familiar interface for a smoother transitionInstall a lightweight suite with free core tools
microsoft office alternative - wps office

Frequently Asked Questions

Why should the combo box store PatientID instead of the person's name?

Names can be duplicated, but a unique PatientID identifies one record. Set PatientID as the first query column and Bound Column to 1, then use that ID in the OpenForm filter.

How can I show a custom message for a name that is not in the list?

Set Limit To List to Yes and add a NotInList event procedure on the combo box's Event tab. Display your translated message with MsgBox, set Response to acDataErrContinue, and undo the invalid entry.

What happens if no person is selected?

Check IsNull on the combo box value in the open button's On Click procedure. Show a message and use Exit Sub before calling DoCmd.OpenForm.

Do I need a separate form for every person?

No. Use one form whose record source includes PatientID, and pass a PatientID filter in the WhereCondition argument of DoCmd.OpenForm.

What if a person is removed from the list after being selected?

Before opening the form, use DCount against the same query and filters as the combo box row source. If the ID is no longer present, clear the selection and requery the combo box.