- Local time
- Today, 00:04
- Joined
- Feb 19, 2002
- Messages
- 45,303
The original question was about how to change "all" the places where material shows when it is changed in the primary table.
when updating the description of a part then create ECN report showing which products it has effected
- Thread starter rainbows
- Start date
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
MajP
You've got your good things, and you've got mine.
- Local time
- Today, 00:04
- Joined
- May 21, 2018
- Messages
- 8,888
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.
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
Enthusiastic Amateur
- Local time
- Today, 05:04
- Joined
- Sep 21, 2011
- Messages
- 15,640
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.
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.
Last edited:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
docmd.RunSQL "INSERT INTO stocklistchanges select * FROM stocklist " & _
"WHERE MaterialID=" & MaterialID
End Subi 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
Enthusiastic Amateur
- Local time
- Today, 05:04
- Joined
- Sep 21, 2011
- Messages
- 15,640
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.
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.
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
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
Enthusiastic Amateur
- Local time
- Today, 05:04
- Joined
- Sep 21, 2011
- Messages
- 15,640
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.
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
Enthusiastic Amateur
- Local time
- Today, 05:04
- Joined
- Sep 21, 2011
- Messages
- 15,640
That is not a report, but a query?
Regardless, it is not going to 'fill up the database'
- Local time
- Today, 00:04
- Joined
- Feb 19, 2002
- Messages
- 45,303
Gasman
Enthusiastic Amateur
- Local time
- Today, 05:04
- Joined
- Sep 21, 2011
- Messages
- 15,640
Pat,
If that was the case then we would not have this problem, surely?
- Local time
- Today, 00:04
- Joined
- Feb 19, 2002
- Messages
- 45,303
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.
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
Active member
- Local time
- Yesterday, 21:04
- Joined
- Aug 18, 2021
- Messages
- 807
@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.
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
Active member
- Local time
- Yesterday, 21:04
- Joined
- Aug 18, 2021
- Messages
- 807
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
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
Active member
- Local time
- Yesterday, 21:04
- Joined
- Aug 18, 2021
- Messages
- 807
- Open the Stocklist for engineering subform
- View the Material field After Update Event in the VBA window
- You will see:
Code:
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 SubThat 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.
Last edited:
