Looking for some basic VBA advice

[granniss]

Hi,

I'm designing an inventory system in Access 2000 using 2 tables (tblInventory and tblDispensed) and a form (frmInventory). When the 'Dispense' command button (cmdDispense) is clicked, I would like to:

1) verify the value of the current quantity (tblInventory.Qty) is greater than the desired dispense amount

2) decrease the value of the quantity field[tblInventory.Qty] in the current record by an amount entered in a unbound textbox (txtDispenseQty) on the form

2) append the product name [tblInventory.ProdName] of the current record to a dispensed table (tblDispensed.ProdName)

3) delete the current record from the inventory table if quantity field[tblInventory.Qty] is zero

I wrote the following incomplete VBA event code for a command button (cmdDispense) on the form (frmInventory) as follows:

Private Sub cmdDispense_Click()
If (fldQuantity - txtDispenseQty) > 0 Then
<Run SQL code here to decrease quantity>
<Run SQL code here to append tblInventory.ProdName to tblDispense.ProdName>
If tblInventory.Qty = 0 Then
<Run SQL code here to delete current record from tblInventory>
End If
End If
End Sub

Any advice/solutions would be helpful.

Best Regards.

 

[Mel Henderson]

I have a small inventory program that I have been working on with help from some good people at the Access forum. It will give you starting qty and when you use a part it will auto adj your inventory qty. It will also track returns and received parts and make adj to your inventory count, if you like I can e-mail you a copy to maybe give you some ideas. let me know

Mel