Access form - how to make text field have a contro

Question

Background

I have two tables:

• Projects
  • EmployeeID
• Employee
  • EmployeeID
  • Name

I have a query I am basing a form on containing, among other items:

SELECT e.Name FROM Projects p JOIN Employee e ON e.EmployeeID=p.EmployeeID

When I make a form in Access based on this query, I can very easily display e.Name on my form because it is joined from the query.

My real example is of course considerably more complicated than this simple example. The above works fine for read-only queries and scales well. However I would like to use a Splitform view and this becomes very slow with many joins for even small numbers of recordsets. Considering a large percentage of my joins are simple like the above, I am hoping for a way to remove as many as possible.

On the form, e.Name will be read only and not be update-able.

Similar question

In this question I am able to successfully change a combo-box into a lookup. The accepted answer allows a combo box to control Projects.EmployeeID by displaying Employee.Name field in the combo box.

Possible work-arounds

I know one way I could do this is use a combo-box but disable it. This would look a bit weird since it'd have the drop down selector but not be selectable.

Alternatively, I could make it a completely unbound field and write VBA code to update the form each time the recordset changes by running quick queries, getting the text value I am searching for, and updating a label accordingly.

Neither of these are overly appealing, however.

Question

• How can I display a single text field on an Access split-form which is the result of a very simple query lookup, based on an ID from the main table field?

Answer 1

You can use Dlookup to return this reference very simply

Construct a Dlookup formula like:

=DLookUp("Name", "Employee", "EmployeeID =" & "[EmployeeID]"

and use this as the ControlSource for the textbox.

Some notes:

• The & is important as it binds the formula to the single record displayed on the form
• [EmployeeID] refers to the current record displayed on the form. This assumes that "EmployeeID" is included in the query for the form, whether bound to the Projects table or included in the query