MS Access 2013 VBA View Tables

Mac1206

Board Regular
Joined
Jun 3, 2016
Messages
184
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,774
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top