Need help with SQL error

[Jencylivez]

I've looked over the code 100 times and I can't seem to get it right. Does anyone see anything wrong?

DoCmd.RunSQL INSERT INTO tblGeneralItems (ItemCode, ItemDesc, Species, SubSpecies, Note) VALUES (04211, 'Chicken Trim', 4, 211, 'Added By Sys');

 

[boblarson]

How about adding quotes:

DoCmd.RunSQL "INSERT INTO tblGeneralItems (ItemCode, ItemDesc, Species, SubSpecies, Note) VALUES (04211, 'Chicken Trim', 4, 211, 'Added By Sys');"

 

[SimonB1978]

Hi,

Quotes?

DoCmd.RunSQL "INSERT INTO tblGeneralItems (ItemCode, ItemDesc, Species, SubSpecies, Note) VALUES (04211, 'Chicken Trim', 4, 211, 'Added By Sys');"

Assuming that the data types match I don't see aything else...

Simon B.

 

[SimonB1978]

damn telephone while I'm typing....

 

[Jencylivez]

Sorry about that. The quotes are in the code. I just didnt copy and paste them. Here is the code exactly as it is in the VBA editor.

'Check if item already exists in our system
ItemDupCheck = DCount("[ItemID]", "[tblGeneralItems]" _
, "[Species] = " & ComID & " And " & "[SubSpecies] = " & ComTID)


'If it doesn't, lets create it.
If Not ItemDupCheck = 0 Then
CommDesc = DLookup("[CommodityName]", "tblCommodity", "[CommodityID] = " & ComID)
CommTDesc = DLookup("[Category1Name]", "tblCommCat1", "[Category1ID] = " & ComTID)


DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tblGeneralItems (ItemCode, ItemDesc, Species, SubSpecies, Note) VALUES (" & Format(ComID, "00") & Format(ComTID, "000") & ", '" & CommDesc & " " & CommTDesc & "', " & ComID & ", " & ComTID & ", '" & "Added By Sys" & "');"
DoCmd.SetWarnings True

 

[Jencylivez]

Here are the field types:
ItemCode Number
ItemDesc Text
Species Number
SubSpeices Number
Note Memo

 

[SimonB1978]

hi,

I don't see anything right now... but I can give you one trick: always build your query in a string and then pass the string as an argument. You can then do a "Debug.Print strQuery" and troubleshoot the exact query that gets executed...

Simon B.

 

[boblarson]

If Item Code is a number then get rid of the Formatting on the Value for it. Using Format converts it to a string. Or else you will need to convert your concatenation together back to a number.

 

[Jencylivez]

I appreciate the help guys. I'm still getting the error. Here is the entire sub.

Private Sub btnSaveOffering_Click()
Dim ItemDupCheck As Integer
Dim CustItemDupCheck As Integer
Dim LCustID As Integer
Dim LItemID As Integer
Dim ComID As Integer
Dim ComTID As Integer
Dim CommDesc As String
Dim CommTDesc As String
Dim ThrowSQL As String
Dim LItemCode As Integer

If IsNull(Me!ItemCommodity) Then
MsgBox "Please Choose a Commodity"
Exit Sub
End If

If IsNull(Me!ItemCommodityType) Then
MsgBox "Please Choose a Comm. Type"
Exit Sub
End If

ComID = Me!ItemCommodity
ComTID = Me!ItemCommodityType

'Check if item already exists in our system
ItemDupCheck = DCount("[ItemID]", "[tblGeneralItems]" _
, "[Species] = " & ComID & " And " & "[SubSpecies] = " & ComTID)


'If it doesn't, lets create it.
If ItemDupCheck = 0 Then
CommDesc = DLookup("[CommodityName]", "tblCommodity", "[CommodityID] = " & ComID)
CommTDesc = DLookup("[Category1Name]", "tblCommCat1", "[Category1ID] = " & ComTID)

LItemCode = ComID & ComTID
DoCmd.SetWarnings False


ThrowSQL = " INSERT INTO tblGeneralItems (ItemCode, ItemDesc, Species, SubSpecies, Note) VALUES (" & LItemCode & ", '" & CommDesc & " " & CommTDesc & "', " & ComID & ", " & ComTID & ", '" & "Added By Sys" & "');"
'Debug.Print ThrowSQL
DoCmd.RunSQL ThrowSQL
DoCmd.SetWarnings True
End If


DoCmd.GoToRecord , , acNewRec
SendMail
Me!ItemDescription.SetFocus
End Sub