Lookup field in a table will not allow me to use auto expand 1

HebieBug · Mar 22, 2018

Hi all,
Have a table called students which I want to use as the lookup to a table called [student Attendance].
In the table [student Attendance] I have a field called student which I use the lookup row source

SELECT Students.ID, [Students].[Last Name] & ' , ' & [Students].[First Name] AS LastFirstName
FROM Students
ORDER BY [Students].[Last Name] & ' , ' & [Students].[First Name];

If I did not concatenate the two fields last name and first name and use them separately then I can view the table and make changes by typing the students last name in the field and then quickly selecting the name. But if I concatenate then I have to use the mouse to select the value from the combo list and if I do enter any text it will not help to get the correct selection.
Does anyone know a better way of doing this so I can use the mouse as infrequently as possible?

Apologies for incorrect naming convention

MajP · Mar 22, 2018

You have some wrong terminology. That is not intellisense, that is the "auto expand" property of a combobox.

You can use the AutoExpand property to specify whether Microsoft Access automatically fills the text box portion of a combo box with a value from the combo box list that matches the characters you enter as you type in the combo box. This lets you quickly enter an existing value in a combo box without displaying the list box portion of the combo box

I strongly recommend that you do not use lookup fields directly in a table. This is OK to do in a query or on a form, but do not put it directly in a table. You can google numerous articles on the dangers with table lookup fields.

http://access.mvps.org/access/lookupfields.htm.

With that said,in a query if I set the row source to

Code:

SELECT tblStudents.StudentID, [LastName] & ", " & [FirstName] AS FullName FROM tblStudents ORDER BY tblStudents.LastName, tblStudents.FirstName;

Bound Column:1
Column Count:2
Column Widths:0";1"

The auto expand works fine without issue. Not sure why it is not working for you.

Intellisense deals with the completion of code.

https://code.visualstudio.com/docs/editor/intellisense

HebieBug · Mar 23, 2018

Thanks for that, have amended the subject and message to show as auto expand.
As the table properties do not have the auto expand property, did some tests at the form level.
For my existing form which I removed all fields and then added the studentid field, changed to a combo box and then added the data source ect....
Still would not allow auto expand.
Created a new form from scratch, just adding the studentid, result WORKED
Moved this form over so that it was a subform on existing form (mimicking the problems forms position) WORKED
You beauty! Thanks very much for taking the time to help me out. Think we can close the case.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Lookup field in a table will not allow me to use auto expand 1

HebieBug

Programmer

MajP

Technical User

HebieBug

Programmer

Similar threads

Part and Inventory Search

Sponsor