api username call to restrict record editing

[coasterman]

I am trying to restrict access to different records using the api username call.

The function is the fosusername() code which I guess everyone will be familiar with.

At the moment all controls on a record are locked unless:

fosusername() = Me.TopicOwnerUserID

This is all working fine but I need to have an exception to the above rule to allow other employees with an equal or higher authority (to the topic owner) to be able to edit the record.

I have added an unbound control with its controlsource set to the [LevelAuthNumber] from the subform which contains details about the Topic owner including their UserId (which is the same as the value returned by fosusername function)and the [LevelAuthNumber]. The LevelAuthNumber is simply a scale from 1 to 20 I have included in the tblAuthorities so I have a unique numeric value to work with.

So really what I think I need is an addition to the fosusername() function which can dive into the table where the authority levels are held and compare the [LevelAuthNumber] in that table of the user viewing the record by their fosusername() and the [LevelAuthNumber] recorded in the unbound control on the main form of the record its self:

Would dlookup be available to me here? Hopefully there is some other way as I can never seem to get my dlookups working properly

I did think that as a work around I could

Dim UName As String
LevelAuthNumber as Integer

UName = fosusername()

Select case Uname
   Case SJYG,HYTH,GHYT,HYJF
     LevelAuthNumber= 1
   Case HTRD,NBGH,KIUH,SFRT
      LevelAuthNumber = 2
   Case WRTD,GTYH,HYTD,HYTO
     LevelAuthNumber =3

etc and then compare LevelAuthNumber with Me.LevelAuthNumber.

I don't even know whether that would work but even if it did it seems a ridiculous way to go about it with employees changing levels, leaving etc.

I feel like this is the final major hurdle in my project so I'm hopin you guys can offer some advice to set me off on the final leg.

Thanks for looking

 

[gemma-the-husky]

thsats the way to do it, except it ought to be

Select case Uname
Case "SJYG","HYTH","GHYT","HYJF"
etc

because you need to keep changing code to accommodate new users, the general mechanism is to do this at group level, rather than user level - although you can do it at user level (original post said "group") - but control the access via tables, rather than hard-code the options

set up a group table, then a group-user table, so a user can belong to a number of groups

then your code needs to test the membership of applicable groups before deciding whether to run or not.

it still needs a fair bit of coding, as you need to add code in the open even of every form to test the membership.

 

[coasterman]

Thanks for that quick reply

I have pretty much have that, albeit I haven't gone as far as creating a separate group table. I know I should but I was trying to keep the code to as simple as possible and have simply included the authority levels in tblEmployees for now

Do I take it my thought about dlookup is not too far removed from reality?

Something like

Dim LevelAuthNumber as integer
Dim Uname as String

UName = fosusername()

LevelAuthNumber = Dlookup("UserID","tblEmployees","UserID=", & Uname)

As Uname is the value of a PublicFunction I didn't know whether this was valid. Also as LevelAuthNumber is simply a variable value I would the pass to compare with the Me.LevelAuthNumber value I didn't know whether that was permitted either?

I'll certainly be having a play around with that now but if I'm barking up the wrong tree please chime in at any time

Just on the Dkookup I should mention the UserID is a 4 character string and is not the primary key, but obviously is unique in the table. As the value being matched in the criteria is not numeric is my syntax correct? I have seen examples with a trailing & "") but wasn't really clear on what that did?

 

[coasterman]

Brilliant! Thank you both. And Mr Larson you were of course spot, I was retrieving the wrong field from the table in my example.

My first working Dlookup and with a function variable to boot! May not seem much to you guys but this is revolutionary to me and I can think of many other uses for it in the current project.