Batch Work Order Generation with Variable Dateadd Function

Question

I have a work orders database where I need to batch create work orders that are daily, weekly, monthly, quarterly, etc. I have a main form with fields for [task], [machine], [assigned_to], [StartDate], [enddate] & a combobox for frequency that determines the [DueDate]. [WO_ID] is a autonumber PK and the [WO_Num] will be generated with a Dmax function. My thought was to have the user enter the information in the fields above then have an Insert Statement, DAO or other function to use variables in the frequency combobox for a dateadd that determines the [duedate]:

Frequency.column(2) contains the count # Frequency.column(3) contains the interval (i.e. “d”, “w”, “m”, etc.) The loop will then create the necessary numbers of records between the start date and end date with the info above. Here(https://ibb.co/JQ2kPfF) is the form showing the fields and the subform for the records to be created.

Any help would be greatly appreciated as my coding knowledge is limited to reverse engineering what I find online until I can make work (ineloquently at best).

Here is a code that I found which I am attempting to get working but am not sure how to account for the start date/end date and formatting of the start/end of the loop (the “I” sections)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset("Workorders")
For i = 1 To 52
    rs.AddNew
    rs![WO_ID] = [Workorders]![WO_ID]
    rs![WO_Num] = Dmax([Workorders]![WO_Num])+1
    rs![Date_Completed] = Null
    rs![Assigned_To] = me.Assigned_To
    rs![Task] = me.Task
    rs![Machine] = me.Machine
    rs![DueDate] = DateAdd(Frequency.column(3), Frequency.column(2), me.StartDate) 
    rs.Update
Next i
rs.Close
Set rs = Nothing
Set db = Nothing

Answer 1

So ... assuming your combobox is called Frequency and the frequencies involved are weekly, monthly and quarterly then your code would look something like this ...

If Frequency.column(1) = "Weekly" Then

...

For iWeek = 1 To 52 ' for a years worth of Work Orders

...

' rs![DueDate] = DateAdd(Frequency.column(3), Frequency.column(2), me.StartDate) 

 rs![DueDate] = DateAdd("ww",1,me.StartDate) ' this adds one week to the start date

...

Next iWeek


End If 



If Frequency.column(1) = "Monthly" Then

'...

For iMonth = 1 To 12 ' for a years worth of Monthly Work Orders

'...

' rs![DueDate] = DateAdd(Frequency.column(3), Frequency.column(2), me.StartDate) 

 rs![DueDate] = DateAdd("m",1,me.StartDate) ' this adds one month to the start date

'...

Next iMonth


End If 



If Frequency.column(1) = "Quarterly" Then

...

For iQuarter = 1 To 4 ' for a years worth of Quarterly Work Orders

...

' rs![DueDate] = DateAdd(Frequency.column(3), Frequency.column(2), me.StartDate) 

 rs![DueDate] = DateAdd("q",1,me.StartDate) ' this adds one Quarter to the start date

...

Next iQuarter


End If 

Sorry if this is long winded but it is easier to explain this way. The code is not tested obviously as it is just an example.