send a msg box to another form based on an event in another form (1 Viewer)

sunilvedula

Sunil
Local time
Today, 21:16
Joined
Jan 18, 2007
Messages
138
Hi All,

What i am trying to accomplish here is this:I have 2 forms one is prod form and the other is report form. When user updates something on the report form i need a msgbox to be popped up in the prod form for all other users who are working on it.

I can use a timer control to check if any update happend on the table and then populate a message but it will keep on running on the form continously. there are about 20 users who are woking on this database and on every form if timer event works it will be a lot of unecessary space.

I did give a refresh button which will refersh and show them but if a message box gets displayed the moment an update happens on the report screen then it would be better. Pls give me some ideas as how best i can do this.
 

Mr. B

"Doctor Access"
Local time
Today, 10:46
Joined
May 20, 2009
Messages
1,932
Is this a "split" application where each user has their own copy of the front-end? (It definately should be)

If it is then you will have to use some method to check the status of the data as each front-end will not know about the other one. If you are dealing with only one form that would need to know when the data has been updated, then that would not be a tremendous amount of code or processing. Your idea of using the Timer is a good place to start. You could set your timer to run at a minimual amount of time between each check of the data.
 

sunilvedula

Sunil
Local time
Today, 21:16
Joined
Jan 18, 2007
Messages
138
First of all i am sorry to say but each user does not have his own copy. It is one executable file which is placed on a network drive and all other users access the same copy. It recognizes each users who logged in through a code and submits the data into the tables with each user id. what i did for the present moment was used the timer to check for an interval of every 360000 milli seconds or make it 1hr to see if any updates have happened in the table and if anything is there it will throw a prompt to the user and also refreshes the list box on the form. Any better ideas or suggestion please go ahead
 

Mr. B

"Doctor Access"
Local time
Today, 10:46
Joined
May 20, 2009
Messages
1,932
It is one executable file which is placed on a network drive and all other users access the same copy
If you do not implement the "Split" method that I have previously described you will eventually start to have problems with recrod locking and corruption. Think of it as if you had only one Excel file or Word document and you were expecting your 20 users to use the same file. I think you can imagine what would happen. Although Access manages to handle multiple user a little better than Excel or Word do, it is still an issue that will come back to create problems down the road.

It recognizes each users who logged in through a code and submits the data into the tables with each user id.
Using the "Split" method will have nothing to do with the functinality here.

what i did for the present moment was used the timer to check for an interval of every 360000 milli seconds or make it 1hr to see if any updates have happened in the table and if anything is there it will throw a prompt to the user and also refreshes the list box on the form.

So, what happened. Does this work or what is happening.

I realize that I am really harping on the Spliting of your database, but it is really just that important.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:46
Joined
Feb 28, 2001
Messages
27,317
The timer you describe is not common to the .EXE file OR the .MDB - it is a thing in your active workspace, which is a virtual-memory element on each computer.

I'll say something in passing and then address the other aspect of this question. You do not do yourself a favor by sharing a common database file with tables and other features in the same file. Having a split database is really a stability issue. The backend will be far more stable if it doesn't have the additional lock traffic associated with a shared (rather than distributed) front end. This forum has literally dozens of threads in which someone reports database instability, corruption, and incredibly poor performance related to having a single-file, shared copy of the database.

You say "single executable image" on a server, but I sincerely hope you don't mean that the way it sounded. If you are sharing Access.EXE, please be aware that there are license issues to consider even if you are using Terminal Services through something akin to Citrix. If you meant a "single .MDE file on the the server" and everyone actually has their own copy of Access, that is perfectly OK and a lot more stable - though still not at all the best way to run things.

OK, as to timers and having people immediately notified of an event that should "pop up" when some other event occurs: My original comment about workspaces is relevant here. NOTHING happens in the .MDE or .EXE file, whichever one it is that you share. All work happens in the copy of Access that is loaded to your system's memory.

Access creates a purely localized workspace for you when you open a database. Anything you do occurs in that workspace. This is true even if you are using Terminal Services, since there you get your own copy of a Windows virtual system in which to run your .EXE file.

When you open a form, you open it in your workspace. If there is a timer involved, it runs in your workspace. Since your workspace occurs on your machine and (at most) might have a component resident in your swap file, what happens in your machine stays in your machine. (Must be like Las Vegas?)

The ONLY two ways I can imagine to cause this pop-up to occur on everyone else's system is to either (a) establish active network links between all cooperating systems, or (b) put information in a place everybody can check periodically.

Implementing solution (a) is for network gurus and folks with serious masochistic tendencies. The WinSock interface is not that easy to set up from Access. Not impossible, just not that easy. Another part of your problem would be to know how to connect to everyone else in the connection set, i.e. knowing who is already there and getting them to acknowledge you.

Further, your system would have connections growing geometrically, which gets very big very fast. With five users you have 1 connection outbound to each of four other systems, or 20 connections total. (Each outbound connection from A is an inbound connection on another system, so I'll state it this way and avoid double-dipping.) Make that setup for six users and you have 30 connections. Seven users, 42 connections, and so on. (It is an n*(n-1) situation, statistically). You said 20 users. That's 380 connections. Your network folks will LOVE you for that one.

Solution (b), on the other hand, is very quickly and easily implemented by having a "common" table of critical information and just polling that table from a form-based timer. Be sure to use either the DLookup function or, if you do your own query, use optimistic locking. Then when you want something to happen, you store data in the shared table and wait for the Timer-based DLookups to catch it. (DLookup always uses optimistic locking, as far as I could determine.)

The timer and any pop-up stuff would be in your workspace, your memory, your machine. It will be cheaper in technology and cheaper in the time it takes to correctly implement this for you to define a holding table for your special notifications and update it when some kind of global update is required.
 

sunilvedula

Sunil
Local time
Today, 21:16
Joined
Jan 18, 2007
Messages
138
wow!!! that is a lot of stuff for me and thank you all for your advices. As for split database i completely agree with you in this regard and my 100% support for it. I have this MDE file which is located in a folder on the network drive. Every user opens access from their computer and open this file from the folder. I agree to corruption of database and hence i have stored the backend in a different folder and the mde has links to this backend table. If there is a better way than this i need to know please go ahead and advise me and i promise tomorrow itself i will make the necessary change.

As for the timer bit i used solution b as suggested. what i do is for every one hr the form timer runs a query to check in the table to see if any new update has happened and then it gives a message and also updates the list box which is based on this table. let me know your ideas. I hope i am right as it worked in the testing bit.
 

Users who are viewing this thread

Top Bottom