Don't allow negative values in Inventory 1

[acnovice]

Hi,

I have small inventory system using access. When I create an invoice, shipp q'ty is subtracted from inventory.
What I want to do is if q'ty is 0 or less than shipQty, give the warning message and not to allow to create an invoice until inventory has enough q'ty.

For an Invoice Creation: frmInvoice form with sub form sfrmInvoiceDetail which has ShipQ'ty and TPbaseNo(PartNumber).
Currently, ShipQ'ty is subtracted from Current in tblProduct without checking the q'ty .
Following is my code for info.

Private Sub Form_AfterUpdate()

On Error GoTo ErrorHandler

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE tblProduct SET [Current]=[Current]-[sfrmInvoiceDetail]![ShipQ'ty]" & _
" WHERE [TPbaseNo]=[sfrmInvoiceDetail]![TPbaseNo];"

ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "There was an error updating"
Cancel = True
Resume ErrorHandlerExit:

End Sub

We input the Current q'ty from form InventoryOP and saved to tblProduct.

Any help will be appreciated.

 

[PWise]

in form before update

dim mydb as database
dim rst as recordset
set mydb=currentdb 
set rst=mydb.openrecordset("select 'Not enough in Invortory' as msg from tblProduct  where WHERE [TPbaseNo]=[sfrmInvoiceDetail]![TPbaseNo] and [Current]-[sfrmInvoiceDetail]![ShipQ'ty]<0 ;")
cancel = rst.bof and rst.eof
if rst.bof and rst.eof then
  msgbox rst!msg
end if

 

[TheAceMan1]

How are ya acnovice . . .

Try the following in the [blue]BeforeUpdate[/blue] event of the form [purple](don't forget to disable or remove the code in the AfterUpdate event)[/purple]:

[blue]   Dim sfrm As Form, SQL As String, CurMax As Long, Criteria As String
   Msg As String, Style As Integer, Title As String, DL As String
   
   Set sfrm = Me!sfrmInvoiceDetail.Form
   DL = vbNewLine & vbNewLine
   Criteria = "[TPbaseNo] = " & sfrm!TPbaseNo
   CurMax = Nz(DLookup("[Current]", "tblProduct", Criteria))
   
   If sfrm![ShipQ'ty] > CurMax Then
      Msg = "Ship Quantity is greater than Current Quantity!" ^ DL & _
            "Can't save Invoice!"
      Style = vbCritical + vbOKOnly
      Title = "Not Enough Stock! . . ."
      MsgBox Msg.Style, Title
      Cancel = True
   Else
      SQL = "UPDATE tblProduct " & _
            "SET [Current]=[Current]-" & sfrm![ShipQ'ty] & " " & _
            "WHERE (" & Criteria & ");"
      DoCmd.RunSQL SQL
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[JoeAtWork]

A suggestion I would make is in the table design of tblProduct to make a validation rule on the [Current] field that makes negative quantity invalid. This lets your database protect itself from any bad code. It may lead to the occasional error message to the user (until you fix the bug), but at least you will be alerted right away to a problem.

If you have the programming skills, I would also suggest researching best practices for an inventory database. Instead of using a [Current] field, you should consider a Transactions table that records each incoming/outgoing movement of a product. The "Current Quantity" would be dynamically calculated by summing all the incoming (positive) and outgoing (negative) quantities in the Transactions table. This tends to make a more accurate quantity count, and if there is an inaccurate count you can trace the history of all movement to find out when the error might have occurred.

Finally, I would suggest making a centralized procedure for changing the quantity count. When quantity needs to change, use this procedure exclusively for doing the update. Believe me, this will save you a lot of pain when debugging in the future.

I speak from experience. I've inherited an inventory database that does not use a transaction table, and the Quantity is updated in any of a dozen or more operations, each of which uses their own sets of queries and/or SQL statements (i.e. not centralized). I've spent weeks trying to debug this application to get rid of periodic negative quantity counts. The lack of a centralized function makes it extremely difficult to track down the origin of these problems.

 

[acnovice]

Hi TheAceMan1,

I tried your code and I'm getting following error.

Compile error:
Statement invalid outside Type block.

and highlighted

Msg As String, Style As Integer, Title As String, DL As String

I disabled afterUpdate of the form.

Thank you.

 

[TheAceMan1]

acnovice . . .

[blue]Msg As String, Style As Integer, Title As String, DL As String
   [purple]should be:[/purple]
[purple][b]Dim[/b][/purple] Msg As String, Style As Integer, Title As String, DL As String[/blue]

Sorry about the mess . . .

See Ya! . . . . . .

 

[acnovice]

Hi guys,

Thank you for all of postings and I will review later.
Let me get back to you after Christmas.

TheAceMan1... I really appreciate your posting...
Thank you so much. Talk more detail later.

Merry Christmas.

 

[MichaelRed]

on the other hand, I would advise you to NOT try this at work!

Conceptually, you are saying that a customer is standing in the checkout line with goods in hand and YOU are at the register telling that customer they canot have the merchandise because the inventory says it isn't in stock!

I know you will instantly say that you are not in the retail business, so it doesn't apply. But it does. If the application is wholesale, the stock processing should have a mechanisim to provide the packing info to accounting, and the invoice should be derived from the PACKING information, not some academic version of what you THINK (programmaticallY) is available.

People are all to human, and mistakes DO happen. providing prpoer feedback to the data sets helps minimize both the errors made and their impact on the data processing. But these 'controls' need to applied carefully. Stopping sale/delivery of merchandise because the data says SOME PART of the order is missing is JUST PLAIN WRONG!

Please rethink this and arrive at a more positive soloution!




MichaelRed

 

[acnovice]

Hi TheAceMan1,

Hope you're still there.
I'm getting another compiler error from following line of your code.

MsgBox Msg.Style, Title

hilighted Msg and it says "Compile error. Invalid qualifier."

 

[Golom]

Try

MsgBox Msg[red], [/red]Style, Title

And to add to Michael's comments.

Even though a negative inventory quantity may seem to be unacceptable, it can legitimatly happen. If a product is being sold or picked from a received order before the order is received into stock, the quantities can go negative. Doing the stock receipt will then correct them to the proper values.

Note that reality is what's actually "out there" in stock ... not what the system thinks it is.

 

[acnovice]

Hi MichaelRed,

You're right.
I fully agree with your suggestion with more detail and deep consideration.

Actually, we don't need to say to our customer the products are out of stock. The Inventory tells not only CurrentQ'ty but also SafetyQ'ty and ExtraQ'ty. They are all linked to Stock Adjustment Log and Vendor Order Log and managed by warehouse manager.

Our Inventory systems hasn't fully developed yet so if there's further advice, feel free let me know.

Thank you.

 

[MichaelRed]

so, in the grand scheme, orders received should be processed by the warehouse, with the bill of lading passed back to inventory management. the bill of lading compares the order, generating an invoice for what was shipped and at least an estimated date for the shipping of any items not in stock ...




MichaelRed

 

[acnovice]

I'm stuck in follwing error.

Run-time error '2465'
Microsoft Access can't find the field 'sfrmInvoiceDetail' referred to in your expression.

Thank you.

 

[PHV]

Have a look here:

http://support.microsoft.com/?kbid=209099

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[acnovice]

MichaelRed,

Similar but Invoice is always working on true q'ty which is greater than 0 but consider SafetyQ'ty.

 

[acnovice]

PHV,

I changed the sub form control and got following error.

Run time error 2001. You canceled previous operation.

Dim sfrm As Form, SQL As String, CurMax As Long, Criteria As String
Dim Msg As String, Style As Integer, Title As String, DL As String

Set sfrm = Forms![frmInvoice]![sfrmInvoiceDetail].Form
DL = vbNewLine & vbNewLine
Criteria = "[TPbaseNo] = " & sfrm!TPbaseNo
CurMax = Nz(DLookup("[Current]", "tblProduct", Criteria))

If sfrm![ShipQ'ty] > CurMax Then
Msg = "Ship Quantity is greater than Current Quantity!" ^ DL & _
"Can't save Invoice!"
Style = vbCritical + vbOKOnly
Title = "Not Enough Stock! . . ."
MsgBox Msg, Style, Title
Cancel = True
Else
SQL = "UPDATE tblProduct " & _
"SET [Current]=[Current]-" & sfrm![ShipQ'ty] & " " & _
"WHERE (" & Criteria & ");"
DoCmd.RunSQL SQL
End If

 

[TheAceMan1]

acnovice . . .

Open the mainform in [blue]design view[/blue]. Double-Click on the little square box just to the left of the ruler

on the mainform. The [blue]click once[/blue] on the same for the subform. On the [blue]Other Tab[/blue] whats in the [blue]Name property[/blue] is what you should be using in code. We need to know if its the same as you've been posting . . .

See Ya! . . . . . .

 

[MichaelRed]

so, you are essientially persuing the same path, not to process the invoice on the basis of what is ACTUALLY available for shipping (e,g, what the warehouse found to send out), but some 'mythical' computation about what is 'thought' to be 'available'?

Sorry I took your attention away from your goal.




MichaelRed

 

[acnovice]

Still I have a problem on this code.
When I create an Invoice, it seems like doesn't check the Current q'ty and return the following message.

Ship Quantity is greater than Current Quantity!
"Can't save Invoice!"

I checked there are enough q'ty in inventory.

Following is the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim sfrm As Form, SQL As String, CurMax As Integer, Criteria As String
Dim Msg As String, Style As Integer, Title As String, DL As String

Set sfrm = Forms![frmInvoice]![sfrmInvoiceDetail].Form
' Set sfrm = Me!sfrmInvoiceDetail.Form
DL = vbNewLine & vbNewLine
Criteria = "'" & [TPbaseNo] & "' = '" & sfrm!TPbaseNo & "'"
CurMax = Nz(DLookup("[Current]", "tblProduct", Criteria))

If sfrm![ShipQ'ty] > CurMax Then
Msg = "Ship Quantity is greater than Current Quantity!" & _
"Can't save Invoice!"
Style = vbCritical + vbOKOnly
Title = "Not Enough Stock! . . ."
MsgBox Msg, Style, Title
Cancel = True
Else
SQL = "UPDATE tblProduct " & _
"SET [Current]=[Current]-" & sfrm![ShipQ'ty] & " " & _
"WHERE (" & Criteria & ");"
DoCmd.RunSQL SQL
End If
End Sub

Help !

 

[TheAceMan1]

[blue][purple]change:[/purple]
   Criteria = "'" & [TPbaseNo] & "' = '" & sfrm!TPbaseNo & "'"
[purple]to:[/purple]
   Criteria = "[TPbaseNo] = '" & sfrm!TPbaseNo & "'"[/blue]

See Ya! . . . . . .