Codes Work in ODBC Connection Not in Project 1

[Zurich98]

I'm using Access 2003 connecting to SQL server 2k via ODBC. The codes below work fine when I use ODBC connection and Link tables. However, when I create a Project, import the form and run it, i receive this message: "Line 1: Incorrect syntax near '!'." Can someone please help identify why the codes work in an ODBC connection but not in a Project? I want to create a project so that the data entry people do not have to supply the username and password when they're entering data. Your help is greatly appreciated.

strSQLAppend = "INSERT INTO tbl_Data ([Community],[Bldg],[Lot],[Task],[Vendor],[GeneralNotes],[InspDate],[Notes],[CrewName])" & _

"SELECT [Forms]![frmDataEntry]![TxtCom],[Forms]![frmDataEntry]![TxtBldg],[Forms]![frmDataEntry]![TxtLot],[Forms]![frmDataEntry]![TxtTask],[Forms]![frmDataEntry]![TxtVend],[Forms]![Forms]![frmDataEntry]![TxtGeneralNote],[Forms]![frmDataEntry]![TxtBldrInspectionDt],[Forms]![frmDataEntry]![TxtNote],[Forms]![frmDataEntry]![TxtCrewName]"

DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQLAppend
DoCmd.SetWarnings (warningson)
Me.CboComm = ""
Me.TxtBldg = ""
Me.TxtLot = ""
Me.CboTask = ""
Me.CboVendor = ""
Me.TxtGeneralNote = ""
Me.TxtBldrInspectionDt = ""
Me.TxtNote = ""
Me.TxtCrewName = ""

Thanks

 

[PHV]

Use this syntax instead:
INSERT INTO ... VALUES (...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zurich98]

thank you for your quick respond. i change the codes to the following

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Dim strSQLAppend As String

strSQLAppend = "INSERT INTO tbl_FTQ_DataCollection ([CommunityCode],[Block], [LotNumber],[TaskCode],[VendorCode],[ScoreReady],[ScoreTrade],[ScoreBuilder],[GeneralNotes],[InspDate],[NotesReady],[NotesTrade],[NotesBuilder],[CrewName],[ActivityNo] )" & _
"Values ([Forms]![frmDataEntry]![TxtComCode],[Forms]![frmDataEntry]![TxtBlock],[Forms]![frmDataEntry]![TxtLot],[Forms]![frmDataEntry]![TxtTaskCode],[Forms]![frmDataEntry]![TxtVendCode],[Forms]![frmDataEntry]![TxtJobReadyScore],[Forms]![frmDataEntry]![TxtTradeScore],[Forms]![frmDataEntry]![TxtBuilderScore],[Forms]![frmDataEntry]![TxtGeneralNote],[Forms]![frmDataEntry]![TxtBldrInspectionDt],[Forms]![frmDataEntry]![TxtJobReadyNote],[Forms]![frmDataEntry]![TxtTradeNote],[Forms]![frmDataEntry]![TxtBuilderNote],[Forms]![frmDataEntry]![TxtCrewName],[Forms]![frmDataEntry]![TxtActNo])"
DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQLAppend
DoCmd.SetWarnings (warningson)
Me.CboCommCode = ""
Me.TxtBlock = ""
Me.TxtLot = ""
Me.CboTaskCode = ""
Me.CboVendor = ""
Me.TxtJobReadyScore = ""
Me.TxtTradeScore = ""
Me.TxtBuilderScore = ""
Me.TxtGeneralNote = ""
Me.TxtBldrInspectionDt = ""
Me.TxtJobReadyNote = ""
Me.TxtTradeNote = ""
Me.TxtBuilderNote = ""
Me.TxtCrewName = ""

and got this message:
"The name 'Forms' is not permitted in this context. Only constants,expressions, or variables allowed here. Column names are not permitted."

any idea what's going on here. thanks

 

[PHV]

The SQL code should contain the values of the controls, not their names:
strSQLAppend = "INSERT INTO tbl_FTQ_DataCollection ([CommunityCode],[Block],[LotNumber],[TaskCode],[VendorCode],[ScoreReady],[ScoreTrade],[ScoreBuilder],[GeneralNotes],[InspDate],[NotesReady],[NotesTrade],[NotesBuilder],[CrewName],[ActivityNo])" & _
[tt]"VALUES ('" & Me![TxtComCode] & "','" & Me![TxtBlock] & "','" & Me![TxtLot] ...[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zurich98]

hi PH, sorry to bother again, i try [TxtCom] and get a similar message. Please see below. I have also try [TxtCom].value and got similar message. what else should i try? thanks


Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click
Dim strSQLAppend As String

strSQLAppend = "INSERT INTO tbl_Data ([Community],[Bldg], [Lot],[Task],[Vendor],[GeneralNotes],[InspDate],[CrewName])" & _
"Values ([TxtCom],[TxtBldg],[TxtLot],[TxtTask],[TxtVend],[txtGeneralNotes],[TxtBldrInspectionDt],[TxtCrewName])"
DoCmd.SetWarnings (warningsoff)
DoCmd.RunSQL strSQLAppend
DoCmd.SetWarnings (warningson)
Me.CboComm = ""
Me.TxtBldg = ""
Me.TxtLot = ""
Me.CboTask = ""
Me.CboVendor = ""
Me.TxtGeneralNote = ""
Me.TxtBldrInspectionDt = ""
Me.TxtCrewName = ""

and got this message:
"The name 'TxtCom' is not permitted in this context. Only constants,expressions, or variables allowed here. Column names are not permitted."

 

[PHV]

Please, reread carefully my previous post ...

 

[Zurich98]

PHV,

Thank you very much for your patience. Your solution is working great. You are a life saver. Thanks for all your helps.