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

 

[jackd]

When you have a frontend and backend, the tables are in the backend. Forms,queries, reports etc are in the frontend. Each user has
Can you open your BD Proposal table in design view and take a screenshot?

You should be working/investigating on a copy of your database in order to prevent unwanted changes to your operational database.

Somehow it appears that you have changed the form to display/list the BD Proposal table contents.
From my view the purpose of the change to the rowsource of the combo on the form to was simply to ensure that no new records could select and record the inactive values.

It's possible the table has lookup field that is causing issues. We really need something to work with to help identify the cause of your issue.

Does your application or process have a backup procedure to keep backups in case of some failure?

 

[Micron]

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!

What happens when you run that query? Do you get any records?

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

Understood. You would have to copy both into a new folder, relink them to the new folder location then randomize. Another possibility would be to copy only the fe into a new folder, delete all the table links (this doesn't delete the be tables) and remove everything not needed for this issue. Then import the required table (not link it). Now you have a stand alone db with the required table. You could randomize that.

 

[MissDB101]

When you have a frontend and backend, the tables are in the backend. Forms,queries, reports etc are in the frontend. Each user has
Can you open your BD Proposal table in design view and take a screenshot?

You should be working/investigating on a copy of your database in order to prevent unwanted changes to your operational database.

Somehow it appears that you have changed the form to display/list the BD Proposal table contents.
From my view the purpose of the change to the rowsource of the combo on the form to was simply to ensure that no new records could select and record the inactive values.

It's possible the table has lookup field that is causing issues. We really need something to work with to help identify the cause of your issue.

Does your application or process have a backup procedure to keep backups in case of some failure?

Yes I know about the front end and the back end. The back end Department table is where I added the Active column so I could untick the two Departments I no longer wanted to appear.

I have attached a screenshot of the BD Proposal table in design view. "department" is listed as a code so somewhere there must be a lookup using the Department table to find the name.

I haven't changed the form, it always displayed the BD proposal table contents. The from is the "front end" to that table. As records are added that table grows with all the data and it is stored in the back end.

No back ups etc. The person who created the database left and didn't leave any instructions. No one in the team is fully trained on Access or this specific database and our IT don't provide training or support for systems created by individuals and yes I know how ridiculous that sounds! I have just been asked to try and do this change when I have very little Access experience.

 

[JonXL]

If I understand correctly, the bound field is 'resetting' when you load old records because the value in those records is no longer available on the drop-down's control source.

One way around this would be to create a text box on the form bound to the field in the BD Proposals table and unbind the drop-down to that field. Add a change event to the drop-down to update the text box when a value is selected from the drop-down. Your text box can then display all the old - inactive - values it wants while the drop-down logic means users can only select new values listed as active on the look-up table.

 

[jackd]

I am confused.

. The back end Department table is where I added the Active column

I have attached a screenshot of the BD Proposal table in design view.

No back ups etc. The person who created the database left and didn't leave any instructions

How critical is this database/application to your business?
Are you saying there are no backups for the Frontend or Backend?? If so, then correcting that is a priority.

Re your graphic shown in #33, for each field in the table, view the contents under the Lookup tab. If you find one with "SELECT ...." take a screenshot and post.

 

[Micron]

I don't agree. The query that filters out "Inactive" feeds the list (row source) only. What is displayed in the control is bound to the table field. If it is a value that has become Inactive, it will still display in the control because that's what's in the table. If you choose another value you will not get the original value back. It might be better to use a different approach for that reason. I had this in mind long ago, but figured get this list to work then suggest something to disable the control when navigating through records where department value was made inactive.

Also I think you meant drop-down's row source, not "drop-down's control source."?

Re your graphic shown in #33, for each field in the table view the contents under the Lookup tab. If you find one with "SELECT ...." take a screenshot and post.

I'm not seeing the point in worrying about the bd table. The parent values would be in the department table and that is where they would be flagged as inactive. However, it appears that the department field in bd is an integer and it should be a Long, assuming it is the foreign key related to department as an autonumber id. They should not be able to add beyond 32,767 in the bd table with it being an integer. If they have already then things are not as they appear to be.

 

[jackd]

@MissDB101
As you can see, we are all guessing as to the source of the issue by trying to piece together symptoms and formulate some approach to solution.
I don't know the relationship between Department and BD Proposal.
We need to see the code or a portion of same to understand /review the material in context.

 

[MissDB101]

I am sorry, I was hoping this was more straight forward but my lack of knowledge is clearly a problem and I am getting lost with the replies as I don't understand most of what people are telling me to do. How do I close this thread without causing any more issues please?

 

[jackd]

We are trying to help. But we need to see the issue. Is there someone near you - a database/contractor familiar with Access - that you could contact to assist you.

Having responsibility for an operational database when the developer(s) has left and there are no instructions or training available is a terrible situation. Your lack of knowledge of Access/database is unfortunate, but you are not the first to be thrown into such a situation. It is difficult to offer more focused info remotely with so little facts. Often in similar situation, the poster can supply a copy of the database, or table/relationship diagrams, or relevant vba code with instructions to highlight the issue.

We really need to see the database or have access to someone who knows MS Access basics.You could investigate some software to capture a database session and record same(free). The resultant file (mp4 or similar) could be posted somewhere (dropbox, 4share...) and you could let readers know how to review the file.

You could try youtube for help/instructional videos on MS Access. There are other Access related forums where you could post your question(s) and, if you do, please reference this thread so others will have some background.

How do I close this thread without causing any more issues please?

You are not causing issues. We are trying to help. Why close the thread without getting an answer or options? Perseverance is key.
Good luck.

 

[MissDB101]

We are trying to help. But we need to see the issue. Is there someone near you - a database/contractor familiar with Access - that you could contact to assist you.

Having responsibility for an operational database when the developer(s) has left and there are no instructions or training available is a terrible situation. Your lack of knowledge of Access/database is unfortunate, but you are not the first to be thrown into such a situation. It is difficult to offer more focused info remotely with so little facts. Often in similar situation, the poster can supply a copy of the database, or table/relationship diagrams, or relevant vba code with instructions to highlight the issue.

We really need to see the database or have access to someone who knows MS Access basics.You could investigate some software to capture a database session and record same(free). The resultant file (mp4 or similar) could be posted somewhere (dropbox, 4share...) and you could let readers know how to review the file.

You could try youtube for help/instructional videos on MS Access. There are other Access related forums where you could post your question(s) and, if you do, please reference this thread so others will have some background.


You are not causing issues. We are trying to help. Why close the thread without getting an answer or options? Perseverance is key.

We are trying to help. But we need to see the issue. Is there someone near you - a database/contractor familiar with Access - that you could contact to assist you.

Having responsibility for an operational database when the developer(s) has left and there are no instructions or training available is a terrible situation. Your lack of knowledge of Access/database is unfortunate, but you are not the first to be thrown into such a situation. It is difficult to offer more focused info remotely with so little facts. Often in similar situation, the poster can supply a copy of the database, or table/relationship diagrams, or relevant vba code with instructions to highlight the issue.

We really need to see the database or have access to someone who knows MS Access basics.You could investigate some software to capture a database session and record same(free). The resultant file (mp4 or similar) could be posted somewhere (dropbox, 4share...) and you could let readers know how to review the file.

You could try youtube for help/instructional videos on MS Access. There are other Access related forums where you could post your question(s) and, if you do, please reference this thread so others will have some background.


You are not causing issues. We are trying to help. Why close the thread without getting an answer or options? Perseverance is key.
Good luck

There is only me I am afraid. There is no one else who can help. I have no idea how to copy the database, and randomise it etc and then post it here. I am worried I will do something wrong and I cant risk that.

There is not going to be a solution sadly so would it not be best for me to delete the thread as it's not helpful to the site?