Forum Home
Press F1
 
Thread ID: 63117 2005-10-30 05:28:00 MS Access function - help please..... Diogenes (4901) Press F1
Post ID Timestamp Content User
400358 2005-10-30 05:28:00 Hi All

I have an access database, which contains a table (tbl_Staff) with details of users (login ID's, full name, and access levels).

I have a funtion, fOSusername(), that gets the users loginID when they access the DB. What I'm hoping to do, is to insert a function that I can call, that gets the 'Access Level' from tbl_Staff.

I hope this makes some form of sense. I am a true novice, so much that I'm not sure I've explained it well enough to get the right answer....!!!

Any help welcomed. :badpc: 		Diogenes (4901)
400359 2005-10-30 06:10:00 Hi, I think a Dlookup is what your after but others can correct me. Access level is the name of the field?


Dim varAccessLevel As Variant
varAccessLevel = DLookup("[AccessLevel]", "tblStaff", "[loginID] = " & fOSusername()) 		Parry (5696)
400360 2005-10-30 06:56:00 Thanks Parry :thumbs:

That seems to be exactly what I'm after. I tried the following;

Function Acc_Lev() As Variant
Dim varAccessLevel As Variant
varAccessLevel = DLookup("[Access_Level]", "tbl_Staff", "[User_ID] = " & fOSusername())
Acc_Lev = varAccessLevel
End Function

I keep getting the following error message;

"Syntax error (missing operator) in query expression '[User_ID] = Pete12'.

(User ID changed to protect the innocent.....!!)

Seem to be closer to figuring this out though..... 		Diogenes (4901)
400361 2005-10-30 07:19:00 Hi again, for string expressions (ie text) Access requires a single quote around the value so fOSusername() needs to be enclosed in quotes.

Try...

Dim varAccessLevel As Variant
varAccessLevel = DLookup("[AccessLevel]", "tblStaff", "[loginID] = '" & fOSusername()) & "'" 		Parry (5696)
400362 2005-10-30 07:27:00 :D

Excellent - works like a charm....!!!!

Thanks heaps Parry 		Diogenes (4901)
400363 2005-10-30 07:55:00 My pleasure. :) Parry (5696)
1