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
11 2469 - 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:
- [First] {TEXT}
- [MI] {TEXT}
- [Last] {TEXT}
- 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.
- Copy-N-Paste the following Code wherever appropriate. The Code will:
- Loop thru all the Controls on your Form.
- If the Control is a Check Box AND is Selected, an SQL Statement will start to be built.
- 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.
- 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
- 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;
- Any other questions, feel free to ask.
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?
- 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
-
- 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
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;
-
-
- Do ALL the Fields in the SELECT Statement exist in qry_datafilter1?
- If they do exist, then try a different approach.
- Make an exact Copy of qry_datafilter1 and name it qryTest.
- Modify the SQL of qryTest using the Value of strFinal.
- 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"
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.
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
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
Also, if you get any error messages, please post those as well.
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 5,501
Expert Mod 4TB - 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.
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?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: JDJones |
last post by:
Using PHP and MySQL. Trying to put a list of categories into a drop down
select option of a form like:
<form name="form" action="<? print $_SERVER?>" method="get">
<select name="subject">...
|
by: sheree |
last post by:
I would like to create a query where one of the columns of the queries
comes from a combo list box on a form.
For example, if my table has the following fields:
id
name
interest1
interest2...
|
by: Matthew |
last post by:
Hey,
I have built a form that has certain combo and text boxes on it, which
a user specifies his criteria and then clicks on a search button to
run a query based on that criteria. To build to...
|
by: Matthew |
last post by:
I have a form with fields that act as user specified criteria for a
form. Two of the fields are start and finish date. The fields in my
query are "ID", "TimeStart", "TimeEnd", etc. Currently, I...
|
by: Ferasse |
last post by:
Hi,
I'm an occasional Ms-Access developer, so there is still a lot of stuff that I don't get...
Right now, I'm working on a database that stores contractual information.
One of the form that...
|
by: scolivas |
last post by:
I would like to make a form that creates a query.
I know that I could make a query for each radio button instance but that would take forever!
So basically what i have is 10 Fields in a query ...
|
by: Neil Chinneck |
last post by:
Hi everyone,
I'm looking for some help please. I have a continuous form, with several fields which can be filtered using combo boxes. Once the user has filtered the form, I want them to enter...
|
by: gnawoncents |
last post by:
I have a form (FRMsurveyReports) with several combo boxes (e.g. Class, ClassUnique, SurveyID, etc.) which use an SQL query based on a table (TBLSurveyResponses) for their record source. The...
|
by: Karen D |
last post by:
Help Again!! I’m using Access 2003 and I have a form that allows users to enter criteria for selecting tables and queries as well as the query parameters that will be used to generate a report. The...
|
by: reginaldmerritt |
last post by:
I have a table TBStaff with boolean fields to show their type of employment. Staff can have many types of employment (upto 6).
TBStaff
StaffID_________Autonumber (PK)
Forenames_____Text ...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |