Dropdown Feeds Multiple Fields?

[(PeteCresswell)]

Access 2003.

Got a "Dealer" dropdown that is linked to DealerID.

ID Name
1 Goldman Sachs
2 Merrill Lynch
4 BOA

...and so-forth.

So far so good... user chooses "BOA", ID gets set to 3.


But now let's add a couple more entities: ClearingHouse and
ClearingCode. A given dealer can be associated with many
clearing codes and many clearing houses.

OK, now we create:

- tlkpClearingCode: ID/Name
ID Name
0 [none]
1 3497
2 034
3 7929
4 94589
5 5DDQAQ
6 97618
7 7060
8 97213
9 MLI
10 90217
11 7946
12 GFMAY
13 93827
14 307
15 93205

- tlkpClearingHouse
ID Name
1 Crest
2 EuroClear
3 Cdel

- tblDealerClearingHouseClearingCode
DealerID ClearingHouseID ClearingCodeID

The dropdown is still linked to DealerID, but the query has been
expanded to link tlkpDealer to tblDealerClearingHouseClearingCode to
tlkpClearingCode and tlkpClearingHouse so that when the user drops
the list, they may see, for instance:

BOA Crest 97213
BOA EuroClear GFMAY

Now, after the user picks one of those two, we need to save the proper
DealerID, ClearingCodeID, and ClearingHouseID.

I can see picking apart the dropdown's current ListEntry and updating
accordingly... but that seems like a kludge.

And, besides, if/when the user comes back to that record and drops the
list, we want it pre-positioned at the appropriate combination of
Dealer/ClearingCode/ClearingHouse.

Seems like we want base the combo box on
tblDealerClearingHouseClearingCode using the table's unique identifier
DealerClearingHouseClearingCodeID.

But we would still want to save the three ID's separately in the trade
record because somebody might change tblDealerClearingHouseClearingCode
and we would not want that to change the trading history.

Am I making sense?

Seems like there's going to be an issue when we load the record into a
work table for the user to play with while editing. The work table will
need that DealerClearingHouseClearingCodeID...

Can somebody critique this?

--
Pete Cresswell

[David Hare-Scott]

To get the values of the additional columns in the combo list you need the
Column property of the combo.

You need to be sure that when the use chooses a value for DealerID that you
save the value of ClearingHouseID at the time of choosing. To do this you
would have fields in the table underlying the form that are updated based on
the values in additional columns of the combo when the user selects a
DealerID . The controls displaying ClearingHouse would need additional
lookup as you would want to display the string ClearingHouse not the ID.
You would probably base the form on an updateable query, see also
autolookup. You would be saving ClearingHouseID in the underlying table,
not the string, to eliminate update anomalies where a clearing house changes
its name, for example. Of course if you want to keep all the history that's
another design altogether.

David

[(PeteCresswell)]

Per David Hare-Scott:

>To get the values of the additional columns in the combo list you need the
>Column property of the combo.
>
>You need to be sure that when the use chooses a value for DealerID that you
>save the value of ClearingHouseID at the time of choosing. To do this you
>would have fields in the table underlying the form that are updated based on
>the values in additional columns of the combo when the user selects a
>DealerID . The controls displaying ClearingHouse would need additional
>lookup as you would want to display the string ClearingHouse not the ID.
>You would probably base the form on an updateable query, see also
>autolookup. You would be saving ClearingHouseID in the underlying table,
>not the string, to eliminate update anomalies where a clearing house changes
>its name, for example. Of course if you want to keep all the history that's
>another design altogether.

Thanks. That's pretty much what I am doing now - and now I don't feel
like the Lone Ranger with that approach.

The problem with that approach is that, with the combo box linked to
DealerID, when the record is loaded and the user drops the list, the
list is pre-positioned to the first row that has the DealerID - even
though the correct combination of Dealer/ClearingCode/ClearingHouse may
be on a subsequent row with the same Dealer ID.
--
Pete Cresswell

[(PeteCresswell)]

Per (PeteCresswell):

>Thanks. That's pretty much what I am doing now - and now I don't feel
>like the Lone Ranger with that approach.
>
>The problem with that approach is that, with the combo box linked to
>DealerID, when the record is loaded and the user drops the list, the
>list is pre-positioned to the first row that has the DealerID - even
>though the correct combination of Dealer/ClearingCode/ClearingHouse may
>be on a subsequent row with the same Dealer ID.

FWIW, what I did was to use the unique ID of the Dealer-ClearingInfo
link table and adjust my work tables accordingly. Seems tb the
Good-Right-And-Holy path... I was just trolling for something that would
take fewer man hours/brain cells.
--
Pete Cresswell