Help with Combo Box Display

[cydud3]

Let me get straight to the point. I have a table containing the following fields:

ItemId (Autonumber)
ItemDescription (Text)
Discontinued (Yes/No)

As you might have guessed, the discontinued field is just a field that determines whether this particular item can still be used. So, in a form, I have one combo box with the following as it's Row Source:

SELECT tblItems.ItemId, tblItems.ItemDescription, tblItems.ItemDiscontinued FROM tblItems
WHERE (((tblItems.Discontinued)=False)) OR (((tblItems.Discontinued) Is Null));

Now my problem is this. When I tick Discontinued for some items, previous records with the combo box pointing to those items will be displaying blank since the item is not on the Row Source anymore. Is there any way to still display the item in the combo box even though the item is not in the row source anymore. Or, is there any other way to do this? Basically, the task I'm trying to accomplish is to cut the combo box choices so that only the current usable items are displayed but at the same time preserve old records with items that may have been discontinued.

Hope that makes sense. Thanks.

2B||!2B

 

[HandsOnAccess]

Try this...

Private Sub Form_Current()
    If Me.RecordsetClone.RecordCount = Me.CurrentRecord Then
       me.combo.rowsource = "SELECT tblItems.ItemId, " & _
       "tblItems.ItemDescription, " & _
       "tblItems.ItemDiscontinued " & _
       "FROM tblItems " & _
       "WHERE (((tblItems.Discontinued)=False)) OR  " & _
       "(((tblItems.Discontinued) Is Null)); " 
    Else
       me.combo.rowsource = "SELECT tblItems.ItemId, " & _
       "tblItems.ItemDescription, " & _
       "tblItems.ItemDiscontinued " & _
       "FROM tblItems "
    End If
    Me.combo.Requery
End Sub

Regards

 

[cydud3]

Haven't tried it yet but as I understand it, if you have 10 combo boxes tied to 10 similar tables, you'll have to do this 10 times right?

2B||!2B

 

[barrylowe]

You could try setting the 'limit to list' value of the combo to No.

 

[HandsOnAccess]

No if you have 10 combo box don't do it ten times, just write a standard function and call it 10 times.

You can pass the Control Name and WHERE condition as function parameters.

Regards,

 

[cydud3]

barrylowe, limit to list will not work since my bound column is not the same as my displayed column.

handsonaccess, thanks for the function suggestion. I'll try it.

2B||!2B

 

[BoxHead]

cydud3,

Is it possible that your combo box is bound to another table or query? If not, stop reading right now.

I've found (the hard way) that using combo boxes to display field values runs into problems like this. If you are trying to use the combo box to display items that were ordered as well as items that could be ordered you'll run into the type of problem you have since those two groups of items are not the same.

Adding a textbox to your form to display the item that was ordered and using an unbound combobox to update that value from a list of items that can be ordered would resolve that issue.

In design view, you can
> reduce the width of the combo box to display only the dropdown arrow,
> add a textbox bound to your table's field right next to the drowdown arrow (this will make it look like a normal combo box),
> set the combobox control source to 'unbound', and
> on the combobox AfterUpdate Event, set the textbox value to = the combo box.


HTH

 

[AKMonkeyboy]

For my own curiosity, why do you have the OR tblItems.Discontinued Is Null statement in the row source? Can't you set all items to No automatically, thereby removing the need to include that statement?


Also, if you want to view discontinued items - why not make an option for that on the form? I have a Checkbox that depending on the state it changes a combo box row source.

Or am I missing your point?

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[cydud3]

Boxhead, thanks for your suggestion but on my form, I have around 15-20 similar combo boxes and changing them all would become too complicated.

AKMonkeyboy, I set all items to No already but I just thought I'd include the Null just to be more tolerant. Anyway, I think you're right. It will probably work just as well, so I removed it.

Thanks for all the suggestions but I think I found a much easier way to do it (for my purposes). What I did was simply change the row source of the combo box to something similar to the following:

SELECT tblItemsCases.ItemId, tblItems.ItemDescription, tblItems.Discontinued FROM tblItems WHERE (((tblItems.Discontinued)=False)) Or (((tblItems.ItemId)=Forms!frmFormName!cboComboboxName));

This way, if there's already a value in the combo box it will also display it too. However, I have to do requeries for afterupdate and formcurrent.

2B||!2B