Modules & VBA :: SQL String - Update Query Using A Variable Value
Jul 22, 2013
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 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?
I am trying to set the following variable that will be used to create a PDF file in an already existing folder and name the file WorkorderIDddmmyyyy-hhnnss.pdf
The following variable setting creates the filenameddmmyyyy-hhnnssWorkorderID.pdf.
I just can't figure out how to construct it correctly.
Also, is there a way to dynamically create a folder if the folder doesn't already exist? I currently hard code the folder name but would really like to create the folder name based upon some other variable.
I am amending some Code I found online for an audit table, I need to store additional information in the table that is associated with some forms but not others.
I have researched about putting optional variables in, but I read this only works with the type VARIANT.
Is there a way to make a string optional as my fields contain text?
here is my code so far:
Sub AuditChanges(IDField As String, UserAction As String, Optional UserID As String, Optional DeviceID As String, Optional SimID As String) On Error GoTo AuditChanges_Err Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim ctl As Control Dim datTimeCheck As Date
I am trying to write vba code to enter a form. A am using an SQL statement in vba to enter the form with it.
I want to include a string variable in the WHERE clause of the SQL statement. The string variable is introduced in the DIM statement before coming up to the SQL statement.
The following is the SQL statement. numrukarta is the string variable I want to introduce. I need to know the exact punctuation to be able to insert it into this statement.
StrSq1 = " SELECT Employees.NName, Employees.SSurname, WorkItems.IDcardNo, WorkItems.DDate, WorkItems.EntryTime, WorkItems.FinishTime, WorkItems.Roster" _ & " FROM Employees INNER JOIN WorkItems ON Employees.IDcardNo = WorkItems.IDcardNo" _ & " WHERE (((WorkItems.IDcardNo)= numrukarta) AND ((WorkItems.DDate)=Date()) AND ((WorkItems.FinishTime) Is Null);" AND (Not (WorkItems.Roster) Is Null))
I'd like to expand me tree view automatically when a node matches a string variable from a table. In other words, when a node in a tree matches the given name (variable), the tree will expand all the way down to that name. I guess I need to use "For each node" and when the node and variable match, the tree structure will show up.
I have come up with something like this:
Code: For Each nd In Me.tree.Nodes If nd = level1 Then nd.Child.EnsureVisible Exit For End If Next nd Me.tree.SetFocus
But this only shows the first level of my tree ( btw. I have a 3 level tree). I get lost inside the FOR when I want to make use of another two variables - level2 and level 3
I'm having problems with quotation marks in a sql statement. The string is an array separated by a semicolon.
120/80;70;5'6";125
this string represents patient vitals. I'm using the string to update a record. But I get hung up with the quotation mark.
I've tried: 120/80;70;5''6'"';125 which is a enclosing the quotation mark with apostrophies, but this does not seem to work. The sql still gets hung up. My sql statment looks something like:
original string: 120/80;70;5'6";125
strPreOpVits = "120/80;70;5''6'"';125"
mysql = "UPDATE mytable SET PreOpVits = '" & strPreOpVits & "' " & _ "WHERE nID = " & myRecID
docmd.runsql mysql
I've narrowed it down to the quotation marks and I'm unsure how to handle these. I get a runtime 3075 - Syntax Error.
Here is the code that I use to convert the original string
Public Function FixQuotesInSql(strToFix As String) Dim lgth, y As Long Dim strTemp, char2Add As Variant 'This routine fixes the use of apostrophe and quotation marks in an SQL sequence 'If the apostrophe is at the beginning or end of the string it replaces with 3 x "'" or "'''" 'If in the middle of the string then replaces with 2 x "'" or "''"
I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.
What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).
After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.
I am trying to create a table to show the parent/child relationships between assemblies, sub-assemblies and detail parts. The table I start with has 2 fields a "Part Number" and an "Indenture".
Please forgive all the periods, couldnt think of how else to seperate the values to depict the tables.
My problem is making it so that every part number has its own array, aka [family tree], so that for part "F" the table would hold "A,C,D,F"
I though an update query where for example: *"Zero" is a single field string.
IIF([00]<>null(), Save [00] to Zero, Write Zero to [00])
Would work but have been unable to find any information about passing a field value to a variable for later use or comparison. I would have already done this in excel if the table had less that 65000 records.
Can anyone tell me how I use a variable I have defined within a SQL string.
e.g.
***code start ***
Dim ComputerName as string Dim Sqlstr as string
ComputerName = Environ("ComputerName") Sqlstr = "INSERT INTO ComputerName ("ComputerName") SELECT ***This is where I need help including the variable ComputerName I defined above ****
Hey all, hope this question isn't too newbiefied :D .
I have a database where I must run 10 tests depending on which product I have selected (from a drop box). Each product requires different tests (of the 10) and I am hiding the tests that I do not need after the product is selected. The way I have it set up right now is I have 10 different If statements...one for each test on the "On Change" event.
I have been trying to reduce this down to one If statement nested inside a while loop, but my visual basic isn't very good. I have named the Test buttons Test3- Test13, and want to increment just the number of the test. However, when I try to plug in my concatenated string, I have problems. My code is as follows:
Dim testcount As Integer Dim testcheck as Boolean Dim testnumber as String
testcount = 3
While (testcount < 14)
testnumber = "Test" & testcount If Me.selectedproduct.Column(testcount) = True Me.testnumber.Visible = True Else: Me.testnumber.visible = False End If
testcount = testcount + 1 WEnd
Me.testnumber is obviously not an object on the Form...so I guess my real question is, how do I get visual basic to plugin the variable testnumber so that the code reads Me.Test3.Visible.
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 have a form (Datasheet). I need to define some variable in form as string, which can work with any event. E.G in column "A"on event after update, in column "B" on event after update...
I will try to explain with a simple example : form (datasheet) columns: "A", "B"
on event after update in coulmn "A", I could have some like this: (variable what I need to define) = 3
on event after update in coulmn "B", I could have some like this: If (variable what I need to define) = 3 then msgbox "ok" end if
Now I try to work around the problem and use another column "c" to keep the value from after update A - but I know that, it's bad solution -Right?
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 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 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 have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.
e.g. my table: Partner ID Backup Field 5023949 UPC 501354 GTIN
my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.
eg GTIN = "ABC123" UPC = "123ABC"
if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.
msgbox GTIN would result in a message box ABC123
But I am not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"
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.
