I have a form with 3 cascading search boxes.
The first cbo is Contract Number, but the 2nd cascading cbo is made up of four separate Task Order fields. (cboTOVAC, cboTOHCW, cboTOCDR, cboTOTerm)
Can I use a string to reference all of these fields so I can manage the code a little easier. This will be also be the case for the 3rd cascading box which also has 4 fields. These are called CLINs. (contract line numbers)
So when you select a Contract Number in the first box, I want the 4 Task Order fields that have associated Contract records to appear. Then when I select the Task Order I want the associated CLINs records that are tied to the Task Order to appear.
I found this code online and it seems very straightforward but I thought it would be easier if I could reference the 4 Task Order fields as one string. Is this possible? In this example it is Row, Column, Numeric.
Code:
Private Sub cboRowField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String
cboColumnField = Null
cboNumericField = Null
strSQL = "SELECT DISTINCT tblDemo.ColumnField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "'"
strSQL = strSQL & " ORDER BY tblDemo.ColumnField;"
cboColumnField.RowSource = strSQL
strSQLSF = "SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.RowField = '" & cboRowField & "'"
Me!sfrmForm.LinkChildFields = "RowField"
Me!sfrmForm.LinkMasterFields = "RowField"
Me.RecordSource = strSQLSF
Me.Requery
End Sub
Private Sub cboColumnField_AfterUpdate()
Dim strSQL As String
Dim strSQLSF As String
cboNumericField = Null
strSQL = " SELECT DISTINCT tblDemo.NumericField FROM tblDemo "
strSQL = strSQL & " WHERE tblDemo.RowField = '" & cboRowField & "' And "
strSQL = strSQL & " tblDemo.ColumnField = '" & cboColumnField & "'"
strSQL = strSQL & " ORDER BY tblDemo.NumericField;"
cboNumericField.RowSource = strSQL
strSQLSF = " SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.RowField = '" & cboRowField & "' And "
strSQLSF = strSQLSF & " tblDemo.ColumnField = '" & cboColumnField & "'"
Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""
Me!sfrmForm.LinkChildFields = "RowField;ColumnField"
Me!sfrmForm.LinkMasterFields = "RowField;ColumnField"
Me.RecordSource = strSQLSF
Me.Requery
End Sub
Private Sub cboNumericField_AfterUpdate()
Dim strSQLSF As String
strSQLSF = " SELECT * FROM tblDemo "
strSQLSF = strSQLSF & " WHERE tblDemo.RowField = '" & cboRowField & "' And "
strSQLSF = strSQLSF & " tblDemo.ColumnField = '" & cboColumnField & "' And "
strSQLSF = strSQLSF & " tblDemo.NumericField = " & cboNumericField
Me!sfrmForm.LinkChildFields = ""
Me!sfrmForm.LinkMasterFields = ""
Me!sfrmForm.LinkChildFields = "RowField;ColumnField;NumericField"
Me!sfrmForm.LinkMasterFields = "RowField;ColumnField;NumericField"
Me.RecordSource = strSQLSF
Me.Requery
End Sub