auto creating records on subform

[RangerJeff]

Hi
New to Access, so still learning.
I have a main form (table) called Permits with a start_date and end_date. I have a subform (table) called Permit_locations with a one to many relationship to Permits. After I enter the dates on the Permit form, I want to generate the number of records on the subform (Permit-locations) (= to End_date-Start_date), with the date field filled out for each record on the subform, basically one for each day.

I create a field in the main table called STAYS which is a calculated field of end_ date - start_date,. So I was thinking doing an After_Update event when end date is entered and have it create the records in the subform/Permit_location table.

May I am just missing it, but don't see the commands I would need for my VBA script. I have no problems creating individual records in the subform, but have to enter all the data manually. There is no limits on the number of nights but generally it is not more than 14.

Here is a picture of the subform




All help will be appreciated

[Micron]

You post isn't real clear (e.g. = to End_date-Start_date). Something is equal to end date minus start date? End date to start date? And what equals those/that?
If end minus start gives you some record count (e.g. 6 days = 6 records needed) then I think you'll have to create a loop with that result as your counter, then add a record to a DAO recordset you created based on the form recordsource by appending to at least one field for each loop. When done, you requery the form.

Not sure if I understand the requirements so that's my take for now.

[Missinglinq]

If Start_Date = 8/1/2020...End_Date = 8/5/2020

you want, in the Subform, you want to generate 5 Records, dated

8/1/2020
8/2/2020
8/3/2020
8/4/2020
8/5/2020

Is that the idea?

Linq ;0)>

[RangerJeff]

Thanks, I should have realized a simple loop. Now I just need to figure out how to add records in the subform, since the user will have to enter more data.

[RangerJeff]

yes. I want to gnerate 5 records in the related (child) table which is in the subform and increment the date for each record. The user will have to add other information.
Do either a loop in the main form, or when I enter the subform, it could generate the records then. All the examples I can find online and in the books seem to want you to open the db but it is already open.

I guess I am expect too much ease of use from ACCESS. I am having trouble of how to use the fields in a table to assign a value to a variba]ble in the VBA script. Not sure of the format.

THis should be simple. TO many years of mainframe stuff I guess.