Multiple User Groups Frontend writing to same Master BackEnd Database (1 Viewer)

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
Hi All,

First of all thank you for helping out. Here's the situation that I'm trying to get some help on.

I have 4 distinct groups of users (Group A, B, C and D) and one master database. All 4 groups will have ability to edit different (sometimes same) data fields.

Ideally I have 4 different forms for each group. How do I make this work? I will be splitting the database into backend and frontend when ready to deploy. And will be distributing a FrontEnd copy to each of the Groups (ie. Group A gets FrontEnd A, Group B gets FrontEnd B, etc.).

Any help is greatly appreciated.

Thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:47
Joined
Oct 29, 2018
Messages
11,892
Hi. Welcome to AWF! When users open your FE, how can you determine in which group they belong? You may need some sort of a user login system.
 

Cronk

Registered User.
Local time
Today, 20:47
Joined
Jul 4, 2013
Messages
2,416
I'd strongly counsel against having multiple varieties of front end. Maintenance coordination across the different versions becomes too much hardwork and fraught with risk that a change is missed in one version or the other.


You need to determine on how tightly you need to tie down access to different functionality. You can use a login system with or without passwords. For most of my situations, I find using the network user name sufficient to distinguish users ie
Code:
environ("username")
gives the network name of the person.


Then have a table with the list of user names and the group or groups which user is in. Use code in the form's load event to hide/lock fields for all groups except that the particular user is in.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 05:47
Joined
Feb 28, 2001
Messages
17,720
Generally, the way this is done is ONE form that has ALL POSSIBLE controls in it, but in the form's Form_Load event routine, you determine user group membership (among your four groups, in your case) and adjust the controls accordingly. If you have more than one different form because of interactions with different tables, each form does the same thing.

This next is pseudo-code for demonstration purposes only. It will not declare variables but it will be explicit with names.

Code:
Private Sub Form_Load()

UsrGroup = DLookup( "[UserGroup]", "UserTable", "[UserIDNumber]=" UserID )

SELECT CASE UsrGroup
    CASE GrpA
        [ControlA].Enabled = False
        [ControlD].Enabled = False
    CASE GrpB
        [ControlB].Enabled = False
        [ControlE].Enabled = False
etc.
    END SELECT
End Sub
Note, however, that if there are certain forms you don't want a particular group to use AT ALL, then in the Form_Open routine you would do something like

Code:
Private Sub Form_Open(Cancel As Integer)

UsrGroup = DLookup( "[UserGroup]", "UserTable", "[UserIDNumber]=" UserID )

IF UsrGroup = GrpA Then
    MsgBox "Your group is not allowed to use this form", vbOKOnly, "Disallowed"
    Cancel = -1
End If

End Sub
 

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
All,

Thanks for chiming in with your replies. To give a bit of a background to the situation:
- I'm not a developer, just a bit of a pick up and go and learn type with access and stuff
- my group needed a database, and I developed one and distributed it to the team. The first version I did was - one long form that had all the fields and reports for everyone. I split the database into a backend and a frontend. I distributed the front end to the 15 or so users individually - via email. The frontend connected/talked to the backend that is live on a network drive that only us 15 users have access to. This was sufficent enough.

Now to upgrade the system a bit - people were tired of inputting lot of information or looking at that long form with lots of field. Especially for users that didn't care for a bunch of those fields and never touched them. Hence decided to break it up into 4 different user groups and 4 different forms.

Hope this helps clarify things a bit. I do not have access to their network names or login. I do not know how to control that. I was hoping I'd make one MASTER database that has the "ALL data" and the lists etc, along with 4 distinct forms. And was wondering if there was a way, when I split the database, I can split so that I have 1 BE and the 4 forms as 4 individual FE. Later, I take those Front Ends and email it to the user groups (ie.: the manager gets FrontEnd A with only options for a few specific reports they care about, the 3-4 technicians get FrontEnd B that allows them to view a few data fields and edit the 2 or 3 data fields that they need to; the engineers get FrontEnd C that allows them to view and edit the most number of fields; etc.)

Again - thanks a bunch for all the help, it is truly appreciated.
 

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
Hi. Welcome to AWF! When users open your FE, how can you determine in which group they belong? You may need some sort of a user login system.
Hi - the way the system works currently is there is one backend on the network drive that only the 15 users have access to. And I've emailed out the frontend copy to all those 15 users.

With the new system - I hope to do the same. Email out FrontEnd A to only the 2 users, FrontEnd B to only the 3 users etc.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:47
Joined
Oct 29, 2018
Messages
11,892
Hi - the way the system works currently is there is one backend on the network drive that only the 15 users have access to. And I've emailed out the frontend copy to all those 15 users.

With the new system - I hope to do the same. Email out FrontEnd A to only the 2 users, FrontEnd B to only the 3 users etc.
Hi. That's not a bad plan, but might eventually become a nightmare to maintain. If you could just email one copy of the same FE to all users and have it automatically determine what the user can do, I think that would be better. But to have that one copy of the same FE "know" what each user can do, you will have to have a way to tell it how to figure that out. For example, if you're all on the same network, you can use the user's network login username to know who they are. Then, you can add a list of the authorized user and their permissions or departments or categories that the FE can bounce the username against to be able to lock down or allow certain features of your application.
 

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
@theDBguy - can you give me an example of how to code or implement that?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:47
Joined
Oct 29, 2018
Messages
11,892
@theDBguy - can you give me an example of how to code or implement that?
Hi. First, let us know. Do your users have a network username? If not, we'll need to use a login form, if we can't automatically determine who the user is.
 

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
Hi. First, let us know. Do your users have a network username? If not, we'll need to use a login form, if we can't automatically determine who the user is.
yes the users do have network names, i can use their email ID which is also our network user ID
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:47
Joined
Oct 29, 2018
Messages
11,892
yes the users do have network names, i can use their email ID which is also our network user ID
Okay, sure. I use this code to get the user's login username. Typically, I would have a table simply listing the usernames of all authorized users. But in your case, you will need to include which permissions/departments they belong, so you can tell your FE to unlock certain areas depending on their departments. Let me find a link for you to see a demo or sample db for this.
 
Last edited:

Cronk

Registered User.
Local time
Today, 20:47
Joined
Jul 4, 2013
Messages
2,416
@maverick, did you read Doc's post in #4?
 

maverick235

New member
Local time
Today, 06:47
Joined
Dec 27, 2019
Messages
11
Hey Guys,

Sorry I haven't dropped in a while here. I just wanted to drop in and thank everyone for the support and help. Launched the v1 of my database and it has been performing real well - thanks to all of your help.

Truly appreciated.

Thanks :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:47
Joined
Oct 29, 2018
Messages
11,892
Hey Guys,

Sorry I haven't dropped in a while here. I just wanted to drop in and thank everyone for the support and help. Launched the v1 of my database and it has been performing real well - thanks to all of your help.

Truly appreciated.

Thanks :)
Hi. Congratulations! Continued success with your project! Cheers! 🥂
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 05:47
Joined
Feb 28, 2001
Messages
17,720
Glad to have offered the help. That's why we are here.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:47
Joined
Feb 19, 2002
Messages
29,479
The reason for using a common form instead of four separate forms is that forms include validation code and since some of the columns will appear on all forms, you do not want to support four separate versions of the validation code.

An option might be to use a tab control on the form. Put all the common fields at the top of the form and then for the bottom half of the form, use a tab control with four sets of different data. Each user will click on his tab where his unique data is located. This should minimize confusion and yet give you a better framework to help you avoid creating coding errors when you duplicate code.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom