What's the trick for Update Query's in OpenRecordSet

[Toga]

I can do a Select Query but everytime I attempt an Update Query I get errors.... Can someone show me an example that works!!!!!!!

 

[RickSpr]

You don't do update queries with OpenRecordset. You can either execute a SQL UPDATE statement, or you can open a recordset with a SELECT query and loop through the records, updating one at a time.

Using a recordset to update:
Set rst = db.OpenRecordset("SELECT ...&quot
Do While Not rst.EOF
If IWantToUpdateThisRecord Then
rst.Edit
rst!Field1 = newValue
rst.Update
End If
rst.MoveNext
Loop
rst.Close
Set rst = Nothing

Using an UPDATE query:
db.Execute &quot;UPDATE Table1 SET Field1 = <newvalue> WHERE Field1 = <oldvalue>&quot; Rick Sprague

 

[Toga]

Thanks Rick....A couple Questions...

What if another user has that record open? What can I do to make sure this record ultimately gets updated?

Does a record get locked with db.execute?

 

[RickSpr]

Actually, I've never addressed the first question--good question!

Execute has a second argument that you can use to set locking and exclusive control options, similar to OpenRecordset. dbFailOnError and dbSeeChanges look like they would generate a run-time error you could trap so that you could try again after a moment. dbDenyWrite looks like it would give you exclusive control of the table.

You could also wrap the Execute call between BeginTrans and CommitTrans calls, if you want to have an all-or-nothing guarantee. Rick Sprague

 

[Toga]

I still can't get it to work... It doesn't like my SQL string.

strSQL = &quot;UPDATE OpenOrders SET OpenOrders.OrderQuanity = [OrderQuanity]+ + '& NewOrderQuanity &' &quot; _
& &quot;WHERE (OpenOrders.CustReqID)= &quot; & [Forms].[PurOrderDetail].[CustReqID]

Error: Object doesn't support this property or method or I get Too few Parameters Expected 1....depending on how I change things around.

This will work if I pass the specific parameters

strSQL = &quot;UPDATE OpenOrders SET OpenOrders.OrderQuanity = 2000 &quot; _
& &quot;WHERE (((OpenOrders.CustReqID)= 618));&quot;

but does me no good. What am I doing wrong?

 

[JerryDennison]

Forgive me, I may be missing something obvious here, but why are you changing an existing quantity? It appears that you're basically trying to store a calculated result or a quantity that could be calculated. Correct me if I'm wrong.

 

[ghubbell]

Hope that's just typozs:

strSQL = &quot;UPDATE OpenOrders SET OpenOrders.OrderQuanity = [OrderQuanity]+ + '& NewOrderQuanity &' &quot; _
& &quot;WHERE (OpenOrders.CustReqID)= &quot; & [Forms].[PurOrderDetail].[CustReqID]

Try:

strSQL = &quot;UPDATE OpenOrders SET OpenOrders.OrderQuanity = [OrderQuanity] + &quot; & NewOrderQuanity & &quot; WHERE (OpenOrders.CustReqID) = &quot; & [Forms]![PurOrderDetail]![CustReqID]

NewOrderQuanity is a number?
Gord
ghubbell@total.net

 

[Toga]

Jerry,
It's an dynamic inventory amount which gets added to as product is produced and relieved when shipped out. Certainly open to other suggestions but I'm not sure how else I'd handle this. Seems crazy to relate the Produced & shipped tables considering that they'll just be getting larger & larger each day.

Gord,
Yes, it was a typo. One of these days I will get to the bottom of what makes these darn query's tick! ...... I hope anyways!