Modules & VBA :: Using ADO To Edit Result Of A Query
Sep 30, 2013
For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.
In short my question is. Is it possible to change the values returned from an SQL statement using ADO
Code:
'This is the DAO version which is exactly what i'm looking to using ADO
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset)
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
This is what i've done and it doesn't work.
Code:
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = New ADODB.Recordset
objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
I can think of some work around like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement.
I have an issue I do not know how to tackel. I have a select query that selects from three tables to calculate the commission for each transaction. The query is working just fine.
My problem is that I need an additional function ... What if I need to charge a special commission for that specific trade ?
I need to ammend the commission based on a figure I input in a text box from the form view.
Example: - Commission (calculated by the query) is 100.00 - Special Commission (which is a text box on the form) is 80.00 Then Commission field = 80.00
PLEASE HELP .. Please let me know if you have any other suggestion in tackling this problem. Thanks
Query Commission: IIf([Shares]![Currency]="USD" Or [Shares]![Currency]="CAD",IIf([Blotter]![Quantity]*[Clients]![USD_2]<[Clients]![USD_1],[Clients]![USD_1],[Blotter]![Quantity]*[Clients]![USD_2]),IIf([Shares]![Currency]="GBp" Or [Shares]![Currency]="EUR",IIf([Subtotal]*[Clients]![EUR_GBP_2]<[Clients]![EUR_GBP_1],[Clients]![EUR_GBP_1],[Subtotal]*[Clients]![EUR_GBP_2]),IIf([Subtotal]*[Clients]![HKD_JPY_2]<[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_1],[Subtotal]*[Clients]![HKD_JPY_2])))
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
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"
In the following code, I debug a problem with the CurrentDb.OpenRecordset
Code: ' --- ' ENVOYER UN MAIL DEPUIS ACCESS ' --- ' Entree : strEmail <- Adresse e-mail du destinataire ' strObj <- Objet du courrier ' strMsg <- Corps du message
I have query that creates table with 2 records each with 2 columns (2x2) and they do not have indexed ID,and query is related to combo box in other form so results are not always same but it is always (2x2) and value types are always same,
So how to show those results in text box in form,lets say 4 text boxes ,every value in one text box, i assume that i need to use DLookup() but i was able only to show first record,did not know what criteria put to go to second record.
When i select that query and create report i get what i want but i cant copy those text boxes to form that i want.
I already success to run this dynamic query where the parameters taken from the main form.Now the problem is the query result doesn't show in the subform.But the status bar below tell me that it have 2 records in the subform, but there is no data in the subform, it just Blank.I already apllied the Requery or Refresh to the subform (in the Command Button), but it have no result too.This is the code:
I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.
The current code - this is executed as on-click event when clicked on List Box feed with query below.
What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.
Code: Private Sub search_items_Click() Me.OBSFullFilledOrdersHolder.Enabled = True mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName , ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]"
[Code] ....
The query itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
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 create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.
Private Sub Command9_Click() Dim MyDb As DAO.Database Dim rsEmail As DAO.Recordset Dim sToName As String Dim sSubject As String Dim sMessageBody As String
[code]....
If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link
I have an edit button in my form which activates VBA, collects all data from the fields and edit the dataset. Everything work fine except the case that next to the edit access adds a new record as well with exactly the same data.
This is my code :
Dim db As Database Dim rs As Recordset Set db = CurrentDb Set rs = db.OpenRecordset("select * from tbllocations where locationid = " & cdkey)
[code]...
I checked every parameter and it seems everything is OK which surely is the base for the correct edit of the dataset. locationid is the key of the table.
I have a report that I run which has PT_Ins_ID in it, I am trying to edit that row with the following code.
Code: Private Sub cmd_Deact_Click() Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("dbo_Patient_Insurance") If rs.RecordCount <> 0 Then With rs
[code]....
I've also tried this.
Code: Dim rs_PI As DAO.Recordset Set rs_PI = CurrentDb.OpenRecordset("dbo_Patient_Insurance")
Within my table 'Contacts' I have the field 'City' which as you would expect contains the city.
The data in the City field which is sourced from the web can often merge two regional municipalities into 1 and I know through digging in behind the data what the correct answer should be.
I am looking to accomplish a list of edit replaces that takes 30 + edits and automates.
Change "ClevelandAkron" to "Cleveland" Change "Dublin 2" to "Dublin" Change "Greensboro/Winston-Salem" to "Greensboro" etc. etc.
There are 10,000 plus entries in the table of which many are correct.
I'm currently using the following to edit a publisher document using access VBA.
Code: For Each pubShape In pubDoc.Pages(1).Shapes If pubShape.HasTextFrame Then If pubShape.Name = "Text Box 440" Then pubShape.TextFrame.TextRange.Text = Item1 If pubShape.Name = "Text Box 441" Then pubShape.TextFrame.TextRange.Text = Item2 If pubShape.Name = "Text Box 442" Then pubShape.TextFrame.TextRange.Text = Item3 If pubShape.Name = "Text Box 443" Then pubShape.TextFrame.TextRange.Text = Item4 If pubShape.Name = "Text Box 444" Then pubShape.TextFrame.TextRange.Text = Item5 If pubShape.Name = "Text Box 445" Then pubShape.TextFrame.TextRange.Text = Item6 If pubShape.Name = "Text Box 446" Then pubShape.TextFrame.TextRange.Text = Item7 If pubShape.Name = "Text Box 447" Then pubShape.TextFrame.TextRange.Text = Item8 End If Next
Obviously there are a lot of shapes/boxes etc in the document and this code is very inefficient and takes a few moments to complete. Is there a way to directly reference a text box and change its value. For word I have been using bookmarks but this doesn't seem to work with publisher.
I'm looking for something like pubshape.Value("text Box 440") = Item1 but cannot find the correct syntax.
So i want to be able to make the whole syntax available for user to edit without actually doing it in vba. is this possible in vba? i have attached an image
I have two table. Table 1 (assets) list all my assets etc. Table 2 lists all servicing for each asset.
I have a form which generates a list box of items that require servicing (once servicing has been completed on these items) I would like to update all records, in both tables. Table 2 with all the information about the service. and Table 1 with (only) the next service date.
I have set the list box to allow multiple selection and have used the following code to allow new records to be update in table 2 (which works). but I cant seem to get the code to edit one cell in table 1.
private Sub Command59_Click() Dim strSQL As String Dim db As DAO.Database
While the form itself is bound to tbl_Orders I decided to use unbound text boxes to enter the data (knowing I was in for some work!). Reason is that if textboxes were bound would need to use a subform with multiple combo boxes and that UI did not seem suitable for the application.
I’ve completed the code to add new records to tbl_Orders and tbl_Junction from the unbound textboxes. I'm using the VBA .AddNew method and is working well. Now I need to integrate code that will allow users to edit existing records while they are viewing them on the form.
I have the code to look up the record set I want to edit in tbl_Junction and am familiar with the .Edit method but the issue is that the required changes MAY include not just editing existing records but also deleting and adding new records. For example, the initial order may have been for Apples and Oranges and the revised order may change the number of Apples, eliminate Oranges entirely, and add some Bananas. So, it seems I need to .Edit Apples, .Delete Oranges, and .AddNew Bananas?
Should I try to write code to determine where I need to .Edit/.AddNew/.Delete? -- not looking forward to that. Thought maybe could delete the entire junction record set and then just .AddNew for everything per the revised order. That should work as the display on the form would be correct for the whole order. But maybe would cause a problem as would remove the FK in tbl_Junction that corresponds to the PK in tbl_Orders?
I am attempting to adjust the font color of a date field on a report based on the value of two other fields. I have the below code set in the "On Format" property of my detail section - however it does not work when I open the report to view.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) If Me.Frequency = "Annually" Then If Me.ClassDate < (DateAdd("yyyy", -1, Date)) Then Me.ClassDate.ForeColor = vbRed Me.ClassDate.FontBold End If End If End Sub
I'm trying to write a line of VBA code (in an existing Access 2010 DB) that changes the Subject Line of all selected emails in my Outlook Inbox to today's date.
I can't figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.
Ultimately what I'm trying to do is - when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the "." with enough zeros for 10 digits.
Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.
Function Search() Dim lssql As String Dim lsSn As Recordset Dim db As Database Dim lsMessage As String Dim sMsg As String Dim vRetVal As Variant Set db = CurrentDb()
