Issuing an alert when a control value is changed...this is driving me nuts...

[damjanovic]

Hi all,

I have a DB which is designed to have 1 admin and several users on at the same time. The users and admin all work with the same form, called frmOrders, which is a continuous form that lists out, in real time, orders which the users enter. I have a timer form called frmTimer running (hidden) in the background which updates frmOrders every 2 seconds and checks if a new order has been added. If yes, a MsgBox pops up on the admin's screen saying that a new order has been entered.

I'm trying to add some functionality so that any time a user changes the value in any field for an order that he/she has *already* entered, a MsgBox pops up on the admin's screen as well. However, I'm having some trouble with this. Basically, I want Access to 1) recognize when a field in an order is updated/changed, 2) check if the CurrentUser is the admin, and 3) if yes, give him a popup MsgBox alerting him that the particular field in question has been changed. Below is an example of the code I'm using right now for the Order Quantity field, but it doesn't work - the admin doesn't get a popup when the field is changed. Nothing happens.

Private Sub OrderedLots_AfterUpdate()
  If CurrentUser() = "Admin" Then _
    MsgBox "The order quantity for order ID " & [IDKey] & _
           " has been changed. Please check the Order Status screen", _
           vbExclamation, _
           "Change made to order quantity."
  End If
End Sub

I was wondering if anyone could shed some light on this or provide an idea on how I can get done what I need to get done? This is starting to drive me bonkers...Thanks!

[ChipR]

Your users will all have a different copy of that form in memory. When a non-admin updates a field, that code is called on their form, and nothing happens. When an Admin updates that field, however, they should get a message saying that they updated it.
I have an issue that is similar, but not quite the same, so there may be an easier way for you to do this. I have a table in my DB that as an admin I can add a user to. Every 30 seconds, a hidden form in every user's copy of the frontend checks that table to see if their username is in it, and if it is, quits out of access. So for your problem, any time a user made a change you could insert into the table, and when your admin checked the table, it could give notice if the recordcount > 1. It should also delete the records once it recognizes them. Records could be a single field with yes/no or whatever.

[damjanovic]

Hey ChipR,

Thanks for the response.

So just to get this straight:
1. Every time a user makes a change to one of their orders (I assume I'll use the control's AfterUpdate event), the value is inserted into the "holder" table.
2. The hidden timer form will check every X seconds to see if there is something in the holder table. If yes, issue a MsgBox where CurrentUser = Admin.
3. Delete the entry in the "holder" table.

That sounds good, the only question I have is how will the table be able to handle different field types? If the "holder" table is set up so that it has a text field and a number field, what happens if a user changes a Yes/No control on the form? How will tht be inserted into the "holder" table? Or does this not matter (maybe if I use SQL to do the insert, this issue is irrelevant)?

[ChipR]

Yes, I would use an SQL insert, and you can put whatever you want in the table, just the fact that a record is there will alert your admin that something was changed. If you want to give a more specific message to the admin, and you have AfterUpdate events on each of your controls, you could insert the field name, and you may very well want to insert the record number, because when you tell them that something in the recordset changed, then you could tell them which record and which field it was with a DLookup on your table.
For efficiency, I would test for admin first, and if admin, then check the table.
Also, decide whether you want to process each record in the holder table individually, or just say "1 or more changes have been made to the current recordset" and delete them all.

[NeoPa]

What code do you currently use to determine whether or not a user has added a record, so that the Admin can see this?