• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Access Default Form Value How to Lookup based on Function Result

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!
1 Solution
Rey Obrero (Capricorn1)Commented:
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
billg7Author Commented:
Wonderful! Thank you!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now