How to Create a Notification Based on Frequency

[June7]

Then need apostrophe delimiters:

"[Machine/Plant]='" & Me.Combo35 & "' AND

[Rek 1969]

Well, whilst waiting for your reply I thought ah, I need the single 's somewhere because its text. I did put it around the bit you said as in example above, but i didnt try it.

So, I have just updated as you suggest above, filled out some new records with different dates - all in october - and it WORKS!!

I will tweak msg box msg, etc.



I dont want to complicate things too much, but is it also possible to use the code in conjunction with the type of maintenance. On each record, the engineer selects the type of maintenance - project work, unplanned maintenance, planned maintenance, preventative maintenance, health and safety, etc.

Would it be possible to have the count for say 6 UNPLANNED mrfs?

Excellent, thank you for your help, very much appreciated.

Rek

[June7]

Put as many criteria as you want in the DCount.

How do you know an mrf is unplanned?

[Rek 1969]

Unplanned Maintenance requests would cover reactive maintenance. The user would select the type of maintenance from another combo box on the form. This way I can query to calculate how many unplanned MRFs and Man hours there are per month, and on which machine, which department etc.

[June7]

But what distinguishes an mrf record as 'unplanned' - what criteria could be used as filter? 'Reactive' is a maintenance type choice?

[Rek 1969]

All that distinguishes an MRF as "Unplanned Maintenance", or any other type of Maintenance (Planned, Preventative, etc) is the selection from a combo box list, fieldname " Type of Activity", as below

[June7]

Okay if you don't want to count unplanned, then use that as conditional criteria in the code. Don't need to trigger the code if user selects Unplanned. Something like:

If Me.cbxActivity <> "Unplanned Maintenance" Then
If DCount("*", "[Tbl_STS Maintenance Activity]", "[Machine/Plant]='" & Me.Combo35 & "' AND Format([Date Issued], 'YYYYMM') = Format(Date(), 'YYYYMM') AND [Activity]<>'Unplanned Maintenance'") + 1 >= 6 Then
MsgBox "something"
End If
End If

If you actually save an ID into the Activity field, then the expressions would use the ID number instead of text "Unplanned Maintenance". Number field would not use quote or apostrophe marks for parameters.

[Rek 1969]

I do want to only include unplanned maintenance.
I spoke to the guys today and gave them a demo of how the system will bring up a message box, and their concern was that if MRFs for ALL maintenance types were counted, then the likelihood would be that the message box would annoy them.

Also, from a Maintenance point of view, we are not too concerned about planned maintenance or for example project work, for this is work that we plan around production to do, so there is no negative impact.

If however, a machine had 5 or 6 instances of Unplanned Maintenance, it may indicate that the process/machine is trending toward failure, which will impact on production impact.

Therefore I think it would be better to ONLY count MRFs for Unplanned Maintenance.

[June7]

Okay, I had that backwards. Adjust code as appropriate. Change the logical operator from <> to =.

[Rek 1969]

Brilliant!

Ok, last question - how do I format the message box?

the code now looks like this ...

Private Sub Combo35_AfterUpdate()
If Me.[Type of Activity] = "Unplanned Maintenance" Then
If DCount("*", "[Tbl_STS Maintenance Activity]", "[Machine/Plant]='" & Me.Combo35 & "' AND Format([Date Issued], 'YYYYMM') = Format(Date(), 'YYYYMM') AND [Type of Activity] = 'Unplanned Maintenance'") + 1 >= 5 Then
MsgBox "The following machine has had 5 or more MRFs so far this month. " & Me.[Machine/Plant] & ". This may indicate a trend towards failure, and may require assessment or a change to the frequency of Preventative Maintenance. Please inform the Maintenance Manager immediately."
End If


End If
End Sub

I will change the actual message a little, but I would like the machine name to stand out a little, perhaps on a line by itself, so that it looks like ...


"The following machine has had blah blah blah

CUT SHEET LAMINATOR

This may indicate a trend toward failure blah blah blah"


How would I do that?

[June7]

You can format the Machine/Plant value to display as all upper case: UCase(Me.Machine/Plant)

Use vbCrLf to force multiple lines.

MsgBox "The following machine has had 5 or more MRFs so far this month. " & vbCrLf & vbCrLf & UCase(Me.Machine/Plant) & vbCrLf & vbCrLf & "This may indicate a trend towards failure, and may require assessment or a change to the frequency of Preventative Maintenance. Please inform the Maintenance Manager immediately."

[Rek 1969]

Perfect!!

Hey, thank you for your help, its very much appreciated.

All thats left for me to do now is work on a few queries for the Spare Parts page - search by number, search by machine, search by description, search by supplier - I will be fine with all of those.

Then those forms will need to be populated.

After that? well, I may have a form for entering meter readings, but thats a long way off.

Youve been most helpful.

Rek

[June7]

Glad it's working.

Review http://www.allenbrowne.com/ser-62.html