Results 1 to 3 of 3
  1. #1
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009

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

    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.

    Click image for larger version. 

Name:	InventoryFields2.JPG 
Views:	8 
Size:	16.4 KB 
ID:	9784

    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
        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
        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
    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.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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    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.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Phred is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Dec 2009
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Multi-User Access Open Database problem
    By RayMilhon in forum Access
    Replies: 1
    Last Post: 09-25-2012, 04:23 PM
  2. Multi-user problem
    By Reaper in forum Access
    Replies: 3
    Last Post: 12-06-2011, 11:20 AM
  3. Multi combo box problem
    By g4b3TehDalek in forum Forms
    Replies: 15
    Last Post: 09-28-2011, 10:52 AM
  4. Interesting multi query problem.
    By daltman1967 in forum Queries
    Replies: 1
    Last Post: 07-28-2011, 02:52 PM
  5. Acess 2007 Validation Problem- HELP!?
    By JackLowe in forum Access
    Replies: 1
    Last Post: 04-20-2009, 12:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums