Validation Rule (1 Viewer)

[unclefink]

Might someone help me out with something I hope is a simple solution.

In my table I have a field where a user puts in a "case number". When typed, the case number should look something similar to this.

LL#NN-NNNN

L=Letter
N=Number

Also thinking about it, the last section of numbers may or may not require 4 numbers, this section indicates the number of cases in a calendar year, the fourth number would be used when the case count goes over 1,000.

Any help is appreciated.

 

[Addyman]

I'm not sure how you validate at a table level but if your users enter this case number on a form then you can validate their entry in the Text Box they enter the case number in. You could have a button for saving the record and then have something like this:

Dim strTest As String

strTest = Me.txtMyTextBox

If Len(strTest) = 9 Or Len(strTest) = 10 Then

    If Left(strTest, 2) Like "[a-z][a-z]" And Mid(strTest, 3, 1) = "#" And IsNumeric(Mid(strTest, 4, 2)) And Mid(strTest, 6, 1) = "-" And IsNumeric(Mid(strTest, 7)) Then
    
    MsgBox "Success"
    
    Else
    
    MsgBox "Fail"
    
    End If
    
Else

MsgBox "The String entered is the wrong length"

Exit Sub

End If

There is probably an easier way of doing this, but it works!!

Please note, in the test code above I have simply got the code to tell you if it is a success or fail. You would want to change the Msgbox parts of the code to submit / save the record or whatever.

 

[unclefink]

I just tested it and it works exactly how it should. There is a problem though. I purposly entered data that would fail this rule and after clicking ok, it still let me change to the next cell without further errors. How do i make this not allow data in this particular field unless the code is passed. Thank you very much for the direction, much appreciated.

 

[boblarson]

Validate in the BEFORE UPDATE event (in this case it appears you want to use the control's before update) and then issue a

Cancel = True
Me.ControlNameHere.Undo

to cancel and blank the data for that control should it fail validation.

 

[unclefink]

Thank you gentlemen, I'm catching onto the whole access concept relatively quickly, at least until it involves vba coding.

I should go find a class on this concept.

So the code above in the "Before Update" on the form worked as indicated until it failed and then still allowed me to move onto the next cell.

Bob, where/how do i introduce your reccomendation into the picture?

Would it also be part of the code?

Thank you both in advance for the help and if you have any reccomendations for a class or book regarding vba coding for a step in the door that would also be appreciated.

 

[unclefink]

I did some further checking and found another way of doing this but i'm still running into another problem.

I put the following string in the input mask of the field for case number:

>LL"#"00"-"0009;;_

The problem with this is when I test the reports referencing this information, the # and - are missing; although they show up fine on the form.

When I check the report what Should Read AB#12-3456 is reading AB123456. Anyone have any suggestions to have the table save the entire mask rather than just the data manually entered?

 

[unclefink]

With persistence, i've managed to figure out what the issue was.

The mask I set for the table is LL"#"00"-"0009;;_ when creating masks, there is an option that I missed that asks if you want the mask also saved in the table with the data, I indirectly said no. When I slowed down and repeated the steps, I then saw that prompt and chose the correct desired option.

I then went to the cell inside the form for this specific table field and set the same mask with a > at the beginning to automatically capitalize the letters in the case number.

Waalaaa!!

Thanks again for the help you did offer, it's added to what little vba knowledge I have. I really need to learn it.