Code:
Private Sub Εντολή20_Click()
Dim lookupcheck As String
'Dim cnn1 As ADODB.Connection
'Dim myrecordset As New ADODB.Recordset
'Set cnn1 = CurrentProject.Connection
'myrecordset.ActiveConnection = cnn1
'myrecordset.Open " SELECT Sum([TransactionLine].Quantity) as SumofQuantity" & _
' " FROM [products] INNER JOIN TransactionLine ON " & _
' "[products].ProductId = TransactionLine.ProductId " & _
' " GROUP BY [products].ProductId", cnn1, adOpenStatic, adLockOptimistic
lookupcheck = DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId")
If TransactionTypeId = 1 Then
If DLookup("[Apothema]", "products", "products.productid=TransactionLine.ProductId") < Forms![TransactionLine].Sumqtr Then
MsgBox "No stock, current stock is: " & lookupcheck & " ", vbCritical, "Warning"
Else
mysql = "UPDATE products INNER JOIN TransactionLine ON " & _
"[products].ProductId = TransactionLine.ProductId " & _
"SET products.apothema = products.apothema" & _
" - forms![TransactionLine].sumqtr" & _
" where products.Productid=forms![transactionline].Productid"
DoCmd.SetWarnings False
DoCmd.RunSQL mysql
mysql2 = "UPDATE products INNER JOIN TransactionLine ON " & _
"[products].ProductId = TransactionLine.ProductId " & _
"SET products.FinalPrice = products.apothema * products.Price" & _
" where products.Productid=forms![transactionline].Productid"
DoCmd.SetWarnings False
DoCmd.RunSQL mysql2
MsgBox "Records Updated", , "Records"
DoCmd.SetWarnings True
End If
End If
End Sub
probably you see some comment with ado.database.
since im newbiew, i keep this for future reference.
i know is something going with the group by, but i cannot do it in update
Do you have any recommendations?