473,574 Members | 2,422 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

error in UPDATE statement

65 New Member
I have spent nearly a day and I am really not able to find my mistake..

this Forum has always the best answers so I am hoping someone can Point out what I am doing wrong!!!

it is a simple UPDATE Statement..

Expand|Select|Wrap|Line Numbers
  1. strSQL = " UPDATE " & Me.Combo81 & _
  2.          " SET " & "[" & Me.Combo81 & "]" & "." & "[" & Me.Combo79 & "]" & " = tbl_Import.[" & Me.Combo83 & "]" & _
  3.          " WHERE [tbl_Import].[pnr] = " & Me.Combo81 & ".[pnr];"
as you can see the Target Table and the field are from Combo boxes. also the field of the tbl_Import.

the SQL on printing gives the perfect one that i want it to be...

Expand|Select|Wrap|Line Numbers
  1. UPDATE MLE_Table SET [MLE_Table].[NHA] = tbl_Import.[Prio] WHERE [tbl_Import].[pnr] = MLE_Table.[pnr];
but the Problem is that the code does not recognise the values tbl_Import.[pnr] and tbl_Import.[Prio].
"Enter parameter" box Pops up everytime.

please tell me if anyone can see what i am doing wrong.
Feb 24 '16 #1
5 984
32,564 Recognized Expert Moderator MVP
Hi Saran.

The problem with the SQL is that you're trying to refer to a table (tbl_Import) that hasn't been included in the tables involved.

In your UPDATE clause you need to include all tables and queries that you intend to use in your query. This is similar to the FROM clause in a SELECT query.

BTW. Well done for including the SQL. The VBA was largely irrelevant as it's the SQL that isn't working, but you included the SQL as well so we had all we needed to look at your problem.
Feb 24 '16 #2
Seth Schrock
2,965 Recognized Expert Specialist
SQL can't just grab a value from a table like that. You have to execute a SELECT statement to get the values. You could instead use a DLookup. Also, in your WHERE clause you have a problem because the WHERE clause isn't telling it which records to pull from for your SET value, it is specifying which records get changed. In your SET statement, you don't need the table name repeated as it gets that from the table in the UPDATE statement.

You have a couple of options since you are building this query in VBA. You could use VBA to get the value from Combo83 and just plug that value into your SQL string, or you can put that retrieval in the SQL statement. I would probably do the former myself, but that is up to you.
Feb 24 '16 #3
32,564 Recognized Expert Moderator MVP
A useful tip when learning to work with SQL directly is to design an example query using the designer, then see what SQL it creates for you. In case you don't know, there's a SQL view for queries in Access that shows the SQL it uses for your QueryDef object.

This doesn't have to be as flexible as that created by your code. A simple example of one particular instance will give you the SQL that you're aiming to replicate in code in a more flexible way.
Feb 24 '16 #4
65 New Member
aahhhh!!!! thank you NeoPa.....

it works.. i should have asked you earlier!! wasted so Long in trying to correct the code when the mistake was in the SQL.

great thank you!!

thanks Seth, DLookup is something i have not done before.. Maybe I will try it next time..
Feb 24 '16 #5
32,564 Recognized Expert Moderator MVP
it works.. i should have asked you earlier!! wasted so Long in trying to correct the code when the mistake was in the SQL.
Such time is rarely wasted time. You'll have learnt from that effort, undoubtedly.
DLookup is something i have not done before.. Maybe I will try it next time..
I strongly advise against that in a scenario like this. It can be horribly inefficient and is never as clean as including the table inside your SQL. If that's not possible for reasons I can't imagine at this point, then it would be worth considering.

Please remember though, our experts will try to do what they can to help. None of us is all-seeing though, and I can absolutely include myself in that category. We offer the best we know at the time. When Seth posted his advice he hadn't seen my first post. It would probably have got you working, but possibly not in the best way.

Nevertheless, all attempts to help are very much appreciated.
Feb 24 '16 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

by: Robert Mark Bram | last post by:
Hi All! I have the following two methods in an asp/jscript page - my problem is that without the update statement there is no error, but with the update statement I get the following error: Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) Syntax error in UPDATE statement. /polyprint/dataEntry.asp, line 158
by: Wing | last post by:
Hi all, I am writing a function that can change the value "Quantity" in the selected row of MS SQL table "shoppingCart", my code is showing below ------------------------------------------------------ static void addQuantity(int rowNum, int oldQuantity) { int newQuantity = oldQuantity++; SqlConnection thisConnection = new SqlConnection...
by: amitbadgi | last post by:
HI i am getting the foll error while conv an asp application to asp.net Exception Details: System.Runtime.InteropServices.COMException: Syntax error in UPDATE statement. Source Error: Line 112: MM_editCmd.ActiveConnection = MM_editConnection Line 113: MM_editCmd.CommandText = MM_editQuery Line 114: MM_editCmd.Execute
by: FayeC | last post by:
I really need help figuring this out. i have a db with mostly text fields but 2. The user_id field is an autonumber (key) and the user_newsletter is a number (1 and 0) field meaning 1 yes the person wants to receive a newsletter and 0 no, don't want to receive it. Now.....when trying to create an UPDATE statement I am running into problems...
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without FOR UPDATE, it is fine and no error. I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF EMPNO"), but it still couldn't help. ...
by: travhale | last post by:
in a new project using .net 2005, c#. getting err message "Update requires a valid UpdateCommand when passed DataRow collection with modified rows." source RDBMS is oracle 8i. I add a new dataset to the project and drag a datatable from server explorer onto the dataset design surface. In the configuration wizard, under advanced options...
by: Stephen Plotnick | last post by:
I have three forms and update one table in an Access DB with sucess in one form. In the other two forms I'm trying to do an update to a different table in the Access DB. The error I'm getting in syntax error on Update on the statement da.update(mydataset, "DATABASENAME") I do have a primary key on both and copied the code from the...
by: Luigi | last post by:
Hello all! I'm a newbie in PHP. I have written a short script that tries to update a SQLite database with the user data. It is pretty simple, something like this: <?php $sqlite = sqlite_open("mytest.db", 0666, $sqlite_error);
by: David Hogston | last post by:
Having a bad time figuring out what is wrong with my update statement. Been a couple of years since I wrote any scripts. Can anybody tell me what I am doing wrong? Select Statement work fine. SELECT DESCRIPTION from maximo.inventory join maximo.item on inventory.itemnum = item.itemnum WHERE...
by: moepusu | last post by:
Hi all I have problem using table variable in update statement. Please see below. mpyemp="febpyemp" && this file name will change everymonth in my update statement DoCmd.RunSQL "update pyemp INNER JOIN {Mpyemp}
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.