Help with Validation Rule/Format

[gnarpeggio]

Hello,

I'm starting out on a new database for my staff to create labels for correspondence folders, and I'm in need of some help.

I have an unbound field that I want a command button to evaluate to check the field before moving onto the report. My first idea was to use a validation rule, but this would be for multiple rules in the same field, which I wasn't sure I could do. I'm trying to get the DB to only accept the following types:

A###### - The letter "A" followed by six digits
D######R - The letter "D" followed by six digits, then the letter "R"
L###### - The letter "L" followed by six digits
WW#### - Two "W" letters followed by 4 digits

I figured using the ElseIf method would work as well, but I'm having difficulty preparing the code:

Sub cmdCreateLabel_Click()

Dim strIncorrect As String

strIncorrect = MsgBox "Please enter only the following: [format examples]" vbOkOnly

If txtAppID (Does Not Equal A######) Then
Msgbox strIncorrect
txtAppID.SetFocus
ElseIf
... Repeat for other types ...
End If

End Sub

My last idea was using the Format property in VBA, but the DB recognized "A" as a literal character.

Any ideas?

Thanks!

 

[Mile-O]

You may want to look at the idea of use the KeyPress event to determine what the user is inputting. But that could get complicated.

If all you're after is some functional code that will check for these four, then how about this:

Private Sub cmdCreateLabel_Click()

    If Not ValidateField(Me.[i]YourTextBoxName[/i]) Then
        MsgBox "Please enter in the appropriate format(s).", vbExclamation + vbOkOnly, "Incorrect Format"
        Exit Sub
    End If

End Sub ' cmdCreateLabel_Click

Private Function ValidateField(ByVal strText As String) As Boolean

    If Len(strText) = 7 and Left(strText, 1) = "A" And IsNumeric(Mid(strText, 2, 6)) Then
        ValidateField = True
    Else If Len(strText) = 8 and Left(strText, 1) = "D" And IsNumeric(Mid(strText, 2, 6)) And Right(strText, 1) = "R" Then
        ValidateField = True
    Else If Len(strText) = 7 and Left(strText, 1) = "L" And IsNumeric(Mid(strText, 2, 6)) Then
        ValidateField = True
    Else If Len(strText) = 6 And Left(strText, 2) = "WW" And IsNumeric(Mid(strText, 3, 4)) Then
        ValidateField = True
    Else
        ValidateField = False
    End If

End Function ' ValidateField

 

[vbaInet]

Or you could try:

If Not ((Me.txtAppID Like "A######") Or (Me.txtAppID Like "A######R") Or _
        (Me.txtAppID Like "L######") Or (Me.txtAppID Like "WW####")) Then
    MsgBox strIncorrect
    txtAppID.SetFocus
End If

 

[vbaInet]

I should have made that slightly shorter (in code):

If Not ((Me.txtAppID Like "[COLOR=Red][AL][/COLOR]######") Or (Me.txtAppID Like "A######R") Or (Me.txtAppID Like "WW####")) Then
    MsgBox strIncorrect
    txtAppID.SetFocus
End If

 

[Mile-O]

Ha. I'm a bit rusty. So used to using Like with both * and ? wildcards, that I never thought of using #. Don't think I ever have, perhaps because such a situation has never presented itself.

 

[vbaInet]

It's been a long time since you touched anything Access right Mile-O?

 

[Mile-O]

It's been a long time since you touched anything Access right Mile-O?

Publically, yes.

 

[gnarpeggio]

Ok, so here's what I got based on vbaInet's suggestion (though I'm not done ruling Mile-O's out!)

Private Sub cmdSaveButton_Click()

Dim strIncorrect As String

strIncorrect = MsgBox("The Label ID must be one of the following formats:" & vbCrLf & vbCrLf & _
"Applications: A012345" & vbCrLf & _
"Livestocks: L012345" & vbCrLf & _
"Small Domestics: D012345R" & vbCrLf & _
"Wastewaters: WW0123", vbOKOnly + vbInformation)

'Data Validation Structure

If Not (Me.txtAppID Like "A######") Then
MsgBox strIncorrect
txtAppID.SetFocus
Else
Exit Sub
End If
End Sub

So, the MsgBox is being shown when the ID doesn't match A######, but a second MsgBox appears after strIncorrect that simply says "1" with a vbOKonly button. Is there some way to stop this from displaying? I wonder if I just forgot to clean up something along the way.

Thanks!

 

[vbaInet]

    [COLOR=Blue]strIncorrect [/COLOR]= [COLOR=Red]MsgBox[/COLOR]("The Label ID must be one of the following formats:" & vbCrLf & vbCrLf & _

    If Not (Me.txtAppID Like "A######") Then
        [COLOR=Red]MsgBox[/COLOR] [COLOR=Blue]strIncorrect[/COLOR]
        txtAppID.SetFocus
    End If

Look at the highlighted parts. Can you see what you're doing wrong?

By the way, the criteria I gave you in my last post covers all four scenarios.

 

[gnarpeggio]

After working on the problem for a minute, I came up with another solution which involved altering the beginning of the IF:

If Me.txtAppID = Not (Me.txtAppID Like "A######")

This removed the extra message I was getting, but was the reason simply because I used the MsgBox call twice?

And BTW, the other 3 scenarios were yet to be added; I just needed the first as an example

 

[vbaInet]

I gave you exactly what you need in post #4. All you need to do is put the MsgBox within the IF statement. That's it! What you've done above is incorrect but your reasoning is almost right.