Lookup problem in Subform

[Fred Worthington]

Greetings:

I have a table called Client using a combo box as the "display control."
The "row source" is another table called Mission. The Mission table
contains a list of items. I have created a subform whose "record source" is
the Client table, which neatly displays the items contained in the Mission
table. I have placed a command button on the main form that opens the
Mission table in datasheet view to allow users to edit the items in the
list. Here is the problem: when an item is removed from the list, it is
also removed from all records that previously displayed this item in the
subform (the form is blank and the table displays the now obsolete "primary
key"). This is not good, because, whereas an item may not currently be
viable, an archive of the item is. Is there a way to remove an item from
the Mission table list (whereby it is no longer an option for current
records) but remains as an item in the Client table from which a historical
perspective can be drawn?

Thanks . . . Fred

 

[Jeff Boyce]

Fred

I don't fully understand your situation, but it occurred to me that you
don't actually need to "remove" an item from the Mission table if you simply
mark it as "archived", or "discontinued" or "completed" (whatever fits your
situation). If you add a Yes/No field to that table, and mark a row "Yes",
you can use a query to leave out that row out of combo boxes, forms, etc.,
but still use the information from the row to show "historical" information.

Good luck

Jeff Boyce
<Access MVP>

 

[Fred Worthington]

Jeff

Thanks for your response.

The problem with "flagging" obsolete items (and not removing them from the
list) is that eventually the list of options in the form would become quite
cumbersome. I have several other forms set up the same way, so I am highly
motivated to find a way to retain these items in the Client table after they
have been removed from the Source table. There seem to be a number of
options regarding the way the form allows data entry (e.g., limiting entries
to items on the list, which is desirable), so it just seems like there would
be a way to keep an entry (selected from the list) from disappearing from
the Client table when it is removed from the Source table (especially since
it retains, in its place, the removed item's autonumber ID).

I am using this combination of two tables and a subform because I thought it
was a better way to record and store my data. If I set up my table with
"display control" = Combo Box, "row source type" = Value List, and type the
items into the "row source," I can retain data already entered even after it
has been removed from the row source, but this method is restrictive and
impractical for users.

Is there perhaps another "row source" (such as a query) that can be used for
the list that would not be so tightly integrated with my Client table?

Thanks again for your help.

Fred

 

[Jeff Boyce]

Fred

Please re-read my response -- I suggested that you can modify the current
queries you use as sources for your combo boxes (?list boxes) by excluding
the archived items. There's no reason why the archived rows need to show up
in list boxes, combo boxes, or your forms.

Also, if you have used the "Value List" approach to filling a combo/list
box, you've just encountered one of the issues of doing so. As the
underlying values change, you have to modify each/every Value List.

If, instead, you use a query to provide the values in a combo box, any
change in the underlying table (a new item added to a table of Products) is
immediately reflected in any/all combo boxes based on a query on that table.
"Value List" works fine for short, unchangeable lists of possibilities --
say, gender (M, F).

When you say "the list of options in the form would become quite
cumbersome", does that mean you are, literally, using an option group?
Typically, if there are more than 3 choices, it takes less room on the form
to use a combo box and is less confusing for the user (form designers will
differ on the magic number of choices that trigger this).

Good luck!

Jeff Boyce
<Access MVP>

 

[Fred Worthington]

Thanks Jeff,

As you suggested, I re-read your message and realize that I failed to
clarify that I am not using a query to provide values for my combo box, and
therein may lie the root of my problem. Currently, I am simply using
another table (Mission table) as the "row source" for my Client table. The
Client table is the one I will subsequently use to create my reports because
it is the one on which my combo box subform is based. One of my objectives
in using another table for the list items was to simplify updating and
editing that list for the user (which ultimately will not be me). I have
created a command button on the form that opens the Mission table in
datasheet view for that purpose. However, as we both know, this won't work
as long as items removed from the Mission table are automatically removed
from the Client Table as well. For the reasons you indicated, I have ruled
out using the Value List approach. I am not using an Option Group either.
One reason I chose the combo box for my list is because it will accomodate
multiple entries without having to display them all.

Unfortunately, I haven't had much spare time this weekend to work on this
issue. I will try setting up a query to provide my combo box values and let
you know how that works. Thanks again for your help.

Fred