Why does the insert into not work with my variables?

[cheberdy]

I have these two text fields Quantity1 and Textstore. When someone clicks the button I want to feed the Storage function with the values entered in the text fields.
With this function I access two tables. However, there is a problem with the Insert into statement. It does not recognize the VALUES. Whenever I execute, a parameter question comes up for the three values.
I have already checked if the variables contain values, and they do.
What am I doing wrong ?

Private Sub Store_Click()
    Dim Itemnumber As Integer
    Dim Changeamount As Integer
    Changeamount = Quantity1.Value
    Itemnumber = Textstore.Value
   
    Storage Changeamount, Itemnumber
End Sub

Private Sub Storage(Changeamount As Integer, Itemnumber As Integer)
    Dim Quantity As Integer


    Quantity = DLookup("Quantity", "Item", "Itemnumber=" & Itemnumber)
   
    DoCmd.RunSQL "UPDATE item SET quantity=" & quantity + Changeamount & " WHERE Itemnumber=" & Itemnumber
   
    DoCmd.RunSQL "INSERT INTO Goodsmovement(Itemnumber, Quantity, Quantitychange, [type of change])VALUES(Itemnumber, Quantity, Changeamount, 'Store')"
End Sub

 

[Jason Lee Hayes]

Dim Part Number as Integer?

 

[cheberdy]

Dim Part Number as Integer?

Sorry, lost in translation

 

[Josef P.]

What am I doing wrong ?

You don't check the SQL statement, so you don't see the error.

Private Sub Storage(Changeamount As Integer, Itemnumber As Integer)
    Dim Quantity As Integer
    dim SqlText as string

    Quantity = DLookup("Quantity", "Item", "Itemnumber=" & Itemnumber)
  
    SqlText = "UPDATE item SET quantity=" & quantity + Changeamount & " WHERE Itemnumber=" & Itemnumber
    debug.print SqlText
    stop ' see text in immediate windows
    DoCmd.RunSQL SqlText
    'or currentDb.Excecute SqlText, dbfailonerror
  
    SqlText = "INSERT INTO Goodsmovement(Itemnumber, Quantity, Quantitychange, [type of change])VALUES(Itemnumber, Quantity, Changeamount, 'Store')"
    debug.print SqlText
    stop ' see text in immediate windows =>  ... values are missing :)
    DoCmd.RunSQL SqlText

End Sub

 

[cheekybuddha]

Separately, you probably ought not store the quantity in the Item table.

Just calculate as required it from the sum of goods movement for that item.

 

[ebs17]

Why does the insert into not work with my variables?

Additionally: Variables (VBA objects) are unknown for Jet/ACE, in the query itself these cannot be resolved into the values they contain.
There are different ways to deal with this.

 

[cheberdy]

You don't check the SQL statement, so you don't see the error.

Private Sub Storage(Changeamount As Integer, Itemnumber As Integer)
    Dim Quantity As Integer
    dim SqlText as string

    Quantity = DLookup("Quantity", "Item", "Itemnumber=" & Itemnumber)
 
    SqlText = "UPDATE item SET quantity=" & quantity + Changeamount & " WHERE Itemnumber=" & Itemnumber
    debug.print SqlText
    stop ' see text in immediate windows
    DoCmd.RunSQL SqlText
    'or currentDb.Excecute SqlText, dbfailonerror
 
    SqlText = "INSERT INTO Goodsmovement(Itemnumber, Quantity, Quantitychange, [type of change])VALUES(Itemnumber, Quantity, Changeamount, 'Store')"
    debug.print SqlText
    stop ' see text in immediate windows =>  ... values are missing :)
    DoCmd.RunSQL SqlText

End Sub

the values are correct

 

[Josef P.]

Output in immediate window:
INSERT INTO Goodsmovement(Itemnumber, Quantity, Quantitychange, [type of change])VALUES(Itemnumber, Quantity, Changeamount, 'Store')
Where do you see the values in this string?

 

[cheberdy]

Output in immediate window:
INSERT INTO Goodsmovement(Itemnumber, Quantity, Quantitychange, [type of change])VALUES(Itemnumber, Quantity, Changeamount, 'Store')
Where do you see the values in this string?

When I go over it gives like itemnumber= 1

 

[Josef P.]

This is a String!
Compare the insert statement with the update statement.

 

[cheberdy]

This is a String!
Compare the insert statement with the update statement.

It seems to be an integer

 

[cheekybuddha]

no its an integer

No, the SQL you output and run is a string.

So you must concatenate the actual values from your variables in to the string.

Look how you did it with your UPDATE statement (which is still not required, even if you choose to ignore the advice!)

 

[cheberdy]

No, the SQL you output and run is a string.

So you must concatenate the actual values from your variables in to the string.

Look how you did it with your UPDATE statement (which is still not required, even if you choose to ignore the advice!)

oh, yes of course

 

[The_Doc_Man]

Whenever I execute, a parameter question comes up for the three values.

You were given pointers earlier in this thread about how to make your code work. I'm going to answer this question in a different way, to explain the error occurred and what it means.

Whenever you execute SQL and get an "Enter Parameter xxxxx" pop-up, whatever name is in the place of "xxxxx" was in named in the query but SQL cannot find it and therefore doesn't know what it is. SO ... it asks you using a parameter input box. But WHY does it not know how to find "xxxxx"?

When you use VBA, you are in the VBA user interface. Even when the code is running, that code is under control of Access itself, MSACCESS.EXE, the Access Main program. When you do a "DoCmd.RunSQL sql-string" or a "CurrentDB.Execute sql-string" you are transferring an SQL string to the behind-the-scenes Access database engine, which runs in a separate memory segment. This is true whether it is native Access that uses the ACE database engine or some SQL-based backend server such as ORACLE, SQL Server, Ingres, SYBASE, or something else.

The key is that in ALL such cases, your "xxxxx" is defined in the Access context but the SQL executes in its own context that DOES NOT SHARE MEMORY with Access. So SQL can't look for the "xxxxx" value because Windows won't let programs randomly look in other programs' memories. (It's a Microsoft security thing.)

The solution is that your SQL query string must contain ALL of the values it needs BEFORE you pass it along - though if the values come from another table there are ways to make them available through other SQL methods. If you are doing one-at-a-time INSERT or UPDATE statements, the odds greatly favor that you have to do string substitution to include the literal values in the SQL string BEFORE you pass it to the SQL environment.