Insert into Statment Error

[balvinder]

Hi,

I've been trying a lot to sort this out from last 3 days but every time new error comes out.

I'am trying to add new records using my MS - Access 2007 user form to access table. I'am getting run time error-3075 Syntax error (Missing operator) in query expression 'Balvinder Rayat'.

Not able to understand what is wrong in my code & why it's showing error.

Private Sub Save_Record_Click()

Dim strSQL As String
 
 strSQL = "INSERT INTO ATF_Base (ATF_ID, First_Intimation_HO, Request_Date, Transferor_Location, Autorized_Person, Autorized_Person_Designation, Autorized_Person_Emp_ID, Autorized_Person_Signature, Security_Name, Security_Signature, Security_Gate_Pass_No, Transferee_Location, Date_of_Transfer, Transferee_Authorized_Person, Transferee_Authorized_Person_Designation, Transferee_Authorized_Person_Emp_ID, Transferee_Authorized_Person_Signature, Transferee_Security_Name, Transferee_Security_Signature, Transferee_Security_Gate_Pass_No, Admin_User_Name, Admin_User_Designation, Admin_User_Emp_ID, Admin_User_Signature, Admin_Head_Approval, Admin_Head_Approval_Date, HO_Commercial_Approval, HO_Commercial_Approval_Date," & vbCrLf
 strSQL = strSQL & "Scan_Image_Link, Handover_to_Finance_date, Receiving_Confirmation_Mail_Link, ATF_Updation_Confirmation_Status, ATF_Updation_Confirmation_Status_Email_Link)" & vbCrLf
 strSQL = strSQL & "VALUES('" & Me.ATF_ID.Value & "',#" & Me.First_Intimation_HO.Value & "#,#" & Me.Request_Date.Value & "#, " & Me.Transferor_Location.Value & "," & Me.Autorized_Person.Value & "," & Me.Autorized_Person_Designation.Value & " , " & Me.Autorized_Person_Emp_ID.Value & " , " & Me.Autorized_Person_Signature.Value & " , " & Me.Security_Name.Value & " , " & Me.Security_Signature.Value & " , " & Me.Security_Gate_Pass_No.Value & "," & vbCrLf
 strSQL = strSQL & "" & Me.Transferee_Location.Value & " ,#" & Me.Date_of_Transfer.Value & "#, " & Me.Transferee_Authorized_Person.Value & " , " & Me.Transferee_Authorized_Person_Designation.Value & " , " & Me.Transferee_Authorized_Person_Emp_ID.Value & " , " & Me.Transferee_Authorized_Person_Signature.Value & " , " & Me.Transferee_Security_Name.Value & " , " & Me.Transferee_Security_Signature.Value & " , " & Me.Transferee_Security_Gate_Pass_No.Value & " , " & Me.Admin_User_Name.Value & " , " & Me.Admin_User_Designation.Value & " , " & Me.Admin_User_Emp_ID.Value & " , " & Me.Admin_User_Signature.Value & " , " & Me.Admin_Head_Approval.Value & " , " & vbCrLf
 strSQL = strSQL & "#" & Me.Admin_Head_Approval_Date.Value & "#, " & Me.HO_Commercial_Approval.Value & ",#" & Me.HO_Commercial_Approval_Date.Value & "#, " & Me.Scan_Image_Link.Value & " ,#" & Me.Handover_to_Finance_date.Value & "#, " & Me.Receiving_Confirmation_Mail_Link.Value & " , " & Me.ATF_Updation_Confirmation_Status.Value & " , " & Me.ATF_Updation_Confirmation_Status_Email_Link.Value & " );" & vbCrLf
 
  Debug.Print strSQL
 
DoCmd.SetWarnings True
CurrentDb.Execute (strSQL)



 Me.Refresh
 
End Sub

any help would be highly appreciated.

[sapator]

Haven't used access in years, at least from an insurance company i worked for in 2009 also i am always again this type of passing variables (in code) as i see them coming more and more in ERP's i work along.An that is because you can miss a dot and have to spend hours to find the problem. Anyhow, i don't know if this is correct here:
" Me.First_Intimation_HO.Value & "#,#" & Me.Request_Date.Value & " you have "#,#" so you start your variable with "#". That might not be an error but i just spotted it because it difference from your pattern.
Anyhow doesn't access have a query editor to run the code and see what is going on?

[TysonLPrice]

any help would be highly appreciated.

Start by helping us to help you. Show us what the SQL looks like that is being passed to Access. We don't have a clue based on what you posted. I see that debug.print there. Show us that the results of that or key ?strSQL in the immeadiate window, press enter and show us that. Right now you are showing something that doesn't look anything like what is getting passed to Access.

If you don't know what I mean let me know and I'll explain it better.

Thanks!

[techgnome]

using the # around hte dates is correct... but what isn't is the missing tick marks surrounding the strings... anytime you're inserting string data they must be enclosed in single quote marks... or use parameters (highly recommended) ... string concatenation is jsut begging for trouble (try using parameters) ... even if you put quote makrs for your string into your SQL, it'll work until the first time you try to insert O'Banion or Al'Alajawarski ... because that will cause it to drop out of the string (parameters will prevent this) ... not to mention it opens yourself up to SQL Injection attacks (which parameters will prevent) ... here's a great example of SQL Injection Attacks - http://xkcd.com/327/

Have I mentioned parameters? If not... I should... look into using parameters for your query.

-tg