Create Records in Date Range (VBA)

[bmcgree1]

Here is my problem. I have a lot of different fields that all revolve around two date fields, BeginDate and EndDate. The user enters information into both these fields along with other data. My problem is that I need the begin and endDates that they enter to be broken up by months. For example, if a user enters a begin date of 1/20/2009 and end date of 5/20/2009 access would automatically create 5 new records looking something like this (and duplicating the data in the other fields along with it):

BeginDate EndDate
1/20/2009 1/31/2009
2/1/2009 2/28/2009
3/1/2009 3/31/2009
4/1/2009 4/30/2009
5/1/2009 5/20/2009

Below is some logic that might be used to do this, I'm thinking a loop could be the answer but don't know exactly how to set it up in VBA... any help is appreciated!

Dim LngMTH as Long
'1st record BeginDate as value
LED=Dateserial(Year(BeginDate),Month(BeginDate)+1, 0) ' This gives the last day of the month.
exit or goto
LngMTH=Month(BeginDate)
Do until LngMTH=Month(EndDate)+1
BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
EndDate=Dateserial(Year(BeginDate),LngMTH+1,0)
LngMTH=LngMTH+1
Loop
'lastrecord
BeginDate=Dateserial(Year(BeginDate),LngMTH,1)
EndDate as given value

[NeoPa]

It seems you need to add in the part of the code that adds new records. Currently your pseudo-code loop does nothing other than process the loop.

Here is a link to basic DAO recordset processing (Basic DAO recordset loop using two recordsets). .AddNew() & .Update() are the methods you need to prepare and save the data.

Please have a go with this if you can. If you have further problems, please post the code you've attempted with any error messages and which lines they pertain to.

Welcome to Bytes!

[ADezii]

Just subscribing, as NePa has stated, let us know if you continue to have problems.