on dirty function in access

[mrijet]

Who can solve this problem....

how to use on dirty function in access

I use this 2 function...

I have 2 textbox for user key in..which is QtyReq and QtyIssued

QtyReq is for user to request how many item they want...that's didn't involve any calculation...and for QtyIssued that involve calculation. For example the main stock is 10...then user will put QtyReq 20 and QtyIssued to 10....then user will save it...and the main stock will auto deduct after user put 10 in Qty Issued...Now the stock become 0...Then the problem comes..when user want to update back without seeing the stock have or not...they open back the form that list as pending...which is QtyReq = 20 QtyIssued=10 and still got Balance=10....So user will open and change the QtyIssued as 20....that goes to the below coding...

QtyIssued_OnDirty()
Dim user As Integer
 
user = Nz(Dlookup("Stock","tableName","ItemCode='" me.ComboBox "'"))
If Me.QtyIssued.Value Then
DoCmd.OpenQuery "try1", acEdit''Stock+QtyIssued
ElseIf user < QtyIssued Then
MsgBox "Out Of Stock"
DoCmd.Close
EndIf

After user change the QtyIssued to 20...It will prompt MsgBox and tell user "Out Of Stock"...and it will add 10 in the main stock...how can I solve this problem...??

[NeoPa]

I would suggest that nothing should be updated until the record is saved. Processing on the entry of the value is not appropriate and will cause you difficulties. The way to avoid those difficulties is to design the process properly - which is to say not to apply any updates until changes are saved. I hope that makes sense.

[mrijet]

Thanks @NeoPa for replying...I make it updated because sometimes what user required are not in the stock...that why I make QtyIssued for user to key in what item they already take in the store and balance will be show. Later, if the item already restock user will open back this form to key in until the QtyReq

[zmbd]

Every so often, when it seems that the problem is very complex I find that for me there might be better logic/method to solve the problem at hand.

In your case the workflow appears to look like:

User requests 30 of item x
Item x has 10 in stock
User is issued 10 of Item x
Item x in stock is zeroed
Any subsequent requests for Item X need to be denied.

So as an example of what I'm thinking might be a different/easier method would be:
Records

Initial
[tbl_inventory]
[pk]   [Dscp]    [instock]
[1]    [ItemX]    [10]
 
<user makes request/>
[tbl_partorder]
[pk]   [fk_User]   [fk_invntry]    [amntreq]    [amntissued]
[1 ]   [1]         [1]             [30]         [0]

final
<so after the user has the parts in hand/>
[tbl_inventory]
[pk]   [Dscp]    [instock]
[1]    [ItemX]    [0]
 
[tbl_partorder]
[pk]   [fk_User]   [fk_invntry]    [amntreq]    [amntissued]
[1 ]   [1]         [1]             [30]         [10]

Now your query can calculate any records where [amntreq]<>[amntissued] to determine pending part orders.

If at anytime another (same) user attempts to request against the part while the inventory is zero then message the user with the out of stock.

If the user attempts to re-issue a part request for the same parts and has a record where [amntreq]<>[amntissued] for that part, then the user can be charged against that record.

I would also look at either a FIFO method of fulfilling the requests with an option for priority requests.

I would also consider "project" charge too... say a user needed 10 of Item X for project-a and 20 for project-b etc... you have a method to handle that.... but such conversation is OFF-Topic to this thread.

[mrijet]

Thanks for the replying @zmbd...I really appreciate your suggestion..I still can't imagine it...


There are 2 table involve in my case which is tblStock that have the quantity of the stock...the other is tblOrder...


Example :-

tblStock

Quantity = 20

tblOrder = OrderForm for user

Attribute
-QtyReq = quantity stock that customer request
-QtyIssued = quantity stock that user give to customer
-Balance = Pending stock for customer

The situation might be like this if the main stock= 10

If customer request 20 quantity for the item while the main stock have 10.....my coding will be..

txtboxQtyRequired_BeforeUpdate
 
Dim stock as String
 
stock = Nz(Dlookup("StockQuantity","tblStock","ItemCode='" & ComboBox & "'"))
 
If stock < txtboxQtyRequired
MsgBox ("Current stock for this item is " & stock)
Me.txtboxQtyRequired = ""
EndIf

This coding will tell user if they put quantity more than the main stock which is 10

Then...

What if the user wrongly put the quantity...for example user put 20..but the main stock is 10

My coding will be...

txtboxQtyIssued_AfterUpdate
 
Dim stock as String
 
stock = Nz(Dlookup("StockQuantity","tblStock","ItemCode='" & ComboBox & "'"))
 
If stock < txtboxQtyIssued
MsgBox ("Current stock for this item is " & stock)
ElseIf Me.txtboxQtyIssued Then
'Call query
DoCmd.OpenQuery "QueryName", acViewNormal, acEdit 'Operation : MainStock - QtyIssued
EndIf

The coding above will tell user that the stock user put are insufficient...

But then, there might be for user forgot that the stock are insufficient...there open back the form and key in the textboxQtyIssued to 20 and the msg box appear and tell insufficient stock and tell the stock = 10...

So user key in textboxQtyIssued to 10...the coding insist of going to prompt user Insufficient stock...


Did my if else statement wrongly..or..did I need to looping for this statement?....If I'm wrong please help me...

[NeoPa]

mrijet:
I make it updated because sometimes what user required are not in the stock...that why I make QtyIssued for user to key in what item they already take in the store and balance will be show. Later, if the item already restock user will open back this form to key in until the QtyReq

That may well be so. That doesn't mean it's a good idea to do so even so.

If that is your requirement then you need to design a way that handles that properly. Your original approach handles nothing reliably. You need a solid foundation from which to build. To update values from a considered (and not confirmed) change is asking for trouble.

It is possible to show information on the form that reflects the what-if situation of how it would look were the value to be entered and saved. Clearly, this should not be saved away anywhere until after the change has been saved though - if even then.

[Stewart Ross]

Observations
1. Commercial stock control systems distinguish between quantity required and quantity on hand. Stock may be available when an order is placed but unavailable when the order is filled, in which case the excess is automatically placed on back order - in effect it is placed in a priority queue for fulfilment once stock is available (like what Z mentioned about using FIFO techniques).

2. Stock is never recorded as issued at the time of order. The order is a commitment against the stock, not a physical issue of stock. Fulfilling an order takes time, and stock on hand quantities can change in the meantime, or as a result of things beyond your control such as defective stock, breakages, and mistakes. Some companies (Amazon for instance) allow cancellation or modification of orders until they are moved to the processing stage - this just changes the quantities of stock committed, not the quantities of stock on hand at all. Commitments to stock are reconciled as physical stock issued when the order is fulfilled and not before.

3. The relationships in an ordering module are less complex than in a stock issuing module. An order can have many items, each with just one quantity required, but the issuing system often has to be able to record the additional 1-m relationship of stock issued for each item, and the queuing of back-ordered quantities etc, even though the majority of orders will be issued and fulfilled in one go.

4. Real stock control systems are more complex than you have considered. I agree entirely with NeoPa that your data model has to reflect what you are really wanting to achieve. You may not need or want to model the full complexity that commercial stock systems allow for, but there is more to it than you've modelled so far.

-stewart

[mrijet]

Thanks for replying @NeoPa and @Stewart Ross.....I appreciate it....

If I didn't put the if else statement...the quantity of the main stock will be not accurate....but the calculation work and accurate....


In this problem I'm using 2 type of events which is AfterUpdate() and OnDirty()...

Private Sub txtuse_AfterUpdate()
 
Dim stock As String
 
stock = Nz(DLookup("StockLevel", "Parts", "ItemCode='" & Me.Combo32 & "'"))
 
If Me.txtuse > stock Then
MsgBox ("Insufficient stock..Please put the quantity less or equal than " & stock)
ElseIf Me.txtuse <= stock Then
DoCmd.OpenQuery "test_1", acViewNormal, acEdit 'Operation = Main Stock - txtboxissued(me.txtuse)
End If
 
End Sub

The above coding working properly.......after update it...

Private Sub txtuse_Dirty(cancel As Integer)
Dim stock As String
 
stock = Nz(DLookup("StockLevel", "Parts", "ItemCode='" & Me.Combo32 & "'"))
 
If Me.txtuse < stock Then
MsgBox "Insufficient stock"
ElseIf Me.txtuse < stock Then
DoCmd.OpenQuery "test_2", acViewNormal, acEdit 'Operation = Main stock + txtboxIssued(me.txtuse)
End If
 
End Sub

This coding will work if...
main stock 20...
Required is 25..
issued is 20...
Balance will be 5...

The ondirty event coding will be run on this pending case...

The problem is when add the main stock to 3....
This event ondirty show messagebox "insufficient stock"..
Are ondirty event not userfriendly?


Or...

Maybe I need to do another method which is passing parameter?


Sorry if to troublesome...

[zmbd]

mrijet,
Stewart and Neopa have been doing this a very long time and both of them have suggested that you are doing this the hard way...

It might be helpful to understand why you are not following the normative methodology for database design for what appears to be a standard inventory/order database. What you are attempting to do is difficult by design because it's a non-standard, IMHO ill advised, attempt at a solution. The method you are attempting will be a nightmare to maintain and the people following you will bless you many times as they repair the coding be work the way it should.

[mrijet]

I m sorry for make this conversation to long....actually what is the standard procedure for Inventory System regarding the stock....

Did I need to give what I have in Inventory to the system?

Because I just want to create a system that record control stock in and stock out only....

If I'm wrong...pls correct me...

[zmbd]

Don't worry about the length... We are more concerned about getting you on the right path then anything else... Topics can be split and titles changed of needed :)



My prior post describing the general work flow is more along the lines of what I have seen in other's databases and in my own.

How much detail do you need?
For my database I have to track lot numbers, serial numbers, where and when received and sent along with expiration date so that the oldest stock of sent to the highest priority job. From there I have to track ageing.

You may only need the aggregate which would be easier to design.

Sent from my phone please forgive any weird words :)

[mrijet]

Thanks for your time replying this @zambd....My mission for create this system..to make sure the stock in and stock out are accurately recorded....

[NeoPa]

mrijet:
If I'm wrong...pls correct me...

That's exactly what I was trying to do in post #2. It's also what everyone else here has been trying to do.

If you're to benefit from our offerings you need to read them carefully and accept that your current approach may not be a very sound one.

None of us here is out to criticise you. We all want to help. For that though, you must be prepared to give what we say proper consideration. As ZMBD says, we all have considerable experience in the area.