Hi everyone
I have a subform which have Combo(ProductID) and when click on a productID(which contains items: Biscuit, Sweet and Milo) it will display the price. The problem am encountering no is that if I select sweet for the 1st customer it will display the price and I will enter the quantity to get the Amount. Once I select sweet for the 2nd customer it will display the data for the 1st customer.
Please what could be the problem. And please am an Ms access learner not expert . Thank you for your usual assistance
It sounds like your problem stems from a fault in the relationships between the underlying tables. The model for an Orders or similar database would include tables like those in the image below:
The relationship type between Orders and Products is many-to-many, i.e. each order can include one or more products, and each product can be included in one or more orders. A many-to-many relationship type cannot be modelled directly between two tables as would be the case with a one-to-many relationship type. Instead it is modelled by a third table, OrderDetails above, which resolves the relationship type into two or more one-to-many relationship types.
The user interface for the above would comprise an orders form in single form view, and within it a subform based on the OrderDetails table. The subform would be linked to the parent form on the OrderNumber columns, and would contain a combo box bound to the ProductID column, and text boxes bound to the Quantity and UnitPrice columns, along with a computed coontrol in which the Quantity and UnitPrice values are multiplied to return the total price.
You'll notice that the OrderDetails and Products table each have a UnitPrice column. This is because the price of a product will change over time, but each order should retain the price of the products from the time when the order was created. In the subform the ProductID combo box's RowSource should return the ProductID, Product, and UnitPrice columns from the Products table, with the first column being hidden. Code in the combo box's AfterUpdate event procedure assigns the selected product's unit price to the UnitPrice control in the subform by referencing the combo box's Column property. This property is zero-based, so the third column would be referenced as cboProductID.Column(2), i.e referencing its third column.
I've attached a little inventory demo database. This includes an orders form which illustrates the above.
