Add comma's on AfterUpdate

hsiddique · #1 (**permalink**) 12-29-11, 11:29

Hi,

I have 4 textboxes that will contain sq ft values. I want to set so that the user can enter for e.g. 12345, Once the next textbox is selected, I want to automatically change the value to 12,345.

I've tried the following

Code:

Public Sub CheckArea(Area As Integer)

  If Len(Area) = 7 Then
        Area = Left(Area, 1) & "," & Mid(Area, 4) & "," & Right(Area, 3)
    ElseIf Len(Area) = 6 Then
        new_value = Left(Area, 3) & "," & Right(Area, 3)
    ElseIf Len(Area) = 5 Then
        new_value = Left(Area, 2) & "," & Right(Area, 3)
  End If


End Sub
Private Sub txtExistingSpace_AfterUpdate()
        Call CheckArea(frmCityDev.txtExistingSpace.Text)
End Sub

But when I test it i get an Overflow error, am i on the right track or is there an easier way.

Thanks

ieuan · #2 (**permalink**) 04-24-12, 04:51

No, you are not on the right track. Use the Format function instead.

Private Sub txtExistingSpace_AfterUpdate()
txtExistingSpace = Format$(txtExistingSpace,"#,##0")
End Sub

*Or if you are using MS Access forms, you would not need to write a code. Just select the textbox, and in the Property Sheet, select Format and choose the correct formatting from the dropdown list (currency, standard, general number, dates, etc.).

Internet Services

Web Development

Digital Marketing

Consumer Tech