How do I preserve existing entries in a combo box?

[Guest]

I have two tables, one with names and another with email addresses. The first
table has a link to the second in many-to-one relationship (many names can
have the same email but each name is only allowed one email).

I've created a query containing all fileds of both tables. I have created a
form based on this query. I have made the email address entry box a combo box.

What I want is to be able to enter records of names and emails. Ok. Now the
problem occurs if I enter more than one name with the same email and then
later try to change the email of one of these names without causing the
emails of all the names to change.

How do I make a combo box force a new row in the tables entry with new ID
and link to the name whilst preserving the other names IDs which were
pointing at the original email name?

 

[Guest]

You need to change your table relations. There can be no such thing in a
database as a many to many relation. It is always necessary to have an
intermediate table that resolves that problem.
Based on your statement that one name can have one email address, and an
email address can be shared by multiple name, you need to establish the
relationship so that the email address table is the one side and the names is
the many side.

 

[Guest]

I may not have been clear. I have a one-to-many relationship set up.
Suppose you have two names in one table, Frank and John, linked to a single
email address in the second table. Some time later, Frank gets a new email,
but John keeps his old one. When I modify the email address of Frank in my
form, the email of John also changes because both Frank and John have the
same key to the same email entry.
Another way of saying what I need is a combo box where a new field entry
does not overwrite an exisiting field entry, but instead creates a new row in
the table and leaves the exisiting key link in place for other names that
shared the original field entry.

 

[Guest]

I understand what you are looking for. This post reinforces what I said
earlier. Your relationship is upside down. If you had the E-mail on the one
side and the names on the many side, then you would not have that problem.

 

[Guest]

The relationship is as you prescribe already. Many on the name side, one on
the email side.

The problem is more subtle than this. The names table has two fields: name &
email_lookup_ID. The email table has two fields: ID & emailAddress. Imagin we
start with the names table containing:
Frank, 1
John, 2
Harry, 1

and the email table containing:
1, (e-mail address removed)
2, (e-mail address removed)

Note that frank and Harry both have the same email address - this is the
many to one relationship. Now, I have a single form which has entry boxes for
the name and emailAddress fields of both tables, via a query. The email
address box is a combo box. Harry tells me that he has got a new email
address but Frank's has not changed. I open the form, select the record for
Harry and use the combo box to change his email. It is a brand new entry so I
have to type it in to the combo box.

This appears fine, but the tables now look like:
Frank, 1
John, 2
Harry, 1

and:
1, (e-mail address removed)
2, (e-mail address removed)

Frank's original address has been overwritten. This is because the combo box
thinks I want to change the original email address rather than add a new
address and a new ID.

Get it?

 

[Guest]

Okay, your original post said it was many to many. I see the problem, I
think. Normally in a one to many, you start with the one side and make
changes to the many side. Here you are starting with the many side and want
to change the one side. Since the email is the one side, when you make a
change to it, it is cascading the updates to all the child records for the
email address. To change an E-mail address for a name and avoid the problem
you are having, you need this sequence of events:
1. Create a new record in the email table
2. Requery the combo box
3. Change the email_lookup_ID in the name table

I think this should happen in the Not In List Event of the Combo box