DLookup Function

[Aubrey]

Hi All,

For my A-Level Computing Project I have created a database that holds details on students in a boarding college and details of where they are at half terms/end of terms etc. For the security of this database i need to lookup the user ID from a table and then give the user specific permissions i.e. students can only see the forms for the holidays and teachers can see the reports of all forms.

I have a module (modGetCurrentUserName) which finds the currently logged on user in the form "forename.surname"

To lookup from the table I have tried this code...

Dim UserName As String
UserName = GetCurrentUserName()
strUser = DLookup("[Role]", "tblStaff", "[UserID] = UserName")

The problem that I am having is that an error is always produced when processing the [criteria] part of the function.

From this function I want to be able to use a select case statement such as

Select Case strUser
Case "SMT"
Call AdminPermissions
Case "Housemaster"
Call HousemasterPermissions
Case "Prefect"
Call PrefectPermissions
Case Else
Call StudentPermissions
End Select

to allocate different buttons and provide the security.

If anyone could help me with the criteria problem it would be very much appreciated...my coursework deadline is soon!!!

Thanks a lot!

James Aubrey

 

[boblarson]

You would need to change this part:

"[UserID] = UserName")

to this

"[UserID] ='" & UserName & "'")

 

[boblarson]

Oh, and by the way -

 

[Aubrey]

Thank you so much...has worked perfectly!

And thanks for the welcome! I don't doubt that I'll be back with more questions before long!

James Aubrey

 

[Aubrey]

As I said...I'll be back...same question basically...

This works fine as long as the user is in the staff table. Any other student reuturns a null value from this line:
strUser = DLookup("[Role]", "tblStaff", "[UserID] = UserName")
The I get an error message saying 'invalid use of null' and it won't call the student permissions at the end of the select case statement. Can anyone tell me what I do about this?

Thanks

Aubrey

 

[boblarson]

it would be:

strUser = [COLOR="red"]Nz([/COLOR]DLookup("[Role]", "tblStaff", "[UserID] ='[COLOR="red"]"[/COLOR] & UserName [COLOR="Red"]& "'"),"[/COLOR]")