INSERT INTO Code errors (1 Viewer)

[mba_110]

Dear

Please need your help on following code its giving me error in INSERT INTO condition the error is "Syntax error in INSERT INTO statement" and some time its give error "Too few Parameters. expected 1."

Option Compare Database

Private Sub cmdAdd_Click()
'When we click on button add their are two options
'1. for insert
'2. for update
If Me.txtemployeeno.Tag & "" = "" Then
'this is for insert new
'add data to table
CurrentDb.Execute "INSERT INTO tblPersonalDetails(EmpID, EmployeeName, Surname, DOB, MaritalStatus, Gender, Nationality, Department, Jobtitle, Religion, ImageAddress, Notes)" & _
" VALUES (" & Me.txtemployeeno & ",'" & Me.txtemployeename & "','" & Me.cmbGender & "', '" & Me.cmbMaritalstatus & "','" & Me.cmbNationality & "','" & _
Me.cmbDepartment & "','" & Me.cmbJobtitle & "','" & Me.cmbReligion & "','" & Me.txtSurname & "','" & Me.txtdateofbirth & "', '" & Me.txtPicAddress & "','" & Me.txtMemo & "')"

Else
'otherwise (tag of txtemployeeno store the id of tblpersonaldetails to be modified)
CurrentDb.Execute "UPDATE tblPersonaldetails " & _
"SET EmpID=" & Me.txtemployeeno & _
",EmployeeName='" & Me.txtemployeename & "'" & _
",Surname='" & Me.txtSurname & "'" & _
",DOB = '" & Me.txtdateofbirth & "'" & _
",MaritalStatus = '" & Me.cmbMaritalstatus & "'" & _
",Gender = '" & Me.cmbGender & "'" & _
",Nationality = '" & Me.cmbNationality & "'" & _
",Department = '" & Me.cmbDepartment & "'" & _
",Jobttitle = '" & Me.cmbJobtitle & "'" & _
",Religion = '" & Me.cmbReligion & "'" & _
",ImageAddress = '" & Me.txtPicAddress & "'" & _
",Notes = '" & Me.txtMemo & "'" & _
"WHERE EmpID=" & Me.txtemployeeno.Tag
End If

'Clear form
cmdClear_Click
'refresh data in list on form
formfrmPersonaldetails_subformForm.Form.Requery

End Sub

Private Sub cmdclose_Click()
DoCmd.Close
End Sub

Private Sub cmdClear_Click()
Me.txtemployeeno = ""
Me.txtemployeename = ""
Me.cmbDepartment = ""
Me.txtdateofbirth = ""
Me.cmbGender = ""
Me.cmbJobtitle = ""
Me.cmbMaritalstatus = ""
Me.cmbNationality = ""
Me.cmbReligion = ""
Me.txtPicAddress = ""
Me.txtSurname = ""
Me.txtMemo = ""

'focus on ID text box
Me.txtemployeeno.Set

End Sub

Private Sub cmddelete_Click()
'delete record
'check existing selected record
If no(Me.frmpersonaldetails_subform.Form.Recordset.EOF And Me.frmpersonaldetails_subform.Recordset.BOF) Then
    'confirm delete
    If MsgBox("Are you Sure you want to delete the record?", vbYesNo) = vbYes Then
        'delete now
        CurrentDb.Execute "DELETE FROM tblPersonaldetails " & _
            "WHERE EmpID =" & Me.frmpersonaldetails_subform.Form.Recordset.Fields("EmpID")
        'refresh data in list
        Me.Form.frmpersonaldetails_sub.Requery
    
        End If
    
    End If
    

End Sub

Private Sub cmdEdit_Click()
    'Check whether there is exist data in list
    If no(Me.frmpersonaldetails_subform.Form.Recordset.EOF And Me.frmpersonaldetails_subform.Recordset.BOF) Then
        'get data to text box control
        With Me.frmpersonaldetails_subform.Form.Recordset
            Me.txtemployeeno = .Fields("EmpID")
            Me.txtemployeename = .Fields("EmployeeName")
            Me.cmbDepartment = .Fields("Department")
            Me.txtdateofbirth = .Fields("DOB")
            Me.cmbGender = .Fields("Gender")
            Me.cmbNationality = .Fields("Nationality")
            Me.cmbJobtitle = .Fields("Jobtitle")
            Me.cmbReligion = .Fields("Religion")
            Me.txtSurname = .Fields("Surname")
            Me.txtMemo = .Fields("Notes")
            Me.txtPicAddress = .Fields("ImageAddress")
            Me.cmbMaritalstatus = .Fields("MaritalStatus")
            'store id of tblPersonalDetails in tag of txtEmpID in case id is modified
            Me.txtemployeeno.Tag = .Fields("EmpID")
            'change caption of button add to update
            Me.cmdAdd.Caption = "Update"
            'disable button edit
            Me.cmdEdit.Enabled = False
        End With
    
    End If

End Sub

This code is mentioned on form and on form their is page tabs i am calling this code from page tab name "Personal Details" i dont know is it necessary to change coding for this or ok with above.

thanks & regards,

MA

 

[arnelgp]

why not use a bound form so less code is needed.

 

[JHB]

The order of the data must be identical, which they are not here, (they are mixed around each other):
One sample:

EmpID, EmployeeName, [B][COLOR=Red]Surname,[/COLOR][/B] ..
VALUES (" & Me.txtemployeeno & ",'" & Me.txtemployeename & "','" & [B][COLOR=Red]Me.cmbGender[/COLOR][/B] ...

 

[mba_110]

I am sorry but even its identical still the same error message coming.

CurrentDb.Execute "INSERT INTO tblPersonalDetails(EmpID, EmployeeName, Surname, DOB, MaritalStatus, Gender, Nationality, Department, JobTitle, Religion, ImageAddress, Notes)" & _
"VALUES (" & Me.txtemployeeno & ",'" & Me.txtemployeename & "','" & Me.txtSurname & "','" & Me.txtdateofbirth & "','" & Me.cmbMaritalstatus & "','" & _
Me.cmbGender & "','" & Me.cmbNationality & "','" & Me.cmbDepartment & "','" & Me.cmbJobtitle & "','" & Me.cmbReligion & "','" & Me.txtPicAddress & "','" & Me.txtMemo & "',)"

Any help on this please..........

 

[arnelgp]

ok since you dont want it bound, try this, not tested:

dim strEmpID as string
dim strEmployeeName as string
dim strSurName as string
dim strDOB as string
dim strMaritalStatus as string
dim strGender as string
dim strNationality as string
dim strDepartment as string
dim strJobTitle As string
dim strReligion as string
dim strImageAddress as string
dim strNotes as string

strEmpID = iif(trim(me.txtemployeeno & "")="","Null", Chr(34) & me.txtemployeeno & chr(34))
strEmployeeName = iif(trimMe.txtemployeename & "")="","Null", Chr(34) & Me.txtemployeename & chr(34))
strSurName = iif(trim(me.txtemployeeno & "")="","Null", Chr(34) & me.txtemployeeno & chr(34))
strDOB = iif(trim(Me.txtdateofbirth & "")="","Null", "#" & Format(Me.txtdateofbirth,"mm/dd/yyyy") & "#")
strMaritalStatus = iif(trim(Me.cmbMaritalstatus & "")="","Null", Chr(34) & Me.cmbMaritalstatus & chr(34))
strGender = iif(trim(Me.cmbGender & "")="","Null", Chr(34) & Me.cmbGender & chr(34))
strNationality = iif(trim(Me.cmbNationality & "")="","Null", Chr(34) & Me.cmbNationality & chr(34))
strDeparment = iif(trim(Me.cmbDepartment & "")="","Null", Chr(34) & Me.cmbDepartment & chr(34))
strJobTitle = iif(trim(Me.cmbJobtitle & "")="","Null", Chr(34) & Me.cmbJobtitle & chr(34))
strReligion = iif(trim(Me.cmbReligion & "")="","Null", Chr(34) & Me.cmbReligion & chr(34))
strImageAddress = iif(trim(Me.txtPicAddress & "")="","Null", Chr(34) & Me.txtPicAddress & chr(34))
strNote = iif(trim(Me.txtMemo & "")="","Null", Chr(34) & Me.txtMemo & chr(34))

CurrentDb.Execute "INSERT INTO tblPersonalDetails(EmpID, EmployeeName, Surname, DOB, MaritalStatus, Gender, Nationality, Department, JobTitle, Religion, ImageAddress, Notes)" & _
"SELECT " & strEmpID & "," & strEmployeeName & "," & strSurName & "," & strDOB & "," & strMaritalStatus & "," & _
strGender & "," & strNationality & "," & strDepartment & "," & strJobTitle & "," & strReligion & "," & strImageAddress & "," & strNote & ";"

 

[JHB]

What field type is DOB, (you're treated as text)?
You're missing a space before "Values".
If you can't get it post your database with some sample data, zip it, and write where your code is located.

 

[jdraw]

MA,

Can you tell us why you are not using a bound form as arnelgp asked in post #2?

 

[mba_110]

Dear all

Many thanks for all your help.

i forget to mentioned that DOB is date field, so i have changed it to date.

Dim strDOB As Date 

strDOB = IIf(Trim(Me.txtdateofbirth & "") = "", "Null", "#" & Format(Me.txtdateofbirth, "mm/dd/yyyy") & "#")

So, we need to fix the above strDOB condition its giving error "Type Mismatch"

finally, that i don't want to bound the form because it will change the data directly even you put something by mistake typing or something in field but in this form you have to have place to go through step by step, and also i love to learn coding and this is my beginning to new world of technology.

one more thing please if you don't mind after fixing the above date issue, kindly tell me code for following also.

Dim strAmount As integer

Need code for this

Many thanks for all you guys have a nice day...

MA