SwitchBoard system via sql server

  • Thread starter Thread starter Rudi Groenewald
  • Start date Start date
R

Rudi Groenewald

Hi all,

I am trying to make my own switchboard which will automatically update from
the administrators side at the server.

A bit of background:

My database application consists out of several "modules", to define this
modules, I use sql server groups. eg: Lets say I've got a module called
"oil consumption" that gives all the tables and view access which is needed
to capture and retrieve oil consumption.

What I want to try and do is, have an list box or something similar, which
lists to me all the modules the user has access to, and when the user double
clicks on a module, that opens the main form for that module. This will
allow a dynamic, "switchboard" which is controlled from the sql server
administrator as it will only show the modules (groups) to open which the
user has access to. To achieve this I have allready conquired how to show a
user which grroups he has got access to by using the following syntax:

SELECT TOP 100 PERCENT sysusers_1.name AS RoleAccess, dbo.sysusers.uid
AS UserID, dbo.sysusers.name AS Login
FROM dbo.sysusers INNER JOIN
dbo.sysmembers ON dbo.sysusers.uid =
dbo.sysmembers.memberuid INNER JOIN
dbo.sysusers sysusers_1 ON dbo.sysmembers.groupuid =
sysusers_1.uid
WHERE (dbo.sysusers.name = @currentuser)
ORDER BY sysusers_1.name

What I need to do next is have a table with all the Main form names and
assign each form a groupuid for eg:

Module: GroupID: Form:
Energy Consumption 16410
Tbl_EnergyConsumption


When I have a table with that information, I can link the groupid with the
users id, and this will show then which users have access to which forms.

My Problem is, lets say I show these values in a list box, how could I let
the appropriate form open when I have the form name?


Help and suggestions please...

Thanks

Rudi Groenewald
 
Hi Rudi

If you have the ListBox listing the forms (display the friendly Name and
have the real form Name as the bound column Your ListBox should look like
this
2 Columns
Column 0 FormName (sample value "frmEnergyConsumption" )
Column1 FormDescription (sample value "Energy Consumption Module"

Column 1 is the bound column and has a width of zero
Column 2 has a Width of say 2


Then it is only a matter of adding a double click event (or Click Event)
that will do the following
DoCmd.OpenForm Me.lstModules.value
Where Me.lstModules is your ListBox and the bound column is a string with
say "frmEnergyConsumption"

Hope this helps
I do many many databases this way now with some very complex security
applied

Regards
Bruce
 
Thanks Bruce,

Instead of a listbox I used a subform instead, and it is working
wonderfull.. Now I can control which "modules" which users has access to
from my sql server without worrying myself that they will have access to
forms they shouldnt...

Thanks again

Rudi
 
Back
Top