Add additional criteria to a Loop (6 Viewers)

[John Lee]

Hi I have the following Code that is working fine, but I need to add in additional criteria because depending on the job title determines the number of years for the refresher date.

Basically if the persons job title is Production Technician or Storeman, then their refresher date needs to be set to 1 year on from the course start date and if their job title is Quality Inspector or Sales Support their refresher date needs to be set 3 years on from the course start date.

I know that an IF Statement is needed, but I'm not sure how to apply it within the loop.

The code is placed in the On Load event of the form "frmHealth&SafetyMatrix-FireSafetyFireWarden"

The idea is that when ever the form is opened it automatically checks the course start date and assigns the refresher date according the above.

Any assistance would be most appreciated

Option Compare Database

Private Sub Form_Load()

Dim StartDate As Date
Dim RefresherDate As Date
Dim JobTitles As String

Dim db As DAO.Database
Dim rs As DAO.Recordset

' Open the database and recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryHealthSafetyMatrixFireSafetyFireWarden")

JobTitles = strJobTitles

' Check if the recordset contains any records
If Not (rs.EOF And rs.BOF) Then
   rs.MoveFirst ' Move to the first record
  
' Loop through all records
Do While Not rs.EOF
      
      StartDate = rs!dtmCourseStartDate
      RefresherDate = DateAdd("yyyy", 3, StartDate)
      rs.Edit
      rs!dtmFireWardenMarshallRefresherDate = RefresherDate ' Update field value
      rs.Update
      
      rs.MoveNext ' Move to the next record
      
Loop
   Else
   MsgBox "No records found."
End If

' Clean up
   rs.Close
   Set rs = Nothing
   Set db = Nothing

End Sub

I've also attached the part of the database I'm working on so that you can see how I'm working

Kind Regards

 

[Gasman]

I would not hard code it, but store it in the employee record.
Then you just use that value from the record in the DateAdd function.

 

[John Lee]

Hi Gasman, I'm not clear on what your suggesting, how does that deal with the scenario of employees with different job titles requiring different refresher dates?

 

[Gasman]

In the employee record, you have a field that stores the refresh interval.
Then as you process each record, you add that value to the record, just as you are adding a 3 at the moment.
In fact it is quite likely you should have a table that links RefreshInterval to Employee, and you bring that in as a query.

Then, if any of those values need to change, you change them once in the RefreshInterval table, not as many times as they exist in the Employee table. Otherwise simple update query for when any of those values change for whatever position.

Then it would be

RefresherDate = DateAdd("yyyy", rs!RefreshInterval, StartDate)

From ChatGPT

• rs("FieldName") is the most commonly used and concise.
• !FieldName shorthand works mainly with DAO recordsets (like in Access).

Either syntax would work if you are using DAO, which I suspect you are.

 

[MadPiet]

Sounds like you could have a table of Jobs, and put it in there. Then you'd just use a plain select or update query. And no VBA at all.

 

[John Lee]

In the employee record, you have a field that stores the refresh interval.
Then as you process each record, you add that value to the record, just as you are adding a 3 at the moment.
In fact it is quite likely you should have a table that links RefreshInterval to Employee, and you bring that in as a query.

Then, if any of those values need to change, you change them once in the RefreshInterval table, not as many times as they exist in the Employee table. Otherwise simple update query for when any of those values change for whatever position.

Then it would be

RefresherDate = DateAdd("yyyy", rs!RefreshInterval, StartDate)

From ChatGPT

• rs("FieldName") is the most commonly used and concise.
• !FieldName shorthand works mainly with DAO recordsets (like in Access).

Either syntax would work if you are using DAO, which I suspect you are.

So it's not possible to do what I am looking to do with the code that I have, is that what your saying, you appear to be suggesting a totally different approach, which will mean I will have to make significant changes to the work I have already done.

So is it not possible to use an IF statement as I have highlighted in my initial post to identify the different records with those job titles and then assign the refresher date.

Just to note that these records are not unique and updated each time their refresher date is due, what happens is that once that individual has the refresher training a new entry into their training record is made with the start and end date of that training, the past training of the same subject matter remains in their individual training records as evidence of all the training they have done since joining the company and the new entry sets a new refresher date accordingly.

what I am trying to do is to automatically have those refresher dates generated so that the line managers don't input incorrect dates, which is what I have found to be case, which has prompted me to try do this, this way.

If it's not possible to do that's fine, I'll just have to live with it.

Kind Regards