Validate a Combo Box Selection Before Opening a Form in Access
Question details
Validate a person selected in a combo box before opening the related Access form.

- 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.
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.
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.
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.
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.
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
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
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.

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.

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.




