Decode Table Deprecate Values

[samye]

Hello,

I have several decode tables with values that I do not want used anymore but I want the value to remain on the record.

For example, the application has a Building table that contains Building information, name, address, etc. It has a relationship with a Status table that contains the decode of the StatusCode. The values are SOLD, LEASED, DEMOLISHED, DUPLICATE. Duplicate is used when a record was created in error. On the Entry Form the Status is a drop-down that contains the fore mentioned values. I don't want the users to see DUPLICATE because those records are updated on the back-end. There is a query associated with the drop-down box. I added a field, StatusDeprecated as a YES/NO field to the Status table and modified the query to not show if YES/NO is true.



This works, except if I bring a record up that has a status of DUPLICATE the drop-down is blank.

Please recommend the proper way to do this.

Caveat, this system was written in 1996, so rather than delete a record that was duplicated, they chose to keep it. I'm just trying to clean the data up and create some rules around the current processes.

Thank you,
Carol

[June7]

Are you saving text or a foreign key of lookup table Status?

The dropdown list should not be blank.

Review this question about cascading/dependent/conditional combobox https://stackoverflow.com/questions/...t-show-its-val

[samye]

Hello,

The foreign key is being stored. The dropdown is not blank, the first entry is blank if the value has been set to deprecated. Here are some screen shots:

Here is the table:





Here is the form with the value:




Here is the query:

SELECT lutBuildingStatus.StatusID, lutBuildingStatus.StatusValue, lutBuildingStatus.StatusDeprecated
FROM lutBuildingStatus
WHERE (((lutBuildingStatus.StatusDeprecated)=No))
ORDER BY lutBuildingStatus.StatusValue;





Here I set DUPLICATE to deprecated:




Then I access the form and the field is blank because the query isn't presenting it. I don't want the query to present any deprecated values but I want the database value to show.






Is there a setting or event or combination that I need to use first?




Thank you.

[June7]

Did you review the link I posted earlier?

[Welshgasman]

Here is how I handled this.
Instead of just a boolean flag, I also had a DateDeactivated. In hindsight, that date field is all that is needed.

That way I still could see the caseworker for an old case, yet when I went to select a caseworker for a new case, only current ones were in the combo.

Perhaps you can adapt for your situation?
SELECT Lookups.ID, Lookups.Data
FROM Lookups
WHERE (((Lookups.DataType)='Email') AND ((nz([Lookups].[DeActiveDate],Date()))>=#8/1/2019#))
ORDER BY Lookups.Data;

I picked an arbitary date just before the first person left.

HTH

It is also a good few years since I implemented this.

[samye]

Hello,
Yes, I reviewed the post. It is not the same as what I am doing. I do not have two combo boxes but one and I am using a form not a datasheet view. I kind of understand what the post is explaining but it will not work for me. It is cludgy programming. To layer a box on top of another box is not a good solution but thank you for the post.

[samye]

Hello,
Thank you for your reply. I'm afraid the above will give me the same results. The issue is the query on the combo is discarding any records that are deprecated, whether that is with a date or a yes/no, so the field appears blank. I was thinking I would need an event or maybe adjust the query of the form.
Kind Regards,
Carol

[June7]

Doesn't have to be two comboboxes. You have one combobox with RowSource that is dependent on value in another field/control (textbox or combobox or otherwise). Referenced link still applies.

[samye]

Hello,
I found my answer here: https://www.access-programmers.co.uk...source.295349/

This uses the GotFocus and LostFocus events. The combo box on the form has a query assigned to display the description based on the decode table. When I click the drop down the Got Focus executes a sql statement to display the values that are not deprecated. When the focus is lost the query runs again.

So far, it works.

Thank you all for your replies.

[June7]

Basically what was described in the link referenced in post 2.

Could have code that only filters second combobox when it gets focus then restore the full list when loses focus. Users will still see data briefly disappear from other records and may find distracting at first but will learn to disregard.