Storing monthly and quarterly activities for each staff member in a table

[aman]

Hi Guys

I have created a form with following controls:

Type (Drop down box) : The options are "Audit","Review","Meeting"
Number (Textbox) : how many (Count)
Frequency (Drop down box) : The options are "Monthly","Quarterly","Yearly"

Now my question is suppose If there are 2 audits need to carried out by each employee "monthly" then we can save down 2 records (one for each audit) in the table and once the audit is completed by the employee then the completion Date is saved down against that record. There is a "Automatic allocation" button that needs to be clicked and I will run a loop based on "Number" and store data in the Master table so if Number is 2 then there will be records against that person (one for each Audit).

But what if the there are 2 Audits need to be carried out quarterly by each employee? How will that work ?

Any suggestions are welcome.

Thanks

 

[June7]

Easiest is to require user to do data entry to create records when audit is actually accomplished. The more 'user friendly' the more code is required.

I suppose at some time during the quarter (first day of quarter?) you need to create 2 audit records for each employee. How are you initiating code to save monthly audit? And then there are the 'yearly' audits - create records on Jan 1?

Really don't have enough info about your process and data structure to advise in detail. Attempt code and when you encounter specific issue, post question.

 

[aman]

I have written the following code to add monthly activities for the staff whole roleID is 37. Not sure how can we do it for quarterly activities ?

In the below code FrequencyRef=3 means "Monthly".

Public Sub AllocateMonthlyActivities()
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim rs3 As Recordset

Dim dtmStaffRef As String

Set rs1 = CurrentDb.OpenRecordset("SELECT * FROM tbl_RMS_System_Mas_Staff WHERE RoleRef in (37) AND [DateEnd] is Null")

Set rs3 = CurrentDb.OpenRecordset("tbl_RMS_System_Mas_MinStandAllocation")
If rs1.RecordCount = 0 Then
    'if no staff then no further action required
Else

rs1.MoveLast
rs1.MoveFirst

Do Until rs1.EOF

dtmStaffRef = rs1![strUser]
   
Set rs2 = CurrentDb.OpenRecordset("tbl_RMS_System_Ref_MinimumStandard")

If rs2.RecordCount = 0 Then
            'if no parameters then no further action required
Else
   rs2.MoveLast
   rs2.MoveFirst
   
   Do Until rs2.EOF
      
      If rs2.Fields("FrequencyRef") = 3 Then

         For i = 0 To rs2.Fields("Number")

         With rs3
             
             .AddNew
             .Fields("SupRef") = NameofUser()
             .Fields("StaffRef") = dtmStaffRef
             .Fields("ActivityRef") = rs2.Fields("ActivityRef")
             .Fields("MonthRef") = DateSerial(Year(Date), Month(Date), 1)
             .Update
         
         End With
         
         Next i
         
       End If
       
       rs2.MoveNext
    Loop
    
End If

rs1.MoveNext
Loop
End If
End Sub

 

[June7]

I expect creating quarterly and yearly records would be very much the same.
Again, don't know your data.

The real trick is figuring out what event to trigger code from. What event triggers this code?

 

[aman]

Just got to know from a colleague who used the following function to quarterly allocate activities. case 3 means 3rd month of every quarter .

Public Sub AllocateNonRegular_QTR()
Dim rs As Recordset
Dim rsa As Recordset
'Dim strDate As Date
'Dim strQStart As Date
'Dim strMonth As Integer
strQStart = DateSerial(Year(Date), Int((Month(Date) - 1) / 3) * 3 + 1, 1)
i = Month(Format(strQStart, "dd/mm/yy"))
strQStart = Format(strQStart, "mm/dd/yyyy")
strDate = Format(Date, "mm,dd,yyyy")
ii = Month(Date)
strMonth = ii - i + 1
strMonth = strMonth
Select Case strMonth
Case 3
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("INSERT INTO tblAllocation ( [Staff Number], [Call TypeID] ) SELECT tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID FROM tblSkillMatrix LEFT JOIN qryAllocationRegularAudits_Sub ON (tblSkillMatrix.[Staff Number] = qryAllocationRegularAudits_Sub.[Staff Number]) AND (tblSkillMatrix.CallTypeID = qryAllocationRegularAudits_Sub.[Call TypeID]) GROUP BY tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID, IIf([MaxOfDate of Observation]>=#" & strQStart & "#,0,1), tblSkillMatrix.DateRemoved HAVING (((tblSkillMatrix.CallTypeID)=" & sCallType & ") AND ((IIf([MaxOfDate of Observation]>=#" & strQStart & "#,0,1))=1) AND ((tblSkillMatrix.DateRemoved) Is Null))")
    DoCmd.SetWarnings True
Case Else
    Set rs = CurrentDb.OpenRecordset("SELECT tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID FROM tblSkillMatrix LEFT JOIN qryAllocationRegularAudits_Sub ON (tblSkillMatrix.[Staff Number] = qryAllocationRegularAudits_Sub.[Staff Number]) AND (tblSkillMatrix.CallTypeID = qryAllocationRegularAudits_Sub.[Call TypeID]) GROUP BY tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID, IIf([MaxofDate of Observation]>=#" & strQStart & "#,0,1), tblSkillMatrix.DateRemoved HAVING (((tblSkillMatrix.CallTypeID)=" & sCallType & ") AND ((IIf([MaxofDate of Observation]>=#" & strQStart & "#,0,1))=1) AND ((tblSkillMatrix.DateRemoved) Is Null))")
    If rs.RecordCount = 0 Then
        'do nothing
    Else
        rs.MoveLast
        rs.MoveFirst
        DoCmd.SetWarnings False
        DoCmd.RunSQL ("INSERT INTO tblAllocation ( [Staff Number], [Call TypeID] ) SELECT TOP 55 Percent tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID FROM tblSkillMatrix LEFT JOIN qryAllocationRegularAudits_Sub ON (tblSkillMatrix.CallTypeID = qryAllocationRegularAudits_Sub.[Call TypeID]) AND (tblSkillMatrix.[Staff Number] = qryAllocationRegularAudits_Sub.[Staff Number]) GROUP BY tblSkillMatrix.[Staff Number], tblSkillMatrix.CallTypeID, IIf([MaxofDate of Observation]>=#" & strQStart & "#,0,1), tblSkillMatrix.DateRemoved HAVING (((tblSkillMatrix.CallTypeID)=" & sCallType & ") AND ((IIf([MaxofDate of Observation]>=#" & strQStart & "#,0,1))=1) AND ((tblSkillMatrix.DateRemoved) Is Null))")
        DoCmd.SetWarnings True
    End If
End Select

Set rs = Nothing
End Sub