Cancellation of an event called purchase 1

[malbreht]

Hi!

I'm building a simple DB for tracking little products or items, that employees get from our company, for example an iPod for the director and a cell phone for his secretary

I've already built almost everything, with the help of tek-tips of course, but now I got some new instructions... They want to be able to do at least one more thing - to cancel a part of an event called purchase (this employee got this and this at a this price at this day etc.)

If I disable the quantity field in my form, so that it is only possible to make a purchase for 1 item at a time, everything of course works - I just put a checkmark field in my form and when the checkmark is turned on, the whole purchase is treated as cancelled in my reports.

However, they need to be able to enter quantity, too. I have no idea of how to make a purchase for let's say 5 items at a time, but only cancel one of them after some time.

At the moment, my tables look like this:

tblEmployee (Employee_ID, Name, Surname, Telephone, Mail)
tblPurchase (Purchase_ID, Date, Year, Employee_ID, Bill_No)
tblPurchaseProduct (Purchase_ID, Product_ID, Quantity)
tblProduct (Product_ID, Name, Price)

Thanks for any advice!

 

[Golom]

You state that you have a "checkmark field in my form" but I don't see anything in the tables that would record such a check box. How does the report know about the check box if it isn't in the tables?

You might expand your tables to

tblEmployee (Employee_ID, Name, Surname, Telephone, Mail)
tblPurchase (Purchase_ID, Date, Year, Employee_ID, Bill_No[red],Cancel[/red])
tblPurchaseProduct (Purchase_ID, Product_ID, Quantity[red],Cancel[/red])
tblProduct (Product_ID, Name, Price)

where the fields in [red]red[/red] are Yes/No fields that specify that the Purchase or Product has been cancelled (or not cancelled).

There is also a potential problem in the structure. I infer that you are doing a price lookup in the "tblProduct" table to determine the item and total costs for a purchase. The reality is that prices do change and, if you change the price of a product, then the historical purchase records will also change. You should probably be recording the price of each product in "tblPurchaseProduct" so that you capture the price at the time of purchase.

 

[malbreht]

Aaah, sorry, I'm translating the names of my tables to english and therefore I forgot somehow that of course I've already got the Cancel field in my tblPurchase.

But why do you suggest me another Cancel field in my tblPurhaseProduct? Is that necessary? If the user will enter data with the help of a form - how can the Yes/no data be recorded in 2 tables at a same time?

Thanks for the answer and many thanks for the tip about changing prices!

Mare

 

[malbreht]

I googled and tek-tiped a bit and found out that it is probably the subform that I'm searching for, isn't it? With the subform I could asign many items to the same employee.

However, because of my need for Audit Log (

http://allenbrowne.com/AppAudit.html)

I had to add another autonumber field to tblPurchaseProduct, so my tables are now like

tblPurchaseProduct ([b]PurhaseProduct_ID[/b], Purchase_ID, Product_ID, Price, Quantity, Cancel)
tblPurchase ([b]Purchase_ID[/b], Date, Year, Employee_ID, Bill_No)
tblEmployee (Employee_ID, Name, Surname, Telephone, Mail)
tblProduct (Product_ID, Name)

The subform therefore looks like

frmPurchase_subform (PurhaseProduct_ID, Product_ID, Name, Price, Quantity, Price*Quantity, Cancel)

Do you find that ok? Thanks!

 

[Golom]

Yes ... it does look better.

The implication of putting the Price in "tblPurchaseProduct" and only there, is that someone will need to supply the price of the product for every purchase. Do they necessarily know the price? Will everyone use the same price?

This all depends on exactly how your code works (i.e. it may not really be a problem) but I would put the price back into "tblProduct" and do a lookup on it when creating a new record in "tblPurchaseProduct" ... saving the value there. That gives someone who manages the system, the power to define products and prices and those who merely enter purchases just select products without having to know the price.

 

[malbreht]

OK, so if I understand you correctly, price field in both tables:

tblPurchaseProduct (PurhaseProduct_ID, Purchase_ID, Product_ID, [b]Price[/b], Quantity, Cancel)
tblProduct (Product_ID, Name, [b]Price[/b])

The price, stored in tblProduct, is always proposed by entering the purchase in

frmPurchase_subform (PurhaseProduct_ID, Product_ID, Name, Price, Quantity, Price*Quantity, Cancel)

The price in this subform however stores itself in tblPurchaseProduct wheter left as proposed or adjusted.

But how do I get the data into the subform from a different table? Where do I do a LOOKUP in my subform to get the data from the tblProduct, if my "source field" in the properties of subform's price is already full (with the name of field where it should store itself)

Thanks, mare

 

[malbreht]

With the help of Jerry I've already found out the solution at thread181-1322263.

Mare