Modules & VBA :: Storing Query Results In A Variable
Jan 20, 2014
I have a query which returns a list of reports I have created this session.
I want a button that when I click it runs the query and then stores the results into a variable.
I looked online and found a pretty good connection code and loop code to get the data but I'm having a hard time getting it to store into a variable.
What I want to do next is using code print the reports that were listed in the query.
That Means I need to be able to take that variable and pop the information off it, storing it into another variable (or if i can use an array just use that) and use that to concatenate into a command to print that report.
I am having problems with the syntax of this though. I think I'm just missing some key elements.
I've got such a simple SQL query, but it won't output the friggin results of the query!
I put the query in a "Query" in access, run it... and it outputs me a integer value. I'm running the following VBA code in "Form Load"
Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT Count(*) FROM ContactDetails;"
MsgBox strSQL
What it does is on form load instead of popping up a msgbox saying 41 or whatever the integer value of that count query should be.... it outputs the query itself:
SELECT Count(*) FROM ContactDetails
Any ideas on how I can get the SQL query to run as a query and output the value into the msgbox?
I have a table (tblData) that stores all information for a couple hundred records.
I have a method that copy/pastes all this information to a webbrowser based on the URL TitleBar. If the titlebar changes I need a way to update what to look for so it can paste it to the browser with the new titlebar. I don't want to have to update it for every record so I created a seperate table with one record with just an ID and the string for the titlebar. I have a seperate form from the main one that allows the user to update this and store it back into the table. The problem I am having is using the information from that second table (tblTitleBar) and puting it where it needs to be in the VBA code to access the correct browser.
I don't think any type of relationship will work because that just requires me to input the titlebar straight into each record for the first table anyways. I don't see any way of storing a changing variable besides puting it in a table. I was wondering if there was a way to code a call to the second table to retrieve the single record's field, but so far I have had no success.
Any help/words/advice would be greatly appreciated, thanks for reading my problem.
I'm trying to run an SQL statement to Sum the values of a table column. No problem. It would be useful for me to take that total and be able to set it equal to a variable within my VB code that's running, I just don't know how to do that, I'm very new to VB programming.
I cannot find help anywhere on the internet for this. :( I'd like to do something like this, just don't know how:
VariableName = Run.SQL ("Sum(tblName.Column) AS SumOfColumn FROM tblName")
I'm not a programmer by trade, so I am picking up things piece by piece, and just cannot find my way out of this task. Thanks for your help guys.
Error 91 - Object variable or With block variable not set
I am getting this error telling me that an object variable is not set.
I know which variable it is but when I step through the debugger it sets the variable and all is fine? Issue is that public variable of a class is not getting set when the VBA Editor is not open?
This code runs fine the FIRST time, however trows up a message the SECOND time it is run.
The error is on the line ".Range"
I am trying to sort records which have been exported to Excel.
Dim LR As Integer LR = 5 Set xlApp = CreateObject("Excel.Application") xlApp.Visible = True Set wbRef = xlApp.Workbooks.Add With wbRef
wbRef.Activate .Worksheets("Sheet1").Activate With ActiveSheet .Range("A2", .Cells(LR, "O").End(xlUp)).Sort Key1:=.Range("C2"), Order1:=xlAscending, Header:=xlYes End With end With
I have a form in which am gathering information from the user to populate a table(Customer Master List) and at the same time (btn_Copy_Click Event)transferring the info to another form(Case) populating another table(Customer_Call). It is working fairly well, My issue is that before transferring the info I need to run a check(SQL Query) to make sure the customer or actually the Well ID don't exist in the Customer_Call table. I am trying to do this in pieces .....
1) capturing the well_Id in a variable(WellID) in the first form and using that to build the sql string and query the Customer_Call table.
2)once that works place it in a if / else clause to copy or not with appropriate messages
With that I am stuck in step 1
It works up until Set rst = CurrentDb.OpenRecordset(strSQL) the i get Run-time error '3061' Too few parameters. Expected 1.
Code: Dim WellID As String Dim strModel As String Dim strSQL As String Dim rst As DAO.Recordset WellID = Forms!f_Customer_Lookup.Well_ID MsgBox WellID ' testing to see if it picks up the correct box in form strSQL = "SELECT Customer_Call.[Cus_Well_ID] " & _ "FROM Customer_Call " & _ "WHERE Customer_Call.[Cus_Well_ID] = WellID;" Set rst = CurrentDb.OpenRecordset(strSQL) strModel = rst!Cus_Well_ID rst.Close MsgBox rst ' Testing to see if the strSQL captured the data Set rst = Nothing End Sub
I'm trying to determine if an employee changed departments in 2007.
We maintain a table that records every time an employee transfers, giving an effective date of the change. But, this table also records transferring to a different shift without a department change.
So, my logic was to look at what an employee's most recent assignment was as of 12/31/06 - this way, even if the employee's last transfer was June 3, 1827, it would still be picked up.
Then, I would see if there was any department change in 2007 by comparing any records recorded in this table in 2007 with the list from 2006 and pulling out those records that had different departments.
My first attempt was to use a query for 2006 and earlier with totals, grouping on the employee ID and grabbing the Max Effective date along with the department ID (org). Like so;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate, dbo_assignment_log.workingorg FROM dbo_assignment_log GROUP BY dbo_assignment_log.ppms_ID, dbo_assignment_log.workingorg HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#)) ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
All well and good. But, then I decided that I didn't want the org in the list since it was giving me extra records (because it was on Group By), so I took it out and ran the query again like this;
SELECT dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate) AS MaxOfeffectivedate FROM dbo_assignment_log GROUP BY dbo_assignment_log.ppms_ID HAVING (((Max(dbo_assignment_log.effectivedate))<=#12/31/2006#)) ORDER BY dbo_assignment_log.ppms_ID, Max(dbo_assignment_log.effectivedate);
I am trying to figure out if this line of code is usable, but I don't know: DoCmd.RunSQL ("SELECT Max([BSRangeTbl].[Date]) AS MAXofDate FROM [BSRangeTbl];")
I want to get the output of a vba query (only one solution possible) in to a variable but the variable stays empty.
Dim rst As DAO.Recordset Set rst = CurrentDb.OpenRecordset("SELECT info FROM evaluationtable WHERE evaluation= " & evaluationchoice & " ") var = rst(0).Value rst.Close
some explination: evaluation and info are fields of evaluationtable evaluationchoice is a field in an accessform where I can choice a value from the evaluation field
the table is build as this (only two fields) evaluation - info
Code: Select distinct [tbl_DTP/CTP].id_zlecenia, [tbl_DTP/CTP].Folia_na_lakier_UV, [tbl_DTP/CTP].Wykrojnik, [tbl_DTP/CTP].Makieta, [tbl_DTP/CTP].Matryca_do_tloczenia, [tbl_DTP/CTP].Matryca_do_zlocenia, [tbl_DTP/CTP].kalka, tblGoraZleceniaRoboczeLaczenie.NumerZlecenia from [tbl_DTP/CTP] inner join tblGoraZleceniaRoboczeLaczenie on [tbl_DTP/CTP].id_zlecenia = tblGoraZleceniaRoboczeLaczenie.NumerZlecenia where tblGoraZleceniaRoboczeLaczenie.NumerZlecenia = r1;
Where "r1" is a public variable as string.The variable has value e.g. "10/145" But query can't get this value and all the time ask about value from "r1" :/
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
I have made this sql statement in VBA where rubriek = a variable but I dont know how to implement a variable in a nested query.
SELECT "rubriek, SUM(verkoopprijs) as prijzen FROM voorwerp (inner join VoorwerpInRubriek on voorwerp.voorwerpnummer = VoorwerpInRubriek.voorwerp) inner join Rubriek on rubriek.rubrieknummer = VoorwerpInRubriek.rubriekOpLaagsteNiveau where rubriek in (select rubrieknummer from rubriek where rubriek = variable name ) group by rubriek"
Do I have to treat it as a normal select query or is there something speical that I have to do ?
There are two forms to be filled out on Access. One (maybe two) come from our Contractor. They will input the Bill of Materials, Scrap Rates, Inventory Turns and Cost. This goes to a non-official Bill of Materials table. The second form comes from our Supplier. They will input the the cost they charge the contractor for the given Bill of Materials. Access runs a report that will automatically compare the Contractor costs with the Supplier costs for each component on the Bill of Materials. If the component costs match, it will highlight the Contractor cost green, it they do not, it highlights red.
If all numbers are green, I hit the "Approve" button on the report and there are three queries that need to be ran. An Update Query that changes all the statuses for the components from "Unapproved" to "Approved", an Append Query to add the Bill of Materials to our official table, and a Delete Query that removes the components from the unofficial Bill of Materials table. Once this is all done, an e-mail needs to be sent to Finance to roll costing for the SKU. I do not know the best way, but I currently am having getting the Code to do the following:
1. Put the specific SKU in the e-mail, which is variable. 2. Change the Criteria of the Queries so they only run for the specific approved sku 3. Get the "Approve" and "Deny" buttons on the report to only function for the specific SKU.
Currently all the SKUs that are in "Unapproved" status show on the report. Once I hit approve, it wants to approve all the SKUs, not just the one I'm looking at.
Code: Sub Command36_Click() '''Current Issue: Need Query Criteria to be SKU specific''' 'Update Query to change BOM status to "Approved" DoCmd.OpenQuery "(2302) BOM - 3PM Entry Query Approved", acViewNormal, acEdit
I am using Access 2013. I have the ability to pull a selection from a listbox. I can create a Select Sql string using that variable
sql As String, strCompany As String, strWhere As String strCompany = strCompany & Me.lstResource.Column(0, varItem) strWhere = "[Company name]=" & "'" & strCompany & "'" sql = "select * FROM tblResources WHERE " & strWhere
From here I have trouble. I see lots of examples to run an active query but not much on a select query. I have tried a number of things with no success. How to use this select statement to actually run against an existing access table? I am not putting it into a form or report at this time, just running the query to check results.
I am looking for the vba to have a query cycle through using records from another query as the parameter run and export for each of the records on the second query.
Table 1 has information with all clients query 1 has x, y, z being pulled > client used as parameter query 2 has the list of clients
Currently I have the coding to run the query and export however I am not sure how to get it to repeat for each client.
Code: Private Sub CmdInternalReports_Click() Dim xlApp As Excel.Application Dim rstDetails As DAO.Recordset Dim strTab As String Dim strDir As String Set xlApp = Nothing Set rstDetails = Nothing
[code]...
There can be upto 70 different clients or as little as 2 depending on the day. So the Query I have takes a list of the clients that receive the report and runs it against the imported table to only give the ones with data day.
PHP Code:
tblClientReport.Distro
Is where I have the report via client name linked the the distro list that should be pulled.
I'm running a VBA routine in Excel that loops through a lot of data. As part of the process, I'd like to pass a variable from Excel to an Access database that is open and have it run a query based on that value.
I am using a public function to feed a variable string to a query. So far I have got:
Code: Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String Dim cslStr1 As String, newStreet As String newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
I am trying to set up something to be able to take me to a folder that holds various forms for personnel. I have a query that generates the link for each person. I have tried to set it up as a hyperlink in ACCESS 2013 and it displays as one but doesn't act like one.
I want to be able to click the link and have it open up a personnel folder for that individual. I can't find a MACRO that I can create to do it. Example of my query is that it creates a link K:Main BreakdownSection BreakdownPersonnel FoldersName and the name is the variable part.
My next problem will be to have it create that folder when we have new personnel arriving.
I would also note that I have not worked with ACCESS in many years and much of what I was able to do with 2002 and 2003 doesn't work with the newer versions of ACCESS.
I was beginning to think I had got the hang of creating queries on the fly in VBA. After several hours I have just learned that docmd.runSQL does not work for simple SELECT statements. I'm not going to worry about why that would be. I'm sure there's a jolly good reason.
So... I am able to dynamically create the SQL string for the SELECT statement that I need.
How do I run it? It just needs to return results as a datasheet so I can see them. I'm running Access 2013. I've been searching on this subject for quite a while and found many references to DAO, ADO, ADODAOD, YODELAEYYOUDELAYIO! and other things I don't yet understand ...
First, I'm developing both the Front End and Back End in Access 2007, and I intend to distribute the application using the Access Run Time. The powers that be will not let me use SQL Server.
The database is required to house attachments. Initially, I had created a separate back end database to store the attachments, but the file would quickly grow beyond the 2GB limit - roughly within 3 weeks. Instead, I decided to save the "attachments" in folders (named after the parent records PK ID) on the network. Using VBA, I would populate a list, based on the parent record, from which the user could select the file, do what needs to be done, then save it.
Unforunately, we're running into data security issues - all of the attachments will have private personal information - SSNs, Loan Numbers, Account Numbers etc., so they need to be stored in a location that's not universally accessible. All of the users have different permissions based on their department, acting as a very basic security level (i.e., Department 1 users will not be able to view any parent records associted with departments 2 or 3).
So I need a way for my back end to take a file located in a separate directory not normally available to the Front End User, then move that file (or save a copy that will be overwritten when re-attached to the record) to a location that IS accessible to my Front End User.
Can I create/use modules stored on the back end?
My first thought is to let the back end store the record in a temporary attachment table that is deleted when the user is done with it, but I've noticed that Access has trouble reclaiming the space when the attachment is ultimately deleted once the user is done using it, and I'll run into my 2GB limit again.
Alternatively, I could create a new .accdb file each time the record's attachments need to be accessed, and then delete that database once it's done and the attachments are returned to the secure location, solving my limit problems.
It works great, except that it is also copying the column headings. Is there any way to copy only the results without the headings?
I'm copying this data to the clipboard because I want to be able to paste it in a program called DataLoad which will load this data into one of our company's legacy systems.
