Stock Update using VBA in Access 2007

[thanosgr]

Hi,
im a newbie to access and starting to use vba, to update my warehouse quantity.

i create vba code with a help of some books, well im think im doing something wrong

here is the code...

to summarize it there is a transactionline
which has transactionline id, productid(fk), TransactionTypeId(fk), Quantity, TransactionDate


ProductId has the productname and productstock. What i've done is, i create a form using the above,so if the transactiontypeId has 1(will deduct) product.stock from transanctionline.quantity, 2 will add and 3 will make stock=0

here is the code but gives me some error.
Any help will appreciate
---------------------------------------------------------

Private Sub Button_Click()
Dim mysql, mysql2, recsource, success As String

DoCmd.OpenForm "sumqtr", acViewDesign
recsource = Forms!sumqtr.RecordSource
DoCmd.Close acForm, "sumqtr", acSaveNo


Dim cnn1 As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
myrecordset.ActiveConnection = cnn1

mysql = "SELECT product.ProductId, Sum([TransactionLine.Quantity]) AS SumOfQuantity " & _
"FROM [ " & recsource & " ] " & _
" GROUP BY product.ProductId"

myrecordset.Open mysql
DoCmd.RunSQL mysql

If TransactionTypeId = 1 Then


mysql2 = "UPDATE Product " & _
"SET Product.apothema = SumofQuantity.transactionline - product.apothema " & _
" WHERE product.productid = transactionline!productid"
DoCmd.RunSQL mysql2
MsgBox success
myrecordset.clse

End If
End Sub
-------------------------------------------------

Any help?



Thanks

[June7]

What is the error - the exact message? What line throws the message?

A major principle of database is 'enter raw data, do calcs in reports', especially aggregate calcs. Trying to 'update' a running balance in a table is contrary to this concept and will cause you endless headaches.

[thanosgr]

Hi,

it says something about sumqtr it doesnt have [] but it yellows the


myrecordset.Open mysql

[June7]

You set recsource to the form's recordsource. What is that recordsource - a table, query, an SQL statement? Why do you have to open form to get this - don't you know what table or query data should come from?

Step debug. Follow the code as it executes. Are expected values getting passed?

[thanosgr]

June 7,

Hi june its a query...
i create a query. which has product table, and transaction table with the quantity. it gives you a summary of quantity group by product id.

so from this summary i want to deduct from the product.stock...

i know im doing something wrong in code...

[thanosgr]

Private Sub button_Click()
Dim mysql, yoursql, recsource, success As String

DoCmd.OpenForm "sumqtr", acViewDesign
recsource = Forms!sumqtr.RecordSource
DoCmd.Close acForm, "sumqtr", acSaveNo


Dim cnn1 As ADODB.Connection
Dim myrecordset As New ADODB.Recordset
Set cnn1 = CurrentProject.Connection
myrecordset.ActiveConnection = cnn1
myrecordset.CursorType = adOpenDynamic
myrecordset.LockType = adLockOptimistic


yoursql = "SELECT Sum([TransactionLine].Quantity) as SumofQuantity" & _
" FROM [" & recsource & "] INNER JOIN [TransactionLine] ON [Product].ProductId = [TransactionLine].ProductId " & _
" GROUP BY [Product].ProductId"

myrecordset.Open yoursql
DoCmd.RunSQL yoursql


If TransactionTypeId = 1 Then


mysql = "UPDATE Product SET Product.Stock = Product.stock" & _
" - [yoursql]"
DoCmd.RunSQL mysql
success = MsgBox("Record Updated", vbOKOnly, "Success")
MsgBox success

End If



The error says thats in FROM, but i guess the problem is in the recordset.
so whats the point that should i fix...

i use the forms, cause they say, to use a select command, u need throu a recordsource but only with forms or reports

[orange]

This

Dim mysql, yoursql, recsource, success As String

does NOT do what you think.

Your DIM statement will have success as String,
but mysql, yoursql, recsource will all be VariantVariables are variant by default.

You must explicitly Dim variablesor accept the default.
Dim A as String, ijk as Integer, var as Variant or

Dim A as String
Dim ijk as Integer
Dim var as Variant

[June7]

Orange is correct that if you don't explicitly declare variable type then it defaults to variant. Best practice is to declare each variable on separate line but can do on one, like:
Dim mysql As String, yoursql As String, recsource As String, success As String

However, the variable declarations is not source of the issue.

Took closer look at code. Use DoCmd.RunSQL with action SQL (Update, Delete, Insert). It accomplishes nothing to run a SELECT query. SELECT in VBA is used to open a recordset. In your first code you open a recordset but don't do anything with it, in the second code you attempt to reference it in the UPDATE but syntax is wrong. Not sure of fix because don't know data well enough. If you want to provide project, will look at.

What is TransactionTypeID - where does this get set? Is this a control on form?

i use the forms, cause they say, to use a select command, u need throu a recordsource but only with forms or reports

Who says? Not true, can refer directly to table or query because of the connection setting. Example:
myRS.Open "SELECT * FROM Products;", cnn1, adOpenStatic, adLockPessimistic

MsgBox has two methods - function and simple OKOnly popup - parens designate a function. Simplify the message box with one line instead of two:
MsgBox "Record Updated", , "Success"

I repeat, trying to maintain 'running' total (aggregate) data in a table is one big headache. This should be calculated in a report.

[thanosgr]

Hi June 7,


TransactionType is a field from the transactionline form, if i enter 1 then will deduct, if its 2 then will add to the stock...

I did what you told me, i remove the docmd open form, and i use directly the
myRS.Open "SELECT * FROM Products;", cnn1, adOpenStatic, adLockPessimistic

it reads it, now and i pass the field... recsource=myRS.field(sumofquantity).value..

but dont work

well here is the link to the database

http://hotfile.com/dl/145741193/2319...use.accdb.html


the main form is the product, but the code is in the transactionline form
some fields are in greek, but in the table design view... is has the english description...

Dont worry its a small database






below is the link.. of the database

its in greek,but i use simple form in english. but i use english description on tables..(design view)

[thanosgr]

Any news?


Regards

[Rainlover]

You need to put your code behind the Form not in a Class module.

I don't know where you store the latest Balance or how you intend to find it but the code should be something like this.

Option Compare Database
Option Explicit
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim Balance As Long
 
   'Code here to find the latest value
   If Me.TransactionTypeId = 1 Then
      Me.Quantity = Me.Quantity + Balance
 
      Else
      If Me.TransactionTypeId = 2 Then
      Me.Quantity = Me.Quantity - Balance
 
      Else
      If Me.TransactionTypeId = 3 Then
      Me.Quantity = 0
 
      End If
      End If
   End If
End Sub

Having said that I would suggest that your method is incorrect.

Allen Browne has some good advice.

http://allenbrowne.com/AppInventory.html

[Rainlover]

A major principle of database is 'enter raw data, do calcs in reports', especially aggregate calcs. Trying to 'update' a running balance in a table is contrary to this concept and will cause you endless headaches.

I am sure you have good reasons for feeling this way but I would not class this as a Major Principal nor would I say that it will cause Endless Headaches.

[June7]

Rainlover, quote from that very link you provided:

"One of the basic rules of data normalisation is not to store dependent values. Break the rule at your own peril: it takes more work than doing the job properly; it diminishes the queryability of your data; it leaves you without any certainty that your answer is correct."

Any calculated value is a dependent value, especially 'running balance' or summary calculations.

[thanosgr]

Well guys,
i think i made it...

i just created a sum(quantity) into the form, and done it.

Just a final question....

how to use check into the database, to do update only when product.stock>0

if for example Stock=100 and you need 500, you cannot do any update, but appear an error message

Thanks

[June7]

Maybe a DLookup().

If DLookup("Stock", "tablename", "StockID=" & Me.StockID) < Me.StockNeed Then
MsgBox "Not enough stock."
Else
'run procedure
End If