Re: Syntax error in Form

Hi,

Below is my Add code.

I think the problem is with the "Nz" part. Whenever I enter all the fields
it gets inserted. But when i leave some fields blank it is giving me the
error.

I checked my "Required" property. It is set to "No". I would really
appreciate your help.

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,
"Null") & ", " & Nz(txtProdHrs.Value, "Null") & ", " & Nz(txtSetUp.Value,
"Null") & ")"

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 = ""
lstOpNo.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"
txtEmp.SetFocus

Exit_cmdAdd_Click:
Exit Sub

Err_cmdAdd_Click:
MsgBox Err.Description
Resume Exit_cmdAdd_Click

End Sub

"RuralGuy" wrote:

> <Sangee@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
>
> > Hi,
> >
> > I have a form call Job Card.
> >
> > When I open the form and enter the values and click the add button, it
> > gets added(only for the first time the form is opened).
> >
> > When I enter the values again without exiting the form, it is giving
> > me an error "Syntax error in INSERT INTO statement". Whereas, when I
> > exit the form and enter the values, it gets inserted without any
> > error.
> >
> > So each and every time I insert a record I need to close the form and
> > open it again.
> >
> > Can anyone help me how to solve this issue.
> >
> > Thanks.
> >
> > When i enter the values for the first time it is
> > opened, and click the ADD button it gets inserted into the table but
> > when i enter the values again it is giving me "Syntax error in INSERT
> > INTO statement". Whereas when i exit the form and open again it gets
> > inserted. So, every time i add the record, i need to exit and open the
> > form again to insert another record. Can anyone help me with this
> > issue?
> >
> > Thanks.
>
> Hi Sangee,
>
> It would help everyone here if you would paste your ADD_Click() code in a
> post so we can see what you are trying to do.
>
> --
> RuralGuy
>
> Please reply to the newsgroup so all may benefit.
>
.