alternative combobox

[JohnE]

Hello. There is a situation in which the table that is used for a combobox
is growing in the number of items. Some of the items are out dated and no
longer used. Those items are prefixed with 'zz' so they go to the bottom of
the list. Several items were deleted early on but when the record(s) these
are in no longer showed in the record. Which is why the 'zz' was added so
the items are still available and will show in the record.

What I am looking for is an alternative type of combobox that will show only
the active items (no 'zz' items) but when viewing a record with the item in
it will still show? Has anyone done such and if so, how?

I tried using a textbox, a button, and listbox making it all work similar to
a combobox. The only draw back is the textbox was locked which would not
allow the user to begin typing in the textbox and then begin a search.

Also, I was not able to get a validation if someone typed something in the
textbox it would need to be in the listbox or not allow it. Does anyone know
of an example how this can be done?

Thanks to any who responds.

.... John

 

[KARL DEWEY]

This will not solve your problem but I use a different approach.
Rather than prefix with zz I added a field for Old (displaying it in the
dropdown window) and sort on it then the main field.

 

[Marshall Barton]

Hello. There is a situation in which the table that is used for a combobox
is growing in the number of items. Some of the items are out dated and no
longer used. Those items are prefixed with 'zz' so they go to the bottom of
the list. Several items were deleted early on but when the record(s) these
are in no longer showed in the record. Which is why the 'zz' was added so
the items are still available and will show in the record.

What I am looking for is an alternative type of combobox that will show only
the active items (no 'zz' items) but when viewing a record with the item in
it will still show? Has anyone done such and if so, how?

Here's a neat trick I saw in a recent post.

Create another combo box by using Copy/Paste of your
existing combo box. Modify its row source to be a query
something like:

SELECT itemID, itemname FROM itemstable
WHERE Not itemname Like "zz*"
ORDER BY itemname

Copy/Paste the Top and Left properties from your existing
combo box to the new one (this should put the new combo box
exactly on top of the old one). Use Format - Send To Back
to put the new one behind the old one. Then add a line of
code to the old combo box's Got Focus event procedure:

Me.[new combo namme].SetFocus

Make sure the new combo box's TabOrder is next after the old
combo box.

What happens is the old combo will be in front and display
the all of the items. As soon as it gets the focus (by
tabbing to it, clicking on it or ...), the focus will shift
to the new combo box and it will immediately be displayed in
front. When the new one loses the focus it will once again
be behind the one that displays the zzitems.

 

[Klatuu]

My approach would be similar to Marshall Bartons, but I personnally don't
like having to maintain two ojects when I can use one for both purposes. In
addition, keeping the two objects looking good on the form and tyring to
work with them in design view can be messy.

So, what I would do would use an option group with two buttons. One for
regular and one for zz . Then, in the After Update event of the option
group, I would set the combo's row source property based on the selection.
You would want the default value of the option group to align with the row
source as defined in design view for the combo.

For example, let's say the row source in design view is:
SELECT [InvItemID], [InvItemDescr] FROM tblItems;

And the Default Value of your option group is 1

Now, in the Option Group After Update:

With Me.MyCombo
If Me.MyOpg = 1 Then
.RowSource = "SELECT [InvItemID], [InvItemDescr] FROM tblItems;"
Else
.RowSource = "SELECT [InvItemID], [InvItemDescr] FROM tblItems
WHERE Left([InvItemID],2) = ""zz"";"
End If
.Requery
End With