Can someone please tell me where I went wrong with the following:
SQL = "UPDATE tblSoldCase " & _
"SET tblSoldCase.[Case Name] = tblProspect.[Case Name], tblSoldCase.[Admin Letter App/Decl] = tblProspect.[Admin Leffer App/Decl]" & _
"Where (((tblProspects.[Case Track Nbr]) = " & Me.txtCaseTrackNbr & "));"
DoCmd RunSQL SQL
I want run a update SQL
After a hiatus of a few years I find myself back writing Access applications, so I need to get caught up a bit.
First of all help me out with the whole ADO, DAO RunSQL thing. In the past whenever I needed to do any database operations I almost always used straight SQL with DoCmd.RunSQL, e.g.:
lsSQL = "INSERT INTO tblUsers CenterID, WorkerID..."
DoCmd.RunSQL (lsSQL)
Most other coders seem to use some recordset approach, e.g:
Set rst = dbs.OpenRecordset("tblUsers")
rst.AddNew
rst("ClientID") = Me.ClientID.Value
rst("WorkerID") = Me.WorkerID.Value
...
rst.Update
The only time I ever used recordsets was when I needed to loop through each record and apply some logic that was too convoluted for SQL or at least too convoluted for me to write in SQL.
So, what's the advantage of using recordsets - whether ADO or DAO - over RunSQL?
I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing.
The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing
DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)")
anyone know what I'm doing wrong?
thanx
Hi trying to get my runsql to work
DoCmd.RunSQL "INSERT INTO tblLogTimer (Operator, Dato, LogOn, LogOff, LogTime, Status) VALUES ('" & StrOperator & "', '" & Datotext & "', '" & MeetTime & "', '" & LeaveTime & "', '" & LogTime & "', '" & StrMeetStatus & "')
But if keep updating all the records and I'm only interesting to get the 1 updated...so I've been trying this ....
DoCmd.RunSQL "INSERT INTO tblLogTimer (Operator, Dato, LogOn, LogOff, LogTime, Status) VALUES ('" & StrOperator & "', '" & Datotext & "', '" & MeetTime & "', '" & LeaveTime & "', '" & LogTime & "', '" & StrMeetStatus & "') WHERE Operator=me.txtLogOperator"""
but the access keep telling that I'm needing a ";" but where should I put it???
I've the following SQL query in my database:
DoCmd.RunSQL "INSERT INTO tblPlanner ( RACF, [Date], [Day Capacity], [Role Title], TimeWork ) SELECT tblStaff.RACF, [txtday1] AS Expr1, tblStaff.[Daily Capability], tblStaff.[Role Title], tblStaff.[Contract mins] FROM tblStaff WHERE (((tblStaff.TeamName) Like [txtTeamName])) WITH OWNERACCESS OPTION;"
The problem is everytime it runs it informe that the query will change data in the table. What can I do to stop it?
Thanks
have this code on a on click event of a button on a form
Code:lngMyEmpID = Me.cboEmployee.value yes = "Yes" DoCmd.RunSQL ("UPDATE Users " & _ "SET [loggedIn] = '" & yes & "' " & _ "WHERE [lngEmpID] = lngMyEmpID;")
it prompts the user to enter the value for lngMyEmpID,
i have tried to change the WHERE to
"WHERE [lngEmpID] = '" & lngMyEmpID & "'
buth then get a data type mis match error
the lngEmpID is the column name of the table and its an autonumber
the lngMyEmpID is a number tied to a combo in which the user selects the username.
any ideas on how to get this working
can some one help.
i have two tables table1,table2 each with one same field Country.
In my RunSQl query, i want to delete all records in table1 and table2 simultaneously from one button with a certain country.
The problem is how can i do this from may be one RunSQL statement
i tried to use two deletion runSQl commands, but only one is executed. Actually the one that comes second.
If i put doevents in the middle of these statements, only the first is executed.
Here is my code:
Private Sub RemoveCtry_Click()
docmd.setwarnings false
Docmd.RunSQL "DELETE * FROM table1 WHERE (Country='" & USA & "')"
Doevents
Docmd.RunSQL "DELETE * FROM table2 WHERE (Country='" & USA & "')"
docmd.setwarnings true
End Sub
if I use One Statement:
Docmd.RunSQL "DELETE table1.Country,table2.Country FROM table1,table2 WHERE ((table1.Country='" & USA & "' ) AND (table2.Country=' " & USA & " '))"
I get an error that i have to specify the table to delete from!!!!
Hi guys,
what's the problem in this code:
DoCmd.RunSQL "SELECT * FROM SecounderyInfo WHERE [LangEs]=Yes;"
every time i excute it an error appear:
Run-time error '2342'
thanx
hello,
im creating a small database and am extremely new to vba, ive got stuck on running a select query from within the vba code itself, the book im currently reading while learning about this stuff suggests my code whould work but after looking into it i believe its wrong as ive read various threads saying you cannot use docmd.runsql with a select query, just wondering if anyone could help and throw some light on how to get this little bit of code working. below is the part of my code that falls over,
basicaly it should lookup the weight based on what the parcel type is and find the price, the parcel type is worked out earlier in my code and is held in strParcelType, theirs probably an easier way to do this as well but have'nt got that far in my book :)
intWeight = Me.txtWeight
Select Case intWeight
Case 0 To 100
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[0-100g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 101 To 250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[101-250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 251 To 500
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[251-500g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 501 To 750
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[501-750g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 751 To 1000
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[751-1000g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
Case 1001 To 1250
intCost = DoCmd.RunSQL "SELECT pricetable1.[Postage Name], pricetable1.[1001-1250g] FROM pricetable1 WHERE (((pricetable1.[Postage Name]) = strParcelType))"
End Select
Me.txtEstimate = strParcelType & intCost
many thanks to any gurus who can point me in the right direction or show me a easier way to do this
Hey guys.
I have a VBA application i am writing.
I am trying to use an sql statement with docmd.runsql to insert a value into my table that matches a particular record. (which will be the one open). For testing purposed i have stripped down my code.
When running the code it works, however it puts chinese symbols in all the other fields of the record and dose not insert the record into the CapExFileName Field. Then when you try and delete the record it comes up with no search index found.
Any idea on why it would be doing this ?
here is the code.
.......
Dim SQLstring As String
SQLstring = "UPDATE Assets SET Assets.CapExFileName = 'Test' WHERE Assets.Barcode = 'Testies1234'"
Docmd.RunSql SQLString
....
Mark.
My membership database has worked fine until recently. Now I cannot save inserted data. On attempted saving "Update or CancelUpdate without AddNew or Edit" appears.
The problem. relates to 2 tables Member and Addresses. PK in the parent table Member is ID. In the Addresses Table the FK is ID. There is a One to One relation between the tables and Referential Integrity is set. I know 1 to 1 is not good but it worked fine in this small database.
Browsing the all of the existing records is fine.
Hi Guys,
I have got a query that updates details from one table2 to table1, "Reference" is the primary key and this is what the query uses to determine which need updating.
It all works great but if table2 contains a record in "Reference" that is not in table1 i just want it to ignore it, currently it just seeems to add them.
Any suggestion guys & gals?
Many thanks
Tim
Ok, i have a question about update queries.I have two tables (I'll call table 1 and table two for simplicity) and an update query. I want to get some data from table one to table two (via an update query). But in table two there is a field that isn't in table one but i want to add a value to that field via the query.My question is, can i manually put into the query what data to add to a field instead of/aswell as using data from other tables.I hope you understood my questions.Cheers
View 3 Replies View RelatedI get an error "update or cancel update without add new or edit" which seems to point to this code.I am using MS Access 2010.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Store when record was last modified and by who.
'Initially stores when the record was entered.
If Me.NewRecord = True Then Exit Sub 'Exit if new record
Me.DateLastModified.Value = Now()
Me.LastModifiedBy.Value = getUser()
End Sub
I am trying to stop access displaying the "You are about Update 1 Record" etc message when i run an update query. I know i can do this in Tools/Options screen but the problem is that the database is going to be used by multiple users, and rather than changing each persons Action query option I was wondering whether there is something i can put in to the code Globally to halt the message.
Any help would be appreciated.
Regards
Mark
I am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
I have a database where two tables contain information that I need to update based on the 4 right most digits of a field. There are only 11 values that will need to be updated out of a large list of values. I'm not quite sure how to set up the update query so that I can do this.
View 1 Replies View RelatedI have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
I have two tables, each has a "status" for a project. In the left table there is only one instance of each set, but in the right hand table, each set may be used more than once by different Projects.
I need the Status field of the left table to be set to "Assigned" if ANY of the sets assigned to projects in the right hand table are "Assinged"
Here is the update query I have setup but when it runs, it updates "0" rows.
8 is the id for the status "Assigned" in the SetStatus table
2 is the id for the status "Assigned" in the Status table.
http://img166.imageshack.us/img166/8181/temtinv00210fp.jpg (http://imageshack.us)
Hi,
I read on the Access help about the 'Before Update' property; however, I still don't understand what it does.
Any help will be very much appreciated.
B
Hi:
After I made the query "T1",
I made another query "T2", but this query need query "T1" to work with.
So, when I change the query name from "T1" to "table1", I need to open the query "T2" to add the "table1" and remove the "T1" and change the table name in the query.
So, how can I update the query name? Because "T1" is same "table1' query. I just change the name, change name cause another query does't work. How can I update the query info.?
Thanks.
Hi Guys,
Two Tables:
module
workbook2
UPDATE workbook2 INNER JOIN [module] ON workbook2.ID = module.ID SET workbook2.bankimpid = module.BAImpID
WHERE ((([BASortCode])=[Sortcode]));
Any ideas why this changes 0 records when some records contain the same data in BASortCode and Sortcode??
Cheers
I can never get this right and it's frustrating.
I have a field on the form called OverallProjectStatus - it's a combo box and it has values: green, yellow, red.
If a user selects green as a value I would like BackColor of the form to turn green color.
I tried both before update and after update events on this combo box as well as on the form's before and after update events and it's not working.
Could someone please help me put this code in the right place.
If Me.OverallProjectStatus = "Green" Then
BackColor = 13434828
End If
Thanks!
Can someone tell me if this is the correct/best way to do this.
I have a calculated field "savings". It is calculated on the AfterUpdate event of the "month" field.
I have 2 text boxes (std cost & quoted cost) that have manually entered amounts and one combo with "modules" To get the savings calculation my code looks at the month selected then the module selected in my query and sums up the volumes.
It then multiplies the volume X the std cost minus the quoted cost.
Std Cost Quoted cost Dif Volume Savings
3.00 1.5 1.5 200 $300
Anytime any of the four fields change (std cost, quoted cost, module or month) I need the Savings field to recalculate.
My first thought is that I need to put in the same code calculating the savings in the AfterUpdate event of all of these.
Is that the case? What better way is there?
Thanks!
I have a form that has a combo box, once a user selects the required entry it the form performs several tasks after update.
If the required entry isnt listed the user clicks on the add button which opens a form to fill in the necessary details for that entry. Once complete the user clicks on the save button and the form closes updating the combo box on the previous form. But the after update tasks do not happen.
I have tried things like....
[Forms]![Frm1].reload
[Forms]![Frm1].refresh
etc
But cant seem to get it to work. Any ideas?? Thanks
Hey Guys,
I had a problem a while ago about changing the value in one field and having it update a different field in another table.
I have a table Order Details where I want the user to be able to select a quantity when ordering a particular shoe.
I then want a field named UnitsOnOrder in a table Products to update its value based on the amount ordered by the user in the quantity box in the other table.
The Order Details table is a subform not that it makes any difference.
I started receiving help by a Mr M Walts, but he hasnt been around for ages and I'm so very deperate for a fix as the code isnt working correctly and I've no idea how to fix it.
Private Sub Quantity_AfterUpdate()
'will hold the SQL which will update the Products table
Dim strSQL As String
'will hold the new total quantity after the change
Dim intChange As Integer
'see if the quantity had a previous value, might have to seperate into two
'nested if's if it gives an error on the second part of the condition
'when OldValue is null. It might
If Not IsNull(Quantity.OldValue) And (Not Quantity.OldValue = "") Then
'ok, there is a previous value, let's see which is bigger
'the change will be the new value - the old value
intChange = Quantity.Value - Quantity.OldValue
'ok, now we have the value that needs to be added to the
'quantity, if the old value was bigger then it will be negative
'which is what we want
Else
'if there was no old value, then all the really need to do is get the
'new value as the change
intChange = Quantity.Value
End If
'ok, now we create our SQL statement.
strSQL = "UPDATE Products " & _
"SET Products.UnitsOnOrder = UnitsOnOrder + " & intChange & _
"WHERE Products.ProductID = " & ProductID.Value '<- or whatever your control on the form is
'called that is bound to your productID field in
'the order details sub-form
'now we have created the SQL we need, time to run it
CurrentProject.Connection.Execute strSQL
'uncomment the next line to see the SQL that was generated
'debug.print strSQL
'that should do it for the first one, and it should give you an idea of how to carry on
End Sub
That is the code. I get an error message upon leaving the field sayin 'one of the required parameters is missing'
Any ideas?
Any help greatly appreciated. I'm way behind the rest of my I.C.T class as they have al finished their projects.
Surely somebody knows the answer
THanks in advance
Chris Tempest