Hi,
I have a dropdown e.g. a country which shows the following:

Belgium
England
China
Mexico

I need to keep the
the list as above.

However I do not want the user to choose Belgium. I am not sure how one can do this. I appreciate any help for resolution of this issue.
Thanks

Hmm. The most obvious solution would be to remove Belgium from the list so it doesn't even appear. You say you need to keep the list, but do not want to use one option on it. There must be some REASON for that, but it's not obvious from the data provided.

What is DIFFERENT about Belgium that requires this special treatment?

Our system had records for the country Belgium. However Belgium is a country where there is no more business. To get access to old records if I take away Belgium then the record does not show what country this order belongs to. Actually there are five such countries. However I just put one. Users are making mistake in a hurry by choosing wrong country. I would like to prevent them.
So the idea is such.

Gotcha.

DO you have an active flag in the table which provides this value? If so, a technique I have seen, from Armen at J Street, is to change the way such "inactive" records display and sort.

Assumption: you do have an inactive flag which identifies inactive records (true if they are inactive).

Select CountryID, Iif([InActiveFlag]=True,"*" & [CountryName], [CountryName]) AS Country
FROM tblCountry
ORDER BY InActiveFlag DESC, CountryName

This places an asterisk "*" in front of the name of each inactive countryname, alerting users that they are different. Because Access uses -1 for "True" and 0 for False, it also sorts these names to the bottom of the list so users have to scroll down to see them.

If you have an Inactive date instead of an inactive flag:

Select CountryID, Iif(IsNull([InActiveDate]),[CountryName], "*" & [CountryName]) AS Country
FROM tblCountry
ORDER BY InactiveDate, CountryName

Sorting is not quite so neat, but it as the same overall effect.

Edited by: GroverParkGeorge on Wed Nov 11 10:33:19 EST 2009.

Hi,

Pardon me for jumping in... but in addition to George's suggestion, another alternative is to add code in the BeforeUpdate event of the combobox, so that the selection is rejected if the user selects the inactive countries.

Just my 2 cents...

Good idea. THanks. And it is possible to implement this control in conjunction with the "Starred Country" technique to cover all the bases.

On the other hand, it seems like there are always corner cases to consider. For example, it just occurred to me that there could be a legitimate reason to allow selection of an inactive item on the list-- to correct or even to "back-date" an order, for example.

Hi George,

In that case, perhaps the rejection routine can confirm the user's intention first before resetting the choice.

What do you think?

Folks I am going to work on this either later today or tomorrow.
George with the * concept I am not sure whether it will display old records which will be not active. Do you agree
Thanks

Yes, The only thing the * does, after all, is flag items in the drop down, so you'd still want to confirm selection of of one before using it.

That sounds like a technique I'm going to try to incorporate. Thanks.

The * only flags items in the drop down for country, so it would not have any effect on old records at all.

Thanks Grover and DbGuy for the generous help.. I appreciate it. With the two combined it works pretty good now.
Regards.

Glad to hear you resolved your issue.

Continued success with your project.

George