Creating data entry form using button VBA

Question

I am trying to create a basic data entry form, however, it is turning into more trouble than I anticipated.. I have the form created, now I am just trying to INSERT the data into the DB (TEST). I am receiving an "Object Required" error. Any suggestions? All of the txt boxes are verified to be correct. This is all being done through Access VBA

Private Sub Command28_Click()



Dim sSQL As String

Set sSQL = "INSERT INTO TEST (Carrier_Ent_ID, Row_Insert_TS, Row_Update_TS, 
            Row_Insert_User_ID, Row_Update_User_ID, Carrier_Ent_Name, Active) 
VALUES (" & Me.txtENTID & "," & Me.txtDate & "," & Me.txtDate & "," & 
            Me.cmboUserID & "," & Me.cmboUserID & "," & Me.txtENTNAME & "," 
          & Me.Txtactive & "); "


DoCmd.RunSQL.sSQL

End Sub

I haven't done any access for a long time, but I'm pretty sure you need apostrophes for strings (') and maybe # for date/time? Also it'd be worth looking into parameterized queries. — NDJ, Mar 28 '18 at 15:16
NDJ seems right. Instead of running the SQL, print it out instead. The error will be easy to spot, no ' in values line seems right. If you can't get it from there, post whatever print sSQL returns and we can go from there — Twelfth, Mar 28 '18 at 15:27
Is that exactly as it appears in the VBE? You've got no line continuation characters. — Darren Bartrup-Cook, Mar 28 '18 at 15:45
You are setting a string, strings are not objects and cannot be set. Try removing that. — Jeffrey, Mar 28 '18 at 15:58

score 0 · Accepted Answer · answered Mar 28 '18 at 15:58

From what I can see there's a couple of mistakes in the code.

You only use SET when setting a reference to an object - sSQL is a text string.
DoCmd.RunSQL shouldn't have a full-stop after RunSQL- just the text string.
If Me.txtENTNAME is a text string it should have an apostrophe before and after it.

Private Sub Command28_Click()

    Dim sSQL As String

    'No Set & ' before and after Me.txtENTNAME (assuming it's text).
    sSQL = "INSERT INTO TEST (Carrier_Ent_ID, Row_Insert_TS, Row_Update_TS, " & _
           "Row_Insert_User_ID, Row_Update_User_ID, Carrier_Ent_Name, Active) " & _
           "VALUES (" & Me.txtENTID & "," & Me.txtDate & "," & Me.txtDate & "," & _
           Me.cmboUserID & "," & Me.cmboUserID & ",'" & Me.txtENTNAME & "'," & Me.txtActive & "); "

    'No full stop after RunSQL.
    DoCmd.RunSQL sSQL

End Sub

Add [parameters](https://stackoverflow.com/questions/49509615/how-do-i-use-parameters-in-vba-in-the-different-contexts-in-microsoft-access) to prevent errors by by wrong values entered (e.g. quotes in `Me.txtENTNAME`). — ComputerVersteher, Mar 28 '18 at 20:49

Creating data entry form using button VBA

1 Answers1