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

 

[MissDB101]

The first image - drop down highlighted is the combo box I am referring to. The items I have unticked are “ED, CHILDH & INCL” and “TEACH ED” but I am showing them here so you can see what it normally looks like. As you can see this is record 1 of 384. Some of the records are obviously using the two items that I will later untick. If you bring up a record that has one of these items it shows as blank I assume because I have them unticked in the background table in the second image

In the third image it shows the properties on the form for this combo box for ROW SOURCE

In the fourth image it shows when I go back into normal view and pull up a record that had one of the old items it now shows as blank

 

[jackd]

So the change to the rowsource of the combo does show the drop downs you want/need--is that correct?

Re 4th image:

What is the recordsource of frmProposal?
What is the rowsource of the combo related to Department?

What is the logic for what you want to show in the 4th image?

Are you using LOOKUP fields in any of these tables?

 

[MissDB101]

So the change to the rowsource of the combo does show the drop downs you want/need--is that correct?

Re 4th image:

What is the recordsource of frmProposal?
What is the rowsource of the combo related to Department?

What is the logic for what you want to show in the 4th image?

Are you using LOOKUP fields in any of these tables?

Yes the new drop down is correct and no longer shows the two items I want to "hide" but the problem is the existing records in the from now have blanks for that field where they had those old values. So if someone looked up an old record that field would be blank which is not what I need. I need those values to still be there. I just the the drop down to no longer have the option to select these two old values as they are no longer valid (but I need to keep historical data intact)

Sorry what do you mean what is the "recordsource" of frmProposal - do you mean where is all that data stored?
rowsource of combo box is image3

4th image is what happens when you look at a record that used to have one of the old valules. The combo list is now correct but the field for that record is now empty because that item is no longer available to select from the drop down so it has wiped it.

 

[Micron]

You don't use a table for a combo box rowsource as you cannot filter it. You also don't use the same table for the list as you do for the records it creates. You need to bind the combo (control source) to the data table but use a query for the list items (row source) as has been mentioned OR use a lookup table (not a lookup field in a table). Your problem is probably caused by you using the same table for the row source and the control source.

If you're not allowed to make such changes, then you are stuck with presenting all of the choices. In that case, use form BeforeUpdate event to test if one of the inactive departments were picked and cancel the update and clear the combo choice. When users get ticked off over being allowed to enter data only to be told they can't, tell them to go to management with their complaint because you're not allowed to change things as needed. It makes no sense to me that you're not allowed to do something that will improve data integrity and user experience.

 

[jackd]

RE: what do you mean what is the "recordsource" of frmProposal - do you mean where is all that data stored?

In the Design view of the Form, go to the Data tab in the properties. It will show Recordsource of the form. Post a screen shot of what you find.

It seems the issue concerns your query DB Proposal ( image3). You only want to use that rowsource when selecting records for display. That is, limit my combo box to records selected by this query.

For reporting/displaying all old records - regardless of the active/inactive flag - you would use the Table, or select * from table.
Anything that existed before you added the Active field to your table, should continue to exist and ignore any values in the Active field.

My concern (best guess) is that there may be a lookup field involved in your table design?? Just a guess.

 

[MissDB101]

You don't use a table for a combo box rowsource as you cannot filter it. You also don't use the same table for the list as you do for the records it creates. You need to bind the combo (control source) to the data table but use a query for the list items (row source) as has been mentioned OR use a lookup table (not a lookup field in a table). Your problem is probably caused by you using the same table for the row source and the control source.

If you're not allowed to make such changes, then you are stuck with presenting all of the choices. In that case, use form BeforeUpdate event to test if one of the inactive departments were picked and cancel the update and clear the combo choice. When users get ticked off over being allowed to enter data only to be told they can't, tell them to go to management with their complaint because you're not allowed to change things as needed. It makes no sense to me that you're not allowed to do something that will improve data integrity and user experience.

OK so I think I can create the query but how do I enter that in the Rowsouce?

 

[MissDB101]

RE: what do you mean what is the "recordsource" of frmProposal - do you mean where is all that data stored?

In the Design view of the Form, go to the Data tab in the properties. It will show Recordsource of the form. Post a screen shot of what you find.

It seems the issue concerns your query DB Proposal ( image3). You only want to use that rowsource when selecting records for display. That is, limit my combo box to records selected by this query.

For reporting/displaying all old records - regardless of the active/inactive flag - you would use the Table, or select * from table.
Anything that existed before you added the Active field to your table, should continue to exist and ignore any values in the Active field.

My concern (best guess) is that there may be a lookup field involved in your table design?? Just a guess.

 

[Micron]

OK so I think I can create the query but how do I enter that in the Rowsouce?

In form design view and property sheet showing, select the combo, look for row source property and click the drop down there if you already have the query or the ellipses if not. The latter will open the query builder but it won't build an actual query, it will use the sql statement that the query builder creates as the row source.

I'm afraid I don't see a need for examining the form recordsource. I gather that db uploads are not permitted here, so I will bend my usual rule of not downloading from file sharing sites and look at this for you. If you have some other preference for sharing the db for analysis let me/us know. I'm saying that because this is a simple design change problem but we're now at the 3rd page of posts. Copy the db, compact it and zip it before uploading anywhere. If you need to obscure data, there is this:
Randomizing Data for Posted db's

 

[jackd]

The recordsource of the form should be your table, not the query that uses the Active field.
The rowsource of the combo when selecting records you want to display should be the query that uses the Active field with the Where clause.

I agree with micron that a copy of the database would be a great help.

 

[MissDB101]

The recordsource of the from is a table called BD Proposal. I haven't changed this.
When I use the query to remove the non-active items in the rowsource of the combo box the drop down then becomes totally blank!

I cant copy the database on here. I am too worried about taking a copy and randomizng data because there is a back end and a front end and I dont want to mess anything up