For doing me a favour and rebuilding the structure of database.
In receipt Main, itemid: 7 ===> Actually, In the main warehouse there are sub warehouses with each have different items. This is the receipt in 2nd warehouse.
OK then all you need to do is add another entity type called 'sub warehouse' to tblentitytypes and add the subwarehouse names to the entities table and then either a subwarehouseFK field to tblTransactions if items can go anywhere OR add a subwarehouseFK to items or categories if these are specific locations that an item must go in (i.e. an item or category can only ever go into subwarehouse2 - without exception
I have made some changes. Kindly see the attached files. The following points, I want to discuss with you;
a. I want three forms for data entry ==> Receipt, Transfer and Adjustment of Inventory. The format which is required I have attached the samples.
For Receipt ==> Receipt & Return to vendor
For Transfer ==> Transfer to Branches warehouses, Return from Branches & Expired Inventory at the Main Warehouse
For Adjsutment ==> Monthly Physical inventory adjustment as per the difference of quantity at the Main Warehouse.
b. What would be the possibility for the above requirement because as per the structure there is only one form for data entry.
Splitting the transactions table is fine (better normalisation) although it makes queries a bit more complex. There is a saying 'normalise until it hurts, denormalise until it works'
You use the same form but providing you know what type of transaction you will be doing when you open the form (perhaps based on the user profile, perhaps based on a menu the user selects from) you can set some properties such as a label caption or form back colour to indicate what type of form it is - and modify the rowsource of your combo (which I would rename as cboTranType). Personally I would put this combo at the top and hiding or disabling all the other controls. Then showing or enabling the relevant controls when a selection is made.
for your stock adjustment, there are many types but if this is to do with a stocktake adjustment you can include an additional field in tblTransactions (you could put it in tblTransactionsMain but makes things a bit more complicated with no real benefit) called say 'StockCount' with a default of null. This would be hidden in your form unless the user is doing this sort of transaction - you are using a datasheet so you would set columnhidden to true (or columnwidth to 0).
If the user needs to make this adjustment (or perhaps you have a routine for just entering the stocktake count, and not worrying about comparing values), they enter the stock count in this field and in the afterupdate of the control populate the the quantity field with the difference between actual and theoretical - code would be something like
so say the calculated value is 10 units and the stocktake is 12 you would have
12-10=2
The benefit of storing the stockcount value is as the amount of data grows the dsum might take longer, so you calculate from the last stocktake date - simpler is a query otherwise you need to use nested dsums and dlookups.
Alternatively you can have a control on the main form to show the current stock - controlsource would be something like
and in the current event of your subform you would need code
parent.nameofcurrentstockcontrol.requery
user would then need to calculate the difference themselves
or you can just include the dsum in the recordsource of the subform - you will need to use dsum, otherwise the query becomes unupdateable. But why do potentially 100's of calculations when you only need the odd one?
I hate to jump into the thread at this point but I disagree with splitting the transactions into separate tables. All inventory transactions belong in a single table. You may wish to have separate forms to enter different types of transactions but I would use one form and show/hide the specialized fields. Once the user chooses the transaction type, you code shows the controls for that specific type. You will also need code in the current event to show/hide fields.
@Pat Hartman - it is one table (well two, a transaction header and a transaction detail) but see post #20. Also below
it is a bad idea to use spaces and non alphanumeric characters in field (or table) names - it can generate unexpected errors.
I see you are going back to having multiple forms that basically all do the same thing. I would reconsider that as you will be maintaining duplicate code in multiple locations.
OK a couple of issues which need clarifying
Quantity on hand needs to only include transactions that predate the adjustment entry. As an example, lets say the stock take was done yesterday but not entered. After the stocktake, an item is transferred reducing the quantity on hand. Then the user comes along, enters yesterdays date and the stocktake at the time - the adjustment will be wrong. e.g.
calculated stock before the stocktake was 80
stocktake counts 85
a subsequent transfer occurs of 20 units, reducing the calculated stock to 60
the following day the user enters the stock take of 85 and the system calculates the different as +25, not +5
That is over 2 days, the same issue would apply if a transfer occurs anytime between the stocktake and the entry - could be a matter of minutes.
So timing is essential. At the very least you need a time element so you know the transfer took place after the stocktake. How you handle that depends on how your business works. Perhaps you suspend transfers whilst stocktaking which may be a day or an hour. Or stocktaking is done overnight when nothing else is moving, or simple the time of the stock take is noted. In some clients they put a marker of some sort on the stock so cannot be moved until the marker is removed - which is done after the stocktake has been entered
this is where combining the two transaction tables per my suggestion makes life easier. At the very least move the trandate field across so you can use the domain functions. Otherwise (and I'm pretty sure Pat will agree) you are in to writing more complex solutions requiring recordsets, sub queries and the like.
Once you have resolved that, the calculation is per my post 26 - using your structure
Code:
Private Sub PhysicalQty__Adj_AfterUpdate()
Me.Quantity = Me.PhysicalQty__Adj - Nz(Me.Qtyonhand, 0)
End Sub
As an aside this also demonstrates one of the problems with using non alphanumeric characters and spaces in field names. This doesn't generate an error here but could in some other place. Your field is called
Thanks Pat for the Suggestion, I agree that I should have to use a single form as already suggested CJ ... but i am not a programmer so i will move on as simple as i could do.
@Pat Hartman - it is one table (well two, a transaction header and a transaction detail) but see post #20. Also below
it is a bad idea to use spaces and non alphanumeric characters in field (or table) names - it can generate unexpected errors.
I see you are going back to having multiple forms that basically all do the same thing. I would reconsider that as you will be maintaining duplicate code in multiple locations.
OK a couple of issues which need clarifying
Quantity on hand needs to only include transactions that predate the adjustment entry. As an example, lets say the stock take was done yesterday but not entered. After the stocktake, an item is transferred reducing the quantity on hand. Then the user comes along, enters yesterdays date and the stocktake at the time - the adjustment will be wrong. e.g.
calculated stock before the stocktake was 80
stocktake counts 85
a subsequent transfer occurs of 20 units, reducing the calculated stock to 60
the following day the user enters the stock take of 85 and the system calculates the different as +25, not +5
That is over 2 days, the same issue would apply if a transfer occurs anytime between the stocktake and the entry - could be a matter of minutes.
So timing is essential. At the very least you need a time element so you know the transfer took place after the stocktake. How you handle that depends on how your business works. Perhaps you suspend transfers whilst stocktaking which may be a day or an hour. Or stocktaking is done overnight when nothing else is moving, or simple the time of the stock take is noted. In some clients they put a marker of some sort on the stock so cannot be moved until the marker is removed - which is done after the stocktake has been entered
this is where combining the two transaction tables per my suggestion makes life easier. At the very least move the trandate field across so you can use the domain functions. Otherwise (and I'm pretty sure Pat will agree) you are in to writing more complex solutions requiring recordsets, sub queries and the like.
Once you have resolved that, the calculation is per my post 26 - using your structure
Code:
Private Sub PhysicalQty__Adj_AfterUpdate()
Me.Quantity = Me.PhysicalQty__Adj - Nz(Me.Qtyonhand, 0)
End Sub
As an aside this also demonstrates one of the problems with using non alphanumeric characters and spaces in field names. This doesn't generate an error here but could in some other place. Your field is called
I'm happy to show you how to do things, and have already outlined how that form could be constructed. I am an independent developer with clients so if you want something fully created by me you would have to become a client. So I'll duck out at this point unless you want to be come a client. There are one or two on this forum who might do it for free.
@ahmad_rmh All the people here who help you are donating their time. It is hardly fair for you to get paid for work you ask an expert to do for free. We do our best to offer suggestions and guidance. Sometimes we post code we're written for ourselves. Sometimes, we create small demos if we think they will be useful for many people not just you.
Inventory management is complex to begin with. Once you get to custom solutions that deviate from standard solutions, you need to either buckle down and learn how to design tables and and forms and all the other objects as well as write code OR you need to hire someone to create the application for you. Perhaps your employer would like to hire CJ. He's been very helpful so far.
I'm happy to show you how to do things, and have already outlined how that form could be constructed. I am an independent developer with clients so if you want something fully created by me you would have to become a client. So I'll duck out at this point unless you want to be come a client. There are one or two on this forum who might do it for free.
No problem - you made a start on the forms, now you need to build in a way to meet the requirement for calculating stock in hand at any given moment in time, not just a day - and allow for the fact that data may not be entered in the correct order
No problem - you made a start on the forms, now you need to build in a way to meet the requirement for calculating stock in hand at any given moment in time, not just a day - and allow for the fact that data may not be entered in the correct order
