Modifying an Update Query from JetSQL to ADP???

[Kumba1]

I have the following update query that is from my JetSQL Access DB, and i'm trying to port it over to ADP, and cant get it to run... any ideas? or am I just blatantly missing something?

UPDATE tblTemp INNER JOIN tblBuildParts ON tblTemp.ForeignKey = tblBuildParts.Key SET tblBuildParts.StockNumber = [tbltemp]![stocknumber], tblBuildParts.Description = [tbltemp]![Description], tblBuildParts.VendorNumber = [tbltemp]![Vendornumber], tblBuildParts.Vendor = [tbltemp]![Vendor], tblBuildParts.Quantity = [tbltemp]![quantity], tblBuildParts.Price = [tbltemp]![price], tblBuildParts.Picked = [tbltemp]![pp], tblBuildParts.Ordered = [tbltemp]![op], tblBuildParts.Arrived = [tbltemp]![ap], tblBuildParts.pickdate = [tbltemp]![ppdate], tblBuildParts.expedite = [tbltemp]![ep]
WHERE (((tblTemp.form)=1));

 

[cmmrfrds]

Where is the query sitting? In some vba code that will be subsequently run in an execute statement? Are you building the row source in the onopen event of a Form?

In the statement you show all the variables need to be resolved before the statement is in a form that will be acceptable to sql server.

 

[Kumba1]

basically it's part of a variable called "strsql" which would be the final version of the SQL String (after all the concantate (sp) and such)... all i'm trying to do is set the fields from the static table = the fields from a temp table...

 

[cmmrfrds]

If the table to be updated is tblTemp then why is it trying to update fields in tblBuildParts? The ! is not syntax in transactSQL use the dot. tbltemp.stocknumber

UPDATE tblTemp INNER JOIN tblBuildParts ON tblTemp.ForeignKey = tblBuildParts.Key SET tblBuildParts.StockNumber = [tbltemp]![stocknumber], tblBuildParts.Description = [tbltemp]![Description], tblBuildParts.VendorNumber = [tbltemp]![Vendornumber], tblBuildParts.Vendor = [tbltemp]![Vendor], tblBuildParts.Quantity = [tbltemp]![quantity], tblBuildParts.Price = [tbltemp]![price], tblBuildParts.Picked = [tbltemp]![pp], tblBuildParts.Ordered = [tbltemp]![op], tblBuildParts.Arrived = [tbltemp]![ap], tblBuildParts.pickdate = [tbltemp]![ppdate], tblBuildParts.expedite = [tbltemp]![ep]
WHERE (((tblTemp.form)=1));

 

[Kumba1]

I have two tables, one tables contains updated values, that I want to set in another table... so say the field tbltemp.Vendor is a field that i've updated my value in, I want to set the value of tblItemParts.Vendor equal to tblTemp.Vendor... see what I mean?

 

[cmmrfrds]

Try this.

UPDATE tblBuildParts
SET tblBuildParts.StockNumber = tbltemp.stocknumber, tblBuildParts.Description = tbltemp.Description, tblBuildParts.VendorNumber = tbltemp.Vendornumber, tblBuildParts.Vendor = tbltemp.Vendor, tblBuildParts.Quantity = tbltemp.quantity, tblBuildParts.Price = tbltemp.price, tblBuildParts.Picked = tbltemp.pp, tblBuildParts.Ordered = tbltemp.op, tblBuildParts.Arrived = tbltemp.ap, tblBuildParts.pickdate = tbltemp.ppdate, tblBuildParts.expedite = tbltemp.ep
FROM tblBuildParts
INNER JOIN tbltemp ON tblTemp.ForeignKey = tblBuildParts.Key
WHERE (((tblTemp.form)=1))

 

[Kumba1]

Worked like a charm... thanks for the patience