VALIDATION RULE IN FORM

[Guest]

I have the following validation rule in a form:

Not Like "*Apartment*" And Not Like "*Avenue*" And Not Like "*Boulevard*" And Not Like "*clr*" And Not Like "*Circle*" And Not Like "*crt*" And Not Like "*Court*" And Not Like "*Drive*" And Not Like "*Lane*" And Not Like "*Parkway*" And Not Like "*Place*" And Not Like "*P.O. Box*" And Not Like "*POBox*" And Not Like "*Post Office Box*" And Not Like "*Road*" And Not Like "*Room*" And Not Like "*Rural*" And Not Like "*Route*" And Not Like "*Street*" And Not Like "*Suite*" And Not Like "*Trail*" And Not Like "*trl*" And Not Like "*.*" And Not Like "*,*"

Works just fine but the problem is if I need to delete the entire line entry the validation rule keeps popping up. I've tried IsNull or Not like at the beginning but it doesn't seem to work very well.

Can someone please tell me what I'm doing wrong.

Thanks

 

[Albert D. Kallal]

I am going to suggest you move that big huge rule out into some code.

I always felt that when some code or rule gets REALLY messy, then it is time
to write some code, and clear up this huge thing.

Just tring to maintin that list is a lot of work, and VERY hard to edit. In
fact, why not just make a table called tblIgnore?

Make a table with ONE field. Simply entry all of your ignore list. The
beatury of this system is it will be VERY easy to maintain, and you can add
to that list over time..adn NOT have to modify the code. (you can even make
a cute screen for your users, or the admin of the system..and they then can
change this list.

Furhter, if you have another screen, or address field else where in your
system, then you can re-use the list.

So, type in that list into a table.

We then use the contorls before update event, and write some code:

dim rstBadList as dao.RecordSet
dim bolBad as boolean

if isnull(me.Address) = false then

set rstBadList = currentdb.OpenRecordSet("tblIgnoreList")
do while rstBadList.EOF = false
strMatch = "*" & rstBadList(0) & "*"
bolBad = me.address match strMatch
if bolBad = True then
exit while
end if
strCList = "Apartment,Avenue,Boulevard,clr,Circle,ctr,Court

Apartment,Avenue,Boulevard,clr,Circle,crt,,Cour

Drive,Lane,Parkway,Place,P.O. Box,POBox,Post Office
Box,Road,Room,Rural,Route,Street,
Suite,Trail,trl,.,*"

Works just fine but the problem is if I need to delete the entire line

entry the validation rule keeps popping up. I've tried IsNull or Not like
at the beginning but it doesn't seem to work very well.

 

[Albert D. Kallal]

Opps..bumped send....

Here is the code:

Private Sub Address_BeforeUpdate(Cancel As Integer)

Dim rstBadList As dao.Recordset
Dim bolBad As Boolean
Dim strMatch As String

If IsNull(Me.Address) = False Then

Set rstBadList = CurrentDb.OpenRecordset("tblIgnoreList")
Do While rstBadList.EOF = False
strMatch = "*" & rstBadList(0) & "*"
bolBad = Me.Address Like strMatch
If bolBad = True Then
MsgBox "Bad text in address = " & rstBadList(0) & _
vbCrLf & "Please re-edit", vbExclamation, "Bad data
entry"
Cancel = True
Exit Do
End If
rstBadList.MoveNext
Loop
rstBadList.Close
Set rstBadList = Nothing
End If

End Sub

 

[Guest]

Now that's awesome. Thanks. I just have a few questions if I may.

Do I need to setup a primary key field in the table. Also, on the validation rule I had text that told the user what the acceptable abbreviations are. Is there a way to also do that with this?

 

[Albert D. Kallal]

Now that's awesome. Thanks. I just have a few questions if I may.

Do I need to setup a primary key field in the table.

No you don't. But, on the other hand, why not make the single field the
primary key, after all, you NEVER would want to enter the same "bad" text
twice..would you? So, you can open up this little table in design mode, and
while the field is highlighted..whack the little "key" at the top of the
screen. So, you don't have to..but it makes sense to do so.

Also, on the validation rule I had text that told the user what the

acceptable abbreviations are. Is there a way to also do that with this?

Hum, I am not sure what you mean by the above? Do you just want to show a
nice list in the error message?

I kind of assumed that you are talking about a address type field?. I mean,
if you need to limit choices..then use a combo box..right?

If you are talking about a free text field like address, then you will have
to include every word in the English language..right?

I might not be understanding this question? (fell free to expand on this?).

Perhaps you want some code to "show" a list of abbreviations that are
allowed? I would consider some type of popup form.

You *can* display the list in the help box that pops up..but I don't think
the msgbox is very good for more then about 10 to 15 lines.

(I can show you how to display a bunch of text in the msgbox that pops
up..just not sure if that is what you want)

 

[Guest]

You've been extremely helpfull.

Didn't think about making the field the primary key field so I will do that today.

On the other item you pretty much got what I was saying. But let me provide an example.

Let's say someone types in the word Boulevard. Currently with your new design it works great and tells the user the word not allowed is Boulevard. I think that's awesome. Sorry get excited over little things.

Ok let's say the user types in Boulevard and the error message comes up telling them that Boulevard is inappropriate and the correct abreviation would be Blvd?

Would I just need to setup another field in the ignore list table and just somehow reference the inappropriate word and provide the acceptable abreviation?

Thanks so much for you help.

 

[Albert D. Kallal]

Ok let's say the user types in Boulevard and the error message comes up

telling them that Boulevard is inappropriate and the correct abreviation
would be Blvd?

Would I just need to setup another field in the ignore list table and just

somehow reference the inappropriate word and provide the acceptable
abreviation?


That is a excleent idea on your part!. And, yes..just adding another collum
to that bad list is good idea.

(in fact, you might even consider make msgbox "offer" to replace the text
for you! (but lets not get too ahead of ours selves just yet))

So, lets assume two collums, and the 2nd collum is the "correct" or your
suggested text. You could use:

MsgBox "Bad text in address = " & rstBadList(0) & _
vbCrLf & "Please re-edit" & _
vbCrLf & "Suggested text = " & rstBadList(1),
vbExclamation, "Bad data

 

[Guest]

That is so awesome. It works. Thanks so much for the help. Your the best.