Prev: Option Group, Unbound Form: 2467 Object closed or doesn't exist?
Next: Access 2002 question
From: yorkielover02 on 12 Apr 2010 11:10
I have two tables. both with store id fields that i would like to link the
two tables together by. At this point there is a one to many relationship
that should allow for anytime a new store is added that store id is added to
the it table. At this time anytime i add a new store id to the store table it
is not creating a new store in the it table. Any ideas as to why?
From: Al Campagna on 12 Apr 2010 11:46
yorkielover02,
You can't maintain relationships by directly editing tables.
That the job of a form, which maintains both tables as data is
entered. Working directly with related table/s is will not work.

Create a form, the main part of which will be based on your primary
table (the ONE table).
Create a subform, based on the related table (the MANY table).
Besides relating them in Relationships, relate them on the form by using
the Parent/Child link.
Now, whenever you add a new ONE store, the subform will be ready to
accept realted MANY records... whether you enter any now... or later.
--
hth
Al Campagna
Microsoft Access MVP 2007-2009
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

"yorkielover02" <yorkielover02(a)discussions.microsoft.com> wrote in message
news:FAD3F470-39AD-4533-99EE-A64F5F5E351C(a)microsoft.com...
>I have two tables. both with store id fields that i would like to link the
> two tables together by. At this point there is a one to many relationship
> that should allow for anytime a new store is added that store id is added
> to
> the it table. At this time anytime i add a new store id to the store table
> it
> is not creating a new store in the it table. Any ideas as to why?


From: KARL DEWEY on 12 Apr 2010 12:21
You seem to have a 'store' table and 'it table'. I assume that the 'store'
table is the one side and that is where you are adding a new record and
expecting it also to appear in the 'it table'. One-to-many relationships do
not work that way.

You also can not add to the many side without first having a record on the
one side.

Maybe I misunderstood you, if so then try and explain it another way for me.

--
Build a little, test a little.


"yorkielover02" wrote:

> I have two tables. both with store id fields that i would like to link the
> two tables together by. At this point there is a one to many relationship
> that should allow for anytime a new store is added that store id is added to
> the it table. At this time anytime i add a new store id to the store table it
> is not creating a new store in the it table. Any ideas as to why?
From: John W. Vinson on 12 Apr 2010 13:49
On Mon, 12 Apr 2010 08:10:01 -0700, yorkielover02
<yorkielover02(a)discussions.microsoft.com> wrote:

>I have two tables. both with store id fields that i would like to link the
>two tables together by.

Is the StoreID an Autonumber in both tables? If so, it will NOT WORK for what
you're proposing.

>At this point there is a one to many relationship
>that should allow for anytime a new store is added that store id is added to
>the it table. At this time anytime i add a new store id to the store table it
>is not creating a new store in the it table. Any ideas as to why?

Relationships don't work that way: a relationship will *prevent* an invalid
record from being added, but it won't (and needn't and shouldn't)
automagically create a new record. What is the "it" table? What information is
stored in the stores table, and what is in this table? What real-life "entity"
(person, thing, or event) does each table model? More info please!
--

John W. Vinson [MVP]
From: Melissa Brown Melissa on 12 Apr 2010 15:40
Maybe I have been reading too long, but which should be the 'one' table? My
example: cuptomers making annula reservations. Is the customer the one table?
should the 'many' be the reservations? or vice versa? The reservations
change, the customer stays the same. If I am making a form for reservations,
does that make the customer the parent on the form or is the reservation form
the parent and then the customer the child?

Help. Al.

"Al Campagna" wrote:

> yorkielover02,
> You can't maintain relationships by directly editing tables.
> That the job of a form, which maintains both tables as data is
> entered. Working directly with related table/s is will not work.
>
> Create a form, the main part of which will be based on your primary
> table (the ONE table).
> Create a subform, based on the related table (the MANY table).
> Besides relating them in Relationships, relate them on the form by using
> the Parent/Child link.
> Now, whenever you add a new ONE store, the subform will be ready to
> accept realted MANY records... whether you enter any now... or later.
> --
> hth
> Al Campagna
> Microsoft Access MVP 2007-2009
> http://home.comcast.net/~cccsolutions/index.html
>
> "Find a job that you love... and you'll never work a day in your life."
>

 |  Next  |  Last
Pages: 1 2
Prev: Option Group, Unbound Form: 2467 Object closed or doesn't exist?
Next: Access 2002 question