Problem with VBA + SQL Statement

[TheJokerV]

Could someone please tell me what is wrng with the following statement. I'm trying to execute a SQL statement in VBA. I've been working on this one statement for an hour and am starting to get frustrated.

DoCmd.RunSQL "INSERT INTO TBL_Base_Year[(Year[,Revision])] VALUES(" & [List_Base_Year].[Value] & "[," & [List_Base_Revision].[Value] & "])"

Edit:
BTW it is a runtime syntax error '3134'

Edit II:

OK I tried

DoCmd.RunSQL "INSERT INTO TBL_Base_Year(Year,Revision) VALUES('" & [List_Base_Year].[Value] & "'," & [List_Base_Revision].[Value] & ")"

with the same result. Any ideas?

 

[Nevermind]

Try it with spaces after the table name and VALUES. If that doesn't work, try printing the string you're creating to see if you get anything in the revision field in the value list.

 

[ameoba]

Looks like you're building strings & probably getting the strings wrong - maybe your quotes are in the wrong places, maybe your variables don't hold what you think they do... You might want to print this out and make sure you have what you expect. You could have 3 completely different problems here (at least):
a) you can't build the string you want (problems with variable contents or string interpolation)
b) you want to build the wrong string (you're trying to build invalid SQL)
c) you can't get the database to execute your SQL (you've got some sort of problem with your DB interface)

B & C are easy to detect by executing a fixed string. Some print statements will clue you in on A.

 

[TheJokerV]

OK I solved the problem. My listboxes didn't work quite the way I thought they did so the variables were just null. Thanks for your help guys, your suggestions really did help solve the problem.

 

[Y2K SE]

You could potentially have issues inserting a record that way. It'll work until someone does something like enter quotation marks in the user input section. A better option would be to utilize ADO (http://msdn2.microsoft.com/en-us/library/ms678086.aspx). Assuming your using Access:

Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim lngRecCount as Long

Set cn = Access.CurrentProject.Connection

Set cmd = New ADODB.Command
With cmd
    set .ActiveConnection = cn
    .CommandText = "INSERT INTO TBL_Base_Year(Year,Revision) " & _
                   "VALUES(@Year,@Revision)"
    .Parameters.Append .CreateParameter("@Year", adBigInt, adInput, , intYear)
    .Parameters.Append .CreateParameter("@Revision", adBigInt, adInput, , intRevision)
    .Execute lngRecCount
End with