when updating the description of a part then create ECN report showing which products it has effected

[Gasman]

Private Sub Form_BeforeUpdate(Cancel As Integer)

docmd.RunSQL "INSERT INTO stocklistchanges select * FROM stocklist " & _
"WHERE MaterialID=" & MaterialID



End Sub

i tried this yesterday and it reports the changes i have made . but the problem with this is it reports all the fields which i would imagine soon fills the database? i had to change the id and stocknumber to index to no duplicates. so still not sure if i did this correct ??
steve


View attachment 107753

That is not a report, but a query?
Regardless, it is not going to 'fill up the database'

 

[Gasman]

@Gasman, when the spec for a part changes, a new part number is ALWAYS issued. Otherwise the confusion would be never ending.

Pat,
If that was the case then we would not have this problem, surely?

 

[LarryE]

@rainbows
The attached file has new VBA code to record changes to the Material field in the StockList table using the Stocklist for engineering form. Changes are recorded in the new TblDataChanges table. You can see the code I used by viewing the Material field After Update event. The code uses the Material field OldValue property. You can see which products were affected in the StockListMaterialChanges query. You can use the query to create a report of changes made.

Since I have no idea what your table and field relationship design looks like, I think this is the best I can do for you. In this file, I changed all the table field lookups back to textboxes, but you do what you want. All of us have given our best advice.

 

[LarryE]

@rainbows
Would it be possible for you to take a screenshot or snip shot of your relationships so we can see what you have? It could make a difference in how your changes report is structured to show what products are affected by changes to the stocklist table.

Larry

 

[rainbows]

HI , I have been away for a couple of days so did not get time to see what was written . there seems to be many things i am not sure about now

like this. it seems i think that everyone says this is what it should look like yet i make use i get the description showing in the material column

the second screen shot i have no idea how you got the old and new value to show up . but i am working my way throu what has been sent to me so i can understand what i am doing wrong and what i am doing correct .

just for interest if someone creates a description on a part without noticing it was done incorrectly it will also be transfered to other documents also. then if we then notice that it was wrong we have to record the changed and update the description of that part on all documents

steve

 

[LarryE]

1. Open the Stocklist for engineering subform
2. View the Material field After Update Event in the VBA window
3. You will see:

Private Sub Material_AfterUpdate()
Dim OldValue As String
Dim NewValue As String
Dim MaterialID As Double
MaterialID = Me.MaterialID
OldValue = Me.Material.OldValue
NewValue = Me.Material
DoCmd.RunSQL "INSERT INTO TblDataChanges ( MaterialID, ChangeDate, ControlName, OldValue, NewValue ) VALUES ('" & MaterialID & "',Now(),'Material' ,'" & OldValue & "', '" & NewValue & "');"
Exit Sub
End Sub

That is how to write changes in the Material field to the TblDataChanges

Of course the entries were just for test purposes. You can delete them from the table if you wish.