Ado update for multiply records

[thanosgr]

Hi, im practising with ADO and i have the following problem
I need to subtrack from order details quantity with productid.
but how can i use subtrack? it gives me debug error

here is the code

Private Sub button_Click()
Dim lookupcheck As String
Dim apo As Integer
Dim cnn1 As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
myrecordset.ActiveConnection = cnn1

myrecordset.Open " SELECT * " & _
" FROM [products] INNER JOIN TransactionLine ON " & _
"[products].PrId = TransactionLine.Prid ", cnn1, adOpenStatic, adLockOptimistic



lookupcheck = DLookup("[CurrentStock]", "products", "products.prid=TransactionLine.PrId")
If DLookup("[Apothema]", "products", "products.prid=TransactionLine.PrId") < Forms![transactionline].Quantity Then
MsgBox "Error : " & lookupcheck & "", vbCritical, "Error"


Else
Do Until myrecordset.EOF

myrecordset.Fields("CurrentStock") = Forms![transactionline].Quantity - myrecordset.Fields("CurrentStock")
myrecordset.UpdateBatch '//(all the records like productid=1,2,3,or 4 with different quantity)
myrecordset.MoveNext
Loop

MsgBox "Updates Success", , "Update"
End If

myrecordset.Close
Set myrecordset = Nothing

End Sub

[June7]

For one thing, the SQL and DLookup syntax is wrong. Variables must be concatenated, not placed within quotes. Is TransactionLine.PrId a reference to form and textbox? Is TransactionLine name of form button is on? You named command button 'button'? Should use something less generic.

Try:
"[products].PrId = " & Me.PrId

Is PrId a number datatype?

[thanosgr]

Hi,
TransactionLine is the Form based on table name transactionline
Prid is the primary key from product table, transactionline.prid is the Foreign key..

button is an event, i named it update inventory

There is error highlighted on myrecorset.updatebatch.
i guess there is the problem with the subtract. If i use for example myrecordset.Fields("CurrentStock") = Forms![transactionline].Quantity, where in logic dont work, the dlookup works fine, it gives me the available quantity.

The problem is how to subtrack from the current quantity to the product table which host the stock

[June7]

Don't know, don't really understand your code and what you are trying to. I don't see any subtraction expressions in the code.

Trying to maintain aggregate data in tables is problematic. Ideally, would enter stock in/out transaction records and calculate balances when needed.

[thanosgr]

Hi June
There is the form name TransactionLine, (order details)..

I put product id, produt description, quantity(unbound).

When i press the command button "update inventory" will take all the quantity for each productid and subtrack it from the stock table.

i use this

myrecordset.Fields("CurrentStock") = Forms![transactionline].Quantity - myrecordset.Fields("CurrentStock")

But dont work..

Regards

[June7]

Try:

Me.CurrentStock = Me.CurrentStock - Me.Quantity

[thanosgr]

No this dont work

Do you want me to give you the mdb?

[June7]

At this point, probably a good idea.

[thanosgr]

http://hotfile.com/dl/149145163/6aef...House.rar.html

[June7]

Sorry, I can't open rar files, only Windows Compression.

[thanosgr]

http://hotfile.com/dl/149193064/19ea...use.accdb.html

[June7]

I opened TransactionLine form. I can see two buttons at bottom of form, both captioned in Greek. The right button has English name 'Close transactionline' so must be the left button is for 'Update inventory'. The Click event is set as Event Procedure, however, there is no VBA procedure with this button name. The only VBA procedure behind this form is a button click event but does not have name of either of these buttons. Perhaps if you correct name of button or the VBA procedure, this will work.

[thanosgr]

Hi, if you open the transactionline it has all the data
Try to open it from the main menu (the second button from the top).

Thanos

[June7]

Data is not the issue, code is. The button has no code associated with it. Re-read my previous post.

[thanosgr]

Hi June
no work, i change the command button name... and gives me the same error
highlighted

myrecordset.MoveNext

and says runtime error -2147217887 (80040e21)