User changes in a table/form

[ukdane]

If a User changes the data in a form/table, how can I find out excatly which row of data has been changed, and what it was changed from and to? (To will be recorded on the table, but that means that the old data will be overwritten)

The reason I need to know this, is for use with my warehouse stock database.

Everything works fine, as long as the user doesn't make an error, and have to correct an order. The reason for this, is that as soon as the data is input into the form, a series of calculations take place, telling the user which area of the warehouse the user should take the stock from.
If the user then edits the amount of stock required, the database will need to reset the data, and then recalculate where to pull the stock from(as there might not be enough stock in the area originally suggested).

The process I need to run through if the user needs to change the data is:
1) Add the original amount of stock pulled back to each area it was pulled from.
2) Change the status of the changed data from "Calculated" to "Requires Calculation"
3) Run the code that is activated if an order row has the status "Requires Calculation".

Hope this makes a little bit of sense.

[OBP]

Hello again, I have VBA code for a form that logs all of the changes made to it's records and stores the log in a Table.
This could be used by you to do what you want, however if you are really only interested in certain fields you could just store those.
I am not sure how you can log the other changes to the Stock Location areas, do you use a query to do that or VBA that we generated on here?

[ukdane]

I've been playing around with various solutions, and believe that all I need to know is which row is being edited. That way I can get the unique id, and change the 1/0 field accordingly.
How do I find out the uniqueid of the row that is being changed?
(I only need to know the id when it is being changed, NOT when it is being created for the first time).

Do you have any code you can share for doing this?

Edit: With regards to the stock location changes, these are stored in another table, along with the "unique id" for the row in the order that it refers to. Therefore if I know which unique id is being changed, I was thinking, I can look this id up in the stock location table, and either delete, or create a negative copy (to add the stock back in).

[OBP]

I assume that the UniqueId is on the form when the User inputs the Change?

[OBP]

Basically there are 2 ways to do this, one the a Recordsetclone and the other with a Public Variable.

[ukdane]

It isn't on the userform, but only because I didn't put it there, as it isn't necessary for the user to see. BUT it can be put there, as it is just an autonumber created when the user adds a new line to the dataform.

[ukdane]

Basically there are 2 ways to do this, one the a Recordsetclone and the other with a Public Variable.

Go on...

[OBP]

Which one do you want to try?
What is the name of the Unique Key Field on the Form?

[ukdane]

As I'm learning, I'm happy to try both ideas, and see which one works best for me.

(Start with the easiest) .... what's a recordsetclone?

The uniqueid, is called salesorderlineid: you should be able to see it on the relationships table in the other post, on the TblSalesOrderLine.

[OBP]

OK, in the form's Before Update Event Procedure enter

[vba]Dim rs As Object
Set rs = Me.RecordsetClone
msgbx "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock
rs.Close
Set rs = Nothing
[/vba]
Where fieldnamewhichhasstock is the actual name of the stock value that is being changed.
The Recordsetclone (rs) value is the one when the record is "current" and the me. value is the new one that will be written to the table.

That should show you how it works.

[ukdane]

[vba]m rs As Object
Set rs = Me.RecordsetClone
msgbox "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock
rs.Close
Set rs = Nothing
[/vba]

Tried this, and I'm getting an error in the line:

msgbox "For record " & me.salesorderlineid & " Old value = " & rs.fieldnamewhichhasstock & " New Value = " & me.fieldnamewhichhasstock

the me.fieldnamewhichhasstock (which should be the same as the text i've used as re.fieldnamewhichhasstock) is returning an error: "Sub or function not
defined"


Just to be clear, this is the actual code that I used
[vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim rs As Object
msgbox "For record " & Me.saleorderlineid & " Old value = " & rs.saleorderlineunitrequired & " New Value = " & Me.saleorderlineunitrequired
rs.Close
Set rs = Nothing
End Sub[/vba]

I removed the " & Me.saleorderlineunitrequired" and I then got a similar error for the "Me.saleorderlineid".
Something isn't right. :-)

Edit: I've just noticed I was missing the line [VBA]Set rs = Me.RecordsetClone [/VBA]

[ukdane]

My mega post has now been deleted twice!

I'll post in short bursts to avoid this happening for a third time.

I changed the Msgbx to Msgbox
so the code [VBA]MsgBox "For record " & Me.saleorderlineid[/VBA]
this now returns the number of the line that has been changed.

This is good.
I now need to use this number to do two things.
1) Table TblSaleOrderLine contains the following info:

• saleorderlineid (this is a unique id created by an autonumber and is the number found above.)
• saleorderid (this is the sales order id)
• productid (this is the product id)
• saleorderlineunitrequired (this is the number of units that are required)
• sakeprocessed (this is a tick box it is ticked when a sereis of code/marcos have been run. This needs to be reset to not ticked/0 for the row that has been changed.

and post....

[ukdane]

Yes (succes)
The second thing that I need to do is:
Table TblOrderPull contains:

• pullid (a unique autonumber id)
• productid (the product number)
• areaid (the location where the goods have to be taken from)
• saleorder id (the number of the sale order)
• saleorderline (this is the number that needs to be located, and could occur on this table more than once)
• unitstopull (this is the number of units that must be pulled from each location)

I need to to one of two things, (unsure which is best)
Either: delete each occurance of the salesorderline (this is probably more accurate with regards to other calculations)
Or: the entire row needs to be copied, and the unitstopull number needs to be negated (so that the total product/area/unitstopull = 0) I'm not so sure this will be as accurate.


Any clues/code/help with both these things problems please?

[ukdane]

With regards to the first question, I came up with the following code:
[vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myconnection As ADODB.Connection
Dim myRecordChange As New ADODB.Recordset
Dim myorderline As Variant
myRecordChange.ActiveConnection = myconnection
myorderline = Me.saleorderlineid
myRecordChange.Open "TblSaleOrderLine", , adOpenKeyset, adLockOptimistic
myRecordChange.MoveFirst
While Not myRecordChange.EOF
If myRecordChange.Fields("saleorderlineid").Value = myorderline Then
myRecordChange.Fields("saleprocessed").Value = 0
Else
End If
Wend
myRecordChange.Close
Set myRecordChange = Nothing
End Sub[/vba]

BUT, the line [vba]myRecordChange.Open "TblSaleOrderLine", , adOpenKeyset, adLockOptimistic [/vba] is causing a runtime error 3709 "The connection can not be used to perform this operation. It is either closed or invalid in this context."

[ukdane]

[vba]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid

myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
myRecordChange.Update
End If
myRecordChange.Close
Set myRecordChange = Nothing
End Sub[/vba]

Tried the above code, but the saleprocessed field doesn't change from ticked to unticked.

[OBP]

Use
myRecordChange.Fields("saleprocessed").Value = 0

[ukdane]

Hi OBP,
Actually I did have it set to 0 to start with.
= false does work... but the tabel doesn't keep the change.
the myRecordChange.update works, and the table changes,
but the change isn't kept, when the sub ends.

[ukdane]

Still not keeping the changes.

[ukdane]

Can I somehow use this
[VBA]
mySQL = "UPDATE TblSaleOrderLine SET TblSaleOrderLine.saleprocessed = 0" & _
"WHERE (((TblSaleOrderLine.saleorderlineid)= " & myorderline & "))"
[/VBA]
within the beforeupdate instead?

[ukdane]

Finally sussed out what was missing wrong.
[VBA]Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim myRecordChange As Recordset
Dim myorderline As Variant
Set myRecordChange = Me.RecordsetClone
myorderline = Me.saleorderlineid

myRecordChange.FindFirst "saleorderlineid = " & myorderline
If myRecordChange.NoMatch Then
'MsgBox "record not found"
Else
myRecordChange.Edit
myRecordChange.Fields("saleprocessed").Value = False
myRecordChange.Update
End If
myRecordChange.Close
Set myRecordChange = Nothing
End Sub[/VBA]

I added [VBA]Me.[saleprocessed].Value = False
[/VBA]
in between the else and end if.... and hey presto, it seems to work!