Inventory issue

[Anasua]

I am trying to develop an inventory application in msaccess. There is a stock table having assetno , quantity and entrydate field . another table is issuedet . it contains assetno, quantityissued field . Now I want to design an issue form which according to my choice of assetno it will show the stock quantity according to the ascending order of entrydate of that particular asset and then i will enter the issued quantity .

I can't design the form . please help

[jzwp11]

Welcome to the forum!

Do you have a table that holds the basic asset information? You need one in order to join the additions to and the subtractions from inventory.

An inventory database is one of the more challenging applications one can design in a relational database.

Allen Browne has a general table structure and discusses how to determine the quantity on hand on his website here.

Another option is to treat all additions/subtractions from inventory in 1 transaction detail table. Whether you use Allen's structure or the following you still need a table that hold the basic info on the assets (or products or items)


tblAssets
-pkAssetID primary key, autonumber
-txtAssetName


tblTransaction
-pkTransID primary key, autonumber
-dteTrans (transaction date)
-fkTransTypeID foreign key to tblTransTypes

tblTransTypes (holds the possible types of transactions, Additions to inventory or subtractions from inventory-you might call these by other names, so use what you are familiar with)
-pkTransTypeID primary key, autonumber
-txtTransTypeName

tblTransactionDetails
-pkTransDetailID primary key, autonumber
-fkTransID foreign key to tblTransaction
-fkAssetID foreign key to tblAssets
-QTY

[Anasua]

Actually I want to issue from stock maintaining the FIFO logic . That means the item that entered in the stock at previous date should issued first and then the subsequent one . How do I proceed

[jzwp11]

I'm not an expert on inventory control, but if I remember, FIFO is associated with the cost of the items assuming that older items came it at a lower cost than more recent items. I believe that Allen's design I mentioned previously should work, the trick will be to determine the unit cost of the item when they are pulled from inventory. You might be able to determine the unit cost either through a query or a custom function.

If you opt for the single table design, you would just need to add a unit cost field to the transaction detail table and again use either a query or custom function to determine the appropriate unit cost for items being removed from inventory.

I found a couple sites that had some discussions on the topic

site1
site2

[Anasua]

I am developing the Inventory Transaction Form having Issue subform .
I am giving the table details below

InventoryTransaction
ID - Primary Key
Transactionitem-
Employee
Transactiondate

Issue
Id-Primary Key
Transactionitem
QuantityIssued

Now there are two problem

i. In the main Inventory Transaction Form I have created a text box called quantity required . In the sub form ( Issue Details I want as and when a quantity is entered or issued the required quantity in the main form should be reduced . How could I do that .

ii. Another problem is that when one Issue has been done in Issued details subform I want that Inventory table should be updated by reducing the Quantity of that particular item . I have written the following code in Save button of Issue Subform .

Dim SQLstrg As String
' Suppress the Update Message from Access.
DoCmd.SetWarnings False
' For the updation...
SQLstrg = "UPDATE Inventory SET Quantity=Quantity-[Forms].[Issue Details].[Quantity] WHERE Id= [Forms].[InventoryTransaction].[Transactionitem]"
DoCmd.RunSQL (SQLstrg)

DoCmd.SetWarnings True
MsgBox "*** D O N E ***"
[Forms]![InventoryTransaction].[Text16] = [Text18]
On Error GoTo Save_Update_Err
With CodeContextObject
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If (.MacroError <> 0) Then
Beep
MsgBox "[MacroError].[Description]", vbOKOnly, ""
End If
End With

Save_Update_Exit:
Exit Function
Save_Update_Err:
MsgBox Error$
Resume Save_Update_Exit

But it is not working .

Please help .

[jzwp11]

With the designs I provided references to in my earlier post, you would not need a formal inventory table which would avoid the need to update it. To find the quantity on hand at any given time, you would use a function similar to what Allen Browne had on his site.