How to Create a Notification Based on Frequency

[Rek 1969]

Hello Everyone

In my Maintenance Database I have a Maintenance Activity form onto which an engineer enters data for machines - nature of fault, corrective action, parts used, repair time, type of activity etc etc.

I have some Queries on the form - Man Hours per Machine, Maintenance Request Forms (MRFs) per Machine, etc etc. The Queries group together (sum for man hours, count for MRFs) based on a date parameter - the user enters the month (for example 06/2015), and the data for that month appears.

I also have a preventative maintenance form, with a Whats Due and Whats Overdue button/s. there are also a few other bits.

We are due a customer audit, and the database will be part of that audit. The customer has a scale - 1=fail, 2=fail, 3=pass,4= highest score.

Currently, the database will give us a 3, but I want to aim for a 4!

To do this, I need to incorporate a "predictive" element to the system.

At the moment, when I send my Monthly Report to my boss, I manually look at a machines history, and if it has required more man hours, or has required maintenance more frequently, I instruct one of my guys to assess the machine, and perhaps increase the frequency of the preventative maintenance carried out on it to bring it back under control.

However, this is very time consuming. So, now for the question...

Can I write a bit a code (or a query or something) whereby I give the database a number, say 6, which relates to the maximum MRFs in a month for any machine. If the number of MRFs is = to or >than, a pop up (or dialog box) appears and says "HEY!! - Sort this machine out!!" (or perhaps a more polite message ), so that the monitoring is done automatically.

Therefore, incorporating a predictive element.

The database would need to automatically only consider MRFs within the current month.An MRF is created for each new record (MRF is an autonumber and PK).

Alternatively, is there a way to expand on that and have an automatic function where the database calculates the average count of MRFs per month, and for example if the count of MRFs in the current month is AVERAGE + 2, it will display the same warning. the warning should obviously include the name of the machine.

Hopefully I explained that correctly, and apologies for the long read.

Thanks in advance for any assistance anyone can give me.



Thanks all

Rek

[June7]

Can certainly have code or calculation in query that compares data to a given parameter.

What is questionable is what you mean by 'automatic'. Access is event driven. Something must happen to trigger the analysis you want - open db, open form, open report, click button, enter data, etc.

[Perceptus]

You may be able to insert a ActiveX Timer Control onto a a main form and have it Poll a query for intended results. Setup your sequence of events based on the results given from there on.

[Rek 1969]

Thanks for your replies,

June7,

I guess that the event would be the creation of an mrf? - when the machine is selected (combo box), the count of mrfs (i.e records) for that machine in the current month is added to (+1) and if the count exceeds 6 (for example) the pop up appears?

The user usually clicks from field to field (I havent sorted a tab order yet, as each engineer has their own preference to the order that they complete the fields) - so an on click event?

Perceptus,

I have no idea how that works to be honest.

My database is established in 3 sites (its split), with frontends on I think 6 or 7 workstations, and I have built it from scratch.

This may give you the impression that I know what I'm doing, but to be honest I still consider myself a novice. I am ok with forms, tables, pretty good with queries, and pretty good at cut and shut vba etc. When I have an issue (usually created by my boss when he says "get your database to do this, or that") I trawl through the internet for bits of code that almost do what I need them to do, and then experiment with it until it works!

So, Ive not come across ActiveX timer control before?

Rek

[Perceptus]

There is a TimerInterval in access for this(its a property of the form). You set its value to a value in milliseconds. Then set the event for timer in vba to handle what happens when the elapsed time in milleseconds occurs.

[June7]

I've never used any ActiveX controls. All forms have OnTimer event. It's my understanding timer code can hog processor.

This 'frequency' is not exactly time based, not like 'check this every 10 minutes'. You only need to know when the number of records in a month reaches a threshold.

Seems the record creation is a good trigger for the data check. Can use a DCount() domain aggregate function. Something like:

If DCount("*", "tablename", "MachineID=" & Me.MachineID & " AND Format([MrfDate], 'YYYYMM') = Format(Date(), 'YYYYMM')") + 1 => 6 Then
MsgBox "This is the 6th MRF for Machine " & Me.MachineID
End If

Of course, this assumes you are inputting records in the same month the activity takes place. If you enter December data in January, this will not be useful.

Now exactly what event? Combobox AfterUpdate or form AfterUpdate?

[Perceptus]

you could invoke the windows api sleep function in a loop on another thread. if cpu clock ticks are an issue.

[Rek 1969]

OK,

That looks like it will do what I want.

A couple of Qs ...

1: MachineID - will this be the name of the control source for the combo box, or the name of the combobox? (control source name = machine/plant, combobox name = combo29) - and yes I know combo29 is not a good name for anything - I learnt that lesson a long time ago. The form in question was the first i created so had many bad habits

2: there are about 40 machines on the combobox list - the above code will count the mrfs for each?

And I guess the event doesnt matter - but perhaps Combobox Afterupdate? - will have a good instant impact!

Thanks for your help

Rek

[June7]

1. either should work, but maybe better to reference combobox name

2. the code will return count for only one machine - the one selected in combobox

Did some edits on previous post, might review again.

[Rek 1969]

Ok,

So when I select a machine name from the combobox, on update it will count the number of MRFs (or other records) for that machine only? - thats fine, and makes perfect sense.

And the entry will be made during the same month. What normally happens if an Operator, or somebody (including Maintenance Engineers) will raise a Maintenance Request Form (on paper) and give it to one of the maintenance guys, who will then enter the info on the paper form to the database. This happens the same day. The machine may not necessarily be fixed that day of course!

[Rek 1969]

Hello Again,

Ok, when I enter the following code ...

Private Sub Combo35_AfterUpdate()
If DCount("*", "[Tbl_STS Maintenance Activity]", "Combo35=" & Me.Combo35 & " AND Format([Date Issued], 'YYYYMM') = Format(Date(), 'YYYYMM')") + 1 >= 6 Then
MsgBox "This is the 6th MRF for Machine " & Me.Combo35
End If
End Sub


I get this error when i select a machine ...

Runtime error 2471:
the expression you entered as a query parameter produced this error:'database - database is the machine name that I selected previously so was already in the combobox as the selection.

So, I tried another machine (selecting another machine name on the same record)and got this error...

Runtime error 3075:

syntax error (missing operator) in query expression 'combo35=kerry cleaner-sts 004 AND Format([Date Issued], 'yyyymm') = Format(Date90,'yyyymm')'

I know its going to be something, but I need help if possible please.

Thank you

Rek

[June7]

Why would you have condition where combobox compares to itself? That is not what my example intended. Should be field name (is it MachineID ?) compared to combobox.

[Rek 1969]

Field Name is Machine/Plant.

Swapped out the combo35 with Machine/Plant, but same sort of error - because Im comparing that with itself also?

Now looks like this ...

Private Sub Combo35_AfterUpdate()
If DCount("*", "[Tbl_STS Maintenance Activity]", "[Machine/Plant]=" & Me.[Machine/Plant] & " AND Format([Date Issued], 'YYYYMM') = Format(Date(), 'YYYYMM')") + 1 >= 6 Then
MsgBox "This is the 6th MRF for Machine " & Me.[Machine/Plant]


End If
End Sub


The table name is

tbl_STS Maintenance Activity.

Field Name/ control source is

Machine/Plant

Combo box name is

Combo35

Ive never used Dcount before, and as previously stated I am a bit of a novice when it comes to VBA (as Im sure you can see )

Rek

[June7]

I said field compared to combobox.

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

Is Machine/Plant a number type field?

[Rek 1969]

Aah right, ok.

The field is Short Text - as some of the machine names contain asset numbers also.