re-useable validation rule

[Rondy]

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and we
WILL be updating SOON! but in the meantime if anyone can recall back that
far...

I have a validation rule that can be applied to the same field on various
forms, so I need to make it re-useable and have code in one spot for easy
admin. The rule is conditional based on data from switchboard, so putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*' or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

.... works like a charm! Problem is the Like list may need updating, so I
would prefer to have this in one place for admin and then refer to it on the
15 forms that can use it (rather than having to update 15 forms each time the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or have
the valid values in a table?

Thanks in advance for any help

 

[Jeff Boyce]

Rondy

(you are in good company here, then. why do you consider an Access
front-end connected to SQL-Server "antiquated"? Oh wait, I get it, A2K to
SQL ... if it runs and doesn't require a lot of maintenance, who cares how
old it is?!)

If you want to reduce your maintenance, don't use the list. Instead, create
a lookup table that holds the comparison values. Get a new value to check?
Add a new row to the table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Rondy]

Jeff... yes, right, a table would be better, but I'm having trouble figuring
out how to use the records as a validation string... Can you please point me
in the right direction? TIA.

Rondy

(you are in good company here, then. why do you consider an Access
front-end connected to SQL-Server "antiquated"? Oh wait, I get it, A2K to
SQL ... if it runs and doesn't require a lot of maintenance, who cares how
old it is?!)

If you want to reduce your maintenance, don't use the list. Instead, create
a lookup table that holds the comparison values. Get a new value to check?
Add a new row to the table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and we
WILL be updating SOON! but in the meantime if anyone can recall back that
far...

I have a validation rule that can be applied to the same field on various
forms, so I need to make it re-useable and have code in one spot for easy
admin. The rule is conditional based on data from switchboard, so putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*' or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

... works like a charm! Problem is the Like list may need updating, so I
would prefer to have this in one place for admin and then refer to it on
the
15 forms that can use it (rather than having to update 15 forms each time
the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or have
the valid values in a table?

Thanks in advance for any help

 

[Rondy]

Chris... I'm going to need to be able to add to the list on the fly for users
with .mde copies, so I really need to use a back-end table instead. Thanks
for your suggestion, though -- I will definitely use it if I can't make it
happen with a table.

To keep this simple you can create a public constant in a standard module
like this:

Public Const constBldgs As String = "Like '*area*' or '*bldg*' or
'*building*' or '*phase*' or '*room*' or '*site*'"

Any form can use it for text box validation rules like this:

Me.txtRegion.ValidationRule = constBldgs

Chris

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and we
WILL be updating SOON! but in the meantime if anyone can recall back that
far...

I have a validation rule that can be applied to the same field on various
forms, so I need to make it re-useable and have code in one spot for easy
admin. The rule is conditional based on data from switchboard, so putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*' or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

... works like a charm! Problem is the Like list may need updating, so I
would prefer to have this in one place for admin and then refer to it on the
15 forms that can use it (rather than having to update 15 forms each time the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or have
the valid values in a table?

 

[Jeff Boyce]

Rondy

One approach might be to create a procedure that:
* opens a recordset based on that lookup table
* iterates through the records, building a concatenated string (", *" &
YourRecordset!YourField & "*")
* after getting through all the table values and building the string,
"tacks on" any additional punctuation
* run a query you dynamically create, using the concatenated string in the
WHERE clause

Hopefully one of the other newsgroup readers can come up with a more elegant
solution...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff... yes, right, a table would be better, but I'm having trouble
figuring
out how to use the records as a validation string... Can you please point
me
in the right direction? TIA.

Rondy

(you are in good company here, then. why do you consider an Access
front-end connected to SQL-Server "antiquated"? Oh wait, I get it, A2K
to
SQL ... if it runs and doesn't require a lot of maintenance, who cares
how
old it is?!)

If you want to reduce your maintenance, don't use the list. Instead,
create
a lookup table that holds the comparison values. Get a new value to
check?
Add a new row to the table!

Regards

Jeff Boyce
Microsoft Office/Access MVP

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and
we
WILL be updating SOON! but in the meantime if anyone can recall back
that
far...

I have a validation rule that can be applied to the same field on
various
forms, so I need to make it re-useable and have code in one spot for
easy
admin. The rule is conditional based on data from switchboard, so
putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*'
or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

... works like a charm! Problem is the Like list may need updating, so
I
would prefer to have this in one place for admin and then refer to it
on
the
15 forms that can use it (rather than having to update 15 forms each
time
the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when
field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or
have
the valid values in a table?

Thanks in advance for any help

 

[John W. Vinson]

Is there a better way to do this?

Rather than making the user *type in* a text string and slapping their hand if
they type it wrong, how about just providing a combo box so they can select
from a list of valid values? You could base the combo on a query selecting the
appropriate subset of values based on the switchboard selection.

 

[Clifford Bass]

Hi Rondy,

Borrowing from Chris's idea: Create a table that will contain one row
with two columns.

tblSettings
Record_ID (integer, default = 1, validation rule = 1)
Like_Clause (text or memo)

Add your row with your like clause. Then use:

strSiteRef.ValidationRule = DLookup("Like_Clause", "tblSettings")

Update Like_Clause as needed when information changes.

However, John's point is good. How about having them select the area
type in a combo box separately from the reference?

Clifford Bass

 

[Paul Shapiro]

It looks like your rule is verifying valid SiteTypes for a Site or Job? If
so, then Site.siteTypeCode should be a FK from a SiteType table, or
Job.siteTypeCode or Job.jobTypeCode should be a FK from another table.
Depending on the complexity of your validation rule, you could have a
SiteType.isAcceptableForSingleSiteJob attribute or you might need a
JobTypeSiteTypeAllowed table. Now your validation rule is either enforced
automatically be enforcing relationships, or with very simple code that
verifies the value of the SiteType.isAcceptableForSingleSiteJob attribute.

I might have guessed quite wrongly about your data structure and your rules,
so if this isn't close enough, please post back with the data structure
details and rule requirements.

 

[Rondy]

John... this is a field where users need to be able to type almost anything
they want -- I just need to make sure that they include one of the words in
my validation rule. I don't slap hands, I put controls in place to make sure
the data makes sense.

 

[Rondy]

Paul... you are half right. I need to be sure users input data that relates
to a particular area or jobsite, but they need to be able to type anything at
all. Could be Area B, Parking Lot, Detention Pond, Phase 3, Building 20,
Room 299... you get the idea. They need to be specific, and info is specific
to (and changes for) each project, so the field can't be limited. The
validation rule will need to be adjusted many times and will apply to many
different forms, which is why it needs to be re-useable and easily
administered.

A table is going to be the best way to handle this issue because I will need
to be able to add to the list so users have the "new" rule instantly (in lieu
of having it in my design copy where I would then have to re-create my
distributable .mde files and users would have to get the new update, every
single time it changes).

I just need to wrap my head around how to turn the table data into a
validation string. Or actually, Clifford's idea sounds like the most
promising... I hope it works as easily as it reads in code!

It looks like your rule is verifying valid SiteTypes for a Site or Job? If
so, then Site.siteTypeCode should be a FK from a SiteType table, or
Job.siteTypeCode or Job.jobTypeCode should be a FK from another table.
Depending on the complexity of your validation rule, you could have a
SiteType.isAcceptableForSingleSiteJob attribute or you might need a
JobTypeSiteTypeAllowed table. Now your validation rule is either enforced
automatically be enforcing relationships, or with very simple code that
verifies the value of the SiteType.isAcceptableForSingleSiteJob attribute.

I might have guessed quite wrongly about your data structure and your rules,
so if this isn't close enough, please post back with the data structure
details and rule requirements.

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and we
WILL be updating SOON! but in the meantime if anyone can recall back that
far...

I have a validation rule that can be applied to the same field on various
forms, so I need to make it re-useable and have code in one spot for easy
admin. The rule is conditional based on data from switchboard, so putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*' or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

... works like a charm! Problem is the Like list may need updating, so I
would prefer to have this in one place for admin and then refer to it on
the
15 forms that can use it (rather than having to update 15 forms each time
the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or have
the valid values in a table?

Thanks in advance for any help

 

[Rondy]

Paul... you are half right. I need to be sure users input data that relates
to a particular area or jobsite, but they need to be able to type anything at
all. Could be Area B, Parking Lot, Detention Pond, Phase 3, Building 20,
Room 299... you get the idea. They need to be specific, and info is specific
to (and changes for) each project, so the field can't be limited. The
validation rule will need to be adjusted many times and will apply to many
different forms, which is why it needs to be re-useable and easily
administered.

A table is going to be the best way to handle this issue because I will need
to be able to add to the list so users have the "new" rule instantly (in lieu
of having it in my design copy where I would then have to re-create my
distributable .mde files and users would have to get the new update, every
single time it changes).

I just need to wrap my head around how to turn the table data into a
validation string. Or Clifford's idea sounds most promising... I hope it
works as easily as it reads in code!

It looks like your rule is verifying valid SiteTypes for a Site or Job? If
so, then Site.siteTypeCode should be a FK from a SiteType table, or
Job.siteTypeCode or Job.jobTypeCode should be a FK from another table.
Depending on the complexity of your validation rule, you could have a
SiteType.isAcceptableForSingleSiteJob attribute or you might need a
JobTypeSiteTypeAllowed table. Now your validation rule is either enforced
automatically be enforcing relationships, or with very simple code that
verifies the value of the SiteType.isAcceptableForSingleSiteJob attribute.

I might have guessed quite wrongly about your data structure and your rules,
so if this isn't close enough, please post back with the data structure
details and rule requirements.

(Sorry if dup, window went blank when I hit Post!)

Working in A2K with linked SQL tables -- I know, it's antiquated, and we
WILL be updating SOON! but in the meantime if anyone can recall back that
far...

I have a validation rule that can be applied to the same field on various
forms, so I need to make it re-useable and have code in one spot for easy
admin. The rule is conditional based on data from switchboard, so putting
code in the form's Open Event, like so:

Private Sub Form_Open(Cancel As Integer)
If Forms!frmSwitchboard!Site.ListCount < 2 Then
strSiteRef.ValidationRule = "Like '*area*' or '*bldg*' or '*building*' or
'*phase*' or '*room*' or '*site*'"
strSiteRef.ValidationText = "Please reference an Area, Phase, Building,
Room, etc. for single-site jobs."
End If
End Sub

... works like a charm! Problem is the Like list may need updating, so I
would prefer to have this in one place for admin and then refer to it on
the
15 forms that can use it (rather than having to update 15 forms each time
the
list changes).

I tried creating a macro and setting it to field's BeforeUpdate (If
Forms!frmSwitchboard!Site.ListCount < 2 Then strSiteRef.BeforeUpdate =
"SiteVal" [macro name]), but it still gives validate error even when field
contains one of the appropriate words.

Is there a better way to do this? Maybe create a Public Function, or have
the valid values in a table?

Thanks in advance for any help

 

[Rondy]

Clifford... Awesome solution! Works like a charm and solves all the admin
issues. Thank you so much!!

And thanks everyone else, too, for all your suggestions. Have a wonderful
day!

 

[Rondy]

Clifford... Awesome solution! Works like a charm and solves the admin
headaches. Thank you so much!!

And thanks to everyone else who offered suggestions... hope you all have a
wonderful day!

 

[Rondy]

Clifford... Awesome solution! Works like a charm and solves the admin
headaches. Thank you so much!!

And thanks to everyone else who offered suggestions... hope you all have a
wonderful day!

 

[Rondy]

Sorry for all the duplicate replies this morning... Watchguard kept throwing
up an error when I clicked post... Stupid Firewall! LOL

 

[Clifford Bass]

Hi Rondy,

Glad to hear it. You are welcome!

Clifford Bass