CODEWITHSUNDEEP
ms-accessvba
Karicula
Karicula

Reputation: 1

INSERT INTO code returns a Compiler Error: Expected: End of Statement

I wrote simple code to insert a record in a test data table. Followed the MSDN example but cannot get past the compile error. I have tried it with the recordset and with the table name alone. Same error.

Private Sub cmdCommitChg_Click()
'this is my tester environment
Dim ztemp1, ztemp2, ztemp3 As String  'these are dummy data to append to the table
Dim tblInsert As String
Dim dbsDonors As DAO.Database
Dim rcdTempTester As DAO.Recordset

Set rcdTempTester = dbsDonors.OpenRecordset("tblTempTester")
ztemp1 = "TempId"
ztemp2 = "TempEntity"
ztemp3 = "tempName"
INSERT INTO rcdTempTester!tblTempTester (id_members, id_entity, member_Name) VALUES (ztemp1, ztemp2, ztemp3)

End Sub

Upvotes: 0

Views: 727

Answers (2)

Gustav
Gustav

Reputation: 55831

You are mixing VBA and SQL. Do this:

Private Sub cmdCommitChg_Click()

    'this is my tester environment
    Dim ztemp1 As String
    Dim ztemp2 As String
    Dim ztemp3 As String  

    Dim tblInsert As String
    Dim dbsDonors As DAO.Database
    Dim rcdTempTester As DAO.Recordset

    ztemp1 = "TempId"
    ztemp2 = "TempEntity"
    ztemp3 = "tempName"

    Set rcdTempTester = dbsDonors.OpenRecordset("tblTempTester")
    rcdTempTester.AddNew
        rcdTempTester(ztemp1).Value = <some id>
        rcdTempTester(ztemp2).Value = <some entity>
        rcdTempTester(ztemp3).Value = <some name>
    rcdTempTester.Update
    rcdTempTester.Close

    Set rcdTempTester = Nothing
    Set dbsDonors = Nothing

End Sub

Upvotes: 1

Karicula
Karicula

Reputation: 1

I started over and now things are working. Attached is the simple code to add a record (and it works).

Private Sub cmdAddNew_Click()
Dim dbsDonors As DAO.Database
Dim rstTester As DAO.Recordset
Dim zBEN As String

zBEN = "zip1"

Set dbsDonors = CurrentDb
Set rstTester = dbsDonors.OpenRecordset("tblTester")

rstTester.AddNew
rstTester!id_members = "newData"
rstTester.Update
End Sub

Upvotes: 0

Related Questions