Solved DLookup Question

[randallst]

Hi Everyone,

This has been bugging me for far too long now that I'm going blind and stupid!

Im Trying to do automated access permissions to a database based on the users Active Directory Alias, but want to set 3 user levels being Admin, User and Read Only.
I have done a Dlookup for one criteria, but seem to go stupid trying to do two criterias from the same Table

The single criteria code I have done which works is;

If DLookup("[Alias]", "Tbl_Users", "[ID]=" & Environ("Username")) Then Flag = 1

I have done the following but doesnt work;

If DLookup("[Alias]", "Tbl_Users", " "[ID]=" & Environ("Username") And [Permissions] = 'User'") Then Flag = 1

If anyone is able to help, it'd be much appreciated. I believe i'm going blind as I have been looking at it for too long. Going to go for a coffee and come back to it I think haha!

All the best
Stuart

 

[Isaac]

Without commenting on the overall structure of coding "if dlookup", I think your actual Dlookup statement might need to be:

If DLookup("[Alias]", "Tbl_Users", "[ID]='" & Environ("Username") & "' And [Permissions] = 'User'") Then Flag = 1

The CODE tags tried to add all kinds of crap to my quotation marks so I hope I got that right.

 

[theDBguy]

Hi Stuart. Is [Alias] a Yes/No (boolean) field?

 

[randallst]

Without commenting on the overall structure of coding "if dlookup", I think your actual Dlookup statement might need to be:

If DLookup("[Alias]", "Tbl_Users", "[ID]='" & Environ("Username") & "' And [Permissions] = 'User'") Then Flag = 1

The CODE tags tried to add all kinds of crap to my quotation marks so I hope I got that right.

Thank you for your quick response, unfortunately that didn't work. I don't know what i've done wrong but even my original code don't work either now ha-ha.

My original full code is;

Private Sub Form_Load()
On Error Resume Next

Dim Flag As Integer
Flag = 0

'Hides all fields
Me.Label0.Visible = False

'Check if current user is added in User Table
If DLookup("[Alias]", "Tbl_Users", "[ID]=" & Environ("Username")) Then Flag = 1

'Makes items visible for Approved Users
If Flag = 1 Then Me.Label0.Visible = True

End Sub

The table layout is;
ID = Autonumber
Full Name = Short Text
Email Address = Short Text
Alias = Short Text
Permissions = Short Text

Alias is the Windows Active Directory Alias for that user (mine for example is randalls)

All the best
Stuart

 

[randallst]

Hi Stuart. Is [Alias] a Yes/No (boolean) field?

Hi there. No Alias is a Short Text field that I will enter the Windows Active Directory Alias for that user (mine for example is randalls)

 

[Isaac]

Ok, so going back to the "if dlookup" part after all, it seems..
Try something more along the lines of:

If IsNull(Dlookup) then ...

And use the specific dlookup I provided...if those 2 criteria are really what you want - which it doesn't seem like it is, since at first you were comparing ID to the username, but now you say it's an Autonumber. (?) (I see your latest post only has one, so I'm a bit confused of the requirement).

 

[June7]

Makes no sense to compare ID autonumber to string returned by Environ("USERNAME")

Sounds like should be use Alias field instead.

 

[randallst]

I think i've confused myself everyone ha-ha! I think its time to walk away from databases for an evening and start fresh in the morning.

 

[theDBguy]

I think i've confused myself everyone ha-ha! I think its time to walk away from databases for an evening and start fresh in the morning.

Hi. Sounds like a good plan. Have a good night.

 

[randallst]

Hi Everyone,

So I closed the database down last night and looked at it this afternoon with a fresh head and I have solved it all within the hour

So I was building myself an inital test before embedding it into the database to show certain fields based on Permission settings, managed to get it to do what I wanted be doing the following;

Private Sub Form_Load()
On Error Resume Next

Dim Flag As Integer
Flag = 0

'Hides all fields
Me.Label0.Visible = False
Me.Label3.Visible = False
Me.Label4.Visible = False

'Check if current user is added in User Table
If DLookup("[Alias]", "Tbl_Users") = Environ("Username") Then

'Check current users pemissions
If DLookup("[Permissions]", "Tbl_Users") = "User" Then Flag = 1
If DLookup("[Permissions]", "Tbl_Users") = "Admin" Then Flag = 2
If DLookup("[Permissions]", "Tbl_Users") = "Read Only" Then Flag = 3

'Makes items visible for Approved Users
If Flag = 1 Then Me.Label0.Visible = True
If Flag = 2 Then Me.Label3.Visible = True
If Flag = 3 Then Me.Label4.Visible = True
 
Else
MsgBox "You do not have permission to access this database", vbOKOnly, "Access Rights Error"
End If

    If Label0.Visible = False Then
MsgBox "You do not have permission to access this database", vbOKOnly, "Access Rights Error"

End If
End Sub

Looking back at my posts yesterday, I was obviously having a bad day ha-ha! Thank you for your offers of help though

All the best
Stuart

 

[Isaac]

I am surprised this line of code is accomplishing what you intend:

If DLookup("[Permissions]", "Tbl_Users") = "User" Then Flag = 1

Then again, it may just be a knowledge deficiency on my part. Wouldn't be the first time. Glad you got it worked out!

 

[theDBguy]

Hi Everyone,

So I closed the database down last night and looked at it this afternoon with a fresh head and I have solved it all within the hour

So I was building myself an inital test before embedding it into the database to show certain fields based on Permission settings, managed to get it to do what I wanted be doing the following;

Private Sub Form_Load()
On Error Resume Next

Dim Flag As Integer
Flag = 0

'Hides all fields
Me.Label0.Visible = False
Me.Label3.Visible = False
Me.Label4.Visible = False

'Check if current user is added in User Table
If DLookup("[Alias]", "Tbl_Users") = Environ("Username") Then

'Check current users pemissions
If DLookup("[Permissions]", "Tbl_Users") = "User" Then Flag = 1
If DLookup("[Permissions]", "Tbl_Users") = "Admin" Then Flag = 2
If DLookup("[Permissions]", "Tbl_Users") = "Read Only" Then Flag = 3

'Makes items visible for Approved Users
If Flag = 1 Then Me.Label0.Visible = True
If Flag = 2 Then Me.Label3.Visible = True
If Flag = 3 Then Me.Label4.Visible = True

Else
MsgBox "You do not have permission to access this database", vbOKOnly, "Access Rights Error"
End If

    If Label0.Visible = False Then
MsgBox "You do not have permission to access this database", vbOKOnly, "Access Rights Error"

End If
End Sub

Looking back at my posts yesterday, I was obviously having a bad day ha-ha! Thank you for your offers of help though

All the best
Stuart

Hi Stuart. Congratulations! Glad to hear you got it to work. Good luck with your project.

 

[randallst]

Im suprised any of it works with the brainfarts I keep having lately haha!

I may be back soon as Im now struggling (or not sure if its possible) to pull multiple email addresses from a query into outlook haha!

 

[theDBguy]

Im suprised any of it works with the brainfarts I keep having lately haha!

I may be back soon as Im now struggling (or not sure if its possible) to pull multiple email addresses from a query into outlook haha!

You should be able to just concatenate them. If they are stored in multiple records, take a look at this simple function.

Code Snippets

 

[Deleted member 147267]

'Check current users pemissions
If DLookup("[Permissions]", "Tbl_Users") = "User" Then Flag = 1

This should return a random record when there is no criteria - likely the first record in the recordset. That should make everyone a user, or at least always set the flag to one. If 1 represents the user permissions, why not get that from the table rather than creating a value on the fly - a value that might not persist?

This also means you're not looking up the 'current' user permissions - just anyone's.