Hiding used values, showing only second field

[michele]

I don't want the value list to show the used values (which are key fields= numbers and have no use to the user).

Is there a way of doing this?

Thankyou for reading and eventually answering my question.

[danjacoby]

Not necessarily a solution -- just making sure I understand the question:

Let's say you have a field with a pop-up list from a value list that contains the values 1,2,3,4,5. And let's say previous records used 1 and 3. Do you want the next record's list to include only 2, 4 and 5 (i.e. the unused values)?

If so, I'm not sure it can be done (I believe it requres circular definitions). But there may be a workaround -- validate that the value of the field is unique. You'll still see all the values in the pop-up list, but you won't be able to use a previously used value.

[The Bridge]

I did a preliminary test with this calculation, it may be what you want:

ValueList_Index= (unstored, number), PatternCount(Replace(ValueListItems(Status(CurrentFileName), "vl_Names"), Position(ValueListItems(Status(CurrentFileName), "vl_Names"), Name, 0, 1) + Length(Name), 99999, "") & "

[michele]

No, I wasn't clear.

I'm defining a value list:

Use values: 'keyfield' (=numbers- 1,2,3,...etc...)

Also show values of field: 'name'

Gives as a result:

1 Thomas

2 Irene

3 Lucy

...

The database uses the keyfield to store (=the number) in the record, the user just needs to see the names without the numbers.

So if the user chooses Irene, the database stores '2'.

I hope this makes sense to you

[The Bridge]

Addendum: After further tests with the above calculation I've determined that it may be useful only for Custom-defined valuelists, and separating lines, i.e. "-", will mess up the results.

Just to forewarn you!

[Thom]

The code serves as the key field for a relationship match, otherwise you wouldn't need it. By eliminating the code, the name (or other descriptor) becomes the relationship match, eliminating the need for the code. It's important then that your names are unique and follow all of the restrictions of a primary key.

That said, you can make a relationship from the MAIN file to the NAME file based on matching names. Make the code field in MAIN lookup code in NAME using the Names relationship. Your value list takes its values only from the name field in NAMES. The rest of the related fields in MAIN should use a relationship matching code in MAIN to code in NAMES.

[michele]

[Thankyou, Bridge, for your solution and warning.

Thorn, a key field will always be a number to me. Storing names is a waste of space and against (my) database rules. But then again thankyou for offering an answer.

In MS Access you can define which part of the value list you want to see and which part you want to store (without seeing).

I was just looking for a similar solution.