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

[Pat Hartman]

The original question was about how to change "all" the places where material shows when it is changed in the primary table.

 

[rainbows]

with the material description we manually do a PDF file on the product and the material so i can refer to that as say rev A after changing i make it revB for consumable parts i dont have a record after changing it

 

[rainbows]

The original question was about how to change "all" the places where material shows when it is changed in the primary table.

it does that , the original question was how to a report all the product numbers that part was located in and if possible record the old text and new text in that report

 

[MajP]

If your database was properly structured, this would be doable. Does not look properly structured as far as I can see.
Basically you make an audit log and fire it on the controls beforedelconfirm, afterdeleconfirm, beforeupdate, afterupdate.

This would store the before and after properties.
Since you would know the ID of the item change, you simply have to run a query show records related to that item and include the before and after values.

Allen Browne - Creating an Audit Log

How to create an audit log to record the history of changes to records in a Microsoft Access database - edits, inserts, and deletions.

allenbrowne.com

 

[Gasman]

Here is one way.
Personally I think you should be storing the actual description, so if the material changes in someway, let's say from steel to titanium, then it would be recorded that early products had the steel version? Or would you create a new MaterialID for that?

Anyway, this gives you something to start with.
Open frmStockList and look for a stock number. I used 5060B9513, amend the description and move off the record.
That triggers the BeforeUpdate event which produces the report rptStockDescChange

Just the basics, you can make it pretty.

Edit: You will likely need to add date/time to the report filename, else it will be overwritten each time a change is made.
As I said this is just the basics.

 

[rainbows]

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

 

[Gasman]

As far as I can see, you are using the links correctly, just obscured by the Lookup field.

Have you looked at what I posted?

All you do is amend a Material record description in the Stocklist table.
That propogates through to your Products form with the latest amendment?

The report reports on what it used to be and the products affected.
So apart from using Lookup fields it is working as it would.

Now whether that is what you need, is another matter, however it is what you asked for.

No updates are needed except to the stocklist record description field?

I really hope you are doing this on a copy of your real DB, else you can come unstuck really quick.

 

[rainbows]

yes it just a copy of a piece of the database as the database does sales orders, engineering , purchasing , production , stores, quality , invoicing
albeit just a novice version but it works so know i am trying to add the engineering change note information into it as well as an audit log . yes i have just checked what you posted and see how that works also so i will try and sort out what could be the best way to do it

can you tell me how i can do

1, just take over the feilds i change and not all the fields.
2. if i do a report and it does tell me there is 50 products affected and it list them in a line and that takes up say 3 pages can then be all spread out so it it one page

steve

 

[Gasman]

1. Not sure what you mean by that? My method does not 'take over any fields'? You amend the description and that is it.?
2. You can make your report a multiple column report

You could also make the variables, OldText,NewText,StockNumber Tempvars, which would make amending the report easier, as it stands now, it needs the those passed from the form for the report to work, so tweaking the report is a little laborious.

However that does not distract from the logic.

 

[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'

 

[Pat Hartman]

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

 

[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?

 

[Pat Hartman]

I assumed he was correcting a typo. Changing something material about the part would always be a new part number. This is not something like a price change where the item remains the same and the price changes over time.

If in fact the OP is planning on reusing partnumbers, that means that all historical reporting needs to select and/or join on partName rather than PartID. This is a such a seriously bad idea, I don't even want to think about it.

 

[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.