MS Access VBA: Added columns to table but now SQL "INSERT INTO" won't work anymore

user_16980717 0
Viewed 168

I added two new fields in our table "MTOStudy" & "OtherDesc" as Short Text data types. The other data types within the table are Number and Yes/No values.

However, adding the two columns and updating the table's respective form in Access I can't get to update the table after clicking the button.

Things I've confirmed:

  1. The button works, using the message box function.
  2. Variables line up with the table's.
  3. The code is identical to the working function excluding the addition of the "MTOStudy" and "OtherDesc" variables
  4. The Error Code I've been seeing is "Error number: 3078 ; The MS Access db engine cannot find the input table or query '128'. Make sure it exists and that its name is spelled correctly."

Debug's line: INSERT INTO tbl_MTO_vs_ETO ([Order], [Line], [MTO], [ETO], [DUP], [MTOStudy], [OtherDesc]) VALUES ( , , -1, 0, 0, "TEST ONE PUMP", "")

Here's the function below:

Private Sub btn_save_Click()
'On Error GoTo Err_Execute

    If Me.Check_MTO = False And Me.Check_ETO = False And Me.Check_DUP = False Then
        MsgBox ("Please select one of the classification options."), vbCritical
    Else
    
    Dim Append_SQL As String, tbl_target As String, _
        target_fields As String, field_values As String, _
        errLoop As Error
    
    tbl_target = "tbl_MTO_vs_ETO"

    target_fields = "([Order]," _
    & " [Line]," _
    & " [MTO]," _
    & " [ETO]," _
    & " [DUP]," _
    & " [MTOStudy]," _
    & " [OtherDesc])"
            
    
    field_values = "(" _
    & " " & Me.order & "," _
    & " " & Me.line & "," _
    & " " & Me.Check_MTO.Value & "," _
    & " " & Me.Check_ETO.Value & "," _
    & " " & Me.Check_DUP.Value & "," _
    & " """ & Me.MTOStudy.Value & """," _
    & " """ & Me.OtherDesc.Value & """)"
    
    Call Check_MTO_Dropdown

    Append_SQL = "INSERT INTO " & tbl_target & " " & target_fields & " VALUES " & field_values
    
    CurrentDb.Execute Append_SQL
    
    Debug.Print Append_SQL

    On Error GoTo 0
    
Err_Execute:
    
    If DBEngine.Errors.count > 0 Then
    For Each errLoop In DBEngine.Errors
    MsgBox "Error number: " & errLoop.Number & vbCr & _
    errLoop.description
    Next errLoop
    End If

    'DoCmd.Close acForm, Me.name
    'MsgBox ("Test"), vbOKOnly
    
    End If
End Sub
ms-access vba sql
3 Answers

As HackSlash mentioned, I too prefer Recordsets.

If you want to go that route, try this:

Private Sub btn_save_Click()
    
    'Create new record in tbl_MTO_vs_ETO
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
    
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tbl_MTO_vs_ETO")
        
            With rs
                .AddNew
                    ![Order] = Order
                    ![Line] = Line
                    ![MTO] = Check_MTO.Value
                    ![ETO] = Check_ETO.Value
                    ![DUP] = Check_DUP.Value
                    ![MTOStudy] = MTOStudy.Value
                    ![OtherDesc] = OtherDesc.Value
                .Update
            End With
    
        rs.Close
        db.Close
    
        Set rs = Nothing
        Set db = Nothing

End Sub
user_183116720

The VALUES clause of your INSERT statement would trigger Error 3134: "Syntax error in INSERT INTO statement" because no values are included before each of the first two commas:

... VALUES ( , , -1, 0, 0, "TEST ONE PUMP", "")
            ^ ^

You need to supply something for each of the listed fields. If those first two fields allow Null, you should be able to avoid the error using that:

... VALUES (Null, Null, -1, 0, 0, "TEST ONE PUMP", "")
user_773350

If MTOStudy or OtherDesc have quote/apostrophe characters in them (" or ') this will blow up unless you filter these characters out of the input or write yet more code to handle them. You're much better off using a recordset as HackSlash notes above.

user_182173140
Related