Query not combining and filtering values based on a form

[GinaWhipp]

Looking now...

 

[GinaWhipp]

Okay, so here I am going crazy and I finally figured out the problem AND feeling stupid for not seeing it earlier :banghead:

This is an .MDB which is being opened in Access 2010. The take away is an .MDB! I made some adjustments and all should be good now.

 

[mayestom]

I'm sorry, I didn't think of that. Thank you for fixing it for me.

So I set the switchboard to open automatically when you open the db and nothing happened. It didn’t load the frmNewUser. It just opened the switchboard.


I’m also not entirely sure on how to use it? How does one assign permissions to users (to only read, read/write or Admin)? Do I need to add all users in tblUsers before hand and assign the permissions? And, I didn't think of this before, but I don't have a way to use a password for users to access the db.

 

[GinaWhipp]

Didn't do that for me, I had to enter my name and then the Switchboard opened. Were you already in tblUsers?

The New User Form will assign permissions, I think I set it to 2 but you can check that. To manage the Users permissions you are going to need a separate Form. No worries about the Password it is not required or need for permissions.

You do not need to add the Users before hand as they will add themselves upon opening the database for the first time.

 

[mayestom]

I closed and opened it. The switchboard opens and nothing else. The new user form does not open.

I’m not following how this works. New users will be able to add themselves to gain access to the db? So how does this restrict access to the db? And how would I be able to assign different levels of permissions?

 

[GinaWhipp]

First things first, is your Name already in tblUsers? If it is delete it and then close the database. Does the New User Form show now?

 

[mayestom]

I deleted it and reopened and the new user form pops up in from of the switchboad. Entered name and pop up goes away. Closed db, reopened and just switchboad shows. So I guess it works correctly, right?
But I'm still not following how this is supposed to work? A new user will be able to add themselves to be able to use the db? With no restrictions? How are restrictions applied?

 

[GinaWhipp]

That is correct, you didn't want the Users logging on all the time so it only shows if they are not already in the table.

The purpose is so that you can now *code* their permissions based on what you assigned them to, Read, Read/Write/ADMIN. It is not designed to keep Users out. Now, you can use the code that is on the On_Current event to control what your Users can do.

 

[mayestom]

Would you explain how I can use the code to control what users can do? How do I control what permissions are assigned to a user?
Example: I opened the db from my home computer and the new user form popped up and I entered my name and it opened the switchboard. I opened the tblUsers and it showed uSecurityID as 2 (which means read only, correct?). But I'm still able to edit/add records in all tables, change the design of the table, able to change all forms.
I'm not sure I'm getting how to use this or how this works? Am I using it properly?

 

[GinaWhipp]

You can make it so Users can only view the data or view and modify the data. You can use the ADMIN portion to make it so only ADMIN's can do certain functions like add new values to Combo Boxes.

In order to actually enforce those permissions you need to apply the On_Current event code to specific Forms, plus other coding for ADMIN's. Have you looked at that On_Current code?

 

[mayestom]

I'm assuming we are talking about the On_Load event code that is under the switchboard? Yes, I looked at the code (read it many times when I was trying to adapt it to the db to make it work). But I'm still not sure on how do I apply it to specific forms/tables for specific users? I thought it was is enforced when the switchboard loads?

 

[mayestom]

Oh wait, the On_Current code at the bottom of Control Form Access?
I've been reading and re-reading your past couple of posts and the code all day and can't figure out what I'm supposed to do? What I'm getting so far from your posts:
-I need to make a new form to manage permissions (Form is based off of what table? What fields do I place in the form?)
-On-Current code is what actually controls permissions.
-I need to apply the On_Current code to each form that I would like to enforce permissions. (What about table permissions?)

 

[GinaWhipp]

No, I was referring to the On_Current event...

Private Sub Form_Current()
On Error Resume Next

If Forms![frmMainMenu]![txtSecurityID] = 9 Then
[URL="http://www.access-diva.com/vba6.html"]fncLockUnlockControls Me, True[/URL]
Else
[URL="http://www.access-diva.com/vba6.html"]fncLockUnlockControls Me, False[/URL]
End If

End Sub 

Private Sub Form_Current()
On Error Resume Next

If Forms![frmMainMenu]![txtSecurityID] <> 13 And Forms![frmMainMenu]![txtOverride] = False Then
[URL="http://www.access-diva.com/vba6.html"]fncLockUnlockControls Me, True[/URL]
Else
[URL="http://www.access-diva.com/vba6.html"]fncLockUnlockControls Me, False[/URL]
End If

End Sub

You're going to need that.

 

[GinaWhipp]

Hmm, missed your post...

The answer to all three is yes, except... Table permissions? No one should see the Tables let alone be in them.

 

[mayestom]

Ok:
-I need to make a new form based off of what? What fields do I put in it?
-I apply a modified On_Current to all forms that I would like to apply permissions to.
-But I don't have a fncLockUnlockControls form?

 

[GinaWhipp]

1. The fields that are in tblUsers
2. Okay
3. Did you copy fncLockUnlockControls into your modUtilities module? (If you click on it will push to the page showing the code.)

 

[mayestom]

Made form named frmUsers. Record Source tblUsers.
When I modify On_Current I will replace "frmMainMenu" and "Form_Current" with the form name that I'm placing the code in? Or just "frmMainMenu"?
Opps, I copied fncLockUnlockControls into a new module. Will put in under modUtilities.

 

[GinaWhipp]

Oh, yes do change the names of the Forms to match your own. And in that code copy between first line and last line and place in your Forms On_Current event.

 

[mayestom]

Okay applied the code to the On_Current event to the Switchboard to start with and all complied ok. Will apply to the rest of the forms once I have everything working properly.
What would be the next step or steps?

 

[GinaWhipp]

Test one of the Forms to ensure when the SecurityID is Read Only that the Form does not allow changes.