Validation based on numeric type Access 2003

[mayala12]

I have a text box field that is used for putting in the size in inches of a product we sell. We have several different products that use the same box and all but one group can have decimals 2.25 (as an example). However we have one group that must be to the nearest inch.

What I would like is for the validation to pop up if the user enters a decimal that warns them it's not available and prompts them to correct it. I have the validation and the top and bottom limiters but I cannot figure out how to validate for a decimal entry.

below is my current code:

Case 6, 7 'product 1 and product 2
    
        sngheight = Int(frm.TxtDoorHt.Value / 12)
        sngwidth = Int(frm.TxtDoorWidth.Value / 12)
        
        If (frm.TxtDoorHt.Value >= 78 And frm.TxtDoorHt.Value <= 126) Then
            bInvalidSize = False
        Else
            bInvalidSize = True
        End If
        If (frm.TxtDoorWidth.Value >= 48 And frm.TxtDoorHt.Value <= 222) Then
            bInvalidSize = False
        Else
            bInvalidSize = True
        End If

[Mikkeee]

You could restrict certain characters from being entered. Having the following code in the KeyPress event of your text box should do it.

' Eat the decimal key
If KeyAscii = 46 Then
    KeyAscii = 0
End If

But if you really have your heart set on checking for a decimal then this will do it.

If InStr(frm.TxtDoorHt.Text, ".") > 0 Then
    Debug.Print "Found It"
End If

[mayala12]

Thank you, I used the first options and it might work for probably 90 percent of the situation. The problem 10 percent is as follows:

The users first picks Product 1 which allows decimals
Then they enter the dimensions in feet and inches for both height and width, let's say 4 feet 2.25 inches

If they want to change the product line, we have it set to keep the existing dimension to make the program more user friendly the dimensions are left in there. (fewer keystrokes = greater efficiency)

Since Product 1 has decimals in the inches field and Product 2 cannot, how do I have it pop up a message when there is an invalid decimal entry for Product 2?

I hope I making this clear, this is a pretty complex Access Program that we use to configure all our products for pricing purposes so it's quite cumbersome, and I'm trying to keep it as user friendly as possible. So ideally wiping out the dimensions would be the last choice.

[Mikkeee]

Mayala, I partially agree with you (fewer keystrokes = greater efficiency) until you get to the part where you're allowing invalid data in the text boxes. I would (now this is just my opinion here) allow the user to switch the product but I would clean up the data to make it valid. In your example, the user entered 2.25 in one product but the second won't allow the decimal. I would either truncate or round that 2.25 to 2. If you want the user to see the 2.25 when they switch back then you should save the last valid entry in a variable that way you knew that it was 2.25, modified to display 2, and then grab that 2.25 from your variable and put it back in there. There are many different ways to accomplish this but I don't believe knowingly displaying an invalid entry is the right way to go.

[Rabbit]

Use an additional Case statment and check if they entered a decimal in the textbox.

[mayala12]

Thank you all for your help on this, I have been given further direction that allows for the first answer with the code to initially prevent decimals to be adequate, anything after that will be handled by the processes we have in place so Yay, no coding required. But the help on that code to prevent the decimals will be very useful, so thanks again!

[Killer42]

Just a suggestion. Don't simply "eat" the decimal point. That just makes entry confusing for the user, because they think they've typed (for example) "2.3" but actually entered 23. At the very least, it should beep to get their attention.

Although it can be a bit fiddly to code, in a situation like this I actually like to beep and do something visual (such as change the border colour around the field) for a second or two. Oh. This is Access? This sort of effect may be even more fiddly there than in VB6. But I'm sure it's still possible.

Another nice option, interface-wise, is to make the field stand out visually as long as the value in it isn't valid. For example, in an event that fires every time the field changes (.Change, .Validate or whatever) you simply set the background colour of the field to red if not currently valid, or normal otherwise. Ideally, this also happens whenever the situation changes (such as changing the product type) so that it's immediately obvious that the value already sitting there no longer fits.

P.S. I realise this thread's sort of old now, but hopefully these ideas might benefit anyone else who comes looking for similar answers.