Alerting connected users

[stillnew2vb]

Greetings Gurus,

Thanks to Darbid I have a listener set up in Access receiving incoming email and writing them to a table.

What I would like to know is if it is possible for that machine (hosting Access & Outlook) to send a non-email based alert when a new record is written to the table to users connected on the LAN who are also running Access. Something like a mini pop-up form which they can click on which then launches another form.

Thank you for your time...

 

[KenHigg]

You should be able to do this by placing timer event code on a hidden form that stays open.

 

[NigelShaw]

Hi

surely you could run a simple code in the routine that writes to the table to flash a message. i would assume the routine that is listening has a bail out if if no emails are picked up and only runs the 'add the email to the table' routine if a new one come in. a simple-

msgbox ("A new email has arrived")

at the end of the routine would suffice.

you can put yes / no message boxes or select statement boxes or IfMsgbox too....

regs

Nidge

 

[stillnew2vb]

Hi Ken & Nigel,

Perhaps I didn't explain properly, the scenario is...

PC1 runs Outlook & Access. When a new email arrives the Outlook.OTM scripts passes the details to Access which writes the relevant email into a SQL B/E database.

Pc2, pc3 & pc4 then use a form to manipulate the data in that table.

What I would like to do is get pc1 to send an alert - not an email - to pc2/3/4, which all have their own F/Es, saying that an entry has been created. I would like this entry to be a mini pop-up form/msgbox that appears in the bottom left-hand side of the screen on pc2/pc3/pc4. When the users on those pcs click the form/msgbox it will call the form that is used to manipulate the data that pc1 wrote to the dbase.

 

[NigelShaw]

hi

i still think it can be done in the routine that writes to the table. its irrelevant how many pc's there are. pc's 2,3 & 4 all should have the same FE which could be on a timer say, 5 seconds that could ping the pc1. A simple way would be to check a boolean on pc 1 that would be set to true if a new message arrives and then reset when its collected by the other FE's.

i'd go for the baloon notify on the bottom right and i posted a sample recently that allows you to set a heading & a message and it uses the windows balloon.

there are many ways of testing for a new message. it would be easier to work from the other pc,s to check the pc1 rather than the other way round as these other pc's are the ones to get the message.

does that make sense?


Nidge

 

[stillnew2vb]

Hi again,

OK, I think I get what you are saying..

So, create a module?

Do something like

Option Compare Database
Public Sub Timer(Cancel As Integer)

Me.TimerInterval = 10000

Dim db As Database, tb As DAO.Recordset
Set db = CurrentDb
Set tb = db.OpenRecordset("dbo_Support", dbOpenDynaset, dbSeeChanges)
    
Dim strStatus As String
'Need to set strStatus as the "Status" field in the table
If strStatus = Null Then 'Status field has null value
DoCmd.OpenForm "SupportList" ' open the list of new calls
        
End Sub

Is that what you mean?

 

[NigelShaw]

Hi,

here is a very simple sample. you would set a routine that would call out to PC1 database on a timer looking for a boolean every 2 -3 seconds that would be set to true. This would be set when an incoming email is picked up. Once the email has been collected, the boolean is set to false and it all starts again.

the checkbox on the form is to simulate the boolean. true = message and false = no message.

if the boolean is true, a balloon tip appears in the bottom right. Hopefully, it is clear enough. it would need some work but the tools are there etc etc.


let me know if it helps and how you get on



regs


Nidge

 

[stillnew2vb]

Wow..that's some serious code...

It's also WAAAAY over my head and I would love to use it if I could understand it. I think however that it is much more than I need and, if you will excuse me, I think I would prefer to simplify life a little and go with the idea of using the F/Es to query the dbase table for new records every few seconds and raise a message box if new records exist.

I think the easiest way of doing it would be to create a module which queries the database table for records created in the last three seconds. This module would then load as Access is launched.

Your thoughts?

 

[ghudson]

If I was a user of your db, I prefer that you load a form (once every 60 or 30 minutes) that lists all the new records created for the current day. Then have a button beside each record so that I can jump to that record if I want. Having a message box pop up every few seconds each time a new record is added would make me want to close your database real quick.

 

[stillnew2vb]

GHudson...

aaah, if only I could...

It's a support database. Users send us requests for assistance that need to be dealt with A.S.A.P.

 

[gemma-the-husky]

the Access Cookbook demo'ed a messaging system that didnt use emails, but used a timer to check a table for new records every so often.

However, when I tried implementing it, it seemed to slow the system down, and I think there were some problems about the popups not being "on top", so we never actually used it.

Maybe try that though if you can find it - as the idea is sound, and the functionality is supposedly complete. The problems amy have just been my implementation.

 

[ghudson]

GHudson...

aaah, if only I could...

It's a support database. Users send us requests for assistance that need to be dealt with A.S.A.P.

I would still advise against using a message box since the user might be in the middle of typing or doing something and the message box could be closed before the user had a chance to read it. Using a custom form set to pop up and modal or have a message displaying in the status bar of the form might be a working alternative. I am guessing that there is a field that has to be set to mark the record as completed or in progress, etc and you could query for new records that are not completed etc and have those show up in what ever method you decide to go with.

 

[stillnew2vb]

ghudson..

I get your point, the alert has to be interactive so that when it is clicked to acknowledge it then that click triggers the calling of a form which summarises the request. If the user double clicks on a field in that summary it opens the form which is used to process the request.

What I am struggling with is getting the message box to pop up when the underlying form receives a new record. I have tried AfterUpdate, OnDirty, AfterInsert events but I can't seem to get the msgbox triggered.

Alternatively, I am also trying to get a timer event going which starts when Access is opened (I would prefer not to use a hidden form) which polls for new entries to the underlying table and, if there are new records, launches the msgbox.

 

[ghudson]

Using a hidden form with the timer is your best bet since the users will be in other forms within your database. I still suggest using a form that pops up with a listing of the new records. Timers can bog down a computers CPU so I would only check for new records every 60 seconds. Have the form open with a listing of new records and let the user click a button to begin action on the new record. You will have to have a check in place to do something if a user has already opened the new record to begin their action so that no more than one user is in the new record.

 

[stillnew2vb]

I already have the form, it's called SupportList. I am thinking it may be an idea to launch it at Access startup from the "Startup" option, pop-up a message saying "no new entries" or something similar and auto-minimising it to the taskbar. That form could then query the table every 1 minute (if you insist) and auto-maximise if new records are found.

What do you think? Is that do-able?

 

[KenHigg]

So use the timer event to turn on a flag on. Say something like make a label that says 'Alert' that becomes visible. That way a message box will not pop up.

 

[stillnew2vb]

Hi Ken,

I would except then I can't run the form minimised surely?

 

[NigelShaw]

the Access Cookbook demo'ed a messaging system that didnt use emails, but used a timer to check a table for new records every so often.

However, when I tried implementing it, it seemed to slow the system down, and I think there were some problems about the popups not being "on top", so we never actually used it.

Maybe try that though if you can find it - as the idea is sound, and the functionality is supposedly complete. The problems amy have just been my implementation.

Hi

I have a complete message module that works on a timer and checks for new instant messages.

Do you need one GTH?

Nidge

 

[NigelShaw]

Here's a question

Pc1 collects the emails from outlook and posts then to a SQL server

Pc2 3 and 4 then need to query the server for new messages.

What prevents multiple opening of the same email on different pc's? Is there anything to notify another pc that it's been processed?

Nidge

 

[stillnew2vb]

Hi Nigel,

Here's the current method.
As stated, PC1 runs Outlook & Access. There is an Outlook.OTM script that "listens" for messages on an Exchange public folder and when one is received it passes the new mail on to Access. Access stores the required details in a table (with a SQL B/E) and marks the message as read.

The F/Es on PC2,3 & 4 call a "SupportList" form which loads on Access startup. In the form there is a "Status" field and an "Acknowledge" field. The record source for the form defines 'where Status = Null' (if nobody has seen the record it cannot have been marked as Closed/Pending ...) and defines 'where Acknowledge = 0' (acknowledge is a checkbox). So as the record is displayed the person actioning that request checks the acknowledge box. When the timer on the form reruns and requeries the underlying table that record is therefore no longer listed in the form.

It's a tad simple (OK very simple) but I will be testing it today. What I am trying to do though is to get the form to stay minimised until a new record is detected. I'll let you know how it works out...