Linking 2 combos with Col(0) hidden

[Al Camp]

Sorry if this post is a dupe... sent an hour ago and it still hasn't shown
up...

In a continuous subform, I have 2 combo boxes on each record.
cboCID allows the user to select a CName (text), but really
stores the CID.

cboCID field bound to CID - Columns = 2 - BoundCol = 1
Col(0) Col(1)
Width 0" Width .75'
CID CName
Works fine... shows Nickname but stores CID
----------------------------------------
My next combo, cboEventID, allows the user to select one of many
events associated with the CID

cboEventID bound to EventID field - Columns = 3 - BoundCol = 1
Col(0) Col(1) Col(2)
Width .5" Width 2" Width 0"
EventID EventName CID
(criteria =cboCID)
This also works fine. Displays EventID and EventName, and Stores
EventID in cboEventID. I Refresh on cboCID AfterUpdate, and cboEvents
shows only Events associated with the CID for that record.
-----------------------------------------
Now comes the problem...
I try to hide the EventID column and allow the user to select
via EventName...
Col(0) Col(1) Col(2)
Width 0" Width 2" Width 0"
EventID EventName CID
(criteria =cboCID)

What happens is that whenever I'm on a particular record and enter a CID
and Refresh/Requery to re-synch cboEvent, all the cboEvents blank out except
for those that are associated with the CID I'm on!
How can it do that? Those EventIDs are bound and stored in the table.

I think it has some thing to do with the Bound value displayed, vs the
stored value, but can't seem to figure it out. I need to store the EventID,
but I don't want the user to see it.
Can't I synch two combos set up like this?

Thanks in advance,
Al Camp

 

[Graham Mandeno]

Hi Al

Sorry if this post is a dupe... sent an hour ago and it still hasn't shown
up...

Well, it showed up here

Here is my reply to your first post:

The different instances of the combo box on the continuous form are not
independent. They all share the same properties and requerying one
requeries them all.

Presumably cboEventID includes a where clause in its rowsource to filter
according to the current value of cboCID. For this, read "the current value
in the current record". This means that the EventID values in the records
with a different CID will (temporarily) not be in the combo list. Therefore
there is no looked-up EventName in the list to display.

A workaround for this is to add the Events table to your form's RecordSource
query (with an outer join if it EventID is not a required field). Then
place a bound textbox over the top of the text part of the combo box and
bind it to EventName. Make the textbox locked and disabled.

Now, the textbox displaying the EventName will always be visible *except*
when the combo box gets the focus.

Also, instead of using the AfterUpdate event of cboCID to requery the
rowsource
for cboEventID, you should use the GotFocus event of cboEventID.

This is because it's only when the latter combo has the focus that it is
actually visible. However, you might switch between instances of cboEventID
in different records *without* updating cboCID first.

 

[Al Camp]

Graham,
Sorry for the multiple posts. Having trouble between Outlook
Express and a newly installed Agent Newsreader.
What a mess... still can't see my post thru OE...
I read you previous post, and replied...

Thanks,
Al Camp