|
Custom Search
| |
|
Create MS Access Combo Box essential properties by VBA code
We have detailed the essential properties to set up for a Combo Box. In this article, we're going to create the same Combo Box and functions the same way but by using VBA codes with a List Box as a useful feature that is often used together with control box. If you don't like to manually set up everything in the Properties for a Combo Box in Access, you can use VBA code to achieve the same effect. In this way, the combo box is unbound to any column upfront but its row source is bound dynamically by using VBA code. Below is the Suppliers combo box drops down and displays 2 fields and a correlated List Box control. The List Box displays products for the selected supplier in the Suppliers combo box.
Main controls on the Form Open the Northwind database and create a new Form, then create the following controls.
We use VBA to set up property values for the combo box which works exactly the same way as the one manually set up here. 1. Form Load event Below is the VAB code for the Form Load where the last 2 lines are the correlated List control lstProduct. One useful extension to the Combo Box usage is to display a correlated change in List control where it displays related values of a supplier. For example, when a different supplier is selected in the drop down, the list of products supplied by this supplier automatically change in the List control. Sub Form_Load() Dim strSQL As String Dim strSQL2 As String strSQL = "SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers order by SupplierID" Me!cmbSupplier.RowSource = strSQL Me!cmbSupplier.RowSourceType = "Table/Query" Me!cmbSupplier.ColumnCount = 5 Me!cmbSupplier.ColumnWidths = "0cm;4cm;2cm;0cm;0cm" Me!cmbSupplier.BoundColumn = 1 Me!cmbSupplier.ColumnHeads = True Me!cmbSupplier.LimitToList = True Me!cmbSupplier.DefaultValue = Me.cmbSupplier.ItemData(1) Me.txtPhone.Value = Me.cmbSupplier.Column(2) Me.txtContactTitle.Value = Me.cmbSupplier.Column(3) Me.txtContactName.Value = Me.cmbSupplier.Column(4) '' Query for the correlated products by selected supplier strSQL2 = "select p.ProductID, p.ProductName, c.CategoryName " & _ " from Products As p " & _ " inner join Categories As c On p.CategoryID = c.CategoryID where p.SupplierID = " & Me!cmbSupplier.Value & _ " order by c.CategoryName" '' Feed the List control with a query Me!lstProduct.RowSource = strSQL2 Me!lstProduct.RowSourceType = "Table/Query" End Sub 2. On Change event In VBA, using On Change event subroutine can greatly improve the usefulness of a ComboBox to display extra data for the record selected in the ComboBox. Private Sub cmbSupplier_Change() Dim strSQL As String Dim objDB As DAO.Database Dim rstSupplier As DAO.Recordset Dim lngSupplierID As Long Dim strCompanyName As String Dim strPhone As String Dim strContactTitle As String Dim strContactName As String lngSupplierID = Me!cmbSupplier.Value '' Get the supplier by feeding SupplierID strSQL = "SELECT SupplierID, CompanyName, Phone, ContactTitle, ContactName FROM Suppliers where SupplierID = " & lngSupplierID Set objDB = CurrentDb Set rstSupplier = objDB.OpenRecordset(strSQL, dbReadOnly) If (rstSupplier.RecordCount <= 0) Then MsgBox "Can't find any suppliers." Else lngSupplierID = rstSupplier!SupplierID strCompanyName = rstSupplier!CompanyName strPhone = rstSupplier!Phone strContactTitle = rstSupplier!ContactTitle strContactName = rstSupplier!ContactName strPhone = rstSupplier!Phone rstSupplier.Close objDB.Close End If '' Display the values Me.txtContactTitle.Value = strContactTitle Me.txtContactName.Value = strContactName Me.txtPhone.Value = strPhone Set rstSupplier = Nothing Set objDB = Nothing End Sub 3. After Update event After supplier is changed to a different one in the Suppliers drop down, the Products List control is refreshed to show products supplied by this supplier. Here the supplier is changed to 'New Orleans Cajun Delights' and the products supplied automatically updated in the list control.
'' Update the List control when a supplier is changed. Private Sub cmbSupplier_AfterUpdate() Dim strSQL As String Dim lngSupplierID As Long lngSupplierID = Me.cmbSupplier.Value strSQL = "select p.ProductID, p.ProductName, c.CategoryName " & _ " from Products As p " & _ " inner join Categories As c On p.CategoryID = c.CategoryID where p.SupplierID = " & lngSupplierID & _ " order by c.CategoryName" Me!lstProduct.RowSource = strSQL Me!lstProduct.RowSourceType = "Table/Query" Me!lstProduct.ColumnCount = 3 Me!lstProduct.ColumnWidths = "0cm;4cm;4cm;" Me!lstProduct.BoundColumn = 1 Me!lstProduct.ColumnHeads = True Me!lstProduct.DefaultValue = Me.lstProduct.ItemData(1) End Sub
Happy Coding!
|
Copyright © 2024 GeeksEngine.com. All Rights Reserved. This website is hosted by HostGator. No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it. |
Home | Feedback | Terms of Use | Privacy Policy |