Load Combo Box value with First/Last/Previous value

How to Load Combo Box with the First or Last value on the list or Previous Value was selected last time

Load Combo Box value with the First item on the list

1. Create a combo box on form. In this How To, will select data from the Customer Type table. Select option “I want the combo box to get the values from another table or query” on the Combo Box Wizard. Then select CustomerTypeID and CustomerType fields from table Customer Type.

combo box wizard

selectfields

2. Under the Event Procedure of Form On Load, assign the index 0 which is the first item on the list of data (Individual) to combo box below in the procedure as

customerType

Private Sub Form_Load()
Me.ComboCustomerType = Me. ComboCustomerType.Itemdata(0)
End Sub

After form is loaded, the CustomerType “Individual” will populate on the Customer Type combo box as shown below:

Fistcombo

Load Combo Box value with the Last item on the list

1. Under the Event Procedure of Form On Load, assign the index 0 which is the first item on the list of data (Individual) to combo box below in the procedure as

Private Sub Form_Load()
Dim LastRow as Integer
LastRow = Me. ComboCustomerType.ListCount -1
Me.ComboCustomerType = Me. ComboCustomerType.Itemdata(LastRow)
End Sub

After form is loaded, the CustomerType “Non-Profit” will populate on the Customer Type combo box as shown below:

ResultLast

Load Combo Box value with the Previous selected item before form closed

One type of customer maybe selected more than other on the Customer Type combo box. We don’t want to select the same type on combo box every time after loading form. We can set combo box to remember the customer type that was selected last time before closing form. Follow the step below for How To load combo box value with the previous selected value.

1. Create table names “tblLastSelect” with field names; LastID and LastSelect. Also enter any customer type on the first record

LastSelectTable

2. Under the After Update Event Procedure of Customer Type combo box, using command RunSQL Update to add the selected customer type on combo box to the first record on table tblLastSelect.

 Private Sub ComboCustomerType _AfterUpdate()
Dim strUpdate As String
Me.ComboCustomerName.Requery
strUpdate = "Update tblLastSelect SET[lastselect] = '" & Me.ComboCustomerType & "'"
DoCmd.SetWarnings False
DoCmd.RunSQL strUpdate
DoCmd.SetWarnings True
End Sub

3. Under the On Load Event Procedure of Form, assign Customer Type on the first record of table tblLastSelect to Combo Box Customer Type when form is loaded.

Private Sub Form_Load()
Me. ComboCustomerType = DLookup("lastselect", "tblLastSelect")
End Sub

4. If the Customer Type “Business” was selected last time then after form is loaded, the CustomerType “Business” will populate on the Customer Type combo box as shown below:

previousSelect

Related Video: