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]

I am not sure why that would be happening.
Is this an existing form that someone else created, that might have some VBA code running in the background?

Perhaps you need two different forms.
1. Data entry form that filters out the inactive records for new data enty.
2. Historical record form that shows old records. Do NOT filter out inactive options in this form, but make the Form read-only so it can only be used to view old data, and not for new data entry.

 

[Micron]

Whatever query you use for the rowsource of the combo, you don't use it for anything else. What you do to the combo list should have no effect on anything else. If it does, something else is wrong.

 

[jackd]

Post a graphic showing:
a) your table design and a few records, and b
b) the rowsource of your combobox (the query sql)

 

[MissDB101]

Whatever query you use for the rowsource of the combo, you don't use it for anything else. What you do to the combo list should have no effect on anything else. If it does, something else is wrong.

I'm using the original table as the source for the combo box because that is what has always been used in the database to populate this field in the form. I have added a column to the table that has a tick box for those that are active and not active. So the two I don't want to appear anymore are unticked. What this means however is some of the old records on the form are now blank where those two options were used because they no longer appear in the drop down. That's a given surely because each record in the form is live so if those options aren't there anymore it will wipe that field.

I cannot create a new form as I am not allowed to change the way the database works unfortunately. I just don't want people to be able to select these two options anymore. Ideally I don't want them to see the options at all but it seems this is not possible.

 

[MissDB101]

Post a graphic showing:
a) your table design and a few records, and b
b) the rowsource of your combobox (the query sql)

 

[MissDB101]

What is the best way to post images please? I can do a screen shots and put them in a word document? Is that allowed?

Thanks

 

[jackd]

When you post a response, there is a button "Upload Image".

As I understand your current issue, in general you continue to use your table with the additional column. So any processes that use the table contents will continue to do so without knowledge of the "tickbox".

However, the combobox where you wish to restrict values to those values should have its RowSource modified. It would be a query along the lines of this template. You need to supply the proper names.

Select the_necessary_ fields from your_table
where Your_tickbox = False

So the two I don't want to appear anymore are unticked.

 

[MissDB101]

When you post a response, there is a button "Upload Image".

As I understand your current issue, in general you continue to use your table with the additional column. So any processes that use the table contents will continue to do so without knowledge of the "tickbox".

However, the combobox where you wish to restrict values to those values should have its RowSource modified. It would be a query along the lines of this template. You need to supply the proper names.

Select the_necessary_ fields from your_table
where Your_tickbox = False

Yes but the problem is the combo box appears on all the old records that are within the form so any record that has one of the unticked values is now showing as blank

 

[jackd]

As Micron said in post #12

Whatever query you use for the rowsource of the combo, you don't use it for anything else. What you do to the combo list should have no effect on anything else. If it does, something else is wrong.

You may have to post a sample from a copy of your database. If the new field was not known to the existing code/programs, then it is unclear how values from that field are causing issues now. The rowsource of the combobox is where and how you would restrict the values available in the drop down.

I suggest you post some code or screen shots that show the issue in context. This is one of those situations where having access to the "problem" would facilitate corrective action.

 

[MissDB101]

As Micron said in post #12


You may have to post a sample from a copy of your database. If the new field was not known to the existing code/programs, then it is unclear how values from that field are causing issues now. The rowsource of the combobox is where and how you would restrict the values available in the drop down.

I suggest you post some code or screen shots that show the issue in context. This is one of those situations where having access to the "problem" would facilitate corrective action.