Drop Down List to store an ID #

[netcode]

Hey all,

I have two tables, a "sponsor" table and an "events" table.

The "sponsor" table holds info specific to the sponsor. Each sponsor can sponsor ONE event. The event that is sponsored is held in a field within the "sponsor" table called "sponsored event", there is also an "event id" field that I want to hold the ID # of the event for a stable relationship between the two tables, as creating a relationship on names isn't totally reliable.

The "events" table holds all info specific to the event. This includes a field called "ID" which is a unique number for each event, and a field called "event name" to hold the english name of the event. As well a field called "sponsor" which holds the name of the sponsor that has chosen to sponsor the event.

I have created a drop down list. I have chosen a value list that holds data from two fields (first being values from "ID" and second being values from "event name", both from "EVENTS" TABLE), I chose to have the list only show values from the second field("event name"), and as you all know value lists store data from the first field ("ID"). Here is the issue.. where do I chose to display data from? If I choose "event id" from the sponsor table then it will show some ID # that is not useful to the end user. If I choose "sponsored event" from the sponsor table (which is the actual name of the event, useful to the end user) then it ends up storing the ID # in this field and then everything goes to hell. So how can I have the list populated with event names, display the selected event name, yet store the event id?

If that makes any sense I would love some help :

Basically I need to store the ID# of the event but I want it to show the event name that was selected. The ID # is useless to an end user.

Edited January 30, 2009 by Guest

[bcooney]

You need a table occurrence for your Event table. That is the TO you select when you define the value list.

Drop down lists show the value stored in them. That is, they will show the ID. You need to use a popup menu.

Another technique is to layer two fields. The first is your drop down list with Allow Entry in Browse On. The second is a related Event Name with Allow Entry Off, and with a solid fill. This will mask the ID.

Be careful also of drop-down lists, as a user can type what they want (you can set field validation to the value list). If your event list is long, another technique is to have a Select button next to the Event field, and pop a new window with a list of Events and pause. Then the user clicks on the desired Event, and the script closes the window and sets the EventID to the selected Event ID.

Edited January 30, 2009 by Guest

[netcode]

great ideas! I like what I read. I am going to try some of those

I will let you know the outcome!

Thanks