Re: >> Current Items only

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


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

"Jonathan" <Jonathan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0EC39F99-F2FD-4EA9-9D60-04B68244F1B4@xxxxxxxxxxxxxxxx
"Allen Browne" wrote:

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


"Jonathan" <Jonathan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:35F0A1C2-0734-453F-8398-8C43715EE31F@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • Re: >> Current Items only
    ... cancel the BeforeUpdate event of the control if the user chooses an inactive ... Assuming a RowSource like this: ... Allen Browne - Microsoft MVP. ... To do this I have the combobox recordsource listing all items. ...
    (microsoft.public.access.formscoding)
  • Row source of table referring to same table?
    ... Number - combobox (link to same table KEY field) ... The following query explains what I'm trying to do, ... What should the RowSource ...
    (microsoft.public.access.queries)
  • Re: Combo box data type problem
    ... and what field the combobox is bound to. ... combo 'expects' a numeric/date and will not accept a string. ... clear the rowsource and replace it with a rowsource that consists of numeric ... > query at ...
    (microsoft.public.access.formscoding)
  • Re: Add Zip Code From City
    ... what do I click on to "build a query"? ... "Then use that query as the rowsource for a combobox." ... Air code is code typed in the air, ...
    (microsoft.public.access.gettingstarted)
  • Re: Add Zip Code From City
    ... what do I click on to "build a query"? ... "Then use that query as the rowsource for a combobox." ... "Column 1 refers to the second column in the combo box." ...
    (microsoft.public.access.gettingstarted)
Loading