Linked Combos with Col(0) Hidden

[Al Camp]

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

 

[Donovan Glover]

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

 

[Douglas J Steele]

Sounds as though you're describing the scenario Rob Mastrostefano talks
about in http://www.fmsinc.com/free/tips.html#tip16foraccess

I'm not that enamoured with his solution, though, but it does work.

In my opinion, it's better to include the text in the form's recordset (by
joining to the appropriate tables), and put a text box bound to the text
value overtop the combobox (making it slightly narrower, so that the arrow
of the combobox is still visible). Put code in the textbox's GotFocus event
to switch focus to the combobox.

(FWIW, it's caused by the fact that while it may look as though you've got a
number of comboboxes on the form, in actual fact Access only has one
recordset for the combobox, regardless of how many times it appears.)

 

[Graham Mandeno]

Hi Al

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.

 

[Graham Mandeno]

Oh, and I should have said...

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.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Al

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.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

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

 

[Al Camp]

Doug,
My apologies to everyone for the multiple posts. I have been
trying out Agent newsgroup reader, and it's got my Outlook all screwed
up. In OE, I still haven't seen my post show up.
So folks have been answering me, and I don't even know it!!

Yes, that's exactly the problem. Glad to know I wasn't going
batty...
I'll add a CName and EventName field to the subform table, let the
user select by those values, and update the CID field and EventID
field from those combos... manually

Your idea about the text control on top of the combo is really
slick, and I'm going to give that a go.

Thanks Doug

 

[Al Camp]

Graham,
Surprised that after all these years, I've never run into this...,
but you and Doug hit it on the button.
I'll add those fields and try the "cover-up" text control... a
slick idea!
Thanks very much
Al Camp

 

[Al Camp]

Thanks Graham,
Will do... good advice.
Al Camp