Read-only Lookup List? Access 2007

[teachesms]

Have a question that has nagged me for years.

When I create a lookup list to use as a drop-down field whether in a table or a form, how can I make the contents read-only, or better yet, read-only and show me one record only? Example:

In my employee table I have three fields: EmpNo, LastName, FirstName (Parent)
In my absences table I have fields but the linked field is: EmpNo (Child)

In my absences form, I want to click on EmpNo have a dropdown field (built from an invoked query on the field) showing the corresponding EmpNo, LastName, and FirstName fields from the employee table. Reason: So I can tell for example who employee 109 is: 109, Brown, Bill

from the drop down, without having to go lookup who 109 is in the Employee Table. Limiting the results to "1" doesn't help as the list still has a spin button on the left edge...I want that gone.

Just curious, as its eat at me for a very long time.

Thank you,

[HansV]

You could set the Locked property of the combo box to Yes.

Personally, I would create a query based on the absences table and the employees table, linked on EmpNo with a left join. Add the fields from the absences table to the query grid, plus the LastName and FirstName fields from the employee table.
Use this query as record source for the form, and add text boxes bound to the LastName and FirstName fields to the form. Set the Locked property of these text boxes to Yes so that the user can't edit these fields in this form.

[teachesms]

Hans,

Setting the Locked property of the combo box to Yes in the form keeps them from editing the current record (WHO is actually sick, e.g. EmpNo 109), however, it also doesn't allow the user to enter anything into the field when they are on a NEW record, filling in a new entry (as that field has been locked), and that is something they will need to do.

I tried doing what you mentioned (secondly), but I don't think I got the results you likely expected me to get. I'm going to attach a small example so you can see what I'm working with...

Note the EmpNo field in the frmAbsences.

Thanks for peeking at it.

[AccessJunkie]

Hi Nannette,

What Hans is talking about is something we call "Row fixup." At least that's what we call it here internally.

See attached revised form. Notice the form now uses a query as its record source. Select an employee from the combo box and observe how the two text boxes get filled in automatically for you.

Hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com" onclick="window.open(this.href);return false;

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx" onclick="window.open(this.href);return false;
----------

[HansV]

Create the following event procedure for the On Current event of the form:

Code: Select all

Private Sub Form_Current()
  Me.Emp_No.Locked = Not Me.NewRecord
End Sub

The combo box will only be edited in new records, not in existing records.

[teachesms]

Jeff,

Thanks for the clarification and example of what Hans was telling me. That is a pretty cool feature.

Hans,

That piece of code in the current event of the form works perfectly. Thank you for clarifying that for me as well. That has drove me nuts for years. Not anymore.

You guys are GREAT!