Docmd.RunSQL Syntax Error

AdamCLloyd

New member
Local time
Today, 12:57
Joined
Jul 5, 2013
Messages
8
Hi Guys,

New here, and have been a little stuck on a VBA thing. normally I'm quite good at finding solutions, but because this error message is so loose and broad, im finding it hard to decipher what I'm doing wrong, I'm hoping one of you seasoned professionals might just take a glance and know whats wrong.

Basically I'm struggling with this docmd.runsql code:

Code:
DoCmd.RunSQL "INSERT INTO tblCandidate([strFirstName], [strSurname], [strAddressLine1], [strAddressLine2], [strAddressLine3], [strAddressLine4], [strPostcode], [strTelephonePrimary], [strTelephoneSecondary], [strEmail], [strDOB], [strNINumber], [keyGender]) VALUES('" & Nz(Me.FName.Value, "NULL") & "', '" & Me.Surname.Value & "', '" & Nz(Me.Address_1.Value, "NULL") & "', '" & Nz(Me.Address_2.Value, "NULL") & "', '" & Nz(Me.Address_3.Value, "NULL") & "', '" & Nz(Me.Town_City.Value, "NULL") & "', '" & Nz(Me.Postcode.Value, "NULL") & "', '" & Nz(Me.Telephone.Value, "NULL") & "', '" & Nz(Me.Telephone__Mobile_.Value, "NULL") & "', '" & Nz(Me.email_Address.Value, "NULL") & "', #" & Nz(Me.Date_Of_Birth.Value, "NULL") & "#, '" & Nz(Me.N_I__Number.Value, "NULL") & "', '" & Nz(Me.Gender.Value, "NULL") & "')"

1) I put the Null's in to avoid '' 's just in case that was a problem, anyway, so that I dumped the result into a msgbox and text field and here are some dummy data results:

Code:
INSERT INTO tblCandidate([strFirstName], [strSurname], [strAddressLine1], [strAddressLine2], [strAddressLine3], [strAddressLine4], [strPostcode], [strTelephonePrimary], [strTelephoneSecondary], [strEmail], [strDOB], [strNINumber], [keyGender]) VALUES('David', 'Tennant', '5 Gallifrey', 'Somewhere', 'NULL', 'Birmingham', 'B8 8EE', '0121 326 0594', '07876 656207', 'NULL', #15/03/1972#, 'PA 66 66 66 C', 'Male')

Let me know if you need any further information before u can help.
 
Last edited:
Ps, on reviewing post, there is no asterix in my code after the INSERT INTO, that seems to be something this forum has done automatically :S
 
Do you get any error message - then show it + err number.
Else are you sure the value you are trying to insert is the correct type corresponding to the field types in the table?
 
Run Time Error 3134:

Syntax error in INSERT INTO Statement

Field types are all correct for what I'm putting into them :).
 
Copy the line into the sql view of the query designer and run it.

It often gives more clues to where the problem lies.
 
LoL, as i said above... Ps, on reviewing post, there is no asterix in my code after the INSERT INTO, that seems to be something this forum has done automatically :S !

Ive edited my original post to take out the asterix, god knows where they came from!

Issue remains.:confused:
 
Last edited:
LoL, as i said above... Ps, on reviewing post, there is no asterix in my code after the INSERT INTO, that seems to be something this forum has done automatically :S !
Sorry - yes you did. :o
If I take your SQL string and run it, it gives no error here, so ...
I've attached the database, open the form and press the button, maybe you can see what the differences are between yours and mine.
 

Attachments

Users who are viewing this thread

Back
Top Bottom