Single string reference for multiple fields

[PinkLady50]

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.

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

[June7]

To build an SQL string with filter parameters, you have to concatenate the variables, otherwise the variable names would become literal text within the SQL statement, not the variable values. Reference to the combobox value or columns is the same as using a declared VBA variable. Now if you want to build a column within the combobox RowSource SQL that concatenates literal text and fields, then that column could be concatenated into the VBA SQL construction. As example for number, text, and date/time fields, combobox RowSource could be like:

SELECT ID, "[Field1] = " & [Field1] & " AND [Field2] = '" & [Field2] & "' AND [Field3] = #" & [Field3] & "#" AS Criteria FROM tablename;

Then to construct the SQL:

strSQLSF = " SELECT * FROM tblDemo WHERE " & Me.cboName.Column(1)

So you see, somewhere you will have to concatenate variables.


But why are you using code to set Master/Child links and RecordSource properties?

[PinkLady50]

But why are you using code to set Master/Child links and RecordSource properties?

Good Question!!! I am a novice user and sometimes I find code but don't always understand "all the parts of it". I will leave it out when I test and see what happens.

[June7]

Use code to set Filter property or pass filter criteria in OpenForm/OpenReport WHERE argument. Review http://www.allenbrowne.com/ser-62.html

[PinkLady50]

thank you for your explanation. As a novice I still have a number of questions. I understand the concept but I am not sure how to execute!
1. Where does this Select statement go? Is it part of the code (where specifically) or is this a Row Source?
2. I don't understand the ID and Column(1).
3. In the example above it was Column, Row and Numeric. In my code is will be ContractNumber, TaskOrder and CLIN. What will be my TaskOrder and CLIN field name to use in the code above. I understand that we are concatenating 4 TaskOrders in to 1 but what will the variable be called? Theirs is call RowField and NumericField.
4. Could you check the syntax of my code below to make sure I have stringed them together correctly. All are text fields.

SELECT ID, "[cboTOVAC] = " & [cboTOVAC] & " AND [cboTOHCW] = '" & [cboTOHCW] & "' AND [cboTOCDR] = " & [cboTOCDR] & "' AND [cboTOCDR] = " & [cboTOTerm] AS Criteria FROM tblPErfIssues;

Sorry for all the questions. I have never done something like this before. Appreciate your assistance.

[June7]

1. I already stated it goes in combobox RowSource property.

2. I don't know your field names so I used a generic field name ID as the name for table primary key field. Column(1) is reference to combobox column index. Index begins with 0, the string value is in column 2 so it's index is 1. You can ignore the ID and not include in your SQL because if you have this primary key used as foreign key you could use it as filter criteria instead of this string of fields. But the combobox will need to display something to the user that has meaning so they can make a choice. Like:

SELECT [Field1] & ":" & [Field2] & ":" & [Field3] & ":" & [Field4] AS Data, "[Field1] = " & [Field1] & " AND [Field2] = '" & [Field2] & "' AND [Field3] = #" & [Field3] & "#" AS Criteria FROM tablename;

3. Don't really understand the question. Substitute with your field and combobox names where appropriate in the code. And the example concatenates only 3 elements, you have 4 so add another line in the concatenation. Use of Row, Column, Numeric seems to indicate this concerns a CROSSTAB query although the constructed SQL statement is not a CROSSTAB. Rather odd choice of names. But if you are going ahead with the combobox providing the concatenated string, don't need all those variables shown in the example, you will concatenate only one variable and that is the reference to the combobox as already shown in post 2.

4. SQL for combobox RowSource should reference fields, not comboboxes. Review my example again. If these are all text type fields, be sure to get all the apostrophe delmiters correct. Apostrophes and quotes must always be in pairs. You have an unpaired apostrophe for the third parameter and none for the first and last.

You asked if there was a simpler approach from the concatenation of the original example code. I think this demonstrates that there really isn't, just different ways to achieve same outcome, but not really simpler.