Microsoft Access VBA Insert Into Error

Question

The Problem: I keep getting a syntax error for the following statement:

CurrentDb.Execute "INSERT INTO Inventory ([Part #], [Year], [Week], [In], [Out]) " _ 
                & "VALUES ('" & Me.txtFindPart & "'," & Me.txtYear & "," & i & "," _ 
                & Me.Controls("in" & i) & "," & Me.Controls("out" & i) & ");"

Now I have a feeling it is the Me.Controls function that keeps throwing this error but I can't get it to go away.

Context: This is for an update button, where it checks if there is a record, if the two spaces in the form are empty and if neither of these conditions are filled it will create a new record.

Now when this happens the data from textbox in# and out# are used to create the new record.

The Code:

Dim i As Integer
i = 1

Do Until i = 53

    If DCount("[Part #]", "[Inventory]", "([Inventory].[Part #] = '" & txtFindPart & "' AND [Inventory].[Year] = " & txtYear & " AND [Inventory].[Week] = " & i & ")") > 0 Then
        CurrentDb.Execute "UPDATE Inventory SET [In] = '" & Me.Controls("in" & i) & "' WHERE [Part #] = '" & txtFindPart & "' AND [Year] = " & txtYear & " AND [Week] = " & i & ";"
        CurrentDb.Execute "UPDATE Inventory SET [Out] = '" & Me.Controls("out" & i) & "' WHERE [Part #] = '" & txtFindPart & "' AND [Year] = " & txtYear & " AND [Week] = " & i & ";"
        i = i + 1
    ElseIf Me.Controls("in" & i) = Null And Me.Controls("out" & i) = Null Then
        i = i + 1
    Else
        CurrentDb.Execute "INSERT INTO Inventory ([Part #], [Year], [Week], [In], [Out]) " _
                        & "VALUES ('" & Me.txtFindPart & "'," & Me.txtYear & "," & i & "," _
                        & Me.Controls("in" & i) & "," & Me.Controls("out" & i) & ");"
        i = i + 1
    End If
Loop
Me.Requery

Do you actually have textboxs called: in1, in2, in3, in4... and out1, out2, out3, out4... on your form? Also do you get a specific Syntax error? — Newd
– Newd, Commented Jun 22, 2015 at 17:30
Run-Time Error '3134' Syntax Error in INSERT INTO statement. — spriore
– spriore, Commented Jun 22, 2015 at 17:38
@user2517352: Replace CurrentDb.Execute by Debug.Print. This will output all your actual SQL statements into the Immediate Window. Then, edit your question and paste one or two of the SQL statements so we can see the actual SQL that is executed. — Christian Specht
– Christian Specht, Commented Jun 22, 2015 at 20:52
When I replaced the code and clicked on my button it did nothing. Nothing was executed, nothing was updated or added, and there were no error messages or pop up windows. — spriore
– spriore, Commented Jun 23, 2015 at 11:31

Gustav · Accepted Answer · 2015-06-23 11:49:54Z

1

Try this:

Dim i As Integer
i = 1

Do Until i = 53
    If DCount("[Part #]", "[Inventory]", "[Inventory].[Part #] = '" & txtFindPart & "' AND [Inventory].[Year] = " & txtYear & " AND [Inventory].[Week] = " & i & "") > 0 Then
        CurrentDb.Execute "UPDATE Inventory SET [In] = '" & Me("in" & i).Value & "', [Out] = '" & Me("out" & i).Value & "' WHERE [Part #] = '" & txtFindPart & "' AND [Year] = " & txtYear & " AND [Week] = " & i & ";"
    ElseIf Not IsNull(Me("in" & i).Value) And IsNull(Me("out" & i).Value) Then
        CurrentDb.Execute "INSERT INTO Inventory ([Part #], [Year], [Week], [In], [Out]) " _
                        & "VALUES ('" & Me.txtFindPart & "'," & Me.txtYear & "," & i & ",'" _
                        & Me("in" & i).Value & "','" & Me("out" & i).Value & "');"
    End If
    i = i + 1   
Loop
Me.Requery

edited Jun 23, 2015 at 11:49

answered Jun 22, 2015 at 17:40

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

spriore Over a year ago

Run Time Error 3075 Syntax Error (Missing operator) in query expression "100, [Out] = '50' WHERE [Part #] = '1' AND [Year] = 2015 AND [Week] = 1;'.

Gustav Over a year ago

Well, with that little information you leave us guessing.

Christian Specht Over a year ago

@Gustav: That error comes from the UPDATE query in the code from your answer, because you are missing a ' after SET [In] = '" & Me("in" & i).Value & "

Gustav Over a year ago

Thanks Cristian. Edit made; it is present now.

spriore Over a year ago

I figured it out. I wasn't handling null values where I needed too.

|

Collectives™ on Stack Overflow

Microsoft Access VBA Insert Into Error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related