Idiot question

[Saylindara]

This is a question so fundamental I'm embarrassed to ask it and believe it or
not I have searched in books and tried deconstructing Northwind but I am
missing something vital in my quest to learn Access. I imagine it is so
obvious to everyone else.

If I have, say, tables: Orders, Products, Order details (the link table)
with appropriate primary and foreign keys, how do I get the information I
enter on one table to be simultaneously entered on another?

 

[Wayne-I-M]

It's not an idiot question - any thing is easy "if" you know the answer.

The basic answer is = you don't.

If you update a table thats all you are doing (in this case).

If you have 2 linked tables you need to tell your application the foreign
key you want to add.

BUT - with access this is easy. If you create a form based on a table and
include a subform form based on an other form (with the primary and foreign
keys linked in the form) then access will add the foreign key to your subform
for each new record you create - so you don't need to worry about.

Of course if you were to add details directly into the table (which you
should never do by the way) you would need to type in the foreign key in the
table.

Good luck with your application

 

[Saylindara]

Thanks very much for your help. I'll give it a try.

 

[BruceM]

Just to add some detail to the previous reply, the subform is contained
within a box on the main form. That box is the subform control. Right
click the very edge of the subform control and select Properties. The Link
Child and Link Master properties need to be set to the linking field. If
you click the three dots Access will generally make the correct choice for
you, assuming hte table relationship is set up properly.
If you use the wizard I think that sort of thing will be set up
automatically, but I haven't used the wizard lately, so I'm not sure of the
details.

 

[Wayne-I-M]

Good points - I should has remembered to put something like that

ooops

 

[John W. Vinson]

If I have, say, tables: Orders, Products, Order details (the link table)
with appropriate primary and foreign keys, how do I get the information I
enter on one table to be simultaneously entered on another?

You don't, and you needn't.

 

[Saylindara]

Thanks for your help

Just to add some detail to the previous reply, the subform is contained
within a box on the main form. That box is the subform control. Right
click the very edge of the subform control and select Properties. The Link
Child and Link Master properties need to be set to the linking field. If
you click the three dots Access will generally make the correct choice for
you, assuming hte table relationship is set up properly.
If you use the wizard I think that sort of thing will be set up
automatically, but I haven't used the wizard lately, so I'm not sure of the
details.

 

[Saylindara]

I feel sure that sooner or later I'm going to get a "oh now I see - it's so
obvious" moment but I haven't got it yet. Taking Northwind as an example,
what is entered on one table is replicated in another. How and, from what you
say, why do they do that?

 

[John W. Vinson]

I feel sure that sooner or later I'm going to get a "oh now I see - it's so
obvious" moment but I haven't got it yet. Taking Northwind as an example,
what is entered on one table is replicated in another.

No. It isn't.

What you are (probably) seeing is Lookup Fields.

The looked-up value (the product name, let's say) *APPEARS* to be in the
OrderDetails table. But it *isn't* in the OrderDetails table!

What's in the OrderDetails table is a numeric ID (concealed from view). This
is *shown on screen* as if it were the product name, but the product name is
being "looked up" from the Prducts table.

If this example doesn't make sense please post specifics of what data in
Northwind is being replicated from one table into another.

 

[Saylindara]

Thanks for taking the trouble to reply. I'll explore further and post again
if I can't figure it out.

 

[Saylindara]

I have tried to see if I can make sense of this. What I am actually trying to
do is set up a database pulling together lots of information about employees
which will involve several many-to-many relationships. As an example,
employees and courses. I have 3 tables, simplified below:

employeeID
Employee

CourseID
Course

EmployeeID
CourseID

The latter being the link table.

I would like to set up 2 forms -

Employee form with a sub form to enter courses that employee attends
Course form with a sub form to enter the employees that did that course

Is that possible? How do you tie in the link table? I have already entered
the name of some course into the course table and some employees into the
employee table. How do they then appear on the link table?

 

[John W. Vinson]

I have tried to see if I can make sense of this. What I am actually trying to
do is set up a database pulling together lots of information about employees
which will involve several many-to-many relationships. As an example,
employees and courses. I have 3 tables, simplified below:

employeeID
Employee

CourseID
Course

EmployeeID
CourseID

The latter being the link table.

I would like to set up 2 forms -

Employee form with a sub form to enter courses that employee attends
Course form with a sub form to enter the employees that did that course

Is that possible? How do you tie in the link table? I have already entered
the name of some course into the course table and some employees into the
employee table. How do they then appear on the link table?

The Northwind analogy would be: Employees = Orders; Courses = Products; your
link table = OrderDetails.

This is a classic many to many, and you're on the right track! What you need
to do is have a Form based on the Employee table (into which you can enter
employee personal details), with a Subform based on the link table. The
Master/Child Link Field of the subform would be the EmployeeID; on the subform
you would have a combo box based on Courses which would let you select which
course the employee is enrolling in. This combo could have code in its
NotInList event to open the Courses data entry form if you want to enrol the
employee in a course which has not yet been defined in the database.

The flipside would be a Form based on Courses, with a subform (again) based on
the link table. The master/link field would now be the CourseID, and you'ld
have a combo to select the employee. The combo would *store* the employeeID
but display the employee's name.

 

[Saylindara]

Thanks again. I really appreciate your taking the trouble to reply. I'll have
another go.

 

[Saylindara]

It's taken me a couple of days to psych myself up to try this as I was
Accessed out and just felt like kicking the PC to death. I braced myself for
further frustration and disappointment. but I've now done as you said and it
has worked beautifully. Daylight has dawned. I am exceedingly grateful.

 

[John W. Vinson]

It's taken me a couple of days to psych myself up to try this as I was
Accessed out and just felt like kicking the PC to death. I braced myself for
further frustration and disappointment. but I've now done as you said and it
has worked beautifully. Daylight has dawned. I am exceedingly grateful.

Thanks so much for posting back, and I'm delighted that I was able to help.