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

[Gasman]

OK.
You store the old description, possibly in a table? up to you, but you will have it in a variable.
The new description will exist in the table, or you could save that as well as a variable.

When the material description is amended (check old <> new) then Open your report based on your query with criteria of the materialID, also create the report to a pdf file with suitable naming as you require. I would add the product description to your query if you use that one.

I would probably write this change to an audit table somewhere anyway, so I could produce a report on all the amendments. If that was the case, the pdf might not be required as I could generate the report whenever it was required.

 

[CJ_London]

Depends when you want to send the email, but if that is when the change is made I would suggest the form before update event

The control has a .oldvalue property which contains the value when the control was loaded with data. So you would have code along the lines of

with me.txtdescription
    If .oldvalue<>.value then sendemail
End with

sendemail might be a function taking old and new values and the product pk as parameters to formulate and send the email

 

[rainbows]

when i change the material description i would just like it to create a pdf file with the old description and the new description and the associated product numbers and send it to a folder on the server

 

[LarryE]

As Pat Hartman indicated earlier, you are attempting to use ACCESS as you would a spreadsheet. This design will never work and needs to be re-designed so ACCESS can work properly. Before you go any further, design your tables and fields properly and enforce referential integrity. Right now you are keeping the same data in multiple tables and ACCESS cannot give you the answers you need.

You also have tables that should be consolidated and fields you do not need or are blank. Just based upon your description of the problem and looking at the tables you have, there are too many to be able to maintain. The entire system needs to be re-desined using proper Primary Keys and related Foreign keys.

You built a house with a foundation that is cracked and failing to provide proper support.

 

[Gasman]

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.

 

[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]));

 

[Pat Hartman]

And yes the peoples name is entered many times in the task table

The lookups are causing confusion. In the [Product Table], you have a column named Material. However, that is NOT what the field contains. It actually contains MaterialID. It is a lookup field so you "see" the text description but that isn't really there. the MaterialID is what is really there.
1. get rid of the table level lookups. They are a crutch for people who can't build a query. Once you can build a query, they cause more trouble than help.
2. Make sure the FK names match the PK they point to. NEVER use the name of the Lookup value. When you need to see the material, just join to the Stocklist. What you have is technically correct but it is very confusing.
3. When you change the value of Material in the StockList, the value presented by a query will also change. However, on your form, you are showing both the Stocklist in one subform AND a query that includes Stocklist as the RecordSource for the other subform. When you change the value in Stocklist, you will only see the change reflected in the other subform, if you requery it. Queries select data from physical tables and hold it in memory. If you modify the underlying table, that does not modify the value you are looking at in memory unless you requery.

 

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

 

[Pat Hartman]

I had the same problem Better eyesight would have discovered them earlier.

It was either a sanity check or an eyesight test. I failed both but I feel better now that I know i had company.

 

[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

 

[Pat Hartman]

go back to #28 and read it again. You need to start by removing the table level lookups. Then change the column names of the FK's to match the PK they point to. THAT will eliminate your confusion. The rest of the directions tell you how to make the form work.

 

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