Form Lookup From Query - Display different from data in table

[Claire]

Hi Everyone,

I have a form which has a lookup to a query. The query is a list of
department names and includes DepartmentID, Department Name and
Inactive/Active. I want only those departments which are Active to show in
the lookup but to still show the inactive departments where they have
previously been used. I've managed to do this but my problem is with the
lookup itself.

I want the DepartmentID to be stored in the table, but I want Department
Name to be displayed on the form. So far I've made the lookup with the two
columns. If I hide the departmentID column and put Department Name as the
first column I get an error "The value you entered isn't valid for this
field". If I make departmentID the first column on the look up it just shows
the number which is meaningless to everyone. Is there a way of doing what I
want?

I'm using Microsoft Access 2002.
Thank you for your help.
Claire

 

[Claire]

Hi Peter,

Thanks for your response. I tried that but it then doesn't show up the
departments which are inactive. If I choose Limit to List = No (which would
then show inactive departments) I get the message "The first visible column
which is determined by the columnwidths property isn't equal to the bound
column." Ie: it doesn't like the first column set to 0.
The departmentId field is a number and in the field it is going to is also a
number. I did it that way because I thought having unique identifiers was
best practice but now I'm wondering whether I should just go back to having
the department name as the primary key.
Any other suggestions would be appreciated.
Claire