Keep track of each transaction?

[voidcranium]

Hello,
I'm not exactly sure where to put this so I put it here.

Here is my question.
I have an existing Work Order database that prints out a Work Order every time we need to produce that item but, each time we print it, it overwrites the previous data: ie; date, quantity ran, quantity ordered, etc.

What I would like to do is keep track of this information that has previously been overwritten so we can now see the history of each item.

Anybody have a quick easy way to do this?

thanks

 

[DavidAtWork]

all you need is a new table such as 'Completed Order' table to store the values "date, quantity ran, quantity ordered, etc." you listed above. Where are these values stored before printing, if they are just values in text boxes on a form, then you could create an append query that would insert these values into 'Completed Order' table. The action of running this query could be tied to the printing of the order
David

 

[voidcranium]

all you need is a new table such as 'Completed Order' table to store the values "date, quantity ran, quantity ordered, etc." you listed above. Where are these values stored before printing, if they are just values in text boxes on a form, then you could create an append query that would insert these values into 'Completed Order' table. The action of running this query could be tied to the printing of the order
David

Thanks for the info David.
Im looking into an append query.
I have a form that uses the Open Order query that has a command button I use for perform certain calculations and then flags the item as DONE.
Could I put an append query in the code section of my command button?
I haven't done something like this before so want to be sure about everything before I do something.

Thanks

 

[voidcranium]

Ok, I got this to work.
I created an append query; this is the SQL code for the query.

INSERT INTO History ( [Item #], [Part #], [Item Description], Stock )
SELECT [Forms]![Output Calculations].[Item #] AS [Item #], [Forms]![Output Calculations].[Part #] AS [Part #], [Forms]![Output Calculations].[Item Description] AS [Item Descriptoin], [Forms]![Output Calculations].[Stock] AS Stock;

I put this code in my button click event.

DoCmd.SetWarnings False
DoCmd.OpenQuery "AppendToHistory", acViewNormal
DoCmd.SetWarnings True

It does what I wanted it to do BUT, I would like to put the SQL code in the VBA code for my button click event.

How can I do that?

I think this is how I need to start but need some suggestions on how to make it work.

Dim strSQL As String
strSQL = "INSERT INTO History(Item #, Part #, Item Description)" & " VALUES ('" Me.[Item #] "', '" Me.[Part #] "', '" Me.[Item Description] "');"

Thanks

 

[DavidAtWork]

Yes you can v easily using:
Docmd.RunSql(strSQL)
You strSQL above:
strSQL = "INSERT INTO History(Item #, Part #, Item Description)" & " VALUES ('" Me.[Item #] "', '" Me.[Part #] "', '" Me.[Item Description] "')"

The syntax is not correct particularly around your values fields, it depends on their data types, but assuming they are all strings, then it should be:
strSQL = "INSERT INTO History([Item #], [Part #], [Item Description]) " _
& "VALUES (""" & Me.[Item #] & """, """ & Me.[Part #] & """, """ & Me.[Item Description] & """)"
If any of these values are number data types then replace for example:
""" & Me.[Part #] & """ with '" + str(Me.[Part #]) + "' I know it has a str wrap around, but trust me it will work for number data types
This code will work if executed under the button_click event, if it's executed from a module, then you'd have to replace the Me. with: [Forms]![Output Calculations].
David

 

[voidcranium]

Yes you can v easily using:
Docmd.RunSql(strSQL)
You strSQL above:
strSQL = "INSERT INTO History(Item #, Part #, Item Description)" & " VALUES ('" Me.[Item #] "', '" Me.[Part #] "', '" Me.[Item Description] "')"

The syntax is not correct particularly around your values fields, it depends on their data types, but assuming they are all strings, then it should be:
strSQL = "INSERT INTO History([Item #], [Part #], [Item Description]) " _
& "VALUES (""" & Me.[Item #] & """, """ & Me.[Part #] & """, """ & Me.[Item Description] & """)"
If any of these values are number data types then replace for example:
""" & Me.[Part #] & """ with '" + str(Me.[Part #]) + "' I know it has a str wrap around, but trust me it will work for number data types
This code will work if executed under the button_click event, if it's executed from a module, then you'd have to replace the Me. with: [Forms]![Output Calculations].
David

Thank you David that works.
I have one problem tho.
If I have an item with quotations in the description area I get an error message - Error 3075.
Some of our Items are listed as "Special" or "Sale" or 11" Blue.
This is what Im using.

Dim strSQL As String
strSQL = "INSERT INTO History([Item #], [Part #], [Item Description], Stock) " _
& "VALUES (""" & Me.[Item #] & """, """ & Me.[Part #] & """, """ & Me.[Item Description] & """, """ & Me.Stock & """)"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

 

[DavidAtWork]

for each variable instead of: """ & Me.[Part #] & """
try using: """ & Replace(Me.[Part #] , " ' ", " ' ' ") & """
or: ' " & Replace(Me.[Part #] , " ' ", " ' ' ") & " '
be careful as I've put some extra spaces in there so you can see where it should be a
' and "

David

 

[voidcranium]

Thanks David that works perfectly.

Now I just need to understand why and how that works.

 

[DavidAtWork]

which of the 2 lines worked. I've only come across this once before and I couldn't remember which one worked
David

 

[voidcranium]

which of the 2 lines worked. I've only come across this once before and I couldn't remember which one worked
David

The Bottom one.

'" & Replace(Me.[Item Description], "'", "''") & "', '" & Replace(Me.Stock, "'", "''") & "'

 

[JANR]

If you struggle with delimiters or data containg quotes then you can a create on-the-fly parameter query:

Dim qdf As DAO.QueryDef

Set qdf = CurrentDb.CreateQueryDef("", "Insert Into History([Item #],[Part #],[Item Description],Stock) " & _
                                       "Values (p0,p1,p2,p3)")
With qdf
    .Parameters(0) = Me.[Item #]
    .Parameters(1) = Me.[Part #]
    .Parameters(2) = Me.[Item Description]
    .Parameters(3) = Me.Stock
    .Execute
    .Close
End With

or you can use the full form refrence to your controls:

Dim sSQL As String

sSQL = "Insert Into History([Item #],[Part #],[Item Description],Stock) " & _
       "Values(Forms![COLOR="Red"]MyForm[/COLOR]![Item #], " & _
       "Forms![COLOR="Red"]MyForm[/COLOR]![Part #], " & _
       "Forms![COLOR="red"]MyForm[/COLOR]![Item Description], " & _
       "Forms![COLOR="red"]MyForm[/COLOR]!Stock)"
DoCmd.RunSQL sSQL

Just change whats marked in red to the real formname, also note that you must use:

DoCmd.RunSql sSQL

and NOT

Currentdb.Execute sSQL

since .execute goes strait to JET/ACE and it has no knowlage of the form refrence.

JR

 

[voidcranium]

Thanks JANR, Ill try these out this weekend. I want to know all this stuff.

Is there an easy fix for empty text boxes? I'm running into a problem where if the text box has nothing in it I get an error message.

Right now I'm using a bunch of IF statements to put a zero in it if it's empty just to get it working.

If IsNull(Me.InkColor3) Then
InkColor3 = 0
End If

 

[JANR]

Look into the Nz() function

Nz([Control],Alternate Value if null)

JR