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

[LarryE]

TBH, I think the O/P has the DB correct? The description from the Stocklist is carried over from the stocklist table, but for some obscure reason is hidden when you look at stocklist?
How that has been done is beyond me?

Nor is it shown in the form, just the description.

So nothing really complicated here, at least to my limited knowledge.

Time will tell. I expect we will be hearing about more issues in the future.

 

[rainbows]

not sure if i am on the correct track but i have managed to get to this at this time albeit the latest text not the one before i changed it


SELECT Stocklist.MaterialID, Stocklist.Material, [Product Detail].ProductID, Products.ProductNo
FROM Stocklist INNER JOIN (Products INNER JOIN [Product Detail] ON Products.ProductID = [Product Detail].ProductID) ON Stocklist.MaterialID = [Product Detail].Material
GROUP BY Stocklist.MaterialID, Stocklist.Material, [Product Detail].ProductID, Products.ProductNo, [Product Detail].Material
HAVING (((Stocklist.Material)=[Forms]![Products]![Stocklist for engineering]![Material]));

 

[Gasman]

My apologies. I got it wrong, not for the first time. Products is not in my DB, but I can see now that Material is used in Products and not MaterialID.
Still have no clue as to how MaterialID is hidden from Stocklist table? Why would you do that anyway?

OK, found the hidden tables, way to go O/P
Yes, lookup in the tables has caused all my confusion at least.
I am going to take a break and have a beer.

Still have no clue as to how MaterialID is hidden from Stocklist table? Why would you do that anyway?

 

[LarryE]

Pat and Gasman:
I also had never seen anyone change the name of a field in a table when it is displayed, but the OP did that by entering a different name in the fields Caption property so that the TypeID field displayed as "Type" and the UnitID displayed as "Unit". They were Number Data Types BUT he also changed them to lookup fields as you said, but since he indicated 3 fields in the combobox but the number had a 0 width, so it never displayed the number.

I also have no clue why any of this was necessary or even preferred, but good luck to him. In the DB I downloaded, I changed them all back to normal numerical fields with the actual field name showing.

 

[Gasman]

Oh, I have used the Caption property to give more meaningful names when ceating forms myself.
However, I am now thinking that the O/P might actually need those descriptions as they are, at the time of being added?

Otherwise if we have the MaterialID in the Products form, then when the description is changed, it would show the new description if you looked at a particular product from a while back, when it should show what the description was when it was created?

 

[LarryE]

Oh, I have used the Caption property to give more meaningful names when ceating forms myself.
However, I am now thinking that the O/P might actually need those descriptions as they are, at the time of being added?

Otherwise if we have the MaterialID in the Products form, then when the description is changed, it would show the new description if you looked at a particular product from a while back, when it should show what the desscription when it was created?

Using a table field as a lookup and then using a different name and then hiding the number when it's a Number Data Type is dangerous. I also use the caption property in forms, but NEVER to change the name of a field displayed in a table.

And the OP still needs to normalize the tables, so one piece of information is kept in one table.

 

[GPGeorge]

I believe I have everything you are all saying . And yes the name is only entered once in the peoples take . And yes the peoples name is entered many times in the task table .let's say name Fred was on 10 tasks But let's say after a week or so I discover I spelt it wrong ie freb or I wanted to use another person for them tasks say john
How do I record what a that name was or spelt wrong ie freb on all the tasks you were on

So I would like a report that told me task no 1 4 6 9 had the same freb and now is fred
I now have a audit trial for them tasks I changed

Even if it was the persons name ID it would still be in many places in the task table and if I changed that person for that task I still need a report to tell me it was say person ID was say no1 but it is now no 2

Hope this helps

Thanks steve

Read Post #14 again regarding NOT storing the name....

 

[rainbows]

When I first started to create the database I never used the ID fields I used to link material to material but when I talk to the forum I was told never to do that and link materialId to material and I really struggled as the results I was getting was the materialId and not the description so I just used combo boxes to solve the problem right or wrongly
I am still not sure if reading what you are saying I have done it correct or not. ?

 

[rainbows]

ou're missing something - a join table between your products and parts.

In your post #3, the same part appears multiple times eg ProductDetailID 2267,1167, 542, 580 etc

If ProductID 904 had 3 of PartID 204, the join table would have an entry ProductID with 904, PartID 204 and a Quantity of 3.

Use a date to record when a particular part is changed so you can generate a report of anything changed in a particular period.

if you look at post 1 you will see there are 129 different products each product has a list of items to manufacture that product , the one on the screen post 1 shows 21 items lets say product 2 has 30 items selected from the stocklist and product 3 has 50 items from the stocklist and the same part appears in product 1 and 3 that means that parts is in the product details table 2 times until other products use that part so if i modify tha tsame part in the stocklist table it will update that description on both products also of which i need a report to tell me that these 2 prodcuts have been changed from what it was to what it is now and that report sent to a file on the server


re the material id being hidden in the stocklist table , i did not know it was . it was a mistake

steve

 

[Gasman]

No, you link MaterialID to MaterialID, but show Material.

Now you can have this situation with standard Customer,Order,OrderDetail.
When you add an item, you want the price as at that moment in time. If you just use the ID for the item alone and have to reproduce the invoice at a later date and the price of an item has changed, then the invoice will reflect the new price, which you do not want.

Is this your situation?

 

[rainbows]

The stock list material costs do change and yes it will always look at the latest cost . So If i add all the material costs for a product and say it is 200£ and I sell at 500€ and 2 months later the cost for that same product has gone up to 300£ then I dont have the old total material cost availble to me for that product. But I have put a total material cost in the order details so I do have a record

 

[Gasman]

The stock list material costs do change and yes it will always look at the latest cost . So If i add all the material costs for a product and say it is 200£ and I sell at 500€ and 2 months later the cost for that same product has gone up to 300£ then I dont have the old total material cost availble to me for that product. But I have put a total material cost in the order details so I do have a record

That was just an example, using one reason I know of, of having to store data, that could be looked up at any time.
Does your material description have the same issue?

 

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