Append Error In Form

[Delano]

I am using Access 2000. I have a combo box that looks up values in a table
and auto fill other fields in the form using the following code placed in the
“After Update†field in the combo box:

Me.Emp_ID = Me.Combo0.Column(0)
Me.Emp_Last = Me.Combo0.Column(1)
Me.Emp_First = Me.Combo0.Column(2)
Me.Emp_Middle = Me.Combo0.Column(3)
Etc.,

In addition, I have a command button that adds the results of the form to a
table using the following code:

DoCmd.RunSQL "insert into Reqtable (Emp_ID, Emp_last, Emp_first,
Emp_Middle) values (forms!Reqform!Emp_ID, forms!Reqform!Emp_last,
forms!Reqform!Emp_First, forms!Reqform!Emp_Middle)"

This works fine if all the fields contain information. However, if a field
is blank, such as the “Emp_Middle†I keep getting the following error message
and the append table command fails:

“Microsoft Access Can’t append all the records in the append query.
Microsoft Access Set 0 fields(s) to Null due to a type conversion failure,
and it didn’t add 0 record(s) to the table due to key violations, 0 record(s)
due to lock violations, and 1 record(s) due to validation rule violations.â€
Etc.

Why is this happening? Is there a way to fix this without changing the
code too much? Or am I stuck with it?

Thanks in advance.

 

[Dirk Goldgar]

I am using Access 2000. I have a combo box that looks up values in a
table and auto fill other fields in the form using the following code
placed in the "After Update" field in the combo box:

Me.Emp_ID = Me.Combo0.Column(0)
Me.Emp_Last = Me.Combo0.Column(1)
Me.Emp_First = Me.Combo0.Column(2)
Me.Emp_Middle = Me.Combo0.Column(3)
Etc.,

In addition, I have a command button that adds the results of the
form to a table using the following code:

DoCmd.RunSQL "insert into Reqtable (Emp_ID, Emp_last, Emp_first,
Emp_Middle) values (forms!Reqform!Emp_ID, forms!Reqform!Emp_last,
forms!Reqform!Emp_First, forms!Reqform!Emp_Middle)"

This works fine if all the fields contain information. However, if a
field is blank, such as the "Emp_Middle" I keep getting the following
error message and the append table command fails:

"Microsoft Access Can't append all the records in the append query.
Microsoft Access Set 0 fields(s) to Null due to a type conversion
failure, and it didn't add 0 record(s) to the table due to key
violations, 0 record(s) due to lock violations, and 1 record(s) due
to validation rule violations." Etc.

Why is this happening? Is there a way to fix this without changing
the code too much? Or am I stuck with it?

My first guess would be that all the fields in Reqtable -- or at least,
all those that are mentioned in the append query -- have their Required
property set to Yes, in which case you won't be able to add a record
with any of the fields empty. Is that the problem?

 

[ChrisJ]

This is happening because the table has been told to expect data, either by
such means as "required" or "no zero length strings" etc.
If you want to be able to store records where all the fields are not
populated, change the table structure to allow this.
If missing data is not permitted, change your form to require the data
before the record is added or updated

 

[Delano]

Thanks for both responses. They both solved the problem because I had the
field(s) set as a 'required' field and it was also set to 'no zero length
strings'