Not Allow a "," in a input hours Form field.

[mcupito]

Good Morning.

I have a form where users are inputting hours, and the rounding function and everything works correctly, except when a user types 1,25 instead of 1.25, the hours jump to 125.00 instead of 1.25. Is there an easy fix? Or perhaps a statement I can throw in VBA to not accommodate for users to enter that in?

If anyone needs the code, let me know.

I appreciate your time and assistance, as I am new to Access.

Mark

[ADezii]

There are several ways to accomplish this. Let's suppose the Control containing these Values on your Form is a Text Box named txtValue. In the AfterUpdate() Event of txtValue you can place a single Line of Code that will convert any "," in the Value to a ".".

Private Sub txtValue_AfterUpdate()
  Me![txtValue] = Replace(Me![txtValue], ",", ".")
End Sub

[Seth Schrock]

In the control's BeforeUpdate event, enter the following code (I will assume a control name of txtHours):

If InStr(me.txtHours, ",") > 0 Then
    Cancel = True
    MsgBox "This control cannot contain a comma."
End If

If a comma doesn't exist, the value returned will be 0. Otherwise, it will give the position of the comma in the string.

There might be another way that I haven't thought of, but this should work.

[mcupito]

I have an idea that may not be as good as either of yours. I did some research and thought of the idea of prohibiting someone entering the KeyAscii of 44 (a comma). Is that possible? I don't want to set any values to 0, because as is the code works aside from the thousandths placeholder issue. I also implemented some formatting as a catch, changing the color of the box to red when value > 99.

Let me know what you all think about the KeyAscii solution and how would I implement it?

I was going to try

If KeyAscii = 44 Then
    MsgBox "Cannot enter a comma."
End If

But it kept coming up with an error saying KeyAscii was an unassigned variable.

[mcupito]

Private Sub ServiceHours_AfterUpdate()
On Error GoTo Err_Handler
    
    

Dim number, number2

    number = Int(Me.[ServiceHours])
    number2 = Me.[ServiceHours] - number
    
    
    Select Case number2
        Case 0
            Me.[ServiceHours] = Me.[ServiceHours]
        Case 0.0001 To 0.2499
            Me.[ServiceHours] = Me.[ServiceHours] - number2 + 0.25
        Case 0.2601 To 0.4999
            Me.[ServiceHours] = Me.[ServiceHours] - number2 + 0.5
        Case 0.51001 To 0.7499
           Me.[ServiceHours] = Me.[ServiceHours] - number2 + 0.75
        Case 0.76001 To 0.9999
            Me.[ServiceHours] = Me.[ServiceHours] - number2 + 1
            
    End Select
    
    'Me![ServiceHours] = Replace(Me![ServiceHours], ",", ".")
    
Exit_Handler:
    Exit Sub
Err_Handler:
    MsgBox Error$
    Resume Exit_Handler
End Sub

It did not work like this.

[mcupito]

I also tried this

   If KeyAscii <> 44 Then
    KeyAscii = KeyAscii
    Else: KeyAscii = 46
    End If

It didn't work.

[Rabbit]

The AfterUpdate event has no KeyAscii variable. Only the KeyPress, KeyUp, and KeyDown events have it defined.

[NeoPa]

Mark,

You have two sensible suggestions available to you, but it seems you want to do it another way. This other way has very little going for it I'm afraid. I suggest you look at what's been suggested and give that some thought before trying to follow your own lead. Once you understand it better I'm confident you'll prefer the suggestions of those who have some experience in this field.

[Seth Schrock]

Just to be sure, you don't want any values over 99, correct? I never tested my method with values over 1000 where Access would automatically put in the thousands separator (the comma) on its own, so I don't know how that would effect the practicality of my solution (or Adezii's for that matter), but if the values that you are entering are always going to be less than 99, then this won't be an issue. In fact, I personally would probably prefer Adezii's solution as it would fix it for them instead of just telling them that there was an error. You do also have the option of using the KeyDown event to test the character (including if the Shift, Control, or Alt keys were pressed at the same time) and then raise a warning if a comma was entered. The code that you had entered in post #4 should work in that event, but there isn't a way to keep the comma from being entered that is simple so you would be back to trusting the user to remove the comma. Thus I think that you would be better served (and less code entered) by using either ADezii's or my solutions as there would then be no way for the record to be saved with a comma having been entered.

[mcupito]

I apologize, I was out of town. Adezii, and all other users - I appreciate your time in assisting me solving this issue. ADezii, while your solution is logical, it did not work for me. MS Access recognized the "," as a thousands or hundredths place holder.

E.g: 3,2 went to 32.00

Hours are possible to exceed 99.
The only solution I found was setting a MsgBox when Hours > 99 that asks "Are you sure the hours are correct?"

I'm not sure why ADezzi's solution did not work, however, the end users are satisfied with the MsgBox popup.

Thank you all for your help, as a newb I appreciate it very, very much.

[Seth Schrock]

No problem. Glad to be able to help.