Can't assign value

[CW]

I have an Agents table and form, and an AgentContacts table and a subform
that sits on the bottom of each Agent form so that we can maintain details of
the people we deal with at each agent - sometimes several.
I have a problem in that we cannot create more than one contact for each
agent - otherwise as soon as we enter the first field we get the error msge
"You cannot assign a value to this object".
The master and child fields are the AgentIDs.
I have a one-to-many relationship between AgentID in the tables, and this is
an autonumber and is the primary key, indexed with no duplicates.
Could this be the root of the problem, that creating multiple contacts would
create duplicate AgentIDs?
If so, how can I get around it?
Many thanks
CW

 

[Tom Wickerath]

AgentID needs to be a long integer (not an Autonumber) foreign key in the
child (many-side) table. It sounds like you may have set AgentID in the
AgentContacts table as a primary key, in which case you now have a one-to-one
relationship, not one-to-many. You would want to add an AgentContactID
primary key to the child table.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CW]

Tom - many thanks for your assistance. I created a new field AgentContactID
and tried to set this as the primary key but Access says I cannot have this
as the primary key because it cannot contain null values. Where from here??
Many thanks
CW

 

[Tom Wickerath]

Hi CW,

Set the AgentContactID field as an autonumber. This field will likely not be
joined to any other fields. In Relationships view, create a relationship with
enforced referential integrity (RI) between the AgentID primary key in the
Agents table, and the AgentID long integer (not autonumber) foreign key in
the AgentContacts table.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[CW]

This is driving me crazy! (How many times have you heard that, as an MVP??!!)
I followed your advice, but firstly could not set the RI as it said that the
data would have to be fixed in order for it to work (and I've got thousands
of records so this is not viable). So I set the relationship without RI.
Then I tried entering a second contact on the form and got an error msge
saying that my changes to the table were not successful cos ould not be saved
(the input of the new contact)because this would create duplicatesagain,
telling me that they would create dupes in the index, primary key or
relationship.
I went back to the AgentContacts table and tried to unset the index or to
have it there but with Duplicates OK, and it says that I must remove the
primary key in order to be able to do this.
I'm in a loop here, it seems.
You have helped me several times in the past Tom and not failed me yet, so
I'm sure you'll come up with something further on this but heck, it is
frustrating!!
Thanks again
CW

 

[CW]

Whoa! Forget the message of frustration - I exited and re-opened the mdb and
lo and behold it works!!! Sorry to have wasted your time, I should have
done that before of course, but at least we have the solution.
Many thanks again, Tom
CW

 

[Tom Wickerath]

Hi CW,

This is driving me crazy! (How many times have you heard that, as an MVP??!!)

Lots of times. <smile> When I first started working with Access (1994), I
felt like a fish out of water flopping around for several years. So I
certainly have an appreciation for the frustrations you are experiencing now.

I followed your advice, but firstly could not set the RI as it said that the
data would have to be fixed in order for it to work (and I've got thousands
of records so this is not viable).

Not good. However, the solution may be easy if the numeric AgentID foreign
key has a default value of zero (remove this default value in table design
view) that is likely unmatched in the parent table. If that is the case, we
can design an update query pretty easily, to update all those records to
null. Here's why I say it is not good: A relationship without enforced
referential integrity is little more than an exercise in drawing lines. So,
this problem really does need to be fixed.

Is there any way that you can send me a compacted and zipped copy of your
database, so that I can help you fix this RI issue, and the other error that
is giving you a problem? Normally, I have folks clear out all the data, in
case any of it is considered sensitive, but if you are battling a RI problem,
then one would need to have the data present. If you can do this, send me a
private e-mail message with a valid reply-to address. My e-mail address is
available at the bottom of the contributor's page indicated below. Please do
not post your e-mail address (or mine) to a newsgroup reply. Doing so will
only attract the unwanted attention of spammers. Your data will be treated as
absolutely confidential.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Cool! Glad to hear this.
Now would be a good time to compact your database (Tools > Database
Utilities > Compact and repair database), and to make a backup copy for safe
keeping.

The issue about unmatched child records, which is preventing you from
enforcing RI (referential integrity) really does need to be fixed. Oh, and
you shouldn't need to set an index at all on the AgentID foreign key in the
many side table. This field will actually get indexed automatically by JET,
after you are successful in creating a relationship with enforced RI.
However, this index will not show up in the normal Indexes view, in table
design.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________