Modules & VBA :: Query Criteria And Variable In Email
Sep 6, 2013
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'm trying to search for a variable (varCod) in a table(SerialNumberCustomer) but I want to only store the returned variable in an array if its shipDate is between 9/30/2001 and 10/1/2012
this is what my code looks like so far:
Set rst = CurrentDb.OpenRecordset( _ "Select * from SerialNumberCustomer WHERE SerialCardId = " & varCod & " AND (ShipDate BETWEEN #09/30/2001# AND #10/01/2012#) ")
The line of code works and it returns all of the values in SerialCardId where it's = to varCode but it doesn't go through with the shipDate requirement. It's instead returning all of the dates with the found value.
I manage an Access application that in many instances uses data selected from a combo on a form for variable criteria. In this instance it is in the form of:
I recently wrote a routing that exports to Excel based on a record set derived from a query. In testing I hard-coded the criteria (School Year) in the query. Once everything worked I sustituted the variable above. Now, in the VBA, no records are put into the recordset, when I run the queries directly from Access they work correctly, drawing the results for the school year selected on the form referenced.
'Create The Recordset If Me.Frame11 = 1 Then strQueryName = "ExcelHS" GroupTitle = "High School" Else strQueryName = "ExcelMS" GroupTitle = "Middle School" End If Set objRst = Application.CurrentDb.OpenRecordset(strQueryName)
I have a database structure with a series of queries which are all inter-linked.
I want my database to be user friendly, what is the cleanest way to be able to enter a single variable eg as a criteria for a query - say the date, rather than having to change it in a series of queries manually, can I call it from somewhere?
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 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 ?
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 am using vista and access 2010, what I want is to be able to email a report from access that was created by a form with DoCmd.OpenReport "ReportOrder", acViewReport. I have a button that when i click it it will send the report via email, but the email address has to be a variable so when the report is created i can use the email that is attached to the report data.
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 have created something from our transport department and need to set up so he can select a command button and email from a query, but where would I put the query details in the coding below
VBA used
Dim strSQL Dim db As DAO.Database Dim MailList As DAO.Recordset Dim objOutlook As Outlook.Application Dim objemail As Outlook.MailItem Dim Subjectline As String Dim BodyFile As String
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.
