By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,086 Members | 1,452 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,086 IT Pros & Developers. It's quick & easy.

Building Query based on fields selected in a form

P: 11
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
Expand|Select|Wrap|Line Numbers
  1. 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?
  2.  
  3. Option Compare Database
  4.  
  5. Private Sub btn_run_Click()
  6. Dim Answer As String
  7. Dim MyNote As String
  8. Dim MyNote2 As String
  9. Dim MyNote3 As String
  10. Dim Complete As String
  11. Dim Cancel As String
  12. Dim Detail As String
  13.  
  14. MyNote = "This process will take about 15 minutes to complete.  Would you like to proceed?"
  15. MyNote2 = "Process complete.  Thanks for your patience."
  16. MyNote3 = "Process cancelled."
  17.  
  18. Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "Note")
  19.  
  20. 'Detail = "C:\Users\jhasty\My Projects\File_Splitter\Files\*.*"
  21.  
  22. If Answer = vbNo Then
  23.     Cancel = MsgBox(MyNote3, vbInformation, "Process Cancelled")
  24.     Exit Sub
  25.  
  26. Else
  27.  
  28. '    If Dir(Detail) = "" Then
  29. '        'MsgBox "file does not exist"
  30. '    Else
  31. '        'MsgBox "file does exist"
  32. '        Kill Detail
  33. '    End If
  34.  
  35.     Dim db As Database
  36.  
  37.     Dim carrier As Recordset
  38.     Dim setup As Recordset
  39.  
  40.     Dim qd As QueryDef
  41.     Dim qd2 As QueryDef
  42.     Dim qdName As String
  43.  
  44.     Dim sql_scacs As String
  45.     Dim sql_templates As String
  46.     Dim scac As String
  47.     Dim scac2 As String
  48.     Dim query As String
  49.     Dim template As String
  50.     Dim query_sql As String
  51.     Dim query_sql_replacement
  52.     Dim i As Integer
  53.     Dim count_records As Long
  54.     Dim templatecopy As String
  55.     Dim splitfield As String
  56.  
  57.     Set db = CurrentDb()
  58.     'sql_scacs = "Select * from [qry_scacs]" 'where Active = -1"
  59.  
  60.     sql_templates = "Select * from [tbl_setup_detail]" 'where active = -1"
  61.     Set setup = db.OpenRecordset(sql_templates)
  62.     'MsgBox sql_templates
  63.  
  64.     sql_scacs = "SELECT " & setup("split_field") & " AS split FROM qry_pre_data GROUP BY " & setup("split_field") & " ORDER BY " & setup("split_field") & ";"
  65.     'MsgBox sql_scacs
  66.     Set carrier = db.OpenRecordset(sql_scacs)
  67.  
  68.     templatecopy = setup("template") '"C:\Users\jhasty\My Projects\File_Splitter\Templates\Template - Copy.xls"
  69.     splitfield = setup("split_field")
  70.  
  71.     i = 0
  72.  
  73.     If Not carrier.EOF Then
  74.     carrier.MoveFirst
  75.  
  76.     Do
  77.         scac = carrier("split")
  78.         'MsgBox scac
  79.  
  80.         If Not setup.EOF Then
  81.         setup.MoveFirst
  82.  
  83.         Do
  84.             query = setup("query")
  85.             For Each qd In db.QueryDefs
  86.                 If qd.Name = query Then
  87.                     query_sql = qd.SQL
  88.                     query_sql_replacement = Replace(query_sql, "[ENTER SPLIT-VALUE FOR DETAIL]", "'" & scac & "'")
  89.  
  90.                     qdName = query & "_for_" & scac
  91.  
  92.                     For Each qd2 In db.QueryDefs
  93.                         If qd2.Name = qdName Then
  94.                             db.QueryDefs.Delete qdName
  95.                         End If
  96.                     Next
  97.  
  98.                 db.CreateQueryDef qdName, query_sql_replacement
  99.  
  100.                 End If
  101.             Next
  102.  
  103.             template = "L:\Operations\Engineering\JH\Databases\File_Splitter\Output Files\" & scac & "-" & setup("filename")
  104.             'MsgBox template
  105.  
  106.             'Kill template
  107.             FileCopy templatecopy, template
  108.  
  109.             'template = setup("template")
  110.             'MsgBox query_sql_replacement
  111.             'MsgBox setup("template")
  112.             count_records = DCount("[SCAC]", qdName, "")
  113.             If (count_records > 0) Then
  114.                 DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdName, template, False, "data"
  115.                 i = i + 1
  116.                 Call update_excel_loop(template, i)
  117.                 i = 0
  118.             End If
  119.             db.QueryDefs.Delete qdName
  120.  
  121.             setup.MoveNext
  122.  
  123.         Loop Until setup.EOF
  124.         setup.MoveFirst
  125.  
  126.         End If
  127.  
  128.         carrier.MoveNext
  129.  
  130.     Loop Until carrier.EOF
  131.  
  132.     End If
  133.  
  134. End If
  135.  
  136. Complete = MsgBox(MyNote2, vbInformation, "Process Complete")
  137. End Sub
  138.  
  139. Private Sub update_excel_loop(template, i)
  140. 'declare variables
  141. Dim xlApp As Excel.Application
  142. Dim xlBook As Excel.Workbook
  143.  
  144. 'excel application stuff
  145. If i = 0 Then
  146.     Set xlApp = New Excel.Application
  147. Else
  148.     Set xlApp = Excel.Application
  149. End If
  150.  
  151. xlApp.Visible = False
  152. Set xlBook = xlApp.Workbooks.Open(template)
  153.  
  154. 'run the macro
  155. xlApp.Run "Macro1"
  156.  
  157. 'save file
  158. 'xlBook.Save
  159.  
  160. 'done
  161. xlApp.Quit
  162. Set xlBook = Nothing
  163. Set xlApp = Nothing
  164. End Sub
  165.  
  166. Private Sub Detail_Click()
  167.  
  168. End Sub
  169.  
  170. Private Sub filename_AfterUpdate()
  171. DoCmd.RunCommand acCmdSaveRecord
  172. End Sub
  173.  
  174. Private Sub query_AfterUpdate()
  175. DoCmd.RunCommand acCmdSaveRecord
  176. End Sub
  177.  
  178. Private Sub splitfield_AfterUpdate()
  179. DoCmd.RunCommand acCmdSaveRecord
  180. End Sub
  181.  
  182. Private Sub template_AfterUpdate()
  183. DoCmd.RunCommand acCmdSaveRecord
  184. End Sub
Jan 22 '14 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,666
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2. Dim ctl As Control
    3. Dim strName As String
    4. Dim strSQLFinal As String
    5. Dim intNumSelected As Integer
    6.  
    7.  
    8. For Each ctl In Me.Controls
    9.   If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
    10.     If ctl.Value Then                       'It is Selected?
    11.       intNumSelected = intNumSelected + 1
    12.         strName = Replace(ctl.Name, "chk", "")
    13.           strSQL = strSQL & "[" & strName & "], "
    14.     End If
    15.   End If
    16. Next
    17.  
    18. If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
    19.  
    20. strSQL = Left(strSQL, Len(strSQL) - 2)
    21.  
    22. strSQLFinal = "SELECT " & strSQL & " FROM Table1;"
    23. 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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT [First], [Last] FROM Table1;
  5. Any other questions, feel free to ask.
Jan 22 '14 #2

P: 11
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:

Expand|Select|Wrap|Line Numbers
  1. Do
  2. query = setup("query")
  3. For Each qd In db.QueryDefs
  4. If qd.Name = query Then
  5. query_sql = qd.SQL
  6. 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?
Jan 22 '14 #3

ADezii
Expert 5K+
P: 8,666
  1. Build a Comma Delimited String (Lines 7 to 15) and pass it to a Function (Line 22):
    Expand|Select|Wrap|Line Numbers
    1. Dim strSQL As String
    2. Dim ctl As Control
    3. Dim strName As String
    4. Dim strSQLFinal As String
    5. Dim intNumSelected As Integer
    6.  
    7. For Each ctl In Me.Controls
    8.   If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
    9.     If ctl.Value Then                       'It is Selected?
    10.       intNumSelected = intNumSelected + 1
    11.         strName = Replace(ctl.Name, "chk", "")
    12.           strSQL = strSQL & "[" & strName & "],"
    13.     End If
    14.   End If
    15. Next
    16.  
    17. If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
    18.  
    19. strSQL = Left(strSQL, Len(strSQL) - 1)
    20.  
    21. 'Pass the Comma Delimited String to the Function
    22. Setup strSQL
    23.  
  2. Build the SQL String within the Function and not the Calling Code (Lines 9 to 11, 13):
    Expand|Select|Wrap|Line Numbers
    1. Private Function Setup(strFields As String)
    2. Dim varSplit As Variant
    3. Dim intCtr As Integer
    4. Dim strBuild As String
    5. Dim strFinal As String
    6.  
    7. varSplit = Split(strFields, ",")
    8.  
    9. For intCtr = LBound(varSplit) To UBound(varSplit)
    10.   strBuild = strBuild & varSplit(intCtr) & ", "
    11. Next
    12.  
    13. strFinal = "SELECT " & Left$(strBuild, Len(strBuild) - 2) & " FROM Table1;"
    14.  
    15. MsgBox strFinal
    16. End Function
Jan 23 '14 #4

P: 11
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

Expand|Select|Wrap|Line Numbers
  1. SELECT [LaneID], [RefNum], [OFacility], [OCity], [OState], [OZip], [ORegion], [OCountry], [AnnualVol], [CarName], [CarRef], [Equip], [EquipSize], [OrRateType], [RateGuideSeq], [OrMinChge], [OrTotalRate], [DFacility] FROM qry_datafilter1;
  2.  
  3.  
Jan 27 '14 #5

ADezii
Expert 5K+
P: 8,666
  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.
    Expand|Select|Wrap|Line Numbers
    1. Dim strFinal As String
    2.  
    3. strFinal = "SELECT [LaneID], [RefNum], [OFacility], [OCity], [OState], " & _
    4.            "[OZip], [ORegion], [OCountry], [AnnualVol], [CarName], " & _
    5.            "[CarRef], [Equip], [EquipSize], [OrRateType], [RateGuideSeq], " & _
    6.            "[OrMinChge], [OrTotalRate], [DFacility] FROM qry_datafilter1;"
    7.  
    8. CurrentDb.QueryDefs("qryTest").SQL = strFinal
    9.  
    10. DoCmd.OpenQuery "qryTest"
Jan 28 '14 #6

P: 11
They are field names that are created in the query. Fore example, see the below code. Is this an issue?
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 30 '14 #7

P: 11
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?

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Private Sub btn_run_Click()
  4.  
  5. Dim db As Database
  6. Dim rst As Recordset
  7. Dim strSQL As String
  8. Dim ctl As Control
  9. Dim strName As String
  10. Dim strSQLFinal As String
  11. Dim intNumSelected As Integer
  12.  
  13. Set db = CurrentDb()
  14.  
  15.  
  16.  
  17. For Each ctl In Me.Controls
  18.   If ctl.ControlType = acCheckBox Then      'Control is a Checkbox
  19.     If ctl.Value Then                       'It is Selected?
  20.       intNumSelected = intNumSelected + 1
  21.         strName = Replace(ctl.Name, "ck", "")
  22.           strSQL = strSQL & "[" & strName & "], "
  23.     End If
  24.   End If
  25. Next
  26.  
  27. If intNumSelected = 0 Then Exit Sub         'No Column(s) selected
  28.  
  29. strSQL = Left(strSQL, Len(strSQL) - 2)
  30. strSQLFinal = "SELECT " & strSQL & " FROM data;"
  31.  
  32. Set rst = db.OpenRecordset(strSQLFinal)
  33.  
  34. rst.MoveFirst
  35.  
  36. Set rst = Nothing
  37. Set db = Nothing
  38.  
  39.  
  40.  
  41. End Sub
Jan 31 '14 #8

P: 21
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
Expand|Select|Wrap|Line Numbers
  1.  ? strSQL 
Also, if you get any error messages, please post those as well.
Jan 31 '14 #9

P: 11
The value of strSQL is a string that comes out like this
Expand|Select|Wrap|Line Numbers
  1. [LaneID], [RefNum], [OCity], [OState], [OZip], [DCity], [DState], [DZip], [AnnualVol], [CarName], [SCAC], [RateType], [TotalRate], 
Then, the value is trimmed and placed in the Select statement
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 31 '14 #10

zmbd
Expert Mod 5K+
P: 5,397
Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 31 '14 #11

P: 11
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?
Mar 21 '14 #12

Post your reply

Sign in to post your reply or Sign up for a free account.