469,090 Members | 1,295 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,090 developers. It's quick & easy.

error in UPDATE statement

65 64KB
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

✓ answered by NeoPa

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.

5 833
NeoPa
32,159 Expert Mod 16PB
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,957 Expert 2GB
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
NeoPa
32,159 Expert Mod 16PB
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
saranmc
65 64KB
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
NeoPa
32,159 Expert Mod 16PB
SaranMC:
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.
SaranMC:
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

Post your reply

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

Similar topics

1 post views Thread by amitbadgi | last post: by
8 posts views Thread by Stephen Plotnick | last post: by
10 posts views Thread by Luigi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by kglaser89 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.