Combo Box Lookup from value being inserted at the same time?
Darragh
I'm not sure if this is possible, but can one use a combo box lookup
for a value being currently inserted in the same tuple in an Access
table.
For example, say I had a table [ ID, PersonID, PersonAddressID ] which
was a product of an aggregate relation.
With foreign keys to a person table [PersonID]
And another FK to a person-Address table [PersonID, PersonAddressID]
If I'm looking up a value for the PersonAddressID, I want the list to
be based off the value currently selected as PersonID in the same
table (but obviously not saved to that table as it is currenlty also
being inserted). It is like a dynamic insert I suppose
Sorry if this is confusing - I get the feeling this may not be
possible. Prefer not to use code as I'd like the constraints hardwired
into the database.
Darragh
Banana
> Hi all.
>
> I'm not sure if this is possible, but can one use a combo box lookup
> for a value being currently inserted in the same tuple in an Access
> table.
It sounds like you're talking about using NotInList event to enable
adding new values to the same table where combobox is based on?
> For example, say I had a table [ ID, PersonID, PersonAddressID ] which
> was a product of an aggregate relation.
>
> With foreign keys to a person table [PersonID]
>
> And another FK to a person-Address table [PersonID, PersonAddressID]
The schema given doesn't really make sense. Why have two tables that
would contain [PersonID, PersonAddressID]? If the first table [ID,
PersonID, PersonAddressID] had other fields that weren't relevant to the
discussion and may be something more than "Person-Address" table, that's
fine but it still begs the question why can't the first table be:
[PersonAddressID, <other fields>]
while the Person-Address table would be:
[PersonAddressID, PersonID, <rest of address stuff>]
I would think we need to straight the schema out before we can even
consider the combobox problem, and probably will find that it's not needed.
> If I'm looking up a value for the PersonAddressID, I want the list to
> be based off the value currently selected as PersonID in the same
> table (but obviously not saved to that table as it is currenlty also
> being inserted). It is like a dynamic insert I suppose
By definition, combobox has its rowsource based on a table or a query.
Therefore, if the row does not exist in the table/query, then it cannot
be selected.
We certainly can make use of NotInList event to add a new row to the
same table where the combobox is based on, but for practical purposes,
they're usually useful when the combobox is based on a table/query that
is quite simple, maybe two-column lookup table whereas a more complex
table such as Person or Address may be more appropriately handled in a
pop up form.
> Sorry if this is confusing - I get the feeling this may not be
> possible. Prefer not to use code as I'd like the constraints hardwired
> into the database.
You're talking about UI (e.g. combobox displaying a row that's about to
be inserted), and wanting constraint to be enforced at the database
engine level? We need to be mindful of the context we're talking about
because obviously UI works on a different level, and does not interact
with the constraint being enforced by the engine. We can manipulate UI
wiht code to make insertion possible (e.g. the NotInList event I spoke
of) and in no way does it alter or affect the defined constraints being
enforced upon the table.
Darragh
Hi Banana. Thanks for your reply.
1. Schema - I'll explain my schema in a bit more detail. I'll modify
my original question accordingly to try make a bit more sense.
A person is a unique entity obviously. A person may be involved in
other legal cases against another person. For any particular instance
of a case, a person MAY have different address details (i.e they might
have different address/contact information in each case).
Hence, I have a person entity, a person-address entity and a
particular case entity. The person and person address entity are
aggregated together as one entity in my Extended Entity-Relationship
diagram.
2. PersonID, PersonAddressID issue you raised - I think your correct.
I've stuffed my schema somewhere. I will have to think about this a
bit more and get back to you about what my solution is about.
3. NotInList Event - I'm familiar with using that event but hadn't
thought about it in some time. Thanks for the heads up.
Darragh
Banana
> A person is a unique entity obviously. A person may be involved in
> other legal cases against another person. For any particular instance
> of a case, a person MAY have different address details (i.e they might
> have different address/contact information in each case).
>
> Hence, I have a person entity, a person-address entity and a
> particular case entity. The person and person address entity are
> aggregated together as one entity in my Extended Entity-Relationship
> diagram.
This is perfectly reasonable. The way I see it, you can do it in two ways:
1) store the Address in its own table, then relate people to each
address using a junction table (PersonAddress)
2) store Address data in the Persons table as a one-many relationship
between cases -> person (address is included in the person table, so
there's no Address or PersonAddress table anywhere)
3) make Address a child of Persons table which is also a child of
cases... so it's a one-many-many relationship
Both structures has their merits. #1 makes more sense if you need to be
able to track addresses or people independently and perhaps need some
information on who has lived at what address. #2 does not have that
information (well, it could be inferred but not as reliably because it's
not enforced by any constraint as is in #1) but is more straightforward
and appropriate when you really don't care who lived at what, only that
you need that data for each case. #3 is basically a variant of #2 to
cover possibility where you may need to track more than one addresses
per person/case but have no interest in tracking the full history of
where this person lived and when. #1 may help reduce redundancy by
allowing you to select address from a combobox where someone may have
already filled in for another person or another case, whereas #3 can
tell you which addresses belong to which individuals and may contain
different data which may be correct. (e.g. two people lived at same
address but at different time and it may have had changed between due to
say, ZIP code being redrawn for instance) while #2 is most loose and has
very little constraint.
Anyway, hope this helps a bit.
> 3. NotInList Event - I'm familiar with using that event but hadn't
> thought about it in some time. Thanks for the heads up.
No problem. Do feel free to post back with the updates. I'd love to hear
how it eventually turns out.
Darragh
I've trialled a variant of solution #3 you have talked about but have
run into some conceptual problems with that. It is important that I
track the address information on a case by case basis.
I haven't tried variation #1, but I think I will trial it. Will keep
you updated.
Darragh