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

 

[PieterLinden via AccessMonster.com]

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?

The standard way to hide the DepartmentID (meaningless number to users) is to
set the width of the DepartmentID column to zero. (the column widths are
separated by semi-colons, so it would be something like 0;1;1...)

The part you need to make sure of is that the bound column ( the left-most
one) is the DepartmentID.

What is the data type of your DepartmentID FIELD (in the table you based your
form on)? Maybe that's the problem.

 

[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