non selectable table values

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.

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" wrote in message
...
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.

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" wrote:

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

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


"meilani" wrote in message
...
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" wrote:

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

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" wrote in message
...
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" wrote:

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

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" wrote:

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


"meilani" wrote in message
...
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" wrote:

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

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



"meilani" wrote in message
...
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" wrote:

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


"meilani" wrote in message
...
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" wrote:

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

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.
"meilani" wrote in message
...
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.