Set validation rule for many fields with vba?

  • Thread starter Thread starter rocketD
  • Start date Start date
R

rocketD

Hello,

One of the functions of my database is to store survey data and allow
us to clean them before they are analyzed. Some of the data are
imported from another application, and some of the data must be
manually entered in a form I built for this purpose. I want to set
validation rules for 60+ fields, which are multiple choice questions.
I want the validation rule to be " <=4 Or Is Null ", as the possible
answers are 1-4 or blank. Does anyone know if and how I can do this
using vba, so I don't have to enter validation rules for each and
every item? I will have other surveys like this coming up, so a way
to do it would be great.

Thanks,
Dara
 
In table design you can create a field the way you want it (including the
validation rule etc), then select it (the 'record selector' to the left of
the field name), and then duplicate it with Copy (ctrl+C) and paste
(Ctrl+V), After renaming it, you can select the 2 together, copy'n'paste,
and so on.

It is also do it with VBA. The DAO library sets you create/set the
ValidationRule property. Sample code for setting a property via DAO:
http://allenbrowne.com/func-DAO.html#SetPropertyDAO

However, this is *seriously* the wrong way to design a survey database, and
it really troubles me that you way you have "other surveys like this coming
up." For the flexibility and queryability of your databases, I would
encourage you to spend some time examining what a correctly normalized
survey database looks like. There's a free sample from Duane Hookom (MS
Access MVP):
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3&SID=83c5543128be581a64c5cb58a76348bb
 
SQL Server allow you to define custom datatypes, and then you can
easily define a rule on the dataType, and then reuse it in a bunch of
different places

Technically, Access doesn't support anything like this

Microsoft stopped developing new features for Jet approximately 15
years ago.
 
a a r o n . k e m p f @ g m a i l . c o m said:
Microsoft stopped developing new features
for Jet approximately 15 years ago.
Click to expand...

Mr. Kempf, your assertion is absolutely untrue, and you do a dissservice to
users of this newsgroup by stating that untruth.

There have been updates to Jet with every release, and between in Service
Packs, and the ACE data engine, new with Access 2007, is a direct descendant
of Jet, as well.

Larry Linson
Microsoft Office Access MVP
 
In table design you can create a field the way you want it (including the
validation rule etc), then select it (the 'record selector' to the left of
the field name), and then duplicate it with Copy (ctrl+C) and paste
(Ctrl+V), After renaming it, you can select the 2 together, copy'n'paste,
and so on.

It is also do it with VBA. The DAO library sets you create/set the
ValidationRule property. Sample code for setting a property via DAO:
   http://allenbrowne.com/func-DAO.html#SetPropertyDAO

However, this is *seriously* the wrong way to design a survey database, and
it really troubles me that you way you have "other surveys like this coming
up." For the flexibility and queryability of your databases, I would
encourage you to spend some time examining what a correctly normalized
survey database looks like. There's a free sample from Duane Hookom (MS
Access MVP):
   http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3&SID=83...
Click to expand...

Thanks for your suggestions, Allen.

To clarify re: normalization, my "survey" database is not for people
taking surveys. We have two formats for each survey - web based and
paper based. The web based surveys are where we get most of our data,
but we use a software called Remark to design and administer the
survey. It also collects the survey response data, which we then have
to import into Access for storage and analysis. The paper formats
also use the Remark software, which reads scanned, completed paper
surveys, and we then have to import those data in the same fashion.
However, it cannot read comments and if the participant doesn't mark
the bubbles correctly, it won't read those, either. I designed a form
for us to modify these data, so we are only entering comments and the
occasional survey. The data coming from Remark will never violate
validation rules because they are built in when I design those
surveys, but for manual entry into the Access database, I want to make
sure someone can't accidentally enter 7 where the highest possible
value is 4. This form does not have the question options attached -
it's a waste of effort to do that, at 4-5 options per 60 questions. I
just want numbers, limited by valid answers.

When you are referring to normalization, do you think I should have a
lookup table of just numbers, (e.g., 1, 2, 3, 4) and limit to that?

Dara
 
Larry;

It still crashes, it hasn't gotten any new features.

Jet didnt' get _ANYTHING_ that ADP didn't in this last release.

-Aaron
 
message
Larry;

It still crashes, it hasn't gotten any new features.

Jet didnt' get _ANYTHING_ that ADP didn't in this last release.

-Aaron
 
Back
Top