Insert VBA code in MS Access

[nector]

Where am going wrong here , the code keeps giving an error I never thought this can be an issue , kindly check

Private Sub CboWorksOrder_AfterUpdate()

Dim sQLstr As String

sQLstr = "INSERT INTO tblJobWorks ( WorkDate, WorKorder, CompanyName )" & _
"SELECT tblJobQuotation.QTRDate, tblJobQuotation.JobNumber, tblJobQuotation.CustomerName" & _
"FROM tblJobQuotation WHERE [tblJobQuotation.JobQID]= & Me.CboWorksOrder"

sQLstr = "INSERT INTO tblJobworksDetails ( Descriptions, QTY )" & _
"SELECT tblJobQtDetails.Description, tblJobQtDetails.QTY " & _
"FROM tblJobQtDetails WHERE tblJobQtDetails.JobQID)= &Me.CboWorksOrder"


End Sub

 

[sonic8]

Where am going wrong here ,

If you print out your sQLstr (weird casing!) variable to the Immediate Window using Debug.Print you should see the problem yourself.
(I think, it will sharpen your view for the problem if you find it yourself.)

 

[Minty]

I will give you a pointer, concatenation of form values...

 

[gemma-the-husky]

If you prepare a query in the QBE window, you can see what it looks like in SQL, which helps. SQL view is one of the alternative views of a query.

 

[nector]

I think I'm now running out of ideas , the second code is working okay the first it does not want

Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Set db = CurrentDb
sSQLOne = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT tblJobQuotation.QTRDate, tblJobQuotation.JobNumber, tblJobQuotation.CustomerName" & _
"FROM tblJobQuotation WHERE tblJobQuotation.JobQID = " & Me.CboWorksOrder

sSQL = "INSERT INTO [tblJobworksDetails] ( Descriptions, QTY )" & _
"SELECT tblJobQtDetails.Description, tblJobQtDetails.QTY " & _
"FROM tblJobQtDetails WHERE tblJobQtDetails.JobQID = " & Me.CboWorksOrder
db.Execute sSQL, dbFailOnError

 

[tvanstiphout]

As already suggested: print to the immediate window.
Then copy, and paste it in a new query in SQL view, and switch to Design view.

In your case you're missingsomespaces.

 

[theDBguy]

I think I'm now running out of ideas , the second code is working okay the first it does not want

Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Set db = CurrentDb
sSQLOne = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT tblJobQuotation.QTRDate, tblJobQuotation.JobNumber, tblJobQuotation.CustomerName" & _
"FROM tblJobQuotation WHERE tblJobQuotation.JobQID = " & Me.CboWorksOrder

sSQL = "INSERT INTO [tblJobworksDetails] ( Descriptions, QTY )" & _
"SELECT tblJobQtDetails.Description, tblJobQtDetails.QTY " & _
"FROM tblJobQtDetails WHERE tblJobQtDetails.JobQID = " & Me.CboWorksOrder
db.Execute sSQL, dbFailOnError

Maybe it's just me, but I don't see an Execute statement for the first SQL.

 

[nector]

Ms Access sometimes its quite very fun product , what it wanted is [ ]

Dim db As DAO.Database
Dim sSQL As String
Dim sSQLOne As String
Set db = CurrentDb
sSQL = "INSERT INTO [tblJobWorks] ( WorkDate, WorKorder, CompanyName )" & _
"SELECT [tblJobQuotation].[QTRDate], [tblJobQuotation].[JobNumber], [tblJobQuotation].[CustomerName]" & _
"FROM [tblJobQuotation] WHERE [tblJobQuotation].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQL, dbFailOnError
sSQLOne = "INSERT INTO [tblJobworksDetails] ( Descriptions, QTY )" & _
"SELECT [tblJobQtDetails].[Description], [tblJobQtDetails].[QTY] " & _
"FROM [tblJobQtDetails] WHERE [tblJobQtDetails].[JobQID] = " & Me.CboWorksOrder
db.Execute sSQLOne, dbFailOnError

 

[Pat Hartman]

You only need the square brackets when the names contain illegal characters or are reserved words.

At least now you know how to debug concatenated SQL strings. Just stop the code and print the string to the debug window. If you still don't see the error, copy the string and paste it into the QBE and run it for a better error message.

 

[Minty]

The square brackets are hiding your lack of spacing between key words and table/field names.
As already mentioned more than once previously, the best way to do this is (No code tags - I wanted to make a point...)

Dim strSQL as String

strSQL = "SELECT Flibble from MyTable "
strSQL = strSQL & " WHERE Something ='MyValue' AND SomethingElse ='" & me.AnotherFormValue & "' "

DEBUG.PRINT strSQL '<<<<<<<<<<< DO THIS PLEASE

CurrentDB.Execute strSQL

You then SEE what you have and the spacing, quotes, and values from your form.