non selectable table values

[meilani]

Is there a way to prevent a user from choosing (or even seeing) a value in a
table in a form?

For instance: I have 7 software version entries in a table that are all
valid and were used in a form at one time. However, I'd like to prevent a
user from choosing some entries that are no longer used, without changing
past records. They don't necessarily have to be hidden (which I prefer), only
non selectable.

 

[Rick B]

I would probably use a validation rule to specify that the field cannot
contain a value less than X.

Or, change the query upon which that drop-down is based to only include
current values. As long as they do not go into previous records and try to
modify that field, they will not run into problems.

Rick b

 

[meilani]

Thanks Rick, those are great suggestions. However

(1) I don't think the validation rule would work in my case since the values
I need blocked are not all necessarily at the top of the list; meaning items
#1, 5, and 12 is what I want to prevent from being used.

I suppose I could change things around but that would be very tricky to do,
wouldn't it? Also, would this interfere with past record entries that used
these values?

For instance I have tbl_Main that is linked to tbl_Version. A txt_Code field
on my form (tbl_Main source) holds a generated value based on the Version
value (combo box) the user chose.

(2) You're 2nd suggestion wouldn't work b/c I DO need to be able to allow
edits to old records.

Maybe, I can't have it both ways...

 

[Rick B]

If you are going to let them edit old records, then I can't think of a way
to lock them out of selecting certain entries in your combobox.

It can be done, but you would have to give us a way to decide which entries
were valid for each record. We could then try to come up with a validation
rule, a dynamic record source for the combobox, or vba code to do the trick.

You'll have to spell out the IFs that you use when you decide what is valid
for a given record. If you can tell us in English, someone (probably not
me) can most likely turn it into an sql statement that can be used as the
record source for your combobox, or vba code that will do the trick.

Map it out and see where it leads.

Rick B

 

[Rick B]

The fact that the allowed entries are not in sequence won't matter. There
are ither things you can do (other than < or >). So don't worry about that.

Rick B

 

[meilani]

Actually, after your last 2 posts, it made me think about this further. It
may work afterall.

For the validation rule, I see what you mean. I didn't even think of that.
That may be the way to go, and easier way to go.

Your other suggestion (2nd suggestion).. I should have been more specific
because I still may be able to do it this way. I need to allow users to edit
old records but only a certain [memo] field that has nothing to do with the
combo field I'm worried about.

Thanks for kicking my brain into gear!

 

[Rick B]

Cool. As long as they won't change that particular field on old records, I
don't think the validation rule will come into play.

Rick B

Actually, after your last 2 posts, it made me think about this further. It
may work afterall.

For the validation rule, I see what you mean. I didn't even think of that.
That may be the way to go, and easier way to go.

Your other suggestion (2nd suggestion).. I should have been more specific
because I still may be able to do it this way. I need to allow users to edit
old records but only a certain [memo] field that has nothing to do with the
combo field I'm worried about.

Thanks for kicking my brain into gear!

If you are going to let them edit old records, then I can't think of a way
to lock them out of selecting certain entries in your combobox.

It can be done, but you would have to give us a way to decide which entries
were valid for each record. We could then try to come up with a validation
rule, a dynamic record source for the combobox, or vba code to do the trick.

You'll have to spell out the IFs that you use when you decide what is valid
for a given record. If you can tell us in English, someone (probably not
me) can most likely turn it into an sql statement that can be used as the
record source for your combobox, or vba code that will do the trick.

Map it out and see where it leads.

Rick B


to
do, try
to

 

[Pat Hartman]

I keep an ActiveFlg in my lookup tables. When a value is no longer used for
new records I change the activeFlg value from True to False. In the
BeforeUpdate event of the lookup field on a form, I check the ActiveFlg and
if it is False, prevent it from being selected.