Format Numeric Field To Show Commas

You can use user-defined format types instead of pre-built ones like "Currency"

Format(txt1,"##,###")

or use a masked edit control to control what is entered.  Again, you would have to enter a user-defined format as the mask.

'If number is numeric then it can only have numbers, decimals, and commas in it.  So all we have to do is determine if its numeric then format it to remove decimals

Private strMyNumber As String

strMyNumber = "100,000.00"

strMyNumber = FormatMyNumber(strMyNumber)

'***********************************
Private Function FormatMyNumber(ByVal strMyNumber As String) As String

    If IsNumeric(strMyNumber) = True Then
        strMyNumber = Format(strMyNumber, "#,###")
    End If
   
End Function
'***********************************

OR

'***********************************
Private Function FormatMyNumber2(ByVal strMyNumber As String) As String
   
    Const DECIMAL_VALUE = "."
   
    If IsNumeric(strMyNumber) = True Then
        strMyNumber = Left(strMyNumber, InStr(1, strMyNumber, DECIMAL_VALUE) - 1)
       
    End If
   
End Function

Remember that you have to convert Currency and or Decimal/Float data type to string in order for my functions to work, then convert back to Currency and or Decimal/Float when inserting into SQL.  This is done by using the CDbl or CCur or CDec should work just fine.

I believe this validation fits the criteria.

--Must Be Numeric
--Can have commas
--Cannot have decimals


Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
    Exit Sub
Else
    If KeyAscii = 44 Then
       KeyAscii = 44
       Exit Sub
    End If
    If KeyAscii = 46 Then KeyAscii = 0
    If KeyAscii < 48 Then KeyAscii = 0
    If KeyAscii > 57 Then
       
        KeyAscii = 0
    End If
End If
End Sub

RichW

RichW has the right idea...
'This allows only 1234567890, and Backspace
Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 OR KeyAscii = 44 Then Exit Sub 'BS/comma OK
If KeyAscii < 48 OR KeyAscii > 57 Then KeyAscii = 0 'Only numerics OK
End Sub

x="123,123.45"
if format$(x)=clng(format$(x)) then msgbox "It's valid"

Try this (for different formats, adjust for your own purpose)

'************************************************************************************
' Purpose   :   validate a textbox's text
'               does full validation if the argument KeyLevel is False
'
' Accepts   :   ctrl        : Control containing Text to filter
'               Valuetype   : one of VbVarType types
'               KeyLevel    : if True only valid chars are checked. Use this in a
'                             change event
'                             if False full validation. Use this in a Validate event
' Returns   :   retval      : True if the current value is correct
'************************************************************************************
'
Function Validate(ctrl As Control, Valuetype As VbVarType,
                  Optional ByVal KeyLevel As Boolean) As Boolean
   
    Dim Text As String
    Dim i As Integer
    Dim validTbl As String
    Dim value As Variant
   
    ' determine valid chars
    Select Case Valuetype
        Case vbInteger, vbLong
            validTbl = "-0123456789"
        Case vbSingle, vbDouble, vbCurrency
            validTbl = "-0123456789.,"
        Case vbDate
            validTbl = "0123456789."
    End Select
   
    Text = ctrl.Text
   
    ' check that all chars are valid
    For i = 1 To Len(Text)
        If Len(validTbl) > 0 And _
           InStr(1, validTbl, Mid$(Text, i, 1), vbTextCompare) = 0 Then
            ' this char isn't in the list of valid ones
            Exit Function
        End If
    Next
   
    ' check that the sign symbol and the
    ' decimal separator are OK
    Select Case Valuetype
        Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency
            ' the sign symbol can only be the first char
            If InStrRev(Text, "-") > 1 Then Exit Function
            ' there can't be two or more decimal symbols
            If Text Like "*,*,*" Then Exit Function
        Case vbDate
            ' there can't be more than two date separators
            If Text Like "*.*.*.*" Then Exit Function
            ' there can't be two consecutive date separators
            If Text Like "*..*" Then Exit Function
            ' the first char can't be a date separator
            If Text Like ".*" Then Exit Function
    End Select
   
   
    ' this is the end of tests that can be
    ' applied when the value isn't final
    If KeyLevel Then
        Validate = True
        Exit Function
    End If
   
    ' check if required field
    If Len(Text) = 0 Then
        ' return True
        Validate = True
        Exit Function
    End If
       
   
    ' try an assignment to a variable of the proper type
    On Error Resume Next
   
    Select Case Valuetype
        Case vbInteger
            value = CInt(Text)
        Case vbLong
            value = CLng(Text)
        Case vbSingle
            value = CSng(Text)
        Case vbDouble
            value = CDbl(Text)
        Case vbCurrency
            value = CCur(Text)
        Case vbDate
            value = CDate(Text)
        Case Else
            ' no further validation is required
            value = Text
    End Select
   
    ' if value is still empty, exit
    If IsEmpty(value) Then Exit Function
   
    If Valuetype = vbDate Then
        If Format(value, "dd.mm.yyyy") <> Text And _
           Format(value, "dd.mm.yy") <> Text Then
            Exit Function
        End If
    End If
   
    ' if all tests pass, return true
    Validate = True
   
End Function

MSDN
Currency variables are stored as 64-bit (8-byte) numbers in an integer format, scaled by 10,000 to give a fixed-point number with 15 digits to the left of the decimal point and 4 digits to the right. This representation provides a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807.
-----
You can use the added test to make my code block test for the value against the range of the Currency data type:

Private Sub Text1_KeyPress(KeyAscii As Integer)
If KeyAscii = 8 Then
   Exit Sub
Else
   If KeyAscii = 44 Then
      KeyAscii = 44
      Exit Sub
   End If
   If KeyAscii = 46 Then KeyAscii = 0
   If KeyAscii < 48 Then KeyAscii = 0
   If KeyAscii > 57 Then
       
       KeyAscii = 0
   End If
End If

 ' This test checks for the Currency data type range
If Val(Text1) > 922337203685478# Then
    MsgBox "Value is too high." 'Replace with what you want
End If
End Sub

This fits your criteria.
RichW