Make Items in a Drop Down List on a Form Inactive

[MissDB101]

Hi,

I have a drop down list on a form in Access 2010. I have two entries that are no longer relevant on that list however I cannot delete them from the table as there is historical data in the database that links to these items. So I either want to make them invisible (if this is possible) or make it so that users cannot select them anymore from the drop down e.g. when they try to select them they get a message saying "You cannot select this option". Help please! Am not a frequent Access user!

Thanks

 

[Joe4]

Welcome to the Board!

I often create a table to list the values that I would want to use in a drop-down box.
Then, I would add an "Active" field to this table (TRUE/FALSE or Check mark field).
Then, I would use a query for the drop-down box, and have criteria on that query to only show the Active records.

So in order to remove some records from the list, it is simply a matter of making the record in the table Inactive.

 

[MissDB101]

Welcome to the Board!

I often create a table to list the values that I would want to use in a drop-down box.
Then, I would add an "Active" field to this table (TRUE/FALSE or Check mark field).
Then, I would use a query for the drop-down box, and have criteria on that query to only show the Active records.

So in order to remove some records from the list, it is simply a matter of making the record in the table Inactive.

Thank you so much for your quick reply.

If I amended the existing background table to have "Active" True/False and then made two of them "False" would this not affect the existing records in the database?

Thanks!

 

[Joe4]

If I amended the existing background table to have "Active" True/False and then made two of them "False" would this not affect the existing records in the database?

As long as you haven't deleted those two records, it should not affect existing database records. It should only affect what you can select when you click on the drop-down box.

 

[MissDB101]

Ok, great. Sorry to ask more but I am an infrequent and basic Access user!

So in the table where the items are listed I just create another column entitled "Active"? How do I enter true/false or a tick box in each cell?

 

[Joe4]

Yes, you would just enter a new column (field).

Probably the easiest thing to do is to call this field "Inactive", and add it as a "Yes/No" field type.
Then you do not need to worry about going back and checking all the existing records, you just check this box when you want to make the record "inactive".
So your query would exclude any record that has this box checked (equal to "Yes" or "True").

 

[MissDB101]

Ok so I now have a column that is called "Inactive" and check boxes in each row. I have left the two I no longer want to appear as unchecked.

What do I do now to create a query that would only return the checked items AND how do I link this to the combo/drop down box?

 

[Joe4]

Ok so I now have a column that is called "Inactive" and check boxes in each row. I have left the two I no longer want to appear as unchecked.

If you are leaving them unchecked, then it should be labled "Active".
If you are calling the field "Active", then the records you want displayed should be checked.
If you are calling the field "Inactive", then the records you want displayed should be checked

What do I do now to create a query that would only return the checked items AND how do I link this to the combo/drop down box?

See: Access Query for Positive Checkboxes

While this forum can help with specific questions, what we really cannot do is teach you how to use Access (usually, a basic level of knowledge is assumed).
Questions/answer forums are not really conducive to teaching people the basics of a program.

If you need to learn the basics (i.e. how to make a query, etc), then I would recommend doing some Google Searches. There are tons of great resources out there, including Youtube videos, which will show you how to do this stuff in much better detail than we can do in a question/answer forum environment.

 

[MissDB101]

Hi, totally understood. I have manage to amend the list so that is doesn't show the two values anymore but what it does now is wipe these values from all the exciting records.

So for example my combo drop down list has:- A, B, C, D, E, F, G and I have now set B and F to Not Active so they no longer appear, however, now when I look up a record in my form that originally had B or F in that drop down combo box it now shows as blank. I need the old B and F values to remain on those records. Advice?


Thanls

 

[MissDB101]

Sorry for typos....above

Hi, totally understood. I have managed to amend the list so that it doesn't show the two values anymore but what it does now is wipe these values from all the existing records.

So for example my combo box drop down list has:- A, B, C, D, E, F, G and I have now set B and F to Not Active so they no longer appear, however, now when I look up a record in my form that originally had B or F in that combo box it now shows as blank. I need the old B and F values to remain on those records. Advice?


Thanls