error in UPDATE statement

[saranmc]

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..

strSQL = " UPDATE " & Me.Combo81 & _
         " SET " & "[" & Me.Combo81 & "]" & "." & "[" & Me.Combo79 & "]" & " = tbl_Import.[" & Me.Combo83 & "]" & _
         " 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...

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.

[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.

[Seth Schrock]

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.

[NeoPa]

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.

[saranmc]

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..

[NeoPa]

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.