Control Data Display By User Group

Greetings, I am seeking a working example of how to control the viewing of data by different user groups.  For example, I have 4 organizations who each place data into a shared Access database.  I do not want want organization 1 to be able to view organization 2/3/4 data.  I would like to have a sleek, friendly interface form for my organizations to each enter 10 items (things like bio, status, description, or just items 1-10) but not be able to view each other's records.  I need a working example so I can emulate best practices when I build my own.

A solution using VB.NET & SQL would be preferred, but MS Access is the next best thing.
GenericCogAsked:
Who is Participating?
 
Jeffrey CoachmanMIS LiasonCommented:
Besides, there really cannot be a hard defined "Best Practice", until you tell us your very specific needs:
Security?
Preferred filtering method?
Skill level?
Current Design?
...etc

I can think of at least three different ways to approach this, ...all following a different set of "Best Practices"

Here is a very basic example

;-)

JeffCoachman
Database6.accdb
0
 
Jeffrey CoachmanMIS LiasonCommented:
How good are you at VBA...?
Do you already have a login system in place that identifies the Users Group?

In a nutshell, the use logs on, and the "Group" is stored in a global variable
Then create a function to retrieve this value.
Then all forms and Reports have a recordsource that filters for only that group.
SELECT *
FROM YourTable
WHERE GroupID=GetGroupID()
0
 
Jeffrey CoachmanMIS LiasonCommented:
<I need a working example so I can emulate best practices when I build my own.>
Why not post what you have so far?
Instead of forcing us to create a working example from scratch?
...and "guessing" about all of the aspects of your current database...?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jeffrey CoachmanMIS LiasonCommented:
...and this is a mod to insert the Group into all new records
Set the default value property of the Group control to the function:  GetGroup()

I am sure you can examine this db completely and see what was done here.

;-)

JeffCoachman
Database6.accdb
0
 
GenericCogAuthor Commented:
Jeff,  Thanks for the quick response!!  I'm looking at your example files right now.  I'm familiar with VBA to the extent that I can write beginner level code.  I'm comfortable with VB.NET.

I do not have anything for you to work from.  I'm trying to meet the need of a future requirement.  I was hoping I could see what the community already had in their bag before I try and reinvent the wheel.  :-)
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK, keep me posted
0
 
Alpesh PatelAssistant ConsultantCommented:
Enter that UserGRoup in Table and Filter data based in Logged in user's usergroup
0
 
GenericCogAuthor Commented:
Jeff, I was able to completely understand this example and am in the process of building my own based on yours.  Thanks!!!
0
 
Jeffrey CoachmanMIS LiasonCommented:
;-)

BTW if you are wondering why I use the function instead of the variable (to filter the form), ...it is because you can use the function anywhere (like in a query)
(You can use a function for query criteria directly, but not a code variable)
SELECT *
FROM YourTable
WHERE Group=GetGroup()

Also at some point the function can be modified to include validation, and.or other arguments.

;-)

Jeff
0
 
GenericCogAuthor Commented:
AH!!!!!  I was not sure about that... somewhere in the back of my mind I figured there would be a reason.  Thank you so much for your help on this!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.