Error with SQL statement while trying to use Access

[Guest_imported]

I am developing a program with VB 5.0 that takes info from textboxes on several different forms and exports it to cells in Excel 97 and then also to Access 97. The Excel form is for individual entries whereas the Access is for longterm recording. It exports fine to Excel but the SQL statement I wrote for Access is giving me error after error, first it said a "missing semicolon at the end of the statement", and now it is saying "incomplete query clause" Could someone
please look at this and tell me what i am doing wrong or suggest a different way to do this. I appreciate the help.

Here's the code:

Private Sub DoneFrm4cmd_Click()

Dim response
Dim question As String
Dim message As String
Dim file As String
Dim ponum As String
Dim req As String
Dim enter As String
Dim title
Dim dbData As Database
Dim strSQL As String
Dim Cost1
Dim Access2 As String

Static Name2
Static OrderDate
Static Vendor2



Set XlsApp = GetObject(, "EXCEL.APPLICATION&quot
With XlsApp
Set xlsBook = .ActiveWorkbook
Set xlsSheet = .ActiveSheet
.Visible = True

Cost1 = .Range("P34&quot.Value
StrReq = ReqNumtxt.Text
StrReas = SpInsttxt.Text
StrCost = Cost1
StrType = "Emergency VISA"

StrName = .Range("A35&quot.Value
StrOrderDate = .Range("C35&quot.Value
StrVendor = .Range("A5&quot.Value


.Range("G39&quot.Value = SpInsttxt.Text
.Range("N1&quot.Value = VPONumtxt.Text
.Range("N11&quot.Value = ReqNumtxt.Text
End With

XlsApp.Visible = True

question = "Is the information you entered correct?"
title = "Emergency VISA Spending"

response = MsgBox(question, vbYesNo + vbQuestion, title)
If response = vbYes Then



Set dbData = OpenDatabase("c:\Purchasing\Totals\TotalSpending.mdb&quot
strSQL = "INSERT INTO 'tblTotalSpending;' (tblName, tblOrderDate, tblExpenseType,
tblReqNumber, tblVendorName, tblResonForPurchase, tblTotalCost) VALUES (StrName,
StrOrderDate, StrType, StrReq, StrVendor, StrReas, StrCost)"


strSQL = strSQL & Chr$(34) & StrName & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrOrderDate & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrType & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrReq & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrVendor & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrReas & Chr$(34) & ","
strSQL = strSQL & Chr$(34) & StrCost & Chr$(34) & ";"

Debug.Print strSQL

'strSQL = strSQL & Val(txtNumeric4.Text) & &quot"
dbData.Execute strSQL, dbAppendOnly
dbData.Close

xlsSheet.SaveAs strSSpath1
' xlsSheet.SaveAs strSSpath2
xlsBook.Close
XlsApp.Quit
Set XlsApp = Nothing
Set xlsBook = Nothing
Set xlsSheet = Nothing

message = "Please save the following information for your personal records."
enter = Chr(13)
req = "Requisition number: "
req = req & reqnum
ponum = "Purchase order number: "
ponum = ponum & rndnum
file = "File name and path: "
file = file & strSSpath2

message = message & enter & enter & req & enter & ponum & enter
& file
MsgBox message, vbOKOnly + vbInformation, title
End

End If
End Sub

Thanks a lot,

Mike

 

[jabond007]

hi,

Why is there a ; at the end of the following line,
strSQL = "INSERT INTO 'tblTotalSpending;'
Remove that and ur code should work.

james

 

[Guest_imported]

Thanks for the help It WORKS!!!!!