Solved SQL code does nothing (1 Viewer)

[cheberdy]

Hello,
so I have built a table trace log with the attributes modification date, item name and item number. I have a table article which has the attributes article name,quantity and article number. To create a new article there is a form for the article table. I now wanted to track the quantity of items by date. For this I have this SQL
Code

DoCmd.RunSQL "INSERT INTO date log SELECT * FROM article " & "WHERE articlenumber=" & articlenumber.

And I put it in the properties sheet of the form under "events" under "before update". The result was that every time I change the quantity of the item, it makes me a new entry in the trace log table with the date. This is the way it should be. One thing is missing though, the current quantity. That's why I tried a little bit, and wanted to add my SQL code under "after update". But nothing happened, neither a result was displayed in the table nor an error code. What could be the reason for this?

 

[Gasman]

Anything with spaces in it's name need to be surrounded by [].
Best not to have spaces tbh

Plus I *think* you need execute for update queries?

 

[cheberdy]

Anything with spaces in it's name need to be surrounded by [].
Best not to have spaces tbh

Plus I *think* you need execute for update queries?

it had no spaces, that is not the reason

 

[plog]

What could be the reason for this?

Most likely your code, or part of it, didn't run. Not saying it didn't fire when it was suppose to, but perhaps there's a conditional in there that isn't being met. Or the WHERE condition isn't being met and nothing is being returned from the SELECT and it successfully inserts no records.

Provide the entire code please.

 

[cheberdy]

Most likely your code, or part of it, didn't run. Not saying it didn't fire when it was suppose to, but perhaps there's a conditional in there that isn't being met.

Provide the entire code please.

That is all

 

[plog]

If that's the case articlenumber is NULL. Because it doesn't use a prefix (Me!, Forms!MyForm) It is a local variable which means it must be assigned in the sub/function itself. Since your code is only one line, it obviously doesn't get set and thus your SELECT returns nothing. You need to properly assign articlenumber

 

[cheberdy]

If that's the case articlenumber is NULL. Because it doesn't use a prefix (Me!, Forms!MyForm) It is a local variable which means it must be assigned in the sub/function itself. Since your code is only one line, it obviously doesn't get set and thus your SELECT returns nothing. You need to properly assign articlenumber

why did it work before

 

[plog]

I wasn't there then, but I do know the formula.

[WorkingVersion] = [BadVersion] - [ChangesYouMade]

 

[cheberdy]

I wasn't there then, but I do know the formula.

[WorkingVersion] = [BadVersion] - [ChangesYouMade]

so the solution would be article.articlenumber

 

[Isaac]

date log

no brackets?

ending with a period?

 

[cheberdy]

date log

no brackets?

ending with a period?

my mistake there was no period and there were no spaces

 

[plog]

If articlenumber is an input on the form that calls the code, then it would be Me!articlenumber

 

[Isaac]

my mistake there was no period and there were no spaces

Always copy and paste the code exactly.
Else how does anyone know their time critiquing it won't be wasted

 

[arnelgp]

what is Date Log, in your query?
if it is a table, you need to enclosed in bracket:

DoCmd.RunSQL "INSERT INTO [date log] SELECT * FROM article WHERE articlenumber=" & articlenumber

if date your table name, and log is the field?

DoCmd.RunSQL "INSERT INTO date( log ) SELECT * FROM article WHERE articlenumber=" & articlenumber

 

[cheekybuddha]

if date your table name, and log is the field?

DoCmd.RunSQL "INSERT INTO date( log ) SELECT * FROM article WHERE articlenumber=" & articlenumber

This can only work if table article has only one field!

 

[Isaac]

I love the title of this thread.

It's exactly how I feel some days at about 4:00 in the afternoon when looking at my screen.

 

[ebs17]

Thread is solved?

For third parties: If you want to react to a record change in the form and want to write a record to a log table, you should take the data from the form for this append query.
Before saving, the data is not yet in the table record and of course cannot be used from there. A post-save update query also has some risks.
Overall, however, two table accesses are overdressed because of one record.
How can this be solved cleanly: Use a parameter query that writes the data of the current form record to the log table.

Saved Query (qinsArticleQuantity)

PARAMETERS
   parArticleName Text(255),
   parArticleNumber Long,
   parQuantity Double
;
INSERT INTO
   LogTable(
      ModificationDate,
      ItemName,
      ItemNumber,
      Quantity)
VALUES(
   Now(),
   parArticleName,
   parArticleNumber,
   parQuantity
)

A help function for executing a parameter query (action queries) via VBA can be used in many ways:

Public Function ExecuteParamQuery(ByVal MyDB As DAO.Database, ByVal AnyQuery AS String, _
                                  ParamArray QueryParams() As Variant) As Long
' Note: parameter name and assigned value are always passed in pairs, per ParamArray
    Dim qd As DAO.QueryDef
    Dim i As Long

    If UBound(QueryParams) Mod 2 = 1 Then             ' pairs parameter - value exist
        Set qd = MyDB.QueryDefs(AnyQuery)      
        For i = 0 To UBound(QueryParams) Step 2
            qd.Parameters(QueryParams(i)) = QueryParams(i + 1)
        Next
        qd.Execute dbFailOnError
        ExecuteParamQuery = qd.RecordsAffected
        qd.Close
        Set qd = Nothing
    End If
End Function

Call of action:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    ExecuteParamQuery CurrentDb, "qinsArticleQuantity", _
                      "parArticleName", Me.txtArticleName, _
                      "parArticleNumber", Me.txtArticleNumber, _
                      "parQuantity", Me.txtQuantity
End Sub