One-to-Many relationships

[TracyM via AccessMonster.com]

I have a new database that has a main table (Contacts) with Contact ID as the
primary key/autonumber.

I also have another table (Resource) and have it linked to the main in a one-
to-many relationship, it also has a Contact ID as an autonumber, but it isn't
set as a primary key.

I have put (Resource) as a sub-form in my main table form, where the wizard
asks which fields are linked, and I put in the contact ID for each table.

What I am doing is, in the main table capturing contact information from
suppliers, and in the Resource table I capture information each time there is
an update with that contact. So I need to add many resource records to one
record in Contacts.

The problem is when I add a record to the main form, I can add a resource to
the sub form, but it will only let me add one record, as soon as I try and
add another it comes up with an error saying there needs to be a matching
record in the main form.

I have also noticed that the autonumber in the sub form does not match the
auto number in the main form. I have deleted all the records and compiled
database to start again, but it still has the same problem.

I have two other databases with this kind of set up and dont have any
problems at all, so I am not sure what I am doing wrong, have spent all
morning looking through to compare each database to see what differences I
can see and they all look the same.

Can any help please, I'm sure its something really simple

Thanks
Tracy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Keith Wilby]

"TracyM via AccessMonster.com" <u31966@uwewrote in message
news:75b2537d66794@uwe...

>
The problem is when I add a record to the main form, I can add a resource
to
the sub form, but it will only let me add one record, as soon as I try and
add another it comes up with an error saying there needs to be a matching
record in the main form.

I have also noticed that the autonumber in the sub form does not match the
auto number in the main form. I have deleted all the records and compiled
database to start again, but it still has the same problem.

It sounds like you haven't set up the parent/child link between the main and
sub. Select the subform object on the main form and check on the "data"
property tab.

HTH - Keith.
www.keithwilby.com

[TracyM via AccessMonster.com]

Hi Keith

Thanks for your reply, I have checked and I have the following set up:

Master field = Contact ID
Child Field = Contact ID
(Result= Show tblResource for each record in tblContacts using Contact ID)

Is the problem that I am only working with a couple of records at the moment
to test the database before importing the actual data.

But its still a problem with the numbering not matching as it does in my
other databases with the same set up?

Regards

Keith Wilby wrote:

>The problem is when I add a record to the main form, I can add a resource
to

[quoted text clipped - 5 lines]

>auto number in the main form. I have deleted all the records and compiled
database to start again, but it still has the same problem.

It sounds like you haven't set up the parent/child link between the main and
sub. Select the subform object on the main form and check on the "data"
property tab.

HTH - Keith.
www.keithwilby.com

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[paii, Ron]

"TracyM via AccessMonster.com" <u31966@uwewrote in message
news:75b2537d66794@uwe...

I have a new database that has a main table (Contacts) with Contact ID as

the

primary key/autonumber.

I also have another table (Resource) and have it linked to the main in a

one-

to-many relationship, it also has a Contact ID as an autonumber, but it

isn't

set as a primary key.

The Contact ID in your Resorce table should be a Long Integer not
autonumber. When linked, it's value will be filled-in from the Contact table
though the sub form when you add a new record.

>
I have put (Resource) as a sub-form in my main table form, where the

wizard

asks which fields are linked, and I put in the contact ID for each table.

What I am doing is, in the main table capturing contact information from
suppliers, and in the Resource table I capture information each time there

is

an update with that contact. So I need to add many resource records to one
record in Contacts.

The problem is when I add a record to the main form, I can add a resource

to

the sub form, but it will only let me add one record, as soon as I try and
add another it comes up with an error saying there needs to be a matching
record in the main form.

I have also noticed that the autonumber in the sub form does not match the
auto number in the main form. I have deleted all the records and compiled
database to start again, but it still has the same problem.

I have two other databases with this kind of set up and dont have any
problems at all, so I am not sure what I am doing wrong, have spent all
morning looking through to compare each database to see what differences I
can see and they all look the same.

Can any help please, I'm sure its something really simple

Thanks
Tracy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Tom van Stiphout]

On Wed, 25 Jul 2007 11:00:28 GMT, "TracyM via AccessMonster.com"
<u31966@uwewrote:

That's wrong. Resource.ContactID should be a Number type, Long Integer
length, not an Autonumber.
Just think about it: you want a resource record to be associated with
a Contact of your choosing, not with some meaningless sequential
number.

-Tom.

>I have a new database that has a main table (Contacts) with Contact ID as the
primary key/autonumber.

I also have another table (Resource) and have it linked to the main in a one-
to-many relationship, it also has a Contact ID as an autonumber, but it isn't
set as a primary key.

<clip>

[TracyM via AccessMonster.com]

Hi

Thanks, that worked a treat, the only reason I didn't do that is because in
my other databases I have them on autonumber and don't have a problem with it
for some reason.

Anyway, thats a great help Thanks again

Tracy

paii, Ron wrote:

>I have a new database that has a main table (Contacts) with Contact ID as the
primary key/autonumber.

I also have another table (Resource) and have it linked to the main in a one-
to-many relationship, it also has a Contact ID as an autonumber, but it isn't
set as a primary key.

The Contact ID in your Resorce table should be a Long Integer not
autonumber. When linked, it's value will be filled-in from the Contact table
though the sub form when you add a new record.

>I have put (Resource) as a sub-form in my main table form, where the wizard
asks which fields are linked, and I put in the contact ID for each table.

[quoted text clipped - 22 lines]

>Thanks
Tracy

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Tom van Stiphout]

On Wed, 25 Jul 2007 14:07:38 GMT, "TracyM via AccessMonster.com"
<u31966@uwewrote:

That cannot be true. An Autonumber foreign key is always wrong.
-Tom.

>Hi

Thanks, that worked a treat, the only reason I didn't do that is because in
my other databases I have them on autonumber and don't have a problem with it
for some reason.

Anyway, thats a great help Thanks again

Tracy

paii, Ron wrote:

>>I have a new database that has a main table (Contacts) with Contact ID as the
primary key/autonumber.

I also have another table (Resource) and have it linked to the main in a one-
to-many relationship, it also has a Contact ID as an autonumber, but it isn't
set as a primary key.

The Contact ID in your Resorce table should be a Long Integer not
autonumber. When linked, it's value will be filled-in from the Contact table
though the sub form when you add a new record.

>>I have put (Resource) as a sub-form in my main table form, where the wizard
asks which fields are linked, and I put in the contact ID for each table.

[quoted text clipped - 22 lines]

>>Thanks
Tracy