How to set up db so one user sees only forms and other user has access to all?

[AccessIdiot]

Right. My understanding is that this db is going to be sitting on a guest computer. So anyone can launch it without needing a log in (or there is a guest log in on the computer). The guy that will be owner/administrator of the db won't necessarily need to log in either, so it might help to create a log in to differentiate between him and the folks doing the data entry.

Sorry, I'm just realizing how this is going to work over there.

Oh and see post #26 - I'm getting an error message on the close form.

[NeoPa]

AccessIdiot: I am getting this error message:
...

I'm going to need a clearer, more precise, description of exactly what you're doing and what goes wrong where with error message(s) etc if I'm to help debug this at a distance. We may get to attaching a copy of the database for me to look at if we need to, but we're probably not there yet.

[AccessIdiot]

Right, sorry, you probably can't see through the computer screen can you? :-)

When I hit the "exit" button on my switchboard (which is simply docmd.exit on click) I get the error message posted above.

When I simply click in the X in the upper right hand corner I get "Run-time error '2501': The RunCommand action was canceled."

*edit* when I am smart enough to remember to change "MKM" to my actual username on my machine I get a Compile error: variable not defined on conClear in your code.

[NeoPa]

AccessIdiot: Right. My understanding is that this db is going to be sitting on a guest computer. So anyone can launch it without needing a log in (or there is a guest log in on the computer).

In that case I'd think seriously about bothering with this at all. My experience tells me that 99 times out of a hundred one person will come along and leave it running, then another will come along and use the same session. If they all think they'll be using this on the same computer in any sort of controlled way then I think they're naive, frankly.

That goes for giving a password just as much as it does for the account ID.

[AccessIdiot]

Well, I have to have something to give them where he has access to the tables and everyone else has access only to the forms. I would love to be able to hide the navigation pane on launch and the first thing that pops up asks if you want to log on as administrator (navigation pane becomes visible if you log in correctly) or go right to the main switchboard and the navigation pane stays hidden.

[NeoPa]

AccessIdiot: When I hit the "exit" button on my switchboard (which is simply docmd.exit on click) I get the error message posted above.

That's tricky. It's part of the new code I added for your situation (most of the rest I use myself in most projects). Maybe it doesn't like trying to close the database while within the form's Close event. I may need to look at this again.

[NeoPa]

AccessIdiot: Well, I have to have something to give them where he has access to the tables and everyone else has access only to the forms. I would love to be able to hide the navigation pane on launch and the first thing that pops up asks if you want to log on as administrator (navigation pane becomes visible if you log in correctly) or go right to the main switchboard and the navigation pane stays hidden.

I think you missed the point here a little. Even with such a feature it still wouldn't work. Extra work. Extra complexity, all for people who don't appreciate that it is their own ideas and approach that will mean it won't work.

I suggest you go back to them and explain how impractical is their suggestion for a working environment and see what they come back with.

It's easily enough managed, whichever way they want it, but you can tell them from me that it will all be pointless due to their approach.

[AccessIdiot]

I wish that were an option. If it is too impractical then I can just tell them to tell people not to open any of the tables and use the switchboard only, but it sure would be cool not to show the navigation pane. I guess I can tell the guy who wants access about F11.

By the way I don't know if you saw the edit I added in post #33?

[NeoPa]

AccessIdiot: *edit* when I am smart enough to remember to change "MKM" to my actual username on my machine I get a Compile error: variable not defined on conClear in your code.

I so nearly missed this with all the posts going backwards and forwards.

I think I may need to have a closer link to go forward with this. Do you have Skype installed yet?

[AccessIdiot]

Not allowed at work. :-( Shall I send it the 1990's way? :-)

[NeoPa]

Attach it to the thread. I was hoping to chat and iron out some of the complications as so much has been covered but over a forum page which is not the easiest or most reliable medium in the world.

Anyway, see below for the attachment instructions I usually post :

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[AccessIdiot]

Sigh. Well, I'm not sure what happened but I no longer get an error message. So I'll hold off on attaching it right now since that seems to be working fine.

However, I had my coworker log in to a machine and launch it and the navigation pane shows up? How do I hide that from anyone other than:

Private Const conClear As String = "Administrator;MKM"

or is that what you were saying is difficult to do?

[NeoPa]

Much water has passed below this bridge. I'll recap some of it if I can.

AccessIdiot: However, I had my coworker log in to a machine and launch it and the navigation pane shows up?

This indicates that something isn't working as it should. I may have to look at the database after all. The Form_Open() event procedure should handle hiding the database window for all users. The only difference for the Admin person(s) is in the Form_Close() one.

AccessIdiot:

Private Const conClear As String = "Administrator;MKM"

or is that what you were saying is difficult to do?

No. I think you said you'd prefer to manage this with a table. The next point deals with that directly, but for now we can do a test that this part is working if you let me know what the UserName is that is being used. To get this information go to the VBA IDE (Alt-F11) and then the Immediate pane (Ctrl-G) then execute :

?Environ("UserName")

Post the result back in here if you would. That will also be added to the conClear declaration as "Administrator; MKM;New Value".

NeoPa: If you want a table approach, may I suggest that you first create a table to hold the data. I doubt you'll need help with that part.

If you prepare this now then it will exist by the time you attach your database.

If you are still noticing problems then I suggest this point would be a good one to attach your database to the thread. Let me know if ever you want an attachment removed after I've downloaded it myself. Otherwise I'm happy to give you my email address (if you don't have it already) and I'm happy for you to use that if you'd prefer.

[AccessIdiot]

I'm afraid I'm gone for the next few days. I'll address when I return, thanks.

[NeoPa]

No worries M.

It's a shame to miss the opportunity of the weekend, but that's how it is. I'll respond when next you post.

Cheers.