469,167 Members | 2,149 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

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

2,321 Expert Mod 2GB
I have a combobox linked by SQL to a table. The table has standard Primary Key (Autonumber) and text field.

There are 3 records:
Expand|Select|Wrap|Line Numbers
  1. KEY_DocType tx_DocType
  2. 1           RMT Document
  3. 2           Review Document
  4. 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.
Mar 4 '11 #1
10 6340
1,259 Expert 1GB
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.

Mar 5 '11 #2
2,321 Expert Mod 2GB
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.
Mar 6 '11 #3
201 100+
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.
Mar 9 '11 #4
2,321 Expert Mod 2GB
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!
Mar 9 '11 #5
Adam Tippelt
137 100+
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:

Expand|Select|Wrap|Line Numbers
  1. <> [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?
Mar 9 '11 #6
2,321 Expert Mod 2GB
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.
Mar 9 '11 #7
Adam Tippelt
137 100+
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.
Mar 9 '11 #8
2,321 Expert Mod 2GB
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
Mar 9 '11 #9
Adam Tippelt
137 100+
Ah well...you can't blame a guy for trying. :P
Mar 9 '11 #10
32,167 Expert Mod 16PB
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.
Mar 10 '11 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by dr.p.dietz | last post: by
5 posts views Thread by Carrie | last post: by
1 post views Thread by Nick Zdunic | last post: by
1 post views Thread by Gian Paolo | last post: by
2 posts views Thread by gleadams | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.