How to make a combobox make certain items not be selectable?

[TheSmileyCoder]

I have a combobox linked by SQL to a table. The table has standard Primary Key (Autonumber) and text field.

There are 3 records:

KEY_DocType tx_DocType
1           RMT Document
2           Review Document
3           Reference Document

I would like it to be so that, IF the document type is allready set to RMT document, that the user can view the text (RMT document) in the combobox, but I want it so that when the user clicks the dropdown, that the user can only select Review Document or Reference Document, I.e. that the RMT document is simply not presented in the dropdown list.

I know I can handle this through AfterUpdate, but I was hoping for a more elegant solution.

[jimatqsi]

I think it will work if you just add the appropriate Where clause to the criteria of the rowsource of the combo box. The value of the bound data item will show in the text box but when you click to browse other entries you will see what the rowsource returns.

If the bound value is not "RMT", are they allowed to choose RMT? If that is the case then you will have to dynamically build your rowsource string based on the value of the bound data item, if ="RMT" then the rowsource = one thing, if <>"RMT" then the rowsource =something else.

Jim

[TheSmileyCoder]

I have allready tried just setting the where clause to exclude some records simply gives a blank field when i use those values (Since im displaying the second column of the table, the first column being the primary key)

I did find a solution myself. I place a textbox directly above the combobox, in such a way that only the dropdown of the combobox is visible, the controlsource of which is pulled into the recordsource of the form, by a left join. I disable the textbox completely, and use the forms Current event to decide whether or not to enable the combobox. Its a "hack" but it works.

[reginaldmerritt]

Thats a good way around it SmileyCoder. I have had a similar issue before.

You could also set the row source for the ComboBox in Form_Lode routine to the Table allowing any items to be visible. Then set the row source to the query in the ComboBox_Click routine.

Only issue there is that if a user decides to change the ComboBox value they won't be able to change it back to its original value.

[TheSmileyCoder]

I did consider your approach but it requires the combobox to be requeried each time the user clicks on it, which is the reason why I choose the other approach. However, thank you for your input. I can only get wiser!

[Adam Tippelt]

What do you mean by 'more elegant solution'? Surely a more elegant solution to your 'hack' would be to base the combobox on a query (if it isn't already), and set the criteria to:

<> [ComboboxName].[value]

It would require the combobox to be requeried after update, but you can make a new selection in the combo box which is then filtered out of the drop down list on selection, and the old value is put back in the list.

EDIT: What is wrong with requerying it when the user clicks?

[TheSmileyCoder]

Your implementation would merely mean that the user cannot reselect a value which is allready selected.

While I could requery the combobox I would have to query it both when the user clicks the dropdown as well as when the user moves to a new record (otherwise the RMT document value would not be shown).

There is nothing wrong with doing a requery if your database is local or on a fast network. However if you are on slow network, I dont want to give the user a 0.3sec delay on opening the dropdown.

[Adam Tippelt]

I thought that was what you were trying to do - "I.e. that the RMT document is simply not presented in the dropdown list." Perhaps I misinterpreted what you meant.

[TheSmileyCoder]

I have 3 types of documents. If the document is a RMT Document is metadata is created and maintained automatically through my application. The other 2 types of documents are created outside the company and we manually enter metadata about them. Since a RMT document will never need to be manually entered, I dont want to display that in my dropdown.

If I filter it out, it wont be visible in the dropdown, since its a 2 column dropdown, with the first column being the Primary key, and has Width=0 (so its hidden).

Yes I could probably change the recordsource on click, but what about if a user tabs into the field? I thank you for your input, but I dont see how its better then my hack :P

[Adam Tippelt]

Ah well...you can't blame a guy for trying. :P

[NeoPa]

As you say Smiley, this is not a natural way of behaving for a ComboBox, and I for one, can't think there is a way to do it naturally.

That said, if we're looking at hacks, what about simply setting the .ValidationRule property to disallow selection of that record? I can't see a way of both allowing the ComboBox record to reflect that value ("RMT Document") for those form records with it already selected, as well as disallowing it for all others.