Invoice & Subform: Add Multiple Record (Item) at Once

[abzalali]

Dear All,
I almost complete my mini project - Stock Inventory:
To track Items enter and exit from Videoshop or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the Items in the Videoshop, each Items details including category, transaction type description and most importantly quantity on hand.

As mentioned above, I almost complete the database except the most important Form - New Stock Transaction where User enter all the necessary details to issue an IN or OUT Transaction. To make things easy to understand, it share many similarity with Invoice and New Order.

Main Form (Table: StockTrans)
Trans_ID
Trans_Type
Trans_Date
Trans_Cust
Trans_Note

SubForm (Table: StockTrans_Items)
Trans_ID fk
ItemID fk
Trans_Item_Qty

When I finished records entry and enter Invoice completed button then it will auto update the Item Quantity on Hand.

Please help me.

Thanks
Mir

 

[abzalali]

Dear All,

Lets show detail that I'm trying to:

Here's the tables:

tblStoreItem:
StoreItemID
ItemName
TypeID
CategoryID
Copies


tblTemp:
TranDetailID (AutoNumber)
TranID
StoreItemID
TranType
HireType
Copies
UnitPrice
ReturnDate
LineTotal


And also I create a table named "tblTransactions" which is same as "tblTemp"
TranDetailID (Number)
TranID
StoreItemID
TranType
HireType
Copies
UnitPrice
ReturnDate
LineTotal


The "tblTransactions" use only after update "tblStoreItem" (quantity/Copies update from "tblTemp" to "tblStoreItem" and then transfer "tblTemp"data to "tblTransactions" and then delete all from "tblTemp"

I'm trying to use this code below:

Private Sub cmdInvCom_Click()
Dim counter As Integer
Dim totalItem As Integer

Dim UpdateItemQty As DAO.Database
Dim byRecord As String

Dim getEntryItemID As String
Dim getEntryItemQty As Integer

'-To count the number of Item in the Temp Table
totalItem = Val(DCount("[StoreID]", "tblTemp"))

'-For my own checking
MsgBox (totalItem)

counter = 1

'To Do Until ... Loop
totalItem = totalItem + 1

Set UpdateItemQty = CurrentDb

'This Do Until...Loop is suppose to update every each Item's Quantity
'Again, I have not finish the code yet as I not sure how to implement the Update
Do Until counter = totalItem

getEntryItemID = DLookup("[StoreID]", "tblTemp", "[StoreID] = " & counter & "")

MsgBox (getEntryItemID)

getEntryItemQty = DLookup("[Copies]", "tblTemp", "[StoreID] = " & counter & "")

MsgBox (getEntryItemQty)

'This SQL Line is suppose to extract the ItemID for each Item
'I used ItemNo as a Tracking/Comparing Each Record
'If you confuse, just think as:
'In a receipt, there are many items. To differentiate each another, it can by Item Number or Item ID
'Eg. Item No 1 Coca-Cola Coke 1Litre | Item No. 2 iPod Touch 3G 32GB
byRecord = "UPDATE tblStoreItem SET Copies = Copies +" & Nz(Val(getEntryItemQty)) & " WHERE StoreID= '" & getEntryItemID & "'"

UpdateItemQty.Execute byRecord

counter = counter + 1

MsgBox (byRecord)
Loop

UpdateItemQty.Close
Set UpdateItemQty = Nothing

End Sub

Please help me.

Thanks
Mir