Code to check if entry is in the right format

[dedjloco]

I have the following code wich is for entering new Project Nr's in an table, with the use of a combo box.

Private Sub Project_Text_NotInList(NewData As String, Response As Integer)
    
    On Error GoTo Project_Text_NotInList_Err
     Dim intAnswer As Integer
     Dim strSQL As String
     intAnswer = MsgBox("The Project number " & Chr(34) & NewData & _
         Chr(34) & " is not currently listed." & vbCrLf & _
         "Would you like to add it to the list now?", vbQuestion + vbYesNo, "Kenz")
     If intAnswer = vbYes And [B][U]Me.Project_Text Like "P###?###"[/U][/B] Then
         strSQL = "INSERT INTO tblProductSpecs([Project Nr]) " & "VALUES ('" & NewData & "');"
         DoCmd.SetWarnings False
         DoCmd.RunSQL strSQL
         DoCmd.SetWarnings True
         MsgBox "The new Project number has been added to the list.", vbInformation, "Kenz"
         Response = acDataErrAdded
         Dim ctl As Control
        On Error Resume Next
        For Each ctl In Me.Controls
        ctl.Value = ctl.DefaultValue
        Next
        Set ctl = Nothing
        Me.CraneEx_Text = Replace(Me.CraneEx_Text.DefaultValue, Chr(34), "")
        Me.BoomEx_Text = Replace(Me.BoomEx_Text.DefaultValue, Chr(34), "")
        Me.Project_Text = Replace(Me.Project_Text.DefaultValue, Chr(34), "")
    AuxMacro
    TuggerMacro
    AMCMacro
    AHCMacro
    MOPSMacro
    ManridingMacro
    CTMacro
    TelescopicMacro
    ShockMacro
    AntiMacro
    DrivetypeMacro
    ProductTypeMacro_False
    SWLMacro_False
    BridgeMacro_False
    CompetitorsFieldMacro
    FilterMacro
    ScoreMacro
     ElseIf intAnswer = vbYes And [B][U]Not Me.Project_Text Like "P###?###"[/U][/B] Then
        MsgBox "This isn't a valid Project number.", vbInformation, "Kenz"
        Response = acDataErrContinue
     Else
        MsgBox "Please choose a Project number from the list.", vbInformation, "Kenz"
        Response = acDataErrContinue
     End If
Project_Text_NotInList_Exit:
     Exit Sub
Project_Text_NotInList_Err:
     MsgBox Err.Description, vbCritical, "Error"
     Resume Project_Text_NotInList_Exit
End Sub

Now the code works fine, but I added the highlighted part. But it always gives me the message "This isn't a valid Project number.". And it should just add the number when something is typed in like "P017V001". The P is always there and followed by 3 numbers, then a character and aigain 3 numbers. and when it's not like this it should give the message.

An side question can I add something to convert the characters to capitals?

 

[sneuberg]

The condition in the elseif looks the same as the condition in the if. You would want them to be different. As for the side question I believe StrConv will do what you want.

 

[dedjloco]

The difference is in the Not, so everything that is not that format will be rejected.
what is the difference between StrConv and Ucase? and where should I put it because I tried this:

[Project Nr] = UCase([Project Nr])

and this:

Me.Project_Text = UCase(Me.Project_Text)

But I found out this doesn't help because it will not convert it before it adds it to the table.

 

[sneuberg]

Sorry didn't see the Not. I suggest you add

Debug.Print Me.Project_Text
Debug.Print Me.Project_Text Like "P###?###"

before the if and post what you are getting in the Immediate Window

The variable NewData is being inserted in this function. Have you tried

NewData = UCASE(NewData)

before the INSERT statement?

UCASE should work. StrConv just offers more options.

 

[dedjloco]

I'm trying this later on so will update you when I have.

 

[Minty]

I'm intrigued by what all those procedure calls do, it seems like a lot...

 

[sneuberg]

I'm intrigued by what all those procedure calls do, it seems like a lot...

dedjloco uploaded his database in post 11 of this thread. It's interesting. I didn't dig into it much but I suspect it could be simplified.

 

[dedjloco]

dedjloco uploaded his database in post 11 of this thread. It's interesting. I didn't dig into it much but I suspect it could be simplified.

If you could look into it it would be great.
But I reviewed the application with some colleagues and got a lot of new ideas so a lot is going to change. And I will be put on another job next week so I think it will take a while before I will proceed with this.