Data validation code, I need a better solution.

[Phred]

Access 2007, VBA, Windows 2007 64 bit, skill level intermediate, single user, compiled, not split.

Tab order. When the form opens the Tab1=PU On Hand
Tab2=IU On Hand

I think I have a problem with my basic strategy. I need a more graceful solution. I am working on the inventory portion of a database. When the Master Inventory screen opens the Tab order takes the end user to the "PU on Hand" field. They should only be able to enter Zero or a Positive Integer. My three tests will catch the error and notify the end user, and then I start having problems in trying to end this gracefully.

I have tried my code in the "Before Update" event, and in the "On Exit" event. I've tried me.undo which has its problems. I currently have the code inserting Zero when there is an error. But then the code ends and the tab order of the screen takes them to the next field "IU on Hand." I don't know how to get the Tab Order stopped, and not advance to the next field until the correct data is entered. My code entering a Zero is not really a good idea as the end user will just roll on not fully realizing the data meets the test but isn't really correct. When they are tabbed to the IU on Hand and enter the Individual Units on hand it takes the numbers from the PU on Hand, calculates with the IU on Hand and enters the new Inventory Value in the extentions field. See attached screen snap.

When the data is correct my code works fine and gives me the right numbers and calculations.
When the data is not correct such as NULL, Negative, or a Fraction my my code works fine and detects it and notifies the end user.
When the data is not correct i am struggling to find the best way to handle it.

Here is my code:

Private Sub PUOnHand_Exit(Cancel As Integer)


If IsNull(Me.PUOnHand) = True Then
Call MsgBox("The PU On Hand field cannot be empty." _
& vbCrLf & "You must enter 0, or any positive, whole number." _
& vbCrLf & "Please re-enter the number." _
, vbCritical, "Field Empty")
Me.PUOnHand = 0
Exit Sub

End If
If (Me.PUOnHand) < 0 Then
Call MsgBox("The PU On Hand field cannot be negative." _
& vbCrLf & "You must enter 0, or any positive, whole number." _
& vbCrLf & "Please re-enter the number." _
, vbCritical, "Negative Number")
Me.PUOnHand = 0
Exit Sub

End If
If Me.PUOnHand <> Int(Me.PUOnHand) Then
Call MsgBox("The PU On Hand field cannot be a fraction." _
& vbCrLf & "You must enter 0, or any positive, whole number." _
& vbCrLf & "Please re-enter the number." _
, vbCritical, "Fraction")
Me.PUOnHand = 0
Exit Sub
End If
End Sub

I'm not sure what to ask here except my solution detects the problem but doesn't really help the end user to put in the right data. I'm going to run into the same problem with the IUperPU field when they get there.

Thanks,

Phred

[Bob Fitz]

Perhaps this would work in the Before Update event of the control.

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." _
    & vbCrLf & "You must enter 0, or any positive, whole number." _
    & vbCrLf & "Please re-enter the number." _
    , vbCritical, "Field Empty")
  Cancel = True
  Me.PUOnHand.Undo
  'Me.PUOnHand = 0
Exit Sub

[Phred]

Bob thanks,

That is much cleaner and much more intuitive to the end user that something still isn't right. Its smooth. Can I trouble you with a follow on question?

I added your tests to the next field IU on Hand. You're code works great.

The tests work fine. The message box works fine. If the data is changed properly it works fine and recalculates.

New Question: if the PU on Hand field has been changed, but the end user wants to leave the IU on Hand field as is (not change the data) when you exit the IU on Hand field it does not fire the recalculation using the new PU on Hand number. If you leave the number as is, then nothing has been updated so it doesn't calculate. Should I have this in a different event? Should it be split into different events?

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
Exit Sub
End If

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

intID = Me.ID

intPUonHand = Me.PUOnHand

intIUonHand = Me.IUOnHand

numLPVPUprice = DLookup("PUPrice", "tbl_LPVCurrent", "inventoryid = " & intID & "")

numLPVIUprice = DLookup("PricePerUnit", "tbl_LPVCurrent", "inventoryid = " & intID & "")

numPUnewCost = intPUonHand * numLPVPUprice

numIUnewCost = intIUonHand * numLPVIUprice

numExtention = numPUnewCost + numIUnewCost

Me.InventoryValue = numExtention

Me.InventoryDt = Date

End Sub

Thanks again,

Phred

[Bob Fitz]

What result do you get if you take out the line:
Exit Sub
that is in the If/Then code that I posted. I don't think that should be there.

[Phred]

Same thing. I can exit the Iu per PU field without it firing the recalculation. It seems if I don't actually change the number the Before Update event won't fire. If I change the IU per PU number and even re-enter the existing data that makes the recalculation fire and everything is fine. No change, no work.

[Bob Fitz]

Perhaps the code that does the calculation would be better in the fields Lost Focus event.

[Phred]

That did the trick.

On your code what exactly does Cancel=True cancel?

Also, on the forum here, do you know how you get the scrolling window to paste code into?

I'll click the star but I dont' know if that prevents you from answering.



Thanks a lot Bob.

[Bob Fitz]

Glad we got there.

Cancel = True
Stops the Update of the combo box.

To enter code:
Click the blue "Go Advanced" button at the bottom right. You may need to scroll down to see it. You will then have a more extensive selection of buttons on the toolbar. Use the button marked with a hash sign (#) to wrap code tags around your selected text.

[Phred]

Many thanks to you. I just used the Cancel=True in another error handler. Cheers, I will hoist a beer to you.

Phred

[Phred]

I clicked the star but it doesn't give you any feedback if it works. So let me know if you didn't get credit from it. I'm going to click Solved.

[Bob Fitz]

Thank you for your kind thoughts. Good luck with your project.