Hey i'm new to the forum and the whole VBA coding and have come accross a problem. Im using a database to manage the transactions of a software retailer.

Basically it concerns two tables:
STOCK([u]Stock_ID</u>, Product_Name, Manufacturer, Price, Qty_Stock)
and a table ORDER which contains the details for every order made. Every order contains one product only. In this table there's a field "Quantity" which specifies the number of copies of a particular piece of software that is ordered.

My problem is that i want the Qty_Stock field in the STOCK table to update itself every time an order is made i.e. When a button, say, "Confirm Order" is clicked (I have already designed the appropriate forms) the value of Quantity is subtracted from Qty_Stock for the appropriate product.

I would appreciate it if anyone can help me in coding this problem.

Thanks in advance

Are you using DAO or ADO? I do mostly ADO since I do mostly Access / SQL. Other posters can help you with DAO.

Lemme know.


mmcdonal

Umm, to be honest i don't really know. How can i check? Thanks for the quick reply.

You can use either, just a preference. DAO is preferred for Access databases when using Access for the tables as well as forms and reports, etc., and you must use ADO for SQL tables and views, but you can use ADO in Access only applications as well. If it is a very large app and you are doing very complicated things, then you MAY notice some slowness with ADO, but not likely.

Open your form, click <Alt><f11> to open the VBA window, then go to Tools > References, and make sure ADO is in the list of checked references. This should be there.

I am assuming you have this structure in your tables:

Stock table
Stock_ID - PK
etc

Order table
Order_ID - PK
Stock_ID - FK
Quantity - whole number
etc

Then do this on the Confirm Order button's On Click event:

Dim iStockID, iQty, iNewQty, iOldQty As Integer 'assuming whole numbers
Dim rs As ADODB.Recordset
Dim sSQL As String

iStockID = Me.Stock_ID
iQty = Me.Quantity

'This takes only one record
sSQL = "SELECT * FROM STOCK WHERE [Stock_ID] = " & iStockID

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

If rs.Recordcount <> 0 Then
   iOldQty = rs("Quantity")
   iNewQty = iOldQty - iQty
   rs("Quantity") = iNewQty
   rs.Update
End If

rs.Close

Did that work?


mmcdonal

BTW, there are other elegant ways of doing this, but this allows better business rules.

If no Quantity is ordered and the Confirm Order button is clicked, this will subtract 0 from the current Quantity.

mmcdonal

Ok i tried to run it but it gave me this error: Run-time error '-2147217913 (80040e07)': Data type mismatch in criteria epxpression. On depug it highlights this line: [rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic]

Just to make sure i haven't mislead you with some attribute names: tblStock(Stock_ID, Product Name, Manufacturer, Price, Qty_Stock)

tblOrder(Order_ID, Customer_ID, Stock_ID, Product Name, Order Date, Quantity, Invoice Number)

Oh and the ADO wasn't selected when i checked the reference, only the DAO was checked. I checked the ADO box afterwards ( ADOperator 1.0 Type Library)

No, sorry, that should be "Microsoft ActiveX Data Objects 2.1 Library", or some other rev.

mmcdonal

I dont have the 2.1 specifically, i have 2.0 and 2.5-2.8. When i try to enable one of these it comes up with an error message: "Name conflicts with existing module, project or object library." The only way i got it to work was to uncheck the DAO. Then however it still gives the same error.

I am not sure what that is about. Perhaps you need to move DAO up in the list since it looks like you already have some code that is inconsistent.

I am sure another DAO poster here can give you a solution.


mmcdonal