I'm trying to simplify the input by users with a code. It's difficult to explain the process so I show you what I mean.
The user only has to Input the following values:
event1 01.01.2017 5000
event1 01.02.2017 5000
event1 01.03.2017 5000
event1 01.04.2017 5000
Private Sub Save_Click()
Dim strSQL As String
Dim Period As Date
For Period = "' & Me!FromDate & '" To "' & Me!ToDate & '"
strSQL = "INSERT INTO tblEvents (EventName, Date, Costs) VALUES ('" & Me!EventName& "' , '" & Date & "', '" & Me!Costs& "')"
You should use the following date calculation functions:
DateDiff()to calculate the number of months between your 2 dates
DateAdd()to create a date by adding a number of months to another date
Edit: Adding infor for the data input
add 2 textboxes to your form, name them
TextDtTo, and give them the format shortdate so you'll see a calendar appear to pick up the dates easily.
Add a button with this click event :
Private Sub TheButton_Click() Dim strSQL As String Dim dtFrom As Date Dim dtTo As Date Dim dtCurrent As Date Dim intMonths As Integer Dim i As Integer 'dtFrom = DateSerial(2017, 1, 1) 'dtTo = DateSerial(2017, 4, 1) dtFrom = TextDtFrom.Value dtTo = TextDtTo.Value ' Calculate the number of months between the 2 dates intMonths = DateDiff("m", dtFrom, dtTo) ' Looping on the number of months For i = 0 To intMonths ' Computing Datefrom + month dtCurrent = DateAdd("m", i, dtFrom) strSQL = "INSERT INTO tblEvents (EventName, Date, Costs) VALUES ('" & Me!EventName & "' , '" & Format(dtCurrent, "DD.MM.YYYY") & "', '" & Me!Costs & "')" CurrentDb.Execute strSQL Next i End Sub
Documentation: VBA Date Time Manipulation