Alert on new record, only once

[wheeledgoat]

Hello folks,

Cooking up a "Visit Tracking" application in Access (2007-2016 format). Each area has its own Popup form:
-Front Desk
-Nurse
-Doc
-etc...

1. The front desk creates a visit, and it appears on the Nurse's form
2. The nurse rooms the patient, marks which doc and room, and the visit appears on the appropriate Doc's form
3. Same idea as the visit progresses through similar queues for the MOA, pharmacy, etc...

The db is split. All this works great.

I want to set an alert on the form when there has been a new addition to their queue. Running on the form's timer, when it finds an addition it pops the window up on top of other windows (AppActivate) and plays a wav file. This works fine.

My problem: As the trigger for the "new" patient alert, I have it testing if the record is <2min old, running on the form's timer every 60 seconds. This means some new visits will alert twice (if the initial checkin is done just before the next minute, per access rounding. The time is automatically recorded upon ticking a checkbox).

At first I considered creating a field in the table to keep track if the visit has been alerted, but it's possible that two nurses will have their own front end of the same form open, and it should alert for both of them. If it alerts for one nurse and records on the backend table as alerted, the 2nd nurse's front end won't alert.

Any ideas on how to keep track of if a visitID has been alerted for each front end instance?

 

[theDBguy]

Hi. What happens if you change the check to <90 seconds? Also, if you're okay with using a "tracker" record, then to avoid not firing for the second opened FE, then you can put the tracker in the FE instead of the BE.

 

[wheeledgoat]

Hi. What happens if you change the check to <90 seconds? Also, if you're okay with using a "tracker" record, then to avoid not firing for the second opened FE, then you can put the tracker in the FE instead of the BE.

I haven't played around with the exact timing and how it works with access rounding the time - I just threw the 2min thing in when I was building it as "good enough" for then. Now, I'm in the final stages of polishing this thing up, and it just feels sloppy. Would rather have a definitive alerted-or-not status, ya know?

How would I put the tracker record on the FE? I believe this is the approach I would prefer but have no idea how to do it. Keep the alerted visitIDs in a variable? How to parse/read that variable?

 

[theDBguy]

Right, so, create a local table of IDs you've already alerted. Before showing the popup form, check the new record's ID against this table. You can use a query for it or use the DLookup() or DCount() functions. If it doesn't exist, add the new ID and show the popup/alert. Otherwise, don't do anything.

 

[Deleted member 147267]

and it should alert for both of them. If it alerts for one nurse and records on the backend table as alerted, the 2nd nurse's front end won't alert.

If I understand that correctly, I think a local table won't work? How would FE2 see that FE1 got or didn't get an alert when the a local table isn't exposed to the other db's?

 

[theDBguy]

If I understand that correctly, I think a local table won't work? How would FE2 see that FE1 got or didn't get an alert when the a local table isn't exposed to the other db's?

Hi. The way I read the part you quoted is the OP wants the alert to show up if the same FE hasn't done it before, which means the two FEs shouldn't be connected to the same information. In other words, when FE1 shows the alert, then FE2 should also. Did I misinterpret?

 

[wheeledgoat]

Right, so, create a local table of IDs you've already alerted. Before showing the popup form, check the new record's ID against this table. You can use a query for it or use the DLookup() or DCount() functions. If it doesn't exist, add the new ID and show the popup/alert. Otherwise, don't do anything.

Brilliant! I didn't realize a split db could also have FE tables. That sounds about perfect. I'll go look up how to do that and I think I'll be set.

Thank you sir!

 

[Deleted member 147267]

Did I misinterpret?

Probably not. Usually it's me as there is black and white and nothing in between, if you get my drift. What is not wanted is more clear to me.

some new visits will alert twice (if the initial checkin is done just before the next minute, per access rounding.

Maybe the issue is mostly around this statement. There should be no rounding of time, yes? A time stamp even a few (micro)seconds before the timer event should work.

 

[theDBguy]

Brilliant! I didn't realize a split db could also have FE tables. That sounds about perfect. I'll go look up how to do that and I think I'll be set.

Thank you sir!

You're welcome. Please let us know how it goes, just in case I didn't understand the requirements correctly. Good luck!

 

[wheeledgoat]

There should be no rounding of time, yes? A time stamp even a few (micro)seconds before the timer event should work.

It reads off of an already existing text box that shows the minutes elapsed (DateDiff) in whole numbers, thus the rounding. It was a quick-and-dirty way to get on with the project that I'm sorting out better methods for now.

Indeed, I do want each front end to alarm independently, so I believe the FE table will be the trick I was looking for.

 

[Deleted member 147267]

It was a quick-and-dirty way to get on with the project

Understood. Maybe later consider using the system time function Timer() (single data type, I believe). Now that you will have a local table, you can store a time stamp that is accurate within a second or less, and the timer event can check the current system time (Timer() )against the system time that was stored.
@theDbGuy - see, that's why I don't bet on anything.