Username to full name in dropdown

[fitshase]

I have created a database with a front end and back end. Each user has their name listed in a drop down box on the menu (the users' names are stored in tblUsers).

Now, everytime a user opens the database, they are presented with the menu (frmMenu) and they select their name from the drop down box (an unbound combo box named "User"). They then click a button which runs a query on a master table and presents them with only their workload in a form (frmWorkload).

I have stored the Windows username and the user's full name in tblUsers (fields "SurveyorUsername" and "SurveyorName". I would like to have the menu automatically look up the SurveyorUsername using the VBA:-

Environ("Username")

and then compare that to the values in tblUser and display the SurveyorName in the "User" box:-

For example, John Brown opens the database. The menu pulls his Windows username as "johnb" then looks for that value in tblUsers and select the user's full name "John Brown" and put that in the unbound combo box "User" as a default value.

Can you help?

 

[pbaldy]

Have you tried DLookup()?

http://www.mvps.org/access/general/gen0018.htm

The load event of the form would probably be the best place for your code.

 

[fitshase]

pbaldy,

Thanks - that's just what I was looking for. I'm still trying to get to grips with VBA and most of it is just looking for examples online and tailoring to fit but I couldn't figure out what function this would be.

I've got it working fine now but would like to streamline it a bit further if you can help.

Can you have a function within the dlookup function? For example, I have an unbound text box ("SurveyorUsername") which shows the Windows username using Environ("Username"). The dlookup uses the value of that text to find the full name in tblUsers.

Is there anyway to have the Environ("Username") function embedded within dlookup. So far I have:-

Private Sub Form_Load()
Username = Environ("Username")
User = DLookup("[SurveyorName]", "tblUsers", "[SurveyorUsername] = form![SurveyorUsername]")
End Sub

The "SurveyorUsername" text box shows the Windows username and is hidden on the form.

 

[pbaldy]

You should be able to use it, but you need to concatenate it into the formula the way that link did with form references. Either way, your form reference looks off:

http://access.mvps.org/access/forms/frm0031.htm

 

[fitshase]

Paul,

Thanks for the reply, that's done the trick.

Cheers

 

[pbaldy]

No problem, glad we got it working.