CODEWITHSUNDEEP
sqlms-accessvbams-access-2013
KameeCoding
KameeCoding

Reputation: 723

MS access running SQL doesn't insert data, no error

I want to add data to this table from a form.

enter image description here

I have Combo box for the Job and Team ID's that look up data from their respective tables.

Private Sub save_new_Click()
On Error GoTo save_new_Click_Err

    On Error Resume Next
    Dim strSQL As String

    strSQL = "INSERT INTO Employee (Name,ATUUID,Job ID,Team ID,Start Date, Comments) " & _
     " VALUES(" & Me.Employye_Name & ", " & Me.ATTUID & ", " & Me.cboFunc & ", " & _
     Me.cboTeam & ", " & Me.Start_Date & ", " & Me.Comments & ")"
     Debug.Print strSQL
    With CurrentDb
        .Execute (strSQL), dbFailOnError
        Debug.Print .RecordsAffected
    End With

Here is the resulting SQL string:

 INSERT INTO Employee (Name,ATUUID,Job ID,Team ID,Start Date, Comments)  VALUES(asd, asd, 1, 2, 7/10/2015, asdasd)

Debug.Print .RecordsAffected

Prints 0

Upvotes: 1

Views: 3604

Answers (1)

HansUp
HansUp

Reputation: 97101

As @AlexK. explained, the reason you're not seeing errors is because On Error Resume Next hides errors. When you use that, you're telling Access "ignore any error --- don't even mention it --- and continue at the next line."

But the INSERT statement that code builds will definitely trigger an error. You can confirm that fact if you copy the output of Debug.Print strSQL from the Immediate window, create a new query in the query designer, switch the query to SQL View, paste in the statement text and try to run it.

When you have a field name which includes a space, you must enclose it in square brackets so the db engine recognizes it as one identifier instead of two. I would also bracket Name because it's a reserved word, but I doubt it actually contributes to the problem here:

"INSERT INTO Employee ([Name], ATUUID, [Job ID], [Team ID], [Start Date], Comments)"

Beyond that, I suggest you use a temporary QueryDef based on a parameter query, supply the parameter values, and Execute it. 

'On Error Resume Next '<-- leave this disabled, AT LEAST while debugging!
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "INSERT INTO Employee ([Name], ATUUID, [Job ID], [Team ID], [Start Date], Comments)" & vbCrLf & _
    "VALUES (pName, pATUUID, pJobID, pTeamID, pStartDate, pComments);"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSQL)
With qdf
    .Parameters("pName").Value = Me.Employye_Name.Value
    .Parameters("pATUUID").Value = Me.ATTUID.Value
    .Parameters("pJobID").Value = Me.cboFunc.Value
    .Parameters("pTeamID").Value = Me.cboTeam.Value
    .Parameters("pStartDate").Value = Me.Start_Date.Value
    .Parameters("pComments").Value = Me.Comments.Value
    .Execute dbFailOnError
End With
Debug.Print db.RecordsAffected

Upvotes: 4

Related Questions