ComboBox Object (Access)
This object corresponds to a combo box control. The combo box control combines the features of a text box and a list box. Use a combo box when you want the option of either typing a value or selecting a value from a predefined list.
In Form view, Microsoft Access doesn't display the list until you click the combo box's arrow.
If you have Control Wizards on before you select the combo box tool, you can create a combo box with a wizard. To turn Control Wizards on or off, click the Control Wizards tool in the toolbox.
The setting of the LimitToList property determines whether you can enter values that aren't in the list.
The list can be single- or multiple-column, and the columns can appear with or without headings.
The following example shows how to use multiple ComboBox controls to supply criteria for a query.
UtterAccess members can download a database that contains this example from here.
Private Sub cmdSearch_Click() Dim db As Database Dim qd As QueryDef Dim vWhere As Variant Set db = CurrentDb() On Error Resume Next db.QueryDefs.Delete "Query1" On Error GoTo 0 vWhere = Null vWhere = vWhere & " AND [PymtTypeID]=" + Me.cboPaymentTypes vWhere = vWhere & " AND [RefundTypeID]=" + Me.cboRefundType vWhere = vWhere & " AND [RefundCDMID]=" + Me.cboRefundCDM vWhere = vWhere & " AND [RefundOptionID]=" + Me.cboRefundOption vWhere = vWhere & " AND [RefundCodeID]=" + Me.cboRefundCode If Nz(vWhere, "") = "" Then MsgBox "There are no search criteria selected." & vbCrLf & vbCrLf & _ "Search Cancelled.", vbInformation, "Search Canceled." Else Set qd = db.CreateQueryDef("Query1", "SELECT * FROM tblRefundData WHERE " & _ Mid(vWhere, 6)) db.Close Set db = Nothing DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly End If End Sub
The following example shows how to set the RowSource property of a combo box when a form is loaded. When the form is displayed, the items stored in the Departments field of the tblDepartment combo box are displayed in the cboDept combo box.
Private Sub Form_Load() Me.Caption = "Today is " & Format$(Date, "dddd mmm-d-yyyy") Me.RecordSource = "tblDepartments" DoCmd.Maximize txtDept.ControlSource = "Department" cmdClose.Caption = "&Close" cboDept.RowSourceType = "Table/Query" cboDept.RowSource = "SELECT Department FROM tblDepartments" End Sub
The following example show how to create a combo box that is bound to one column while displaying another. Setting the ColumnCount property to 2 specifies that the cboDept combo box will display the first two columns of the data source specified by the RowSource property. Setting the BoundColumn property to 1 specifies that the value stored in the first column will be returned when you inspect the value of the combo box.
The ColumnWidths property specifies the width of the two columns. By setting the width of the first column to 0in., the first column is not displayed in the combo box.
Private Sub cboDept_Enter() With cboDept .RowSource = "SELECT * FROM tblDepartments ORDER BY Department" .ColumnCount = 2 .BoundColumn = 1 .ColumnWidths = "0in.;1in." End With End Sub
The following example shows how to add an item to a bound combo box.
Private Sub cboMainCategory_NotInList(NewData As String, Response As Integer) On Error GoTo Error_Handler Dim intAnswer As Integer intAnswer = MsgBox("""" & NewData & """ is not an approved category. " & vbcrlf _ & "Do you want to add it now?" _ vbYesNo + vbQuestion, "Invalid Category") Select Case intAnswer Case vbYes DoCmd.SetWarnings False DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) " & _ "Select """ & NewData & """;" DoCmd.SetWarnings True Response = acDataErrAdded Case vbNo MsgBox "Please select an item from the list.", _ vbExclamation + vbOKOnly, "Invalid Entry" Response = acDataErrContinue End Select Exit_Procedure: DoCmd.SetWarnings True Exit Sub Error_Handler: MsgBox Err.Number & ", " & Error Description Resume Exit_Procedure Resume End Sub
Luke Chung is the founder and president of FMS, Inc., a leading provider of custom database solutions and developer tools.
UtterAccess is the premier Microsoft Access wiki and help forum. Click here to join.
Holy Macro! Books publishes entertaining books for people who use Microsoft Office. See the complete catalog at MrExcel.com.
Wrox Press is driven by the Programmer to Programmer philosophy. Wrox books are written by programmers for programmers, and the Wrox brand means authoritative solutions to real-world programming problems.