Insert works in SQL VIEW but not in VBA

[DemonDNF]

This code:

Private Sub txtPart_LostFocus()
    Dim db As Database
    Dim strSQL As String
    Dim lngPartKey As Long
    Dim lngSpecNaKey As Long
    Dim intLoop As Integer
    
    If IsNumeric(txtKey) And strPreviousPart <> txtPart Then
        lngPartKey = Me.txtKey
        lngSpecNaKey = DLookup("Key", "tblSpecs", "Spec = 'n/a'")
        Set db = CurrentDb

        For intLoop = 1 To 6
            strSQL = "INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (" _
                     & lngPartKey & ", " & lngSpecNaKey & ", " & intLoop & ");"
            db.Execute (strSQL)
            Debug.Print strSQL
        Next intLoop

        db.Close
    End If
End Sub

Generates these debugs:

INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 2);
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 3);
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 4);
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 5);
INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 6);

But no entries are added to the table.

I open a query in design mode, paste the first debug above in SQL VIEW and run it:

INSERT INTO tblPartSpecs (PartID, SpecID, Sequence) VALUES (19, 14, 1);

And the entry is added in the table.

PartID and SpecID are long integer, Sequence is integer.

I originally had Me.txtKey in the SQL, but I saw a comment in a post about that being a possible culprit. So I copied it into a long integer, still didn't work (latest trial above).

Help!

Robert

 

[Galaxiom]

Try changing the Execute to include the second parameter and it might tell you what it doesn't like.

db.Execute strSQL, dbFailOnError

 

[DemonDNF]

Awesome. Error messages make debugging so much easier.

I had to move the code to AfterInsert so that matching entry was in tblParts.

Robert