Validation Text Format (1 Viewer)

[stu_c]

Hi all
I have a textbox named Staff Number the numbers are one letter followed by 6 digits how to I do a popup window to stay it must be the letters G F or R followed by 6 numbers, if its not done correctly a message box pops up?

 

[bob fitz]

Validation is best done in the form's Before Update event. Try:

If (Not Left([YourField], 3) = "G F " And Not IsNumeric((Replace(Right([YourField], 6), " ", "A"))) And Len(Right([YourField], 6)) = 6) Or (Not Left([YourField], 1) = "R " And Not IsNumeric((Replace(Right([YourField], 6), " ", "A"))) And Len(Right([YourField], 6)) = 6) Then
    MsgBox "Staff Number wrong"
    Cancel = True
End If

EDIT:
Sorry, but after further testing this does NOT work. I will post back if/when I have something else to offer

 

[bob fitz]

This works for me in the form's BeforeUpdate event. Try:

If ((Left([YourField], 4) = "G F " And Len([YourField]) = 10 And IsNumeric((Replace(Mid([YourField], 5, Len([YourField]) - 5), " ", "A"))) Or ((Left([YourField], 2) = "R " And Len([YourField]) = 8 And IsNumeric((Replace(Mid([YourField], 3, Len([YourField]) - 3), " ", "A"))))))) Then   ' Or Not Len(Right([YourField], 6)) = 6)) Then
Else
    MsgBox "Staff Number wrong"
    Cancel = True
End If

 

[Gasman]

I read the initial post as either a G, F or R ? followed by 6 numerics ?

So test for each part seperately.?

 

[bob fitz]

I read the initial post as either a G, F or R ? followed by 6 numerics ?

So test for each part seperately.?

Well spotted Gasman. After your post and reading it again I think you're probably right. Perhaps I should be in a forum that helps with reading skills .
I will alter the code and post back.

 

[strive4peace]

Hi all
I have a textbox named Staff Number the numbers are one letter followed by 6 digits how to I do a popup window to stay it must be the letters G F or R followed by 6 numbers, if its not done correctly a message box pops up?

hi Stu,

you can use InputMask to specify 1 letter and 6 numbers
L000000

TextBox.InputMask property (Access)
https://docs.microsoft.com/en-us/office/vba/api/Access.TextBox.InputMask

and then in the control BeforeUpdate event, test the letter to make sure it is one of the letters you allow

if not, give the user a message and Cancel the update. I'll write some quickie code in just a minute

 

[bob fitz]

If Gasman's comments in post #4 are correct then the code would be:

If ((Left([YourField], 2) = "G " And Len([YourField]) = 8 And IsNumeric((Replace(Mid([YourField], 3, Len([YourField]) - 3), " ", "?"))) Or Left([YourField], 2) = "F " And Len([YourField]) = 8 And IsNumeric((Replace(Mid([YourField], 3, Len([YourField]) - 3), " ", "?"))) Or ((Left([YourField], 2) = "R " And Len([YourField]) = 8 And IsNumeric((Replace(Mid([YourField], 3, Len([YourField]) - 3), " ", "A"))))))) Then   ' Or Not Len(Right([YourField], 6)) = 6)) Then

Else
    MsgBox "Staff Number wrong"
    Cancel = True
End If

This tests for the letters G,F or R followed by a space and 6 figures.

crystals offering in post #6 would appear to be a much easier solution.

 

[strive4peace]

hi Stu,

The InputMask will reduce the checking you have to do.

if you want characters to be converted to uppercase, you can add ">" ... this is not a placeholder. Likewise "<" makes them all lowercase
>L000000

StatusBarText (what displays in lower left corner) for this control might be:
1 Letter (G, F, or R) followed by 6 digits

here is code you can put in the control BeforeUpdate event to test the character

Private Sub MyCode_BeforeUpdate(Cancel As Integer)
'200423 strive4peace
' InputMask = >L000000 (convert to upper case)
'1 Letter (G, F, or R) followed by 6 digits
   'dimension variable
   Dim sChar As String * 1 '1-character string
   'use the MyCode textbox (or whatever your NAME for it is)
   With Me.MyCode
      'let them out, for now
      'if this is required, use Form BeforeUpdate to make sure it is filled
      If IsNull(.Value) Then
         Exit Sub
      End If
      sChar = Left(.Value, 1)
   End With
   'validate the letter
   Select Case sChar
   Case "G", "F", "R" 'ok!
   Case Else
      'character isn't acceptable
      MsgBox "Entry must be 1 letter (G, F, or R) followed by 6 digits" _
         , , "Invalid entry"
      'cancel the update
      Cancel = True
   End Select
End Sub

MyCode = name of the control -- yours will probably be different.

 

[Gasman]

Dim sChar As String * 1 '1-character string

Wow!, I never knew you could specify the size of a string in VBA.

 

[strive4peace]

Dim sChar As String * 1 '1-character string

Wow!, I never knew you could specify the size of a string in VBA.

thanks, Gasman. I learned that back when Access came with books! (which I read cover-to-cover) ... when dinosaurs were still around

 

[stu_c]

hi all thanks for all the comments! I haven't been ignoring unfortunately work has been very busy

Strive, thanks for the code, should this not be in the TEXTBOX001 after update?

 

[strive4peace]

hi all thanks for all the comments! I haven't been ignoring unfortunately work has been very busy

Strive, thanks for the code, should this not be in the TEXTBOX001 after update?

you're welcome, Stu

no, AFTERupdate is too late -- if you are going to validate in the control

BEFOREupdate can be cancelled. Alternately, you can validate on the form BeforeUpdate event, which can also cancel saving the record -- but it makes better sense to trap the error as soon as it happens, in the CONTROL BeforeUpdate event -- so just after the user enters it, but before they move out of the control.

 

[strive4peace]

ps, Stu

Note this:

      'if this is required, use Form BeforeUpdate to make sure it is filled
      If IsNull(.Value) Then
         Exit Sub
      End If

which allows the user not to enter anything, or enter something and delete it -- at the control level.