A Really Cool Idea....If it is possible (1 Viewer)

[ccflyer]

Hey everyone,

I have a crazy idea that I think would be very cool if I got it working, but have no idea where to start. I have a database that is broken down into a front end and a back end, and there are about 10 users who use it off and on throughout the day. This database has an entry form to enter new records. I am wanting to put a text box or a label on that main form that would turn green if the database was in use by another user, besides the one that was currently looking at the form. It would be red if there are no other users accessing the database. Is this even possible? Any suggestions or input would be greatly appreciated.

Thanks,
Chris

 

[KeithG]

is there a form that remains open the whole time the database is open?

 

[ccflyer]

Actually, yes, there is a form that remains open. This is an invoice database where the back end is kept on our company server, and each of the 10 users has his/her own front end on their computer. When they wish to enter an invoice they open their front end and a main form comes on the screen giving them various options (reports, tables, more forms). From here is where they open the invoice-entry form where I am wanting to put this new feature. The main form does in fact remain open the entire time they have the database front-end open.

-Chris

 

[KeithG]

I would make a user table name tblUser. Then on the main forms open event I would use the Date() and Environ() functions to retrieve the date and username of the person who entered the database.

Then in the forms Close event I would agian use the the Date() and Environ function to capture who has left the database.Then you can query the table to see how many users are in the database.

 

[ccflyer]

So if I understand what you are saying, I would have a table named tblUser and it would have two columns, Date and User. The Date() in the open event would get the current date and time and store it in the Date column of tblUser, and the Environ() in the open event would retrieve the user's windows logon name and store it in the User column of tblUser. When the form closed these functions would again store this data (but leave time) in another record of tblUser.

I don't really understand how my query would be set up. Would you be able to explain a little?

Thanks,
Chris

 

[ccflyer]

Sorry for double posting, but I thought of another way that might work -- still based on the same principles of the Environ() function. What if the table tblUser had two columns. The first would list all the names of the users, and the second column would be a simple check box (yes/no). When the user opened the first main form, the Environ() function would look up who they are and then somehow the check mark beside their name would become checked. When they closed the main form, it would remove the check mark in the table, tblUser beside their username.

Using these checkmarks, then a query could be created to determine if there was any checkmarks in the table, tblUser. Any thoughts on this way?

-Chris

 

[KeithG]

Yeah that sounds like a good idea. Except with my way you have a record of when each person was in the database.

 

[ccflyer]

Yeah, it would be neat to have a record of who entered when, but I don't really think it is necessary. My company wouldn't really need to see those records on a daily basis, and they would probably start bringing up the database filesize at a higher rate than normal -- but it was a good idea!

Could you help me get the check box way working? I have a general direction of what to do, but am not totally confident quite yet.

I have created a table named tblUser that has 3 fields (AutoNumber, User, and Check). I have set the check column to a Yes/No datatype. I am not quite sure how to write the code to put in the On Open event of my form. Also, would it be possible to enter the user's record into tblUser the first time they open the database, so I don't have to manually enter the users?

Your help is very appreciated!
-Chris

 

[KeithG]

Here you go! When form StartUp gets opened the database will check to see if the user is in tblUsers if so it will update LogedIn to true. If the user is not in the table it will add a new record. When the form closes it will update the records logedIn to false.

 

[KeithG]

Here you go! When form StartUp gets opened the database will check to see if the user is in tblUsers if so it will update LogedIn to true. If the user is not in the table it will add a new record. When the form closes it will update the records logedIn to false.

 

[ccflyer]

Hey thanks KeithG! It works perfectly. So, in order to get rid of the warning messages when the form is opened and closed, I would just add this line at the beginning of your open code block:

DoCmd.SetWarnings False

Now I just have to figure out write the query needed to make my text box turn green if someone is using the database (someone else besides the current user).

After the main form (the one with the code block) opens, the users open another form to enter invoices. The main form still remains open the entire time. This invoice form is where I wish to place a text box that is green if another user (besides the current one looking at the screen) is using the database, and turn red if he/she is the only one using the database.

Thank you!!
-Chris

 

[KeithG]

You will probably have to use the Dcount function to find out if more than one person is in the database.

 

[The_Doc_Man]

Here is the catch in all of this: If the network is down or your box hangs, you are out of the database but didn't get a chance to clear your tables. So your counters get hosed for a while, giving you "false positives."

The "real" way to do this requires cooperation from the file server to know if anyone else has a shared FILE SYSTEM lock on the .LDB or .MDB file. I don't know that a non-administrator can get to this type of information. The contents of the .LDB file are just a database (which Access can read) but the .LDB file suffers from the same problem as the table method you are describing. If you crash or the network hiccups, you are out without having updated your "I'm here" flags, whatever they are.

 

[ccflyer]

Yes, I guess you are probably right. I hadn't thought of that, but in my case, I don't think that it would be too bad if the false positives occurred once in a while. I still would like to get this table system working so I can see it work.

I really am very new to Visual Basic, so please be patient. The Dcount function is:

DCount(expr, domain, [criteria])

I have a text box on my invoice form that I wish to make turn colors as described above. Do I use the DCount fuction in the code section of the text box's After Update event?

I think what I need out of the DCount function is the number of users currently logged in, then an If..Then statement would determine if there are more one user logged in on the table, tblUsers. So IF there is more than 1 user (the current user) logged in the table, the text box is green. IF there is only 1 user, then the text box is red.

I can explain it, I just have a hard time putting it into Visual Basic code.

Thanks for helping out!
-Chris

 

[KeithG]

Is this what you are looking for? Look at frmExample.

 

[ccflyer]

Wow!! That is exactly what I was looking for. You are a very skilled VB and Access developer, KeithG!

That successfully completes the cool idea that I had, I couldn't have done it without you! But now that it is working, I am wondering if there is a way to list the users that are logged on and using the database?

-Chris

 

[KeithG]

Thanks for the compliments. Here is another example for you listing the user names of the loged in people

 

[KeithG]

To add to The Doc Mann's point if a user hits Ctrl + Alt + Delete and end's the Access program you will also get false results.

 

[ccflyer]

Hey, that worked! That is really cool to be able to see when and who is accessing the database. Thanks once again KeithG! As far as what Doc Mann said, I know that it could happen, and it probably will, but I don't think it will be too big of a deal. I can always manually reset the table right?

-Chris