This allows user to select choices from 3 dynamic rowsources (comboboxes). Selection from box A, drives selections in Box B, then combination of selections in Boxes, A & B, drive selection of choices for Box C. Results from A, B, And C, crossreference data in 4 column table (sorry I think in Xcel) and autoplace a value in TextBox 1. Very useful tool.
Code is below and example of datatable is below that.
Option Compare Database
- 'Code for Combobox B. (selection for rowsource A, taken directly from table)
-
Private Sub Asset_Type_AfterUpdate()
-
On Error Resume Next
-
Manufacturer.RowSource = "Select DISTINCT tblCRX.Mfg " & _
-
"FROM tblCRX " & _
-
"WHERE tblCRX.Type= Asset_Type.Value " & _
-
"ORDER BY tblCRX.Mfg;"
-
End Sub
- 'Code for Combobox C.
-
Private Sub Manufacturer_AfterUpdate()
-
On Error Resume Next
-
System.RowSource = "Select DISTINCT tblCRX.Model " & _
-
"FROM tblCRX " & _
-
"WHERE tblCRX.Type= Asset_Type.Value And tblCRX.Mfg = Manufacturer.Value " & _
-
"ORDER BY tblCRX.Model;"
-
End Sub
- 'Code for TextBox 1.
-
Private Sub System_AfterUpdate()
-
On Error Resume Next
-
strSQL = "(([Type]='" & [Asset_Type] & _
-
"') And ([Mfg]='" & [Manufacturer] & _
-
"') And ([Model]='" & System & "'))"
-
Form_BB_Value = DLookup("[BB_Value]", "[tblCRX]", strSQL)
-
End Sub
Table Example
Column A) Type (CD, VHS, Book)
Column B) Mfg (Sony, Paramount, etc)
Column C) Title (Hair, Led Zeppin, Cars, etc)
Column D) Price ($$$)
Benefit is table values can be updated on a regular basis and almost completly removes the possibility of user error once appropriate additional locks are placed on controls.