VBA problem with data validation, this is a multi stage problem

[Phred]

MenuPro PhaseIIA 42 - Copy.zipAccess 2007, Windows 7 64, Compiled, not split, single workstation, skill level intermediate?, There are no error messages.

This is a food Inventory database.

I have eliminate enough data to get it below 2 MB and attached it.



At the end of the day, Production enters the food they have used so the inventory gets adjusted down. They enter the new inventory values. The idea is they had 1000 hamburger patties before production and they used 200, at the end of the day they change the PuOnHand (Packing Units on Hand) to reflect that the inventory is now 800.When all data is entered new, all the code below works fine. There is conditional formatting on the Par Value field:

Par Value = the number of items in inventory they never want to go below. The Par Value field contains conditional formatting:
If Parvalue < PUonHand turn Par Value Field red if not leave it white.

PUonHand is the number of BOXES of Hamburger Patties. There might be six boxes plus 100 patties that are put back in the frige. The 100 single patties would be entered in the IUonHand = Individual Unites on Hand. After these fields have been updated the "Extension" field calculates giving the current value of the inventory on hand.

It has been a lot of work to get the code loaded into the proper Form Event ie., Before Update, After Update, On Exit. When you enter the PUonHand (Packing Units) the following code runs.This code runs fine unless you enter the SAME number in the field or leave the EXISTING number and tab to the next field IUonHand.

Problem 1: If you leave the EXISTING number in the Pu on Hand and (not change it) or you re-enter the SAME EXACT number, the conditional formatting in the Parvalue field wont fire.



Problem 2: If you leave the EXISTING number in the IU on Hand and (not change it) or you re-enter the SAME EXACT number, the code in the third section below "Private Sub IUOnHand_AfterUpdate()" won't fire.

The next two sections of code are solely to prevent the end user from entering NULL, Negative values, or fractions. That's all.

 Private Sub PUOnHand_BeforeUpdate(Cancel As Integer)
    If IsNull(Me.PUOnHand) = True Or (Me.PUOnHand) < 0 Or Me.PUOnHand <> Int(Me.PUOnHand) Then
        Call MsgBox("The PU On Hand field cannot be empty, negative, or a fraction." _
                    & vbCrLf & "You must enter 0, or any positive, whole number." _
                    & vbCrLf & "Please re-enter the number." _
                    , vbCritical, "Incorrect Entry")
        Cancel = True
        Me.PUOnHand.Undo
    End If
End Sub

So at this point you have entered the PUonHand and the data passes the validation test and you are Tab Ordered into the next field IUonHand. You enter the data for IUonHand and the following code runs when you tab out of the IUonHand field. If all data entry is new, everything is good.

Private Sub IUOnHand_BeforeUpdate(Cancel As Integer)
       If IsNull(Me.IUOnHand) = True Or (Me.IUOnHand) < 0 Or Me.IUOnHand <> Int(Me.IUOnHand) Then
        Call MsgBox("The IU On Hand field cannot be empty, negative, or a fraction." _
                    & vbCrLf & "You must enter 0, or any positive, whole number." _
                    & vbCrLf & "Please re-enter the number." _
                    , vbCritical, "Incorrect Entry")
        Cancel = True
        Me.IUOnHand.Undo
    End If
End Sub

If, in the above field IUonHand, you left the existing value in the field, or re-entered the same value in the field, tab out, the following code will not fire. There are no error messages anywhere.

Private Sub IUOnHand_AfterUpdate()
Dim intID  As Integer
Dim intPUonHand As Integer
Dim intIUonHand As Integer
Dim numLPVPUprice As Double
Dim numLPVIUprice As Double
Dim numPUnewCost As Double
Dim numIUnewCost As Double
Dim numExtention As Double
   On Error GoTo IUOnHand_AfterUpdate_Error
   intID = Me.ID
    intPUonHand = Me.PUOnHand
    intIUonHand = Me.IUOnHand
    numLPVPUprice = DLookup("PUPrice", "tbl_LPVCurrent", "inventoryid = " & intID & "") '

The forum is breaking this code into two sections. the next is the continuation of the code above.

if this test comes up null you go to the Error Handler below.
    numLPVIUprice = DLookup("PricePerUnit", "tbl_LPVCurrent", "inventoryid = " & intID & "")
    numPUnewCost = intPUonHand * numLPVPUprice
    numIUnewCost = intIUonHand * numLPVIUprice
    numExtention = numPUnewCost + numIUnewCost
    Me.InventoryValue = numExtention
    Me.InventoryDt = Date
    Me.NotInLpv = 0
                                                    
   On Error GoTo 0
   Exit Sub
IUOnHand_AfterUpdate_Error:
                  
Call MsgBox("This Item was not found in the Lowest Price Vendor list." _
           & vbCrLf & "" _
           & vbCrLf & "MenuPro cannot look up the LPV Price to calculate the value." _
           & vbCrLf & "" _
           & vbCrLf & "Click OK to cancel." _
           , vbExclamation, "Not in Lowest Price Vendor")
     
    Me.Undo
    
    Me.PUOnHand = 0
           
    Me.NotInLpv = -1
End Sub

After all of the above has executed the database takes the end user to the next inventory item

Private Sub IUOnHand_Exit(Cancel As Integer)
DoCmd.GoToControl "PUonHand"
DoCmd.GoToRecord , , acNext
  
End Sub

When all data is entered new, (Not the same as was previously in the fields above) everything works fine.

Thanks. Fred

[June7]

You are experiencing the conundrums presented by trying to save aggregate data instead of calculating from detail records when needed. Ideally, raw data (transactions of inventory in and out - sales and purchases) would be entered and reports would calculate balances on hand. That is functionality a truly relational database is intended to provide.

It is a challenge to keep the saved aggregate data in sync with the raw detail data. Errors (such as accidental repetition of the adjustment calculation or no adjustment as you are experiencing) are hard to correct because connection to the raw data is lost. Accountability is not preserved as there is no audit trail for the aggregate value.

Having said all that, I admit I never had to build an inventory or accounting or sales db. I do know there are existing templates that might suit your requirements.

[Phred]

June 7 thanks.

It is kind of what I thought the problem was. My solution now will be to call up a modal screen where all the inventory updates will be made. I will have a SQL update statement write to the underlying table each time an update is made. Then when the modal screen closes the Master Inventory form will refresh showing the new inventory info directly from the underlying table.

Thanks for your expertise. If you see any problems or have any thoughts on my solution I would appreciate hearing them. I will mark this solved.

Thanks again.