Building Query based on fields selected in a form

[kadishzm]

Hi Everyone, I have recently been tasked with taking over a bunch of Access databases that I didn't create. My VBA skills are weak to say the best but I have a particular problem I am working on. What I want to do is use a form to identify multiple value that will pass through a query and generate excel documents for a split field. I already have the code for that but I need to add a "column selection" tool to add or

delete particular columns based on customer requirements. I have attached my code below. What I want to do is use my checkboxed form to create unique queries. Any thoughts?
 
Option Compare Database
 
Private Sub btn_run_Click()
Dim Answer As String
Dim MyNote As String
Dim MyNote2 As String
Dim MyNote3 As String
Dim Complete As String
Dim Cancel As String
Dim Detail As String
 
MyNote = "This process will take about 15 minutes to complete.  Would you like to proceed?"
MyNote2 = "Process complete.  Thanks for your patience."
MyNote3 = "Process cancelled."
 
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Note")
 
'Detail = "C:\Users\jhasty\My Projects\File_Splitter\Files\*.*"
 
If Answer = vbNo Then
    Cancel = MsgBox(MyNote3, vbInformation, "Process Cancelled")
    Exit Sub
 
Else
 
'    If Dir(Detail) = "" Then
'        'MsgBox "file does not exist"
'    Else
'        'MsgBox "file does exist"
'        Kill Detail
'    End If
 
    Dim db As Database
 
    Dim carrier As Recordset
    Dim setup As Recordset
 
    Dim qd As QueryDef
    Dim qd2 As QueryDef
    Dim qdName As String
 
    Dim sql_scacs As String
    Dim sql_templates As String
    Dim scac As String
    Dim scac2 As String
    Dim query As String
    Dim template As String
    Dim query_sql As String
    Dim query_sql_replacement
    Dim i As Integer
    Dim count_records As Long
    Dim templatecopy As String
    Dim splitfield As String
 
    Set db = CurrentDb()
    'sql_scacs = "Select * from [qry_scacs]" 'where Active = -1"
 
    sql_templates = "Select * from [tbl_setup_detail]" 'where active = -1"
    Set setup = db.OpenRecordset(sql_templates)
    'MsgBox sql_templates
 
    sql_scacs = "SELECT " & setup("split_field") & " AS split FROM qry_pre_data GROUP BY " & setup("split_field") & " ORDER BY " & setup("split_field") & ";"
    'MsgBox sql_scacs
    Set carrier = db.OpenRecordset(sql_scacs)
 
    templatecopy = setup("template") '"C:\Users\jhasty\My Projects\File_Splitter\Templates\Template - Copy.xls"
    splitfield = setup("split_field")
 
    i = 0
 
    If Not carrier.EOF Then
    carrier.MoveFirst
 
    Do
        scac = carrier("split")
        'MsgBox scac
 
        If Not setup.EOF Then
        setup.MoveFirst
 
        Do
            query = setup("query")
            For Each qd In db.QueryDefs
                If qd.Name = query Then
                    query_sql = qd.SQL
                    query_sql_replacement = Replace(query_sql, "[ENTER SPLIT-VALUE FOR DETAIL]", "'" & scac & "'")
 
                    qdName = query & "_for_" & scac
 
                    For Each qd2 In db.QueryDefs
                        If qd2.Name = qdName Then
                            db.QueryDefs.Delete qdName
                        End If
                    Next
 
                db.CreateQueryDef qdName, query_sql_replacement
 
                End If
            Next
 
            template = "L:\Operations\Engineering\JH\Databases\File_Splitter\Output Files\" & scac & "-" & setup("filename")
            'MsgBox template
 
            'Kill template
            FileCopy templatecopy, template
 
            'template = setup("template")
            'MsgBox query_sql_replacement
            'MsgBox setup("template")
            count_records = DCount("[SCAC]", qdName, "")
            If (count_records > 0) Then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdName, template, False, "data"
                i = i + 1
                Call update_excel_loop(template, i)
                i = 0
            End If
            db.QueryDefs.Delete qdName
 
            setup.MoveNext
 
        Loop Until setup.EOF
        setup.MoveFirst
 
        End If
 
        carrier.MoveNext
 
    Loop Until carrier.EOF
 
    End If
 
End If
 
Complete = MsgBox(MyNote2, vbInformation, "Process Complete")
End Sub
 
Private Sub update_excel_loop(template, i)
'declare variables
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
 
'excel application stuff
If i = 0 Then
    Set xlApp = New Excel.Application
Else
    Set xlApp = Excel.Application
End If
 
xlApp.Visible = False
Set xlBook = xlApp.Workbooks.Open(template)
 
'run the macro
xlApp.Run "Macro1"
 
'save file
'xlBook.Save
 
'done
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
 
Private Sub Detail_Click()
 
End Sub
 
Private Sub filename_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub
 
Private Sub query_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub
 
Private Sub splitfield_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub
 
Private Sub template_AfterUpdate()
DoCmd.RunCommand acCmdSaveRecord
End Sub

[ADezii]

1. From what I can gather, your decision to include/omit Columns from a Query is determined by a series of Check Boxes. Based on this, let's assume we have a Table named Table1 with three Fields:
   1. [First] {TEXT}
   2. [MI] {TEXT}
   3. [Last] {TEXT}
2. For all the Fields you may wish to include in your Query, create a Check Box on your Form. The Name of this Check Box will be chk & [Field Name]. I'm assuming that the only Check Boxes on the Form are those representing Fields which may/may not be included in your Query.
3. Copy-N-Paste the following Code wherever appropriate. The Code will:
   1. Loop thru all the Controls on your Form.
   2. If the Control is a Check Box AND is Selected, an SQL Statement will start to be built.
   3. Once all the Controls on the Form have been processed, the SQL Statement will be trimmed and a Final Statement build and assigned to the Variable strSQLFinal.
   4. If no Check Boxes are selected the Code harmlessly exists.

   Dim strSQL As String
   Dim ctl As Control
   Dim strName As String
   Dim strSQLFinal As String
   Dim intNumSelected As Integer
    
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
       If ctl.Value Then                       'It is Selected?
         intNumSelected = intNumSelected + 1
           strName = Replace(ctl.Name, "chk", "")
             strSQL = strSQL & "[" & strName & "], "
       End If
     End If
   Next
    
   If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
    
   strSQL = Left(strSQL, Len(strSQL) - 2)
    
   strSQLFinal = "SELECT " & strSQL & " FROM Table1;"
   Debug.Print strSQLFinal

4. As an Example, I had three Check Boxes on my Form named chkFirst, chkMI, and chkLast. Selecting only chkFirst and chkLast procduced:
   

   SELECT [First], [Last] FROM Table1;

5. Any other questions, feel free to ask.

[kadishzm]

Thanks for the quick response. I was trying to make sense of a previous post that you had helped someone with but can't figure out exactly where this would go. Does it matter where I declare the dimensions? Also, the form has 4 text boxes that also pass variables through the query. so I'm not sure how this would be added to my existing code:

Do
 query = setup("query")
For Each qd In db.QueryDefs
If qd.Name = query Then
query_sql = qd.SQL
query_sql_replacement = Replace(query_sql, "[ENTER SPLIT-VALUE FOR DETAIL]", "'" & scac & "'")

I want to pass the fields (first, middle) in your example through the select statement of the setup("query")(in bold) above but haven't been able to successfuly combine the two. Any ideas?

[ADezii]

1. Build a Comma Delimited String (Lines 7 to 15) and pass it to a Function (Line 22):
   

   Dim strSQL As String
   Dim ctl As Control
   Dim strName As String
   Dim strSQLFinal As String
   Dim intNumSelected As Integer
    
   For Each ctl In Me.Controls
     If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
       If ctl.Value Then                       'It is Selected?
         intNumSelected = intNumSelected + 1
           strName = Replace(ctl.Name, "chk", "")
             strSQL = strSQL & "[" & strName & "],"
       End If
     End If
   Next
    
   If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
    
   strSQL = Left(strSQL, Len(strSQL) - 1)
    
   'Pass the Comma Delimited String to the Function
   Setup strSQL
    

2. Build the SQL String within the Function and not the Calling Code (Lines 9 to 11, 13):
   

   Private Function Setup(strFields As String)
   Dim varSplit As Variant
   Dim intCtr As Integer
   Dim strBuild As String
   Dim strFinal As String
    
   varSplit = Split(strFields, ",")
    
   For intCtr = LBound(varSplit) To UBound(varSplit)
     strBuild = strBuild & varSplit(intCtr) & ", "
   Next
    
   strFinal = "SELECT " & Left$(strBuild, Len(strBuild) - 2) & " FROM Table1;"
    
   MsgBox strFinal
   End Function

[kadishzm]

Thank you again for the help. I was able to get the first solution to work (for the most part). The issue I have now is that the query does not execute. I pulled the code out from the debugger and it seems to be correct. The problem is that when I enter it into a query/try to execute it via the button, it asks for parameters for each of the fields instead of pulling the information. Any thoughts? The SQL from the debugger is included below

SELECT [LaneID], [RefNum], [OFacility], [OCity], [OState], [OZip], [ORegion], [OCountry], [AnnualVol], [CarName], [CarRef], [Equip], [EquipSize], [OrRateType], [RateGuideSeq], [OrMinChge], [OrTotalRate], [DFacility] FROM qry_datafilter1;
 
 

[ADezii]

1. Do ALL the Fields in the SELECT Statement exist in qry_datafilter1?
2. If they do exist, then try a different approach.
   1. Make an exact Copy of qry_datafilter1 and name it qryTest.
   2. Modify the SQL of qryTest using the Value of strFinal.
   3. Open qryTest.

   Dim strFinal As String
    
   strFinal = "SELECT [LaneID], [RefNum], [OFacility], [OCity], [OState], " & _
              "[OZip], [ORegion], [OCountry], [AnnualVol], [CarName], " & _
              "[CarRef], [Equip], [EquipSize], [OrRateType], [RateGuideSeq], " & _
              "[OrMinChge], [OrTotalRate], [DFacility] FROM qry_datafilter1;"
    
   CurrentDb.QueryDefs("qryTest").SQL = strFinal
    
   DoCmd.OpenQuery "qryTest"

[kadishzm]

They are field names that are created in the query. Fore example, see the below code. Is this an issue?

SELECT [Lane ID] as [LaneID] from Table1

How would making a copy of the query pass the variables from the checkbox? I guess I'm just having difficulty understanding how the Querydefs work in the first place and havent been able to get a good description online.

[kadishzm]

Turns out that some of the fields were not in the table (formatting issue). I pulled the SQL generated from the debugger and was able to run it multiple times (using the checkboxes to determine random column headers) and populate a data table. The code itself will still not "execute". I have tried to modify it based on some tutorial videos I watched but can't figure out what is going wrong. Any thoughts?

Option Compare Database
 
Private Sub btn_run_Click()
 
Dim db As Database
Dim rst As Recordset
Dim strSQL As String
Dim ctl As Control
Dim strName As String
Dim strSQLFinal As String
Dim intNumSelected As Integer
 
Set db = CurrentDb()
 
 
 
For Each ctl In Me.Controls
  If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
    If ctl.Value Then                       'It is Selected?
      intNumSelected = intNumSelected + 1
        strName = Replace(ctl.Name, "ck", "")
          strSQL = strSQL & "[" & strName & "], "
    End If
  End If
Next
 
If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
 
strSQL = Left(strSQL, Len(strSQL) - 2)
strSQLFinal = "SELECT " & strSQL & " FROM data;"
 
Set rst = db.OpenRecordset(strSQLFinal)
 
rst.MoveFirst
 
Set rst = Nothing
Set db = Nothing
 
 
 
End Sub

[TjabbeTjibsma]

Can you put some breakpoints in your code (for example at line 29) and output the value of strSQL and strSQLFinal using the immediate window?

Use the immediate window like this

 ? strSQL 

Also, if you get any error messages, please post those as well.

[kadishzm]

The value of strSQL is a string that comes out like this

[LaneID], [RefNum], [OCity], [OState], [OZip], [DCity], [DState], [DZip], [AnnualVol], [CarName], [SCAC], [RateType], [TotalRate], 

Then, the value is trimmed and placed in the Select statement

SELECT [LaneID], [RefNum], [OCity], [OState], [OZip], [DCity], [DState], [DZip], [AnnualVol], [CarName], [SCAC], [RateType], [TotalRate] FROM data;

.
It runs through everything without errors but I don't know that it is doing anything when I hit the button that it is connected to. Am I missing some critical command to attach it to the database or call/open the query? I know the DoCmd for running a query hasn't worked. I hit the button and nothing ever really happens.

[zmbd]

Set rst = db.OpenRecordset(strSQLFinal)

This only opens the recordset for use within the code.
If you want to open the query for use by a human then either you must have a form that you dynamically add the recordset and controls to (that's fun ;) ) or you will need to add the string to the querydefs collection (thereby converting it from a dynamic query to a stored query) and then you can use the OpenQuery method. (Adezii's post#6 codeblock line8 and line 10)
If you add it to the querydefs, then you must decide to either have a method to delete the exsisting version or to use a different name each time you save the query.

[kadishzm]

Thanks for the help with this everyone. I have one more issue that I am trying to address. The checkboxes were placed in a particular order to reflect the desired order of the columns (fields) on the output document. I went through and changed the tab order to reflect the desired order of operations but the query is not pulling the fields in the right sequence. Is there a particular way that I can set the order of select fields that generates the output document?