Dear readers,
I would like to ask for your help to resolve the next design challenge (I created myself). In Access, I have one table (called: Producten) featuring a list of say twenty products. Furthermore, I have one table (called: Verkoopfacturen) featuring a list of invoices. A third table (called: Productverkopen) links these, by storing the invoice ID and the product ID. This implies I could have four records (Productverkopen ID, invoice ID, product ID):
1 1 3
2 1 7
3 1 8
4 2 5
So what I basically do is making records in which a product is linked to an invoice. In the given example, the invoice with the ID 1 includes the products with ID 3, 7 and 8, and the invoice with ID 2 includes the product with the ID 5.
The challenge is that I want to make a form to define an invoice. I want to have an invoice form, in which all relevant information for the Verkoopfacturen table can be specified (date, customer, etc.), and which includes a sub form with all available products and their associated quantity on the invoice. So, this subform should have a table style layout with the product number, product description and quantity on the invoice. For each product, the quantity ordered can be filled out.
This visual shows the intended result:
Please add www etcetera in front of it yourself lucatu.nl/temp/access.jpg
So the sub form should always show all products available (from the Producten table) with its associated quantity (from the Productverkopen table), and not only those products which are already linked to an invoice (in the Productverkopen table).
How can I achieve a list of all products (later to be extended by one criteria: whether a check box at each product level is true or false) is made based on the Producten table, and the quantity filled out is translated into a link between the invoice ID and the product ID?
I hope you can get me on the right track here.
Many thanks in advance.
Best,
Luc
I would like to ask for your help to resolve the next design challenge (I created myself). In Access, I have one table (called: Producten) featuring a list of say twenty products. Furthermore, I have one table (called: Verkoopfacturen) featuring a list of invoices. A third table (called: Productverkopen) links these, by storing the invoice ID and the product ID. This implies I could have four records (Productverkopen ID, invoice ID, product ID):
1 1 3
2 1 7
3 1 8
4 2 5
So what I basically do is making records in which a product is linked to an invoice. In the given example, the invoice with the ID 1 includes the products with ID 3, 7 and 8, and the invoice with ID 2 includes the product with the ID 5.
The challenge is that I want to make a form to define an invoice. I want to have an invoice form, in which all relevant information for the Verkoopfacturen table can be specified (date, customer, etc.), and which includes a sub form with all available products and their associated quantity on the invoice. So, this subform should have a table style layout with the product number, product description and quantity on the invoice. For each product, the quantity ordered can be filled out.
This visual shows the intended result:
Please add www etcetera in front of it yourself lucatu.nl/temp/access.jpg
So the sub form should always show all products available (from the Producten table) with its associated quantity (from the Productverkopen table), and not only those products which are already linked to an invoice (in the Productverkopen table).
How can I achieve a list of all products (later to be extended by one criteria: whether a check box at each product level is true or false) is made based on the Producten table, and the quantity filled out is translated into a link between the invoice ID and the product ID?
I hope you can get me on the right track here.
Many thanks in advance.
Best,
Luc
