A form that makes multiple entries

[dimits]

I have two tables in my database. First one called tblProducts (with ID, Product, Price) and a second one called tblSales (with ID, Date, ProductID, Quantity). I need a form to add new entries to tblSales. The form must have one textbox to choose a date and then a list with all the products (from tblProducts) with a textbox to fill the quantity of the the product that was sold that date. I don't know if I made my self clear but I'll try to give you an example below.

---

Example:
Let's say that tblProducts has 2 entries:

ID	Product	Price
1	pr1	10
2	pr2	12

The form should be something like this:

date: 09/06/2014
pr1:__10_______


pr2:__12_______


The data in tblSales after I save should be like this:

ID	Date	ProductID	Quantity
1	9/6/2014	1	10
2	9/6/2014	2	12

[June7]

Surely not every product is associated with every sale?

The 'list' should be a combobox where user will select the desired product.

[dimits]

Every day a certain amount of all the products is sold. I want to keep records of the quantity that's been sold. Instead of the user having to input the date and the quantity for each product it would be better if he chose the date just once and then input the quantities. I don't know how easy this can be done or if it is even possible but thank you for your fast reply.

[June7]

So the product sale is not tied to a customer or a specific sale receipt? This is just an end of day total?

Then options are:

1. pre-create a set of product records for the desired date in tblSales - this can be accomplished with an INSERT SELECT sql action:

INSERT INTO tblSales(SaleDate, ProductID) SELECT Date() AS SaleDate, ProductID FROM tblProducts;

code behind a button can run the query

2. an unbound form and lots of code to save data


Should not use reserved words as field names. Date is a reserved word.