Access Default Form Value How to Lookup based on Function Result

Posted on 2012-09-11
Last Modified: 2012-09-12
Hi Experts,

I have a field on a form ("Hours Worked") which calls a query that contains full names (e.g., "William Graves". The Row Source attribute looks like this:

SELECT [Employees Extended].ID, [Employees Extended].[Employee Name] FROM [Employees Extended];

This pulls up a drop down populated with usernames and bound with the ID which is numeric.

In this query, there is also an attribute called "DomainID" which is the user's windows domain name (e.g., "wgraves").

I have a function called "theUserName()" which gets this name from the environment variable of the machine that the user is logged in to.

My question is, how do I get the dropdown that is populated with the query to default to the correct "Employee Name" based on their username? I'm relatively new to access... I thought perhaps something like this would work:

=DLookUp("[Employee Name]","Employees Extended","[DomainID] = theCurrentUser()")


=DLookUp("[ID]","Employees Extended","[DomainID] = theCurrentUser()") -{since the field is apparently numeric in the Work Hours table, i assume it contains the unique ID of the user profile}-

Any ideas? Am I just way off track?

Thank you!
Question by:billg7
    LVL 119

    Accepted Solution

    include the DomainID field in your RowSource

    SELECT [Employees Extended].ID, [Employees Extended].[Employee Name], DomainID FROM [Employees Extended]

    set the following combo property

    Bound Column 1
    Column Count  3
    Column Widths  0;1;0

    you can then use this codes to set the value of the combo box

    dim j as integer

    with me.comboName
         for j = 0 to .listcount - 1
               if .column(2,j) = environ("userName") then
                    .value= .itemdata(j)
                     exit for
               end if
    end with
    LVL 1

    Author Closing Comment

    Wonderful! Thank you!

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now