MS Access 2013 VBA View Tables

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
173
I have a Access DB which I want to upon a successful Login, allow the user to view tables with read or write permissions or both...This is what I have thus far...Any help would be greatly appreciated...

Private Sub btnLogin_Click()
Dim strCBOPassword As String
Dim strPassword As String

strCBOPass = Me.CboPID.Column(1)
strPassword = Me.txtPassword

If strCBOPass = strPassword Then
MsgBox "Login Successful!"
Else
MsgBox "Login Unsuccessful!"
End If
End Sub
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,906
Office Version
  1. 365
Platform
  1. Windows
you need a table of users (tblUser) with all of the user attributes as fields. To exert control, one way is to cross reference their profile/level in this table against a table of permissions, but there might be as many ways you can do this as there flakes in a cereal box. There are determining factors, such as, will this be based on controlling who gets to open a particular form or report? Then it could be as simple as providing a form with command buttons, some of which you hide when the form opens, based on their profile. How to know what this profile is can be as simple as opening a form hidden (when db is opened) and storing the user info, storing that info in an environment variable (see Environ) or as complicated as creating a user object to which you assign any property that you think you might need (e.g. EmplNo, Level, emlAddress, FName, LName, etc.). When they try to open a form, your code checks the Level (permissions) property based on their login info. BTW, I stopped worrying about passwords long ago. Using their Windows login ID, my db looks to see if they're in the users table. If not, they don't get in. I never had to worry about anyone asking for a password reset because they forgot it, nor did I have to worry about it being discovered. Research fosUserName.

Or is it a little more complex, such as allowing a form to be opened by all, but editable by only some? Then your code opens the form in a manner that restricts editing, such as using a dynaset recordset or locking or disabling all the data input controls? You'd need to loop through them and based on the profile, lock/disable or don't. Obviously you don't disable a close button, so the loop can either 'disable by control type', or by looking at the control tag property (which you've assigned a value that makes the control part of a group that can be disabled and your code checks for that value).

Or is it much more complex, such as controls on a form to be editable, but not others? Looping through form controls is the only way, AFAIK.

What's not really a good idea is to have a table with a field for every report, form, or function that you use checkboxes for to restrict access. Add a form, and you have to add a table field. This isn't a good design practice. It would be more proper to have a table where the settings are listed in rows (records). Although this means that if there's 5 forms, each user has up to 5 records whose settings allow or don't allow then to open a given form/report. Might be lots more records, but it's the right approach if it fits your plan, since to add a new form or report only requires the addition of a new record. Note that I said "up to 5 records". You can also only add a person's id and a given form name (and maybe what permission they have) if you want to provide access to it. If they're not listed for that form, they don't get to open it. This would be a more correct way of listing accessibility.

As I said, lots of things to consider, and several ways to accomplish pretty much any of them. Of course, your db must be split, and each person uses their own fe (front end) copy.
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
173
I just need to control who can access the tables and who can view them only...Thanks
 

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
173
Maybe Instead of a form, I should create this on the dashboard manager
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,906
Office Version
  1. 365
Platform
  1. Windows
Admittedly I only scanned the code there. A lot of work for just a yes/no decision on how to open a form (read only or edit). Likely there is already a user table given that there is password code. All it needs is a "Mode" or "CanEdit" field, which could be any of several possible types, and a field for their Windows Login ID. Assuming CanEdit is yes/no and it is OK if a user who can edit can also add, then

Code:
Dim bolMode as Boolean
Dim strUser as String
strUser = fosUserName
bolMode = DLookup("CanEdit","tblUsers",strUser)
If bolMode Then 
 DoCmd.OpenForm "YourFormName", , , , acFormEdit
Else
DoCmd.OpenForm "YourFormName", , , , acFormReadOnly
End If

Rather than write that for every form that might be opened, I'd create a public function that accepts the form name as a parameter, put the code there and only call the function in each form's open event. As already stated, you'd need the fosUserName function code. But that's just me.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,620
Messages
5,523,944
Members
409,543
Latest member
LaMaqu1na

This Week's Hot Topics

Top