Can't append all records in the record query

[Guest]

I have a form which appends the fields to the existing table called Job card.
Whenever i click my add button in the form i receive an error saying that
"CAN'T APPEND ALL THE RECORDS IN THE APPEND QUERY". If i click yes the data
gets inserted into the table. I checked my fields in the table for the
"Required" and "Allow zero lengths" properties. Everything seems to be
correct. Can anyone help me how to avoid this error when i click my add
button.

Thanks.

 

[Guest]

what about allow duplicates property?

 

[Guest]

Do you mean the "indexed" property?
Except the primary key field and all other fields are set to "Yes
(Duplicates ok)".

 

[Guest]

That's what I meant. Without being able to see what is going on first hand,
it is hard to say. I would look to see if there are any Null values, any
fields that the source data type doesn't match, or the length of the data is
too long for the incoming data. These are only thoughts on what to look at.

 

[Guest]

There are 2 or 3 fields that will get Null values depending upon the
situation. But i have set "Allow zero length" property to yes and the
"Required" property to No, for those fields. Do you think this would be a
problem?

 

[Guest]

Null is not the same as a zero length string ( = "") I would try putting the
Nz function around the fields that may be Null coming in in your query.
=Nz([Suspect Field],"")
That way, it will convert nulls to zero length strings.

 

[Guest]

I tried it with Nz but still it is giving the same error.

Below is the query.

Insert into Job_Card(jc_id, jc_emp_id, jc_parts_prod, jc_hrs_prod,
jc_set_up) values ('" & txtID.Value & "', '" & txtEmp.Value & "', '" &
Nz(txtPcsDone.Value, "") & "', '" & Nz(txtProdHrs.Value, "") & "', '" &
Nz(txtSetUp.Value, "") & "')".

Thanks.

Null is not the same as a zero length string ( = "") I would try putting the
Nz function around the fields that may be Null coming in in your query.
=Nz([Suspect Field],"")
That way, it will convert nulls to zero length strings.

That's what I meant. Without being able to see what is going on first hand,
it is hard to say. I would look to see if there are any Null values, any
fields that the source data type doesn't match, or the length of the data is
too long for the incoming data. These are only thoughts on what to look at.

 

[Guest]

Sorry, Sangee, I guess I am lost with you on this. I don't really see the
problem.

I tried it with Nz but still it is giving the same error.

Below is the query.

Insert into Job_Card(jc_id, jc_emp_id, jc_parts_prod, jc_hrs_prod,
jc_set_up) values ('" & txtID.Value & "', '" & txtEmp.Value & "', '" &
Nz(txtPcsDone.Value, "") & "', '" & Nz(txtProdHrs.Value, "") & "', '" &
Nz(txtSetUp.Value, "") & "')".

Thanks.

Null is not the same as a zero length string ( = "") I would try putting the
Nz function around the fields that may be Null coming in in your query.
=Nz([Suspect Field],"")
That way, it will convert nulls to zero length strings.

That's what I meant. Without being able to see what is going on first hand,
it is hard to say. I would look to see if there are any Null values, any
fields that the source data type doesn't match, or the length of the data is
too long for the incoming data. These are only thoughts on what to look at.

:

Do you mean the "indexed" property?
Except the primary key field and all other fields are set to "Yes
(Duplicates ok)".

:

what about allow duplicates property?

:

I have a form which appends the fields to the existing table called Job card.
Whenever i click my add button in the form i receive an error saying that
"CAN'T APPEND ALL THE RECORDS IN THE APPEND QUERY". If i click yes the data
gets inserted into the table. I checked my fields in the table for the
"Required" and "Allow zero lengths" properties. Everything seems to be
correct. Can anyone help me how to avoid this error when i click my add
button.

Thanks.

 

[Dirk Goldgar]

I have a form which appends the fields to the existing table called
Job card. Whenever i click my add button in the form i receive an
error saying that "CAN'T APPEND ALL THE RECORDS IN THE APPEND QUERY".
If i click yes the data gets inserted into the table. I checked my
fields in the table for the "Required" and "Allow zero lengths"
properties. Everything seems to be correct. Can anyone help me how to
avoid this error when i click my add button.

Thanks.

Is that the exact and complete text of the error message? If not,
please post (a) the real message, (b) the definition of the table you're
appending to (with field names and types), and (c) the SQL of the append
query. If that *is* the real message you're getting, I'm thinking it's
one that is actually displayed by the code behind your "add" button, so
please post that code.

 

[Guest]

Below is the error message:

"Efficiency can't append all the records in the append query.
Efficiency set 1 field(s) to Null due to a type conversion failure, all it
didn't add 0 record(s) to the table due to jey violations, 0 record(s) due to
lock violations, and 0 record(s) due to validation rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, clidk Yes."
Table Structure:
jc_id - Number (Primary key)
jc_run_dat - Date/Time
jc_shift - Number
jc_order_num - Number
jc_operation - Number
jc_part_num - Text
jc_part_desc - Text
jc_mach_code - Text
jc_emp_id - Number
jc_parts_prod - Number
jc_hrs_prod - Number
jc_set_up - Number

VBA Code:
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Dim id

DoCmd.RunSQL "Insert into Job_Card(jc_id, jc_run_date, jc_shift,
jc_order_num, jc_operation, jc_mach_code, jc_part_num, jc_part_desc,
jc_emp_id, jc_parts_prod, jc_hrs_prod, jc_set_up) values ('" & txtID.Value &
"', #" & txtdate & "#, '" & txtShift.Value & "', '" & txtOrNo.Value & "', " &
lstOpNo.Value & ", '" & txtMCode.Value & "', '" & txtPartNum.Value & "', '" &
txtPartDesc.Value & "', '" & txtEmp.Value & "', '" & Nz(txtPcsDone.Value, "")
& "', '" & Nz(txtProdHrs.Value, "") & "', '" & Nz(txtSetUp.Value, "") & "')"

id = txtID.Value
'clear all the fields, after adding it to the database
txtID.Value = id + 1 'increment the JOB CARD ID value by 1
txtEmp.Value = ""
txtdate.Value = ""
txtShift.Value = ""
txtOrNo.Value = ""
Call RmvItms
lstOpNo.Value = ""
lstMCode.Value = ""
txtPartNum.Value = ""
txtPcsDone.Value = ""
txtProdHrs.Value = ""
txtMCode.Value = ""
txtSetUp.Value = ""
txtPartDesc.Value = ""
txtEmpName.Value = ""
'Enable the Production hrs and Pieces done text box.
txtProdHrs.Enabled = True
txtPcsDone.Enabled = True
'MsgBox "Job card details has been inserted into database"


Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
End Sub

 

[Dirk Goldgar]

Below is the error message:

"Efficiency can't append all the records in the append query.
Efficiency set 1 field(s) to Null due to a type conversion failure,
all it didn't add 0 record(s) to the table due to jey violations, 0
record(s) due to lock violations, and 0 record(s) due to validation
rule violations.
Do you want to run the action query anyway?
To ignore the error(s) and run the query, clidk Yes."
Table Structure:
jc_id - Number (Primary key)
jc_run_dat - Date/Time
jc_shift - Number
jc_order_num - Number
jc_operation - Number
jc_part_num - Text
jc_part_desc - Text
jc_mach_code - Text
jc_emp_id - Number
jc_parts_prod - Number
jc_hrs_prod - Number
jc_set_up - Number

VBA Code:
Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Dim id

DoCmd.RunSQL "Insert into Job_Card(jc_id, jc_run_date, jc_shift,
jc_order_num, jc_operation, jc_mach_code, jc_part_num, jc_part_desc,
jc_emp_id, jc_parts_prod, jc_hrs_prod, jc_set_up) values ('" &
txtID.Value & "', #" & txtdate & "#, '" & txtShift.Value & "', '" &
txtOrNo.Value & "', " & lstOpNo.Value & ", '" & txtMCode.Value & "',
'" & txtPartNum.Value & "', '" & txtPartDesc.Value & "', '" &
txtEmp.Value & "', '" & Nz(txtPcsDone.Value, "") & "', '" &
Nz(txtProdHrs.Value, "") & "', '" & Nz(txtSetUp.Value, "") & "')"

id = txtID.Value
'clear all the fields, after adding it to the database
txtID.Value = id + 1 'increment the JOB CARD ID value by 1
txtEmp.Value = ""
txtdate.Value = ""
txtShift.Value = ""
txtOrNo.Value = ""
Call RmvItms
lstOpNo.Value = ""
lstMCode.Value = ""
txtPartNum.Value = ""
txtPcsDone.Value = ""
txtProdHrs.Value = ""
txtMCode.Value = ""
txtSetUp.Value = ""
txtPartDesc.Value = ""
txtEmpName.Value = ""
'Enable the Production hrs and Pieces done text box.
txtProdHrs.Enabled = True
txtPcsDone.Enabled = True
'MsgBox "Job card details has been inserted into database"


Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click
End Sub

I can see that you are inserting text values into number fields. That
will work so long as the values you are inserting can be converted to
numbers, but it won't work if they can't. I'd say that's why you're
getting the message about a type conversion failure. I notice also that
your Nz() fixups for Null values are converting them to zero-length
strings, which can't be converted to numbers. You should revise the
query SQL so that you do not wrap the values for numeric fields in
quotes. Only the text fields should be so wrapped.

Which of the fields being inserted are you prepared to allow to be Null?
Just the ones where you're using Nz? If so, change the Nz expressions
to be like this:

Nz(YourControlName, "Null")

(and make sure you don't have quotes around the expression).

 

[Guest]

Dirk,
Thanks for your heklp. I really appreciate your help.