Need help with this code (1 Viewer)

[Jon123]

In the Not in List of a Combo box I have this code and it work to add the value with 1 problem the Part number field has an input mask 0000\-99999;;_
the Part Number are all 0000-00000 4 digits - 5 digits but this code drops the - and makes it a 9 digit number. What do I need to do to make it follow the input mask?

intAnswer = MsgBox("This Part " & Chr(34) & NewData & Chr(34) & " is not currently entered." & vbCrLf & "Would you like to add it to the list now?", vbQuestion + vbYesNo, "CCH Auxiliary")

If intAnswer = vbYes Then
strSQL = "INSERT INTO [Tble-Parts History]([Part Number]) " & "VALUES ('" & NewData & "');"

thanks for the help
jon

 

[rapsr59]

Hi,

This works.

I set the Input Mask for the Part Number in the Tble-Parts History table like this: 0000\-00000;0;_

Then I used the following code in the combo box "On Not in List Event:

    Dim intAnswer As Integer
    Dim strsql As String
 
    'Make sure the user entered a hyphen - also you might want to add
    ' a little more code to make sure that the values that are entered
    ' by the user are numeric.
    If Len(NewData) = 9 Then
        NewData = Left(NewData, 4) & "-" & Right(NewData, 5)
    End If
 
    intAnswer = MsgBox("Part Number " & Chr(34) & NewData & Chr(34) & _
    " does not exist!" & vbCrLf & _
    "Would you like to add it to the " & _
    "list of parts?", vbQuestion + vbYesNo, "CCH Auxiliary")
 
    If intAnswer = vbYes Then
        strsql = "INSERT INTO [Tble-Parts History]([Part Number]) " & _
        "VALUES ('" & NewData & "');"
    End If
 
    DoCmd.RunSQL strsql

Regards,

Richard