Add additional criteria to a Loop (2 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

 

[Gasman]

No, you can use a simple If or Case statement.
I am assuming you have a Position table and use that autonumer as a foreign key in Employee table, or wherever.
If you are using position titles, then use that as the IF criteria.

Option Compare Database
Option Explicit

Private Sub Form_Load()

Dim StartDate As Date
Dim RefresherDate As Date
Dim JobTitles As String
Sim intInterval as Integer

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
      ' Calculate what interval we need to use.
      If rs!EmployeePos = 1 or rs!EmployeePos = 2 Then 'Production Technician or Storeman
            intInterval = 1
      Else
            intInterval = 3 ' Quality Inspector or Sales Support
      End If
    
      StartDate = rs!dtmCourseStartDate
      RefresherDate = DateAdd("yyyy", intInterval, 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

Firstly put Option Explicit in all your modules.
Amend a setting so that it gets added to all new modules.

Just remember where you need to change it, if it does change or a new position wants 2 years.

That is why data driven would be better.

 

[Gasman]

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.

Possibly, but a little more work/thought up front, saves/can save you a lot more work down the road.
It really is a way of thinking, and until you have had to amend code all the time, due to change in business requirements, do you think about getting ahead of it.
If you know that the values will NEVER change, then a simple IF will work. However if you get a new postion, then you need to remember what you need to amend, and it might not just be this code here.

 

[The_Doc_Man]

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.

In programming, all sorts of things are possible, including IF statements inside the body of a loop. However, not every choice is efficient or flexible. Using a hard-coded IF is CERTAINLY possible. But what do you do when you find a new job description that requires a value you had not seen before? You have to modify the code, of course! If that is OK for you, then continue with IF statements. The alternate suggestion was to make that incremental refresher a field in the list of job titles/descriptions/properties. Then using a JOIN between the job table and the employee table, you can simply add or otherwise apply the correct refresher increment when you need it.

Do both methods work? Yes, easily. Is one more efficient than the other? In programming, that kind of either-or question is ALWAYS answered with "Yes, one method is more efficient." Which one is easier can often only be determined by trying the alternative. The final determination is always going to be that for the simplest cases, whichever one makes you more comfortable is better. For complex cases, you might not have the luxury of choice.

which will mean I will have to make significant changes to the work I have already done.

Welcome to the world of programming, John Lee. If you hadn't already learned this reality, it's time you at least recognized the truth of it. The word retrofit comes to mind as a description of the fit you throw when having to go back and change something you thought would never ever have to be changed again.

 

[Ken Sheridan]

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'd strongly recommend that you take The_Doc_Man's advice. The refresher intervals are data, and as such should be stored in a RefresherInterval column in a table ( Codd's Rule #1, the Information Rule). As the interval is an attribute of the Positions entity type, it should be a column in that table or its equivalent. This table can be joined on PositionID or similar to whatever table you are updating in an UPDATE query which updates the RefresherDate column on the basis of the value in the RefresherInterval column. Rather than looping through a recordset you can them simply execute the query to update all rows as a single set operation.

 

[Gasman]

Thinking about it again, and your original question, you might be better just using the simple IF statement (acknowledging the caveats already mentioned).

 

[MadPiet]

If you want, dummy up some data for us. (Representative of your real data is fine). Then we need a few examples of expected results to know if our math is right. I can see using VBA to run something like this one a cycle (say every week or month or whatever), but I'm not a huge fan of loops unless I can't do it any other way. =)
Yeah, I'm clearly getting older and grumpier by the day... LOL

 

[unmateria]

Hi Maybe something cleaner like this... iif is horrible for performance, but I guess you won't have 10 millions of workers!

Dim strSQL As String
strSQL = "UPDATE qryHealthSafetyMatrixFireSafetyFireWarden " & _
    "SET dtmFireWardenMarshallRefresherDate = " & _
    "DateAdd('yyyy', IIf(strJobTitles In ('Production Technician', 'Storeman'), 1, 3), dtmCourseStartDate) " & _
    "WHERE strJobTitles In ('Production Technician', 'Storeman', 'Quality Inspector', 'Sales Support')"
CurrentDb.Execute strSQL, dbFailOnError

 

[Ken Sheridan]

Hi Maybe something cleaner like this... iif is horrible for performance, but I guess you won't have 10 millions of workers!

If the interval is stored as data in a column in a related Positions table, in addition to respecting Codd's Information Rule, the IIF function call won't be necessary. If further positions were to be added to the table, or if the interval values should be changed, there would be no need to amend the UPDATE statement.

 

[MadPiet]

If you have a Positions table, you'd just do an inner join and you'd be sorted. No loops, (YAY!) Some careful indexing would make it really fast.