Validation Rule Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an append query that will not work and causes all the records to be
rejected because of validation rules. This is the SQL view of the query I
bult with the query builder.

INSERT INTO PHYSICIAN ( physician_id )
SELECT DOCLIST_Working.License
FROM DOCLIST_Working
WHERE (((DOCLIST_Working.License) Is Not Null));

Based on reading other posts here, the properties of "physician_id" are:
Required-NO; Allow Zero Lenght-YES; Indexed-YES (Duplicates OK). The same
properties of "license" are: NO; NO; NO, respectively. The datatype of both
fields are the same as Text. The only difference I can see is that "license"
has a Size of 15 and "physician_id" has 50.

There are other fields in the Physician table that are required. Do these
need to be set to No as well? If not, I am at a loss as to what to look for.

Thanks.
 
Martin

I believe you've hit on it. If you have a "required" field in a table and
you don't append a value for that field, you'll be violating the validation
rule for that field/record.

Any chance you can set a default value for the field? That way, any new
record would receive the default value and wouldn't have to be explicitly
appended/inserted.

Regards

Jeff Boyce
<Access MVP>
 
Based on your repsonse, I changed the Required property on the other fields
and the append now works as it should.

It seems strange that the query is apparantly looking at all fields of the
record even though I am affecting only some of the fields. It seems like it
should only be concerned about the fields that are involved in the query.
Oh, well!!!
 
Martin

Your query IS only concerned with the fields it contains. It is your TABLE
that's complaining about validation rules.

Regards

Jeff Boyce
<Access MVP>
 
Back
Top