>> Current Items only

[Guest]

Hi (Access 2003) I want to only allow selection of current list items and to
show all items including non-current. That is an existing record could
include an item that is no longer current. Should a user want to change the
item I only want them to choose from current items.

To do this I have the combobox recordsource listing all items. The _OnEnter
event checks whether the item is current and changes the rowsource to current
items. For items that are no longer current the rowsource is changed to a
union query combining the existing item with current items. The _OnExit event
restores the rowsource to all items.

Testing this simply by entering and then exiting the combobox.

The problem is that although the new rowsource includes the existing item,
the existing item is not displaying as the currently selected item. Instead
an item is displayed that is a random number before or after in the list. I
have checked and the value of the combobox matches the id of the selected
item. On exiting the combobox it correctly displays the selected item.

I have tried including re-assigning the selected item's id. However the
record then becomes dirty and so triggers a bunch of routines that are not
required...

Do you have any ideas or suggestions to explain why changing the rowsource
seems to stop the combobox displaying the item for the current value?

Many thanks,

Jonathan

 

[Allen Browne]

If you modify the RowSource, and the bound column is zero-width, the
inactive items will disappear. So, instead of modifying the RowSource,
cancel the BeforeUpdate event of the control if the user chooses an inactive
item.

Assuming a RowSource like this:
SELECT ItemId, ItemName, Inactive
FROM tblItem ORDER BY ItemName;
the control's BeforeUpdate event procedure would be like this:

Private Sub ItemID_BeforeUpdate(Cancel As Integer)
With Me.ItemID
If .Column(2) = True Then
Cancel = True
MsgBox "Select an active item, or press <Esc> to undo."
End If
End With
End Sub

 

[Guest]

If you modify the RowSource, and the bound column is zero-width, the
inactive items will disappear.

Yes, this is the outcome we want. The existing item however does not
disappear from the list as it is combined using a union statement with
current items. The problem is that the 'textbox' does not display the item.
The user has to dropdown the list to see the item.

So, instead of modifying the RowSource,
cancel the BeforeUpdate event of the control if the user chooses an inactive
item.

Assuming a RowSource like this:
SELECT ItemId, ItemName, Inactive
FROM tblItem ORDER BY ItemName;
the control's BeforeUpdate event procedure would be like this:

Private Sub ItemID_BeforeUpdate(Cancel As Integer)
With Me.ItemID
If .Column(2) = True Then
Cancel = True
MsgBox "Select an active item, or press <Esc> to undo."
End If
End With
End Sub

Thanks Allen, your example provided does work. I was hoping to avoid this
option.

I guess that this is my alternative...
Many thanks,

Jonathan

 

[Allen Browne]

Another option (my messy in my view), is to modify the RecordSource of the
table so that the form is based on a query that includes the lookup table.
You can then use a text box to show the item names. And you can place the
text box over the top of the combo so that it displays the correct text,
even when an item is not in the combo's list. Then use the Enter event of
the text box to SetFocus to the combo.

You can actually avoid all the issue if the combo's bound column is its
visible column (typically, if there is no hidden number column.)

I didn't get the bit about needing a UNION query. Normally the inactive
items are in the same lookup table, but with a field that marks them
inactive. You just sort them to the bottom, so a user hits all the active
ones first as they type.

 

[Guest]

Another option (my messy in my view), is to modify the RecordSource of the
table so that the form is based on a query that includes the lookup table.
You can then use a text box to show the item names. And you can place the
text box over the top of the combo so that it displays the correct text,
even when an item is not in the combo's list. Then use the Enter event of
the text box to SetFocus to the combo.

You can actually avoid all the issue if the combo's bound column is its
visible column (typically, if there is no hidden number column.)

I didn't get the bit about needing a UNION query. Normally the inactive
items are in the same lookup table, but with a field that marks them
inactive. You just sort them to the bottom, so a user hits all the active
ones first as they type.

Thanks Allen, yeah I think your right in saying having a textbox over the
combobox is a bit-over-the-top. <grin>

The bound column is an identity column and of no particular value for user
to know and use.

The benefit of the union query is that the dropdown list only displays the
existing selection and current items. A user does not have to memorise list
items nor have a better spelling intellegence than me to simply scroll
through the list. A complaint is that the list is too long.

In the mean time I've implemented your suggestion in your previous post.

Jonathan