On click Combo Box

[MarionD]

Hi all,

I have a combo box on a sub form. In this combo box I choose certain items to be added to the account.

From time to time these items (in a table) are "deleted". When an item is no longer available - i set a flag in the table - "deleted" =yes. These Items must still display on previous accounts, otherwise the combo box is empty when the account is viewed

My problem is now setting the row source for the combi box. I want these "deleted" items in the row source when viewed - BUT when I click on the combo box, they must not be there for selection.

I have tried setting the row source in the "on click" Event , to show only undeleted items in the dropdown list, setting it back to show all items on the "after update" event.
Somehow, the "on click" code only runs after the "after update" code?? thus having exactly the opposite result to what I want.
Or am I going crazy??

I'm using access 2007

Would be grateful for any advice on this cold and snowy Sunday afternoon!

Marion

 

[Alisa]

I'm not sure I would try to do what you are doing, but in any case, one thought that occured to me is to use the on got focus event instead of the on click event.

 

[MarionD]

Thanks for the reply!

If I use the got focus to filter the Records, the field is empty as soon as I enter the field. I want it to stay set to "all records" unless I click the dropdown to make a selection.

You say you don't think you would do it this way --- I'd be very happy to try another way... do you perhpas have another idea?

Thanks again for your trouble!
Marion

 

[Alisa]

Thanks for the reply!

If I use the got focus to filter the Records, the field is empty as soon as I enter the field. I want it to stay set to "all records" unless I click the dropdown to make a selection.

You say you don't think you would do it this way --- I'd be very happy to try another way... do you perhpas have another idea?

Thanks again for your trouble!
Marion

I'm not really sure what your objective is, but I think I would limit the combo box only to records that are available to actually select, and then have a separate field on the form to show the value that has already been selected.

If you really want to accomplish this within the combo box, another way I would do it is to leave all the records in the combo box, but add an asterisk or some other indication to the unavailable choices. Then in the before update event of the combo box, if they picked one of the unavailable choices, I would do Cancel=True, and give them a message box that the selection is no longer available.

 

[MarionD]

Thanks a million - I think i'll add the extra field as you suggest - I think thats the best solution - otherwise the list looks to confusing.

Thanks for the help - not a good way to be soending Sunday afternoon!

Bye for now

 

[Alisa]

Thanks a million - I think i'll add the extra field as you suggest - I think thats the best solution - otherwise the list looks to confusing.

Thanks for the help - not a good way to be soending Sunday afternoon!

Bye for now

You are welcome. Just make sure you lock that field so they don't type over their selection.