Create multiple records based on date range

mike_222 · Jul 31, 2014

Good morning, programmers. This is only my second post, and you guys helped me tremendously several months ago (which I'm very grateful for), so I'm hoping you all can point me in the right direction again.

Background Info:
I work for a company that manufactures home appliances & electronics. When those products fail within the warranty period, we are obligated to repair the units for the customers.

We have around 200 factory technicians who make those repairs. Each tech is able to make approximately 8 repairs each day. We have a dispatching system that assigns the repairs to the techs based on their availability each day. But the problem is that we have to manually enter and adjust the schedule for all 200 techs every single day, and this takes a lot of time. And of course technicians get sick, take vacation, etc, so we have to adjust the schedule so no techs will be assigned calls when they're off.

The problem:
For example, currently when a technician takes off for 2 weeks, the user has to enter 14 individual records for the tech, which is somewhat time consuming. What I need is to program a button click event to determine the two dates (startDate & endDate) and append multiple records from one single entry in the form for each date in between and including the two date fields.

I know I'll probably need to create a loop that will loop through the two dates on the form and append a record for each day, so I can then cross reference the dates to the master schedule dates to make sure that no availability is opened for the techs taking time off.

I just don't know how to work this out in my mind when it comes to the vba structure. Any help is appreciated!

mike_222 · Jul 31, 2014

Ok....so I continued searching this and other sites and after hours of trial and error, and I believe I have the hard part figured out. Here's what worked for me.

Code:

Dim dteCurdate As Date

dteCurdate = Me.StartDate

DoCmd.SetWarnings False

Do While dteCurdate <= Me.EndDate

DoCmd.RunSQL "INSERT INTO tblTimeBlocks2([TechnicianName], [Code1], [BlockDate]) VALUES (FORMS!frmTimeBlocks!TechnicianName, FORMS!frmTimeBlocks!Code1, #" & Format(dteCurdate, "mm/dd/yyyy") & "#)"

dteCurdate = dteCurdate + 1

Loop

DoCmd.SetWarnings True

MsgBox ("Records Saved")

guinness · Aug 5, 2014

Hi Mike

I think I got a solution similar to what you are after on here a few weeks ago.

Look at this thread: http://www.access-programmers.co.uk/forums/showthread.php?t=266188

The code was specific to my db so will need adapting but there is a sample database there to look at.

If it works be sure to thank Gerry@docshop.ie

Cheers

Guinness

guinness · Aug 5, 2014

Here's the code that Gerry put into my db

Code:

Private Sub Create_multiple_records_Click()
 Dim strSQL As String
 'As you are entering the first record manually with the end date the sql only addes the dates greater than the first date
 'This only works where all fields are filled in - you should probably build in a check to see if the Trainer and the Date are accounted for already
'dlookup("Trainer_Name", "Resourcing", "Trainer_Name = " & [Forms]![Resourcing]![Cmb_Trainer_Name] & " and StartDate = " & [the dates])
 'If you can omit data from a field then you will have to breakdown the sql statement to omit the data if null - example for the  field  "Activity" - you would also remove the validation for Null Field
 'Validate fields are not Null
 If IsNull(Me.Start_Date) Then
      MsgBox "Start Date Missing", vbOKOnly + vbCritical, "Error"
      Me.Start_Date.SetFocus
ElseIf IsNull(Me.End_Date) Then
      MsgBox "End Date Missing", vbOKOnly + vbCritical, "Error"
      Me.End_Date.SetFocus
ElseIf IsNull(Me.Cmb_Activity) Then
      MsgBox "Activity Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Activity.SetFocus
ElseIf IsNull(Me.Cmb_Duration) Then
      MsgBox "Duration Missing", vbOKOnly + vbCritical, "Error"
      Me.Cmb_Duration.SetFocus
Else
      'build the SQL Statement
      If DuplicatesCheck = False Then
            Exit Sub
       End If
       strSQL = "INSERT INTO Resourcing ( Start_Date, Duration, Project_Title, Trainer_Name, Team"
      If Not IsNull(Me.Cmb_Activity) Then
            strSQL = strSQL & ", Activity"
      End If
      
      strSQL = strSQL & ", Training_Type ) "
      strSQL = strSQL & " SELECT Dates.Work_Dates, [Forms]![Resourcing]![Cmb_Duration] AS Expr1, [Forms]![Resourcing]![Cmb_Project_Title] AS Expr2, [Forms]![Resourcing]![Cmb_Trainer_Name] AS Expr3, [Forms]![Resourcing]![Cmb_Team] AS Expr4"
      
      If Not IsNull(Me.Cmb_Activity) Then
            strSQL = strSQL & " , [Forms]![Resourcing]![Cmb_Activity] AS Expr5"
      End If
      strSQL = strSQL & " , [Forms]![Resourcing]![Cmb_Training_Type] AS Expr6 "
      
      strSQL = strSQL & " FROM Dates WHERE (((Dates.Work_Dates)>[Forms]![Resourcing]![Start_Date] And (Dates.Work_Dates)<=[Forms]![Resourcing]![End_Date]))"
      'Run the SQL Statement
      DoCmd.RunSQL (strSQL)
      
End If
 end Sub

Hope this helps

Create multiple records based on date range

mike_222

Registered User.

mike_222

Registered User.

guinness

Registered User.

guinness

Registered User.

Similar threads

Users who are viewing this thread