Syntax error in Form

[Guest]

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.

 

[RuralGuy]

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.

 

[Guest]

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]

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

Hi Sangee,

Others will jump in here but I have a question. Is there a reason you
did not just bind this form to Job_Card and bind the controls to the
fields? Then moving to a new record would accomplish everything you are
doing here and the add button would be basically move to a new record!

I'll have to study your code for a bit to comment further.

 

[Guest]

Hi,

Actually I am new to VBA and I didn't know about the bind. That is why I
coded everything.

 

[RuralGuy]

Hi,

Actually I am new to VBA and I didn't know about the bind. That is why
I coded everything.

Do you want to keep troubleshooting this code or would you like to make a
copy of the form and bind the form and the fields? Leave the navigation
buttons on and you'll see that when you go to the "*" new record,
everything you want to happen happens. Access is pretty good about that!

Eventually you will need to understand Access and use its power at rapid
development. Give it a try. We can always go back to an unbound form
(which you will use on occasion).