Auto Generate Based On Date Range

[ToddNYC]

I am looking to have entries made to the table based on a date range and can't figure out where to begin! Hoping you can point me in the right direction.

I have a database of events and most repeat daily, weekly, etc. When I enter them into Access, they go in as a single entry with a start and end date. But I need to have them as individual entries for each day so that rather than one entry of 5 days, I need to create 5 entries of one day each.

Is there a way I can do this? Or maybe a suggestion on a better approach? (I realize this is not the "normal" way of dealing with data, but I need to import this db into another program that requires it this way.)

The challenge is that many events occur 365 times a year (and some have different details for weekends than on weekdays!) so I need to duplicate records in bulk.

Thanks for your help!!

 

[pbaldy]

One way would be a loop based on the dates, with code to add a record for each date. The code to add might look a little like this:

http://www.baldyweb.com/MultiselectAppend.htm

but with a loop based on the dates rather than a listbox. Within that loop, you could test the date to see if it's a weekday or not and react accordingly.

 

[ToddNYC]

Thanks Paul for the quick reply and pointing me in the right direction. I'll play around with the code you suggest and see where I can get.

 

[pbaldy]

No problem; post back if you get stuck.

 

[ToddNYC]

I'm not making much progress here. Am I thinking about this the right way? Am I trying to write to the 2nd table ("tblOtherTable") the duplicate events, changing only the specific date of that event. And have the code keep looping until the event date = end date?

This way if I enter an event using formtest2 and say the end date is 3 days after the start date, the code will add a total of 3 entries to tblOtherTable.

(I'm checking out another post of yours I found at http://www.accessforums.net/forms/need-help-multiple-duplicate-records-form-button-12958.html, it seems that this could help me...)

 

[pbaldy]

Yes, but I don't see where you tried that. All I see is the listbox code. Your loop would look like:

  Dim dteCurDate              As Date
  dteCurDate = Me.StartDate
  Do While dteCurDate <= Me.EndDate
    Debug.Print dteCurDate
    dteCurDate = dteCurDate + 1
  Loop

Which just tests, putting the output here:

http://www.baldyweb.com/ImmediateWindow.htm