If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Custom Validation Rules
In an access db is it possible to add vba code to provide customized
validation rules on a field ? For instance, say I I have an email field in a record, and so I want to write some code to check that the field contains a string of form string@string ? If so can anyone point me to some help on how to do it ? Regards Tony |
#2
|
|||
|
|||
To programatically set the Validation Rule property for a field named Email
in a table named Table1, try something like this: Currentdb.TableDefs("Table1").Fields("Fee").Valida tionRule = _ "Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))" -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TonyB" wrote in message ... In an access db is it possible to add vba code to provide customized validation rules on a field ? For instance, say I I have an email field in a record, and so I want to write some code to check that the field contains a string of form string@string ? If so can anyone point me to some help on how to do it ? Regards Tony |
#3
|
|||
|
|||
Hi Allen,
Thanks for the reply. I didn't explain myself clearly. I was looking for a general method of attaching some vba code to the field in a record so that when a new record is entered that this code can be run to check the data for that field is OK Basically I am trying to customize the validation rules you get in table design view. Since I posted I have been searching for info about this, and I think I have concluded this is probably not possible in a table, and that this should be done in the form used to enter the data into the table, rather than in the table. Would you agree ? Thanks Tony "Allen Browne" wrote in message ... To programatically set the Validation Rule property for a field named in a table named Table1, try something like this: Currentdb.TableDefs("Table1").Fields("Fee").Valida tionRule = _ "Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))" -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#4
|
|||
|
|||
Yes, you would not normally modify the ValidationRule property of a table
field once the database is in use, though you might need to create or clear a rule as part of a version update on a database that is already installed. If you have a Validation Rule in place in the field of the table or in the table itself, you don't need any code. If the validation is too complex to use the rule in the table, or if you want to give the user the chance to override it and make the entry anyway, then it makes sense to use the form events for this. Use the BeforeUpdate event of the control to validate one field (e.g. to make sure it is in range), or the BeforeUpdate event of the form to validate the record (e.g. to compare values between fields, or to ensure that an entry was made in a field.) Again, once you have this code in place, you would not normally need to change the code, unless you need it for a version update. Hope that's useful -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "TonyB" wrote in message ... Hi Allen, Thanks for the reply. I didn't explain myself clearly. I was looking for a general method of attaching some vba code to the field in a record so that when a new record is entered that this code can be run to check the data for that field is OK Basically I am trying to customize the validation rules you get in table design view. Since I posted I have been searching for info about this, and I think I have concluded this is probably not possible in a table, and that this should be done in the form used to enter the data into the table, rather than in the table. Would you agree ? Thanks Tony "Allen Browne" wrote in message ... To programatically set the Validation Rule property for a field named in a table named Table1, try something like this: Currentdb.TableDefs("Table1").Fields("Fee").Valida tionRule = _ "Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))" |
#5
|
|||
|
|||
Tony (and Allen)
Pardon my intrusion... In Access/JET, the tables have no "triggers" (unlike SQL Server). If you'll be using JET as your back-end for your data, you'll want to do the (more complex) validation via the forms. It is still possible to add a validation rule to fields in a table, but only one, and only if it doesn't relate to other fields. You can also set ONE table-level validation rule. The consensus is that form-level validation testing is much more robust when you are using Access/JET. -- Good luck Jeff Boyce Access MVP "TonyB" wrote in message ... Hi Allen, Thanks for the reply. I didn't explain myself clearly. I was looking for a general method of attaching some vba code to the field in a record so that when a new record is entered that this code can be run to check the data for that field is OK Basically I am trying to customize the validation rules you get in table design view. Since I posted I have been searching for info about this, and I think I have concluded this is probably not possible in a table, and that this should be done in the form used to enter the data into the table, rather than in the table. Would you agree ? Thanks Tony "Allen Browne" wrote in message ... To programatically set the Validation Rule property for a field named in a table named Table1, try something like this: Currentdb.TableDefs("Table1").Fields("Fee").Valida tionRule = _ "Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))" -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
#6
|
|||
|
|||
As always, Jeff, your comments are welcome.
I'm interested in your suggestion that code is more robust? That's not my experience. I strongly prefer the engine-level rules where they are suitable, because it takes a load off me as developer to remember to enforce them regardless of how the data is added (e.g. action query). So, in every database I use: - enforced RI on most foreign keys; - Required property of appropriate fields; - AllowZeroLength set to No for almost all text fields; - Field-level Validation Rules for range checking; - Table-level Validation for comparison between fields. The fact that there's only one table-level rule has never bothered me: you can combine rules with AND, OR, etc, with appropriate bracketing. Certainly, the form-level code is more powerful, and the only choice for warnings that you allow the user to override. And I will admit to running Form-level code for most of the cases above as well, because it seems good practice to avoid unnecessary write-attempts. Maybe that's what you were referring to? Overall, though, the idea of engine-level validation for the simple and crucial rules is very appealing. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Jeff Boyce" -DISCARD_HYPHEN_TO_END wrote in message ... Tony (and Allen) Pardon my intrusion... In Access/JET, the tables have no "triggers" (unlike SQL Server). If you'll be using JET as your back-end for your data, you'll want to do the (more complex) validation via the forms. It is still possible to add a validation rule to fields in a table, but only one, and only if it doesn't relate to other fields. You can also set ONE table-level validation rule. The consensus is that form-level validation testing is much more robust when you are using Access/JET. -- Good luck Jeff Boyce Access MVP "TonyB" wrote in message ... Hi Allen, Thanks for the reply. I didn't explain myself clearly. I was looking for a general method of attaching some vba code to the field in a record so that when a new record is entered that this code can be run to check the data for that field is OK Basically I am trying to customize the validation rules you get in table design view. Since I posted I have been searching for info about this, and I think I have concluded this is probably not possible in a table, and that this should be done in the form used to enter the data into the table, rather than in the table. Would you agree ? Thanks Tony "Allen Browne" wrote in message ... To programatically set the Validation Rule property for a field named in a table named Table1, try something like this: Currentdb.TableDefs("Table1").Fields("Fee").Valida tionRule = _ "Is Null Or ((Like ""*?@?*.?*"") And (Not Like ""*[ ,;]*""))" |
#7
|
|||
|
|||
Allen
I'll wax philosophical for a moment... I trust RI to the system, and don't try to handle this myself. Similarly, zls and "required" settings I handle in the table. But I find that much of the field-level validation I need to enforce loosely falls into the category of "business rules". In the interest of keeping those separated from the actual data (?n-level? design), I place these in the forms. Enough with the philosophy, where's Doug S. -- I think he owes me a dark, chewey beer! Jeff Boyce Access MVP "Allen Browne" wrote in message ... As always, Jeff, your comments are welcome. I'm interested in your suggestion that code is more robust? That's not my experience. I strongly prefer the engine-level rules where they are suitable, because it takes a load off me as developer to remember to enforce them regardless of how the data is added (e.g. action query). So, in every database I use: - enforced RI on most foreign keys; - Required property of appropriate fields; - AllowZeroLength set to No for almost all text fields; - Field-level Validation Rules for range checking; - Table-level Validation for comparison between fields. The fact that there's only one table-level rule has never bothered me: you can combine rules with AND, OR, etc, with appropriate bracketing. Certainly, the form-level code is more powerful, and the only choice for warnings that you allow the user to override. And I will admit to running Form-level code for most of the cases above as well, because it seems good practice to avoid unnecessary write-attempts. Maybe that's what you were referring to? Overall, though, the idea of engine-level validation for the simple and crucial rules is very appealing. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Custom Markers | Jerez_z | General Discussion | 12 | June 14th, 2007 06:06 PM |
Recovering Custom Forms After Additions Have Been Made To It? | Elizabeth | Contacts | 1 | August 11th, 2004 07:44 PM |
IF formula with VALIDATION Rules | LSTOOPS | Worksheet Functions | 2 | January 5th, 2004 04:48 PM |