Help with security for database

[RyanB]

Hi,

I want to be able to program some sort of security for a database I have created.

There is allready code in the database that detects the domain username, so I was thinking of creating a table that holds info on each user and what forms they can access and certain options that they can do within the form.

Would I then use Dlookup to reference to this table from within the form?

I am new to using Dlookup, etc. so bit un-sure on what path to take.

Thanks in advance

Ryan

 

[WayneRyan]

Ryan,

You have a form with a UserName & Password.

On the OK CommandButton, put something like this:

If DCount("[Password]", _
          "YourTable", _
          "UserName = '" & Me.UserName & "' And " & _
          "Password = '" & Me.Password & "'") = 0 Then
   MsgBox("Invalid Username/Password.  Try Again")
   Exit Sub
Else
   Me.SomeFormControl = "User OK"
   Exit Sub
End If

Wayne

 

[RyanB]

HI Wayne,

Thanks for the reply.

But i'm not looking at using passwords as this database will be housed within our citrix enviroment so the user will already have logged in.

I was thinking a field for each form with a yes/no to define access and maybe a field for each option that i want to restict.

Do you think this will work?

 

[WayneRyan]

Ryan,

Well in that case, you need to use a DLookUp.

Somevariable = Nz(DLookUp("[SomeField]", "YourTable", "UserName = ..."), "")

But now, you need the Nz function, because the DLookUp might return a
Null string when it finds no match.

You might need multiple DLookUps if you need to retrieve a few things, OR
better yet, bind a hidden form to your Security table. Then at any time,
from any form, status is available by looking at:

Forms![YourSecurityForm]![OneOfYourFields]

need more specifics,
Wayne

 

[RyanB]

Sounds like just what I need. Thanks heaps!

I'll start making up the table up now and see where I hit a brick wall.

I'm pretty new to DLookup, so do you know of a document, website that can give me a crash course in the Dlookup function.

Cheers,

Ryan

 

[WayneRyan]

Ryan,

Numeric:

SomeVariable = Nz(DLookUp("[SomeField]", _
                          "SomeTable",
                          "[SomeField] = " & SomeNumber), 0)

String:

SomeVariable = Nz(DLookUp("[SomeField]", _
                          "SomeTable",
                          "[SomeField] = '" & SomeNumber & "'"), "")



Date:

SomeVariable = Nz(DLookUp("[SomeField]", _
                          "SomeTable",
                          "[SomeField] = #" & SomeNumber& "#"), #1/1/2005#)

Since DLookUp can return a Null if nothing matches the "[SomeField] =", the
Nz function is used. It will supply a Default Value, since VBA strings can't
contain a Null value (empty yes --> ""), but not Null.

However, if you have a UserName and just want to look up some info in a
table, I'd suggest a hidden form bound to the table. Once you have their
UserName, just do:

DoCmd.OpenForm "YourSecurityForm",,,"[UserName] = '" & Me.UserName & "'"

Then you can get any of the info by referencing:

Forms![YourSecurityForm]![SomeField]

Post some questions as you progress through this.

Wayne