Hi everyone!
Please help me with your Access and VBA knowledge.
Situation:
I have 2 tables:
- Parent Table - "Shops". 2 columns: "Shop_Id" (Autogenerated Number), "Shop_Name" (Text)
- Child Table - "Sales". 4 columns: "Sale_Id" (Autogenerated Number), "Shop" (Number - Foreign key to Shop_Id), "Year" (Number), "Value" (Number).
I.e. for each shop I have many Sales.
I want to create a button that will:
- Add new Shop record,
- Add several Sales records. Number of added Sales records is equal to number of different "Year" values.
- For each of these Sales records will:
- relate it with parent Shop record, i.e. put Shop_Id of created Shop record into "Shop" value,
- put 0 as "Value" value.
- Opens particular form where I want to display both parent and child records (in subform).
Problems:
- I don't know how to create child records and give them Shop_Id of parent record. This Shop_Id is auto-generated. I don't know how to retrieve this number.
- I don't know how to dynamically calculate amount of child records I need to create.
- I don't know how to make a loop that will create as many records as needed.
Will be super grateful for your help and / or guidance about which topics / methods to look at.
Thank you in advance!