Inactivating records

[Jill]

I wanted to run this by you all and see if I'm approaching this the "best"
way, and I know "best" can be subjective.

I have look-up tables which serve to populate comboboxes in data entry forms
and standardize data entry... A user can also add more values to the list in
the look up table (via another form). For example, a lookup table of
WaterColor will have fields of WaterColorID (primary key set to autonumber)
and WaterColor (green, brown, blue). If someone wanted to refine the green
color further to more specific green colors, say "aqua green" and "neon
green", changing the "green" record would mean that all previously entered
green records would be changed because of the ID. So, instead, I wish to
have it so that the user would enter new records of aqua green and neon green
and inactivate the green record. Thus, any new data entry would reflect this
new color refinement but previous records would remain intact.

The way I've figured this out is to add a 3rd field, WaterColorActive, to
the lookup table and set this to be a Yes/No type. Then I would create a
query and use the criteria of Like "1" to pull out those active records. The
combobox on the data entry form would then be set to this query.

Is that right?

Your guidance is much appreciated,

Jill

 

[Arvin Meyer [MVP]]

The way I've figured this out is to add a 3rd field, WaterColorActive, to
the lookup table and set this to be a Yes/No type. Then I would create a
query and use the criteria of Like "1" to pull out those active records.
The
combobox on the data entry form would then be set to this query.

That would be perfect. Your criteria would be -1 though.

 

[Proko]

Hi Jill,
Perhaps you need another table. Lets say RefinedColors.

This table's fields would be:
RefinedColorID
WaterColorID
RefinedColorName

Table RefinedColors would be like a subset of your Watercolor table. Lets
say green has a WaterColoriID of 1, aqua green would still have WaterColorId
of 1 but with RefinedColorId of say 1; neon green WatercolorID of 1 and
RefinedColorId of 2 etc
On your data entry form you would need to add another combo box to display
the refined colors for selection.
The user would select, say "green", from the watercolor combo. Your
RefinedColors combo would then display all your greens ie "aqua green", "neon
green","snot green" etc. There is lots of helpful code available on the web
to help with "displaying results in one combo based on selection in another
combo"

I hope I have understood your question and the info is helpful.

 

[Proko]

Hi Jill,
Perhaps you need another table. Lets say RefinedColors.

This table's fields would be:
RefinedColorID
WaterColorID
RefinedColorName

Table RefinedColors would be like a subset of your Watercolor table. Lets
say green has a WaterColoriID of 1, aqua green would still have WaterColorId
of 1 but with RefinedColorId of say 1; neon green WatercolorID of 1 and
RefinedColorId of 2 etc
On your data entry form you would need to add another combo box to display
the refined colors for selection.
The user would select, say "green", from the watercolor combo. Your
RefinedColors combo would then display all your greens ie "aqua green", "neon
green","snot green" etc. There is lots of helpful code available on the web
to help with "displaying results in one combo based on selection in another
combo"

I hope I have understood your question and the info is helpful.
Proko