Populate secondary table from table with foreign key

Hi,

I'll try to phrase this question correctly. I need to Have a link to a
table that contains store numbers and addresses. I want this table to be able
to be populated by adding data from the first table containing the foreign
key. Whenever I setup a link, it operates as follows. I cannot add data to
the Store# field, and it claims I have to add it to the referenced Store
table. How do I set this up?

Thanks,

Tom
--
When eveythings coming at you at once, you're in the wrong lane.

=?Utf-8?B?VG9t?= wrote in
:

I want this table to
be able to be populated by adding data from the first table containing
the foreign key. Whenever I setup a link, it operates as follows. I
cannot add data to the Store# field, and it claims I have to add it to
the referenced Store table. How do I set this up?


You don't. You store information about Stores in the Stores table, and you
store Address stuff in the Addresses table. If the Addresses table has a
field called BelongsTo that references the StoreNumber field in the Stores
table, then that is what is called a foreign key.

Addresses
========= Stores
*AddressID ======
BelongsTo ------ *StoreNumber
TopLine CommercialName
SecondLine PrimaryContact
ThirdLine etc...
PostCode
PhoneNumber


It's then easy to create a query that places the Stores.CommercialName
above the Addresses.TopLine, or whatever you want to do. But you don't want
to put address stuff in the Stores table, nor stores stuff in the Addresses
table. Ever.

Hope that helps


Tim F

Tom,

It appears that you have two tables between which there is a one-to-many
relationship. For example you have a Stores table, where the
StoreNumber (note that it is not a good idea to use a # as part of a
field name) field is the Primary Key, and the other table has the
StoreNumber as a foreign key field. There can be more than one entry in
the second table relating to the same Store. Am I right? In these
sorts of scenarios, it generally works best if your record is already
entered and saved in the table on the "one" side of the relationship
(Stores) before you try to add the related record to the table on the
"many" side.

Can you please post back with more details and examples if you need more
specific help. Thanks.

--
Steve Schapel, Microsoft Access MVP


Tom wrote:
Hi,

I'll try to phrase this question correctly. I need to Have a link to a
table that contains store numbers and addresses. I want this table to be able
to be populated by adding data from the first table containing the foreign
key. Whenever I setup a link, it operates as follows. I cannot add data to
the Store# field, and it claims I have to add it to the referenced Store
table. How do I set this up?

Thanks,

Tom

Hi Steve,

I think you've got how I set it up exactly. What I want to do is
to have a form, with a "StoreNumber" field that users can add a new store
number to. Currently, I've done away with ther link between the "Stores"
table and the "Transactions" table, so new store numbers can be added in the
"StoreNumber" field.
Is there any way to do that? so that when a new store number is
added to that field, it populates the related tabel with just a store number?
or even the Store number, address, etc. ? Let me know if I'm not being
clear enough on what I want.

Thanks,

Tom

"Steve Schapel" wrote:

Tom,

It appears that you have two tables between which there is a one-to-many
relationship. For example you have a Stores table, where the
StoreNumber (note that it is not a good idea to use a # as part of a
field name) field is the Primary Key, and the other table has the
StoreNumber as a foreign key field. There can be more than one entry in
the second table relating to the same Store. Am I right? In these
sorts of scenarios, it generally works best if your record is already
entered and saved in the table on the "one" side of the relationship
(Stores) before you try to add the related record to the table on the
"many" side.

Can you please post back with more details and examples if you need more
specific help. Thanks.

--
Steve Schapel, Microsoft Access MVP


Tom wrote:
Hi,

I'll try to phrase this question correctly. I need to Have a link to a
table that contains store numbers and addresses. I want this table to be able
to be populated by adding data from the first table containing the foreign
key. Whenever I setup a link, it operates as follows. I cannot add data to
the Store# field, and it claims I have to add it to the referenced Store
table. How do I set this up?

Thanks,

Tom

Hi Tim,

Can I name the "BelongsTo" field "StoreNumber" instead? So the one
to many relationship would be 1(primary key) to many(foreign key)? Is that
correct?

Thanks,

Tom

"Tim Ferguson" wrote:

=?Utf-8?B?VG9t?= wrote in
:

I want this table to
be able to be populated by adding data from the first table containing
the foreign key. Whenever I setup a link, it operates as follows. I
cannot add data to the Store# field, and it claims I have to add it to
the referenced Store table. How do I set this up?


You don't. You store information about Stores in the Stores table, and you
store Address stuff in the Addresses table. If the Addresses table has a
field called BelongsTo that references the StoreNumber field in the Stores
table, then that is what is called a foreign key.

Addresses
========= Stores
*AddressID ======
BelongsTo ------ *StoreNumber
TopLine CommercialName
SecondLine PrimaryContact
ThirdLine etc...
PostCode
PhoneNumber


It's then easy to create a query that places the Stores.CommercialName
above the Addresses.TopLine, or whatever you want to do. But you don't want
to put address stuff in the Stores table, nor stores stuff in the Addresses
table. Ever.

Hope that helps


Tim F

You can call it whatever you want.

What you need to do though is separate your entry form
into two parts. The main form where the user enters the
store number (linked to the stores table) and a subform
where they enter whatever details you need (linked to the
details table).

Then when the user enters a store number that is not in
the stores table, pop up an entry form for details that
you need to keep for the new store. On this new form you
can have a button which they click to "add record" and in
the onclick event for that button, type in

If Me.Dirty then
Me.Dirty = false
endif

DoCmd acCmdClose

which will save the new record and close the popup (I
think).

Alternatively, pop up a message box stating that the store
number does not exist and have an "add record" button on
your current form for the above purpose. Either way, you
need to get details for the new store record separate from
whatever you are entering.


-----Original Message-----
Hi Tim,

Can I name the "BelongsTo" field "StoreNumber"
instead? So the one
to many relationship would be 1(primary key) to many
(foreign key)? Is that
correct?

Thanks,

Tom

"Tim Ferguson" wrote:

=?Utf-8?B?VG9t?= wrote
in
news:B99C6B98-C081-4D0E-87BF-
:

I want this table to
be able to be populated by adding data from the first
table containing
the foreign key. Whenever I setup a link, it operates
as follows. I
cannot add data to the Store# field, and it claims I
have to add it to
the referenced Store table. How do I set this up?


You don't. You store information about Stores in the
Stores table, and you
store Address stuff in the Addresses table. If the
Addresses table has a
field called BelongsTo that references the StoreNumber
field in the Stores
table, then that is what is called a foreign key.

Addresses
========= Stores
*AddressID ======
BelongsTo ------ *StoreNumber
TopLine CommercialName
SecondLine PrimaryContact
ThirdLine etc...
PostCode
PhoneNumber


It's then easy to create a query that places the
Stores.CommercialName
above the Addresses.TopLine, or whatever you want to
do. But you don't want
to put address stuff in the Stores table, nor stores
stuff in the Addresses
table. Ever.

Hope that helps


Tim F


.

Tom,

There are a number of approaches to this type of situation, depending on
the real-life data management processes. From what you have said so
far, it sounds like a good approach would be like this... On your
Transactions form, have your StoreNumber field represented by a
combobox, whose Row Source is the Stores table. And put a little
command button on the form, possibly in the form header section. Then,
when you need to enter a Transaction for a Store which so far does not
exist in the Stores table, click the Command Button, and it will open a
separate form bound to the Stores table, at a new record, where you can
enter the new Store. The only thing you will need then is to Requery
the StoreNumber combobox on the Transacations form, so the drop-down
list includes the newly added Store. This could be done on the Close
event of the Stores form, the Activate event of the Transactions form,
or the Enter event of the StoreNumber combobox.

--
Steve Schapel, Microsoft Access MVP


Tom wrote:
Hi Steve,

I think you've got how I set it up exactly. What I want to do is
to have a form, with a "StoreNumber" field that users can add a new store
number to. Currently, I've done away with ther link between the "Stores"
table and the "Transactions" table, so new store numbers can be added in the
"StoreNumber" field.
Is there any way to do that? so that when a new store number is
added to that field, it populates the related tabel with just a store number?
or even the Store number, address, etc. ? Let me know if I'm not being
clear enough on what I want.

Thanks,

Tom

"Steve Schapel" wrote:


Tom,

It appears that you have two tables between which there is a one-to-many
relationship. For example you have a Stores table, where the
StoreNumber (note that it is not a good idea to use a # as part of a
field name) field is the Primary Key, and the other table has the
StoreNumber as a foreign key field. There can be more than one entry in
the second table relating to the same Store. Am I right? In these
sorts of scenarios, it generally works best if your record is already
entered and saved in the table on the "one" side of the relationship
(Stores) before you try to add the related record to the table on the
"many" side.

Can you please post back with more details and examples if you need more
specific help. Thanks.

--
Steve Schapel, Microsoft Access MVP


Tom wrote:

Hi,

I'll try to phrase this question correctly. I need to Have a link to a
table that contains store numbers and addresses. I want this table to be able
to be populated by adding data from the first table containing the foreign
key. Whenever I setup a link, it operates as follows. I cannot add data to
the Store# field, and it claims I have to add it to the referenced Store
table. How do I set this up?

Thanks,

Tom