Adding records to one table, while working in a related table

[T Payne]

What I need to do should be the simplest thing in the world, but I have
scoured the training pages, the help files, faqs, technical articles and
sample databases, and have found nothing. It may be that I am just using the
"wrong" terminology.

I am review editor for an academic journal. My Access database keeps track
of books received, and people willing to review these books. My job is to
get a reviewer together with a book.

In my "Books" table, each record is a book, and in each record there is a
field for "Authors." I have a related table for "Authors" that includes a
list of all authors and their contact details.

What I want to do is this:

When a book arrives, I want to enter its information INCLUDING AUTHOR(S) in
the "Books" table. I enter the Author information in a ComboBox that looks
at the Author's table for a list of possible values. I have the "Limit to
List" property on this Combo box set to "No." But when I enter a new Author,
the Author table is not updated. The new author is only listed with the
current book.

There must be some way to add a new "Author" record to my Authors table
whenever I enter a new Author to a record in the Books table.

There is probably some simple answer to this question. Thanks for any help.

T

When I enter a new Author

 

[Craig Alexander Morrison]

Use a form and subform.

The form containing data about the books (or authors) and the subform
containg data about the authors (or books).

Working with tables directly may seem quick and easy like a spreadsheet, but
in the end it will be very limiting.

This assumes you have a relation (normalised table) for Books and one for
Authors.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

 

[T Payne]

Thank you. I'm sure this is putting me on the right track. But I still can't
figure out how to make my "Authors" table update when I enter data into the
"Books" form.

The main form is "Books" and the subform is "Author subform." The "Author"
field in the "Books" form is linked to the "Authorname" field in the
subform. The subform is dependent on the Authorname field in the larger
"Authors" table. When I add a new name to the subform, the "Authors" table
adds a new record. Good! But the "Books" table is unaffected.

When I add the name to the Author field in the "Books" table the "Authors"
table is unaffected.

Surely I shouldn't have to enter the author data twice every time I enter a
book that has a new author.

What am I doing wrong?

T

 

[tina]

you need to be clear about the relationship between the Authors table and
the Books table first, and then design your forms accordingly. from your
description, it sounds like there is a one-to-many relationship between
Authors and Books; that is, on Author may have many books, but each Book is
written by only one author. if this is a correct description of the "real
world" relationship, then the relationship should be expressed in Access as

Authors.AuthorID 1:n Books.AuthorID

or to put it in relational terms:

ParentTable.PrimaryKeyField 1:n ChildTable.ForeignKeyField

In my "Books" table, each record is a book, and in each record there is a
field for "Authors." I have a related table for "Authors" that includes a
list of all authors and their contact details.

your above description sounds correct for the one-to-many relationship that
i outlined.

Craig is correct in advising you to use forms for data entry rather than
tables. let me add to that: if you have a Lookup field in your Books table
(or any other table), recommend that you get rid of the Lookup. for more
information, see http://home.att.net/~california.db/tips.html#aTip8.

okay, now that we've laid out the table design, let's look at forms. a
mainform/linked subform setup is as follows: main form is bound to the
*parent* table, and subform is bound to the *child* table. your setup is
backward, with the child table Books bound to the main form, and the parent
table Authors bound to the subform. you cannot force a mainform/subform link
to work that way. if you want to enter "Books" data in a form, and have the
ability to add data about a new author when the book's author is not in the
Authors table, then you need a different setup.

i recommend that you create a single form, bound to the Books table. use a
combo box control (with the *RowSource* property set to the Authors table),
to provide a list of all authors so you can assign an author to each new
book record. when the book's author is not listed in the combo box droplist,
you can use the combo box control's NotInList event to run code that opens
another form which is bound to the Authors table, add the new author record,
update the droplist with the new record, and assign that author to the
control.

the above is a standard solution to your data entry issue. if you need help
implementing it, post back and i'll walk you through it.

hth

 

[T Payne]

Thank you. This is the most helpful response I've received so far.

T

 

[T Payne]

Thank you so much for this. It almost works for me now.

One complication: One book can have several authors, so I set up a junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

I have set up a form "Books" (based on the table "Books") and a subform
"Authors subform" (based on a junction table "Authorship").

I used code by Dev Ashish on the site you referred me to to add new records
to the People table when I am working in the Books form.

That part works great. The new Author names appear in the People table.

BUT, when I go back to that same Book in the Books form, no authors appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box in the
Authors subform:

Control Source BookID
RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes


Thanks again for any help.

 

[tina]

comments inline.

Thank you so much for this. It almost works for me now.

you're welcome; let's see if we can give it that last nudge...

One complication: One book can have several authors, so I set up a junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

excellent! i'm impressed that you correctly identified the relationship as
many-to-many between Authors and Books, and correctly set up a junction (or
linking) table to model the relationship as two one-to-many relationships
between the parent tables (People and Books) and the child table
(Authorship). that's very, very good.

one note: if you posted the literal names of your tables and fields,
recommend that you don't use "Name" in the People table. first, Name is a
reserved word in Access and so should not be used to name anything that
*you* name in Access (clearer than mud, i hope!). second, standard practice
is to NOT store a complete name in one field, because that does not conform
to normalization rules; rather, use two or three fields such as FirstName,
MiddleName (or MI), LastName. you can concatenate these values into a
single, complete name at any time in a query, form, or report, as needed.

I have set up a form "Books" (based on the table "Books") and a subform
"Authors subform" (based on a junction table "Authorship").

again, correct - well done.

I used code by Dev Ashish on the site you referred me to to add new records
to the People table when I am working in the Books form.

ah, you've discovered the excellent advice and examples to be found at
www.mvps.org/access. bookmark that puppy!

That part works great. The new Author names appear in the People table.

BUT, when I go back to that same Book in the Books form, no authors appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box in the
Authors subform:

Control Source BookID

ok, this is incorrect. the ControlSource property of the combo box should be
PersonID - you're choosing an author in this combo box droplist, not a book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the fieldname in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a problem,
post the code you're using in the combo box's NotInList event procedure, and
we'll see if there's something there to be modified.

hth

 

[T Payne]

It works now. Thanks so much. Those little things can be soo frustrating.
T

comments inline.

Thank you so much for this. It almost works for me now.

you're welcome; let's see if we can give it that last nudge...

One complication: One book can have several authors, so I set up a junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

excellent! i'm impressed that you correctly identified the relationship as
many-to-many between Authors and Books, and correctly set up a junction
(or
linking) table to model the relationship as two one-to-many relationships
between the parent tables (People and Books) and the child table
(Authorship). that's very, very good.

one note: if you posted the literal names of your tables and fields,
recommend that you don't use "Name" in the People table. first, Name is a
reserved word in Access and so should not be used to name anything that
*you* name in Access (clearer than mud, i hope!). second, standard
practice
is to NOT store a complete name in one field, because that does not
conform
to normalization rules; rather, use two or three fields such as FirstName,
MiddleName (or MI), LastName. you can concatenate these values into a
single, complete name at any time in a query, form, or report, as needed.

I have set up a form "Books" (based on the table "Books") and a subform
"Authors subform" (based on a junction table "Authorship").

again, correct - well done.

I used code by Dev Ashish on the site you referred me to to add new records
to the People table when I am working in the Books form.

ah, you've discovered the excellent advice and examples to be found at
www.mvps.org/access. bookmark that puppy!

That part works great. The new Author names appear in the People table.

BUT, when I go back to that same Book in the Books form, no authors appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box in the
Authors subform:

Control Source BookID

ok, this is incorrect. the ControlSource property of the combo box should
be
PersonID - you're choosing an author in this combo box droplist, not a
book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the fieldname
in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a problem,
post the code you're using in the combo box's NotInList event procedure,
and
we'll see if there's something there to be modified.

hth

Thanks again for any help.

 

[tina]

you're welcome
and hang in there - building forms to support many-to-many relationships
becomes easier with experience, and before you know it, you'll set it up
right without even having to think about it.

It works now. Thanks so much. Those little things can be soo frustrating.
T

comments inline.

Thank you so much for this. It almost works for me now.

you're welcome; let's see if we can give it that last nudge...

One complication: One book can have several authors, so I set up a junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

excellent! i'm impressed that you correctly identified the relationship as
many-to-many between Authors and Books, and correctly set up a junction
(or
linking) table to model the relationship as two one-to-many relationships
between the parent tables (People and Books) and the child table
(Authorship). that's very, very good.

one note: if you posted the literal names of your tables and fields,
recommend that you don't use "Name" in the People table. first, Name is a
reserved word in Access and so should not be used to name anything that
*you* name in Access (clearer than mud, i hope!). second, standard
practice
is to NOT store a complete name in one field, because that does not
conform
to normalization rules; rather, use two or three fields such as FirstName,
MiddleName (or MI), LastName. you can concatenate these values into a
single, complete name at any time in a query, form, or report, as needed.

I have set up a form "Books" (based on the table "Books") and a subform
"Authors subform" (based on a junction table "Authorship").

again, correct - well done.

I used code by Dev Ashish on the site you referred me to to add new records
to the People table when I am working in the Books form.

ah, you've discovered the excellent advice and examples to be found at
www.mvps.org/access. bookmark that puppy!

That part works great. The new Author names appear in the People table.

BUT, when I go back to that same Book in the Books form, no authors appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box in the
Authors subform:

Control Source BookID

ok, this is incorrect. the ControlSource property of the combo box should
be
PersonID - you're choosing an author in this combo box droplist, not a
book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the fieldname
in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a problem,
post the code you're using in the combo box's NotInList event procedure,
and
we'll see if there's something there to be modified.

hth

Thanks again for any help.



you need to be clear about the relationship between the Authors table and
the Books table first, and then design your forms accordingly. from
your
description, it sounds like there is a one-to-many relationship between
Authors and Books; that is, on Author may have many books, but each
Book
is
written by only one author. if this is a correct description of the "real
world" relationship, then the relationship should be expressed in
Access
as

Authors.AuthorID 1:n Books.AuthorID

or to put it in relational terms:

ParentTable.PrimaryKeyField 1:n ChildTable.ForeignKeyField

In my "Books" table, each record is a book, and in each record there
is a
field for "Authors." I have a related table for "Authors" that
includes a
list of all authors and their contact details.

your above description sounds correct for the one-to-many relationship
that
i outlined.

Craig is correct in advising you to use forms for data entry rather
than
tables. let me add to that: if you have a Lookup field in your Books
table
(or any other table), recommend that you get rid of the Lookup. for
more
information, see http://home.att.net/~california.db/tips.html#aTip8.

okay, now that we've laid out the table design, let's look at forms. a
mainform/linked subform setup is as follows: main form is bound to the
*parent* table, and subform is bound to the *child* table. your setup
is
backward, with the child table Books bound to the main form, and the
parent
table Authors bound to the subform. you cannot force a mainform/subform
link
to work that way. if you want to enter "Books" data in a form, and have
the
ability to add data about a new author when the book's author is not in
the
Authors table, then you need a different setup.

i recommend that you create a single form, bound to the Books table.
use a
combo box control (with the *RowSource* property set to the Authors
table),
to provide a list of all authors so you can assign an author to each
new
book record. when the book's author is not listed in the combo box
droplist,
you can use the combo box control's NotInList event to run code that opens
another form which is bound to the Authors table, add the new author
record,
update the droplist with the new record, and assign that author to the
control.

the above is a standard solution to your data entry issue. if you need
help
implementing it, post back and i'll walk you through it.

hth


Thank you. I'm sure this is putting me on the right track. But I still
can't
figure out how to make my "Authors" table update when I enter data
into
the
"Books" form.

The main form is "Books" and the subform is "Author subform." The
"Author"
field in the "Books" form is linked to the "Authorname" field in the
subform. The subform is dependent on the Authorname field in the
larger
"Authors" table. When I add a new name to the subform, the "Authors"
table
adds a new record. Good! But the "Books" table is unaffected.

When I add the name to the Author field in the "Books" table the
"Authors"
table is unaffected.

Surely I shouldn't have to enter the author data twice every time I enter
a
book that has a new author.

What am I doing wrong?

T

"Craig Alexander Morrison" <[email protected]>

wrote
in

message Use a form and subform.

The form containing data about the books (or authors) and the
subform
containg data about the authors (or books).

Working with tables directly may seem quick and easy like a
spreadsheet,
but in the end it will be very limiting.

This assumes you have a relation (normalised table) for Books and
one
for
Authors.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

What I need to do should be the simplest thing in the world, but I
have
scoured the training pages, the help files, faqs, technical
articles
and
sample databases, and have found nothing. It may be that I am just
using
the "wrong" terminology.

I am review editor for an academic journal. My Access database
keeps
track of books received, and people willing to review these

books.
My

job
is to get a reviewer together with a book.

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

What I want to do is this:

When a book arrives, I want to enter its information INCLUDING
AUTHOR(S)
in the "Books" table. I enter the Author information in a ComboBox
that
looks at the Author's table for a list of possible values. I have the
"Limit to List" property on this Combo box set to "No." But when I
enter
a new Author, the Author table is not updated. The new author is only
listed with the current book.

There must be some way to add a new "Author" record to my Authors
table
whenever I enter a new Author to a record in the Books table.

There is probably some simple answer to this question. Thanks for any
help.

T

When I enter a new Author

 

[T Payne]

I'm stuck again. I really appreciate your time.

I took the advice of the help files and made the primary key of my junction
table span two fields. So instead of what I told you earlier (below), the
Authorship table now is only the following:

Authorship
BookID, Primary Key <related to BookID in Books>
PersonID, Primary Key <related to PersonID in People>

The relationships window looks fine.

But still, Authors are not associated with Books in the Books form.

I can add Authors to the Authors table via the Books form, but when I leave
the record, and then go back to it, the Author has disappeared. It's there
in the table, but its relationship to that Book has been lost. I don't get
it.

T

you're welcome
and hang in there - building forms to support many-to-many relationships
becomes easier with experience, and before you know it, you'll set it up
right without even having to think about it.

It works now. Thanks so much. Those little things can be soo frustrating.
T

comments inline.

Thank you so much for this. It almost works for me now.

you're welcome; let's see if we can give it that last nudge...


One complication: One book can have several authors, so I set up a
junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

excellent! i'm impressed that you correctly identified the relationship as
many-to-many between Authors and Books, and correctly set up a junction
(or
linking) table to model the relationship as two one-to-many relationships
between the parent tables (People and Books) and the child table
(Authorship). that's very, very good.

one note: if you posted the literal names of your tables and fields,
recommend that you don't use "Name" in the People table. first, Name is a
reserved word in Access and so should not be used to name anything that
*you* name in Access (clearer than mud, i hope!). second, standard
practice
is to NOT store a complete name in one field, because that does not
conform
to normalization rules; rather, use two or three fields such as FirstName,
MiddleName (or MI), LastName. you can concatenate these values into a
single, complete name at any time in a query, form, or report, as needed.


I have set up a form "Books" (based on the table "Books") and a
subform
"Authors subform" (based on a junction table "Authorship").

again, correct - well done.


I used code by Dev Ashish on the site you referred me to to add new
records
to the People table when I am working in the Books form.

ah, you've discovered the excellent advice and examples to be found at
www.mvps.org/access. bookmark that puppy!


That part works great. The new Author names appear in the People
table.

BUT, when I go back to that same Book in the Books form, no authors
appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box in the
Authors subform:

Control Source BookID

ok, this is incorrect. the ControlSource property of the combo box should
be
PersonID - you're choosing an author in this combo box droplist, not a
book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the fieldname
in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a problem,
post the code you're using in the combo box's NotInList event
procedure,
and
we'll see if there's something there to be modified.

hth



Thanks again for any help.



you need to be clear about the relationship between the Authors
table
and
the Books table first, and then design your forms accordingly. from
your
description, it sounds like there is a one-to-many relationship between
Authors and Books; that is, on Author may have many books, but each
Book
is
written by only one author. if this is a correct description of the
"real
world" relationship, then the relationship should be expressed in
Access
as

Authors.AuthorID 1:n Books.AuthorID

or to put it in relational terms:

ParentTable.PrimaryKeyField 1:n ChildTable.ForeignKeyField

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

your above description sounds correct for the one-to-many relationship
that
i outlined.

Craig is correct in advising you to use forms for data entry rather
than
tables. let me add to that: if you have a Lookup field in your
Books
table
(or any other table), recommend that you get rid of the Lookup. for
more
information, see http://home.att.net/~california.db/tips.html#aTip8.

okay, now that we've laid out the table design, let's look at forms. a
mainform/linked subform setup is as follows: main form is bound to the
*parent* table, and subform is bound to the *child* table. your
setup
is
backward, with the child table Books bound to the main form, and the
parent
table Authors bound to the subform. you cannot force a mainform/subform
link
to work that way. if you want to enter "Books" data in a form, and have
the
ability to add data about a new author when the book's author is not in
the
Authors table, then you need a different setup.

i recommend that you create a single form, bound to the Books table.
use
a
combo box control (with the *RowSource* property set to the Authors
table),
to provide a list of all authors so you can assign an author to each
new
book record. when the book's author is not listed in the combo box
droplist,
you can use the combo box control's NotInList event to run code that
opens
another form which is bound to the Authors table, add the new author
record,
update the droplist with the new record, and assign that author to the
control.

the above is a standard solution to your data entry issue. if you need
help
implementing it, post back and i'll walk you through it.

hth


Thank you. I'm sure this is putting me on the right track. But I still
can't
figure out how to make my "Authors" table update when I enter data
into
the
"Books" form.

The main form is "Books" and the subform is "Author subform." The
"Author"
field in the "Books" form is linked to the "Authorname" field in
the
subform. The subform is dependent on the Authorname field in the
larger
"Authors" table. When I add a new name to the subform, the
"Authors"
table
adds a new record. Good! But the "Books" table is unaffected.

When I add the name to the Author field in the "Books" table the
"Authors"
table is unaffected.

Surely I shouldn't have to enter the author data twice every time I
enter
a
book that has a new author.

What am I doing wrong?

T

in
message Use a form and subform.

The form containing data about the books (or authors) and the
subform
containg data about the authors (or books).

Working with tables directly may seem quick and easy like a
spreadsheet,
but in the end it will be very limiting.

This assumes you have a relation (normalised table) for Books and
one
for
Authors.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

What I need to do should be the simplest thing in the world, but I
have
scoured the training pages, the help files, faqs, technical
articles
and
sample databases, and have found nothing. It may be that I am just
using
the "wrong" terminology.

I am review editor for an academic journal. My Access database
keeps
track of books received, and people willing to review these books.
My
job
is to get a reviewer together with a book.

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

What I want to do is this:

When a book arrives, I want to enter its information INCLUDING
AUTHOR(S)
in the "Books" table. I enter the Author information in a ComboBox
that
looks at the Author's table for a list of possible values. I
have
the
"Limit to List" property on this Combo box set to "No." But when I
enter
a new Author, the Author table is not updated. The new author is
only
listed with the current book.

There must be some way to add a new "Author" record to my
Authors
table
whenever I enter a new Author to a record in the Books table.

There is probably some simple answer to this question. Thanks
for
any
help.

T

When I enter a new Author

 

[tina]

if you're using Access97 or newer, you can send me a compacted copy of your
db if you want, and i'll look at it and see if i can tell you what you need
to tweak to get it running. if you'd like me to do so, compact the db as i
said, make a copy and change the copy's .mdb extension to .bak (do NOT open
it after doing that). if you can zip it, that would be great (i'm on dial-up
internet, so it's slow downloading).

to get my email address, go to
http://home.att.net/~california.db/tips.html#aTip11 and decode the Example,
also changing the number 2 to a number 1. pls mention the newsgroups in the
subject line, so i don't delete your email as spam - and refer to this
newsgroup and the subject line of this thread in the message text, so i can
find it again to post back to.
hth

I'm stuck again. I really appreciate your time.

I took the advice of the help files and made the primary key of my junction
table span two fields. So instead of what I told you earlier (below), the
Authorship table now is only the following:

Authorship
BookID, Primary Key <related to BookID in Books>
PersonID, Primary Key <related to PersonID in People>

The relationships window looks fine.

But still, Authors are not associated with Books in the Books form.

I can add Authors to the Authors table via the Books form, but when I leave
the record, and then go back to it, the Author has disappeared. It's there
in the table, but its relationship to that Book has been lost. I don't get
it.

T

you're welcome
and hang in there - building forms to support many-to-many relationships
becomes easier with experience, and before you know it, you'll set it up
right without even having to think about it.

It works now. Thanks so much. Those little things can be soo frustrating.
T

comments inline.

Thank you so much for this. It almost works for me now.

you're welcome; let's see if we can give it that last nudge...


One complication: One book can have several authors, so I set up a
junction
table. Here are my tables:

Books
BookID Primary Key
Title
<other info about the book as an object>

People
PersonID Primary Key <autonumber>
Name
<other identifying information>

Authorship
ID Primary Key <autonumber>
BookID <related to BookID in Books>
PersonID <related to PersonID in People>

excellent! i'm impressed that you correctly identified the

relationship
as

many-to-many between Authors and Books, and correctly set up a junction
(or
linking) table to model the relationship as two one-to-many relationships
between the parent tables (People and Books) and the child table
(Authorship). that's very, very good.

one note: if you posted the literal names of your tables and fields,
recommend that you don't use "Name" in the People table. first, Name

is
a

reserved word in Access and so should not be used to name anything that
*you* name in Access (clearer than mud, i hope!). second, standard
practice
is to NOT store a complete name in one field, because that does not
conform
to normalization rules; rather, use two or three fields such as FirstName,
MiddleName (or MI), LastName. you can concatenate these values into a
single, complete name at any time in a query, form, or report, as needed.


I have set up a form "Books" (based on the table "Books") and a
subform
"Authors subform" (based on a junction table "Authorship").

again, correct - well done.


I used code by Dev Ashish on the site you referred me to to add new
records
to the People table when I am working in the Books form.

ah, you've discovered the excellent advice and examples to be found at
www.mvps.org/access. bookmark that puppy!


That part works great. The new Author names appear in the People
table.

BUT, when I go back to that same Book in the Books form, no authors
appear!
It seems I have lost the connection between the Book and the Author.

Here are some relevant values from the properties of the combo box

in
the

Authors subform:

Control Source BookID

ok, this is incorrect. the ControlSource property of the combo box should
be
PersonID - you're choosing an author in this combo box droplist, not a
book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the fieldname
in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a problem,
post the code you're using in the combo box's NotInList event
procedure,
and
we'll see if there's something there to be modified.

hth



Thanks again for any help.



you need to be clear about the relationship between the Authors
table
and
the Books table first, and then design your forms accordingly. from
your
description, it sounds like there is a one-to-many relationship between
Authors and Books; that is, on Author may have many books, but each
Book
is
written by only one author. if this is a correct description of the
"real
world" relationship, then the relationship should be expressed in
Access
as

Authors.AuthorID 1:n Books.AuthorID

or to put it in relational terms:

ParentTable.PrimaryKeyField 1:n ChildTable.ForeignKeyField

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

your above description sounds correct for the one-to-many relationship
that
i outlined.

Craig is correct in advising you to use forms for data entry rather
than
tables. let me add to that: if you have a Lookup field in your
Books
table
(or any other table), recommend that you get rid of the Lookup. for
more
information, see http://home.att.net/~california.db/tips.html#aTip8.

okay, now that we've laid out the table design, let's look at

forms.
a

mainform/linked subform setup is as follows: main form is bound

to
the

*parent* table, and subform is bound to the *child* table. your
setup
is
backward, with the child table Books bound to the main form, and the
parent
table Authors bound to the subform. you cannot force a mainform/subform
link
to work that way. if you want to enter "Books" data in a form, and have
the
ability to add data about a new author when the book's author is

not
in

the
Authors table, then you need a different setup.

i recommend that you create a single form, bound to the Books table.
use
a
combo box control (with the *RowSource* property set to the Authors
table),
to provide a list of all authors so you can assign an author to each
new
book record. when the book's author is not listed in the combo box
droplist,
you can use the combo box control's NotInList event to run code that
opens
another form which is bound to the Authors table, add the new author
record,
update the droplist with the new record, and assign that author to the
control.

the above is a standard solution to your data entry issue. if you need
help
implementing it, post back and i'll walk you through it.

hth


Thank you. I'm sure this is putting me on the right track. But I still
can't
figure out how to make my "Authors" table update when I enter data
into
the
"Books" form.

The main form is "Books" and the subform is "Author subform." The
"Author"
field in the "Books" form is linked to the "Authorname" field in
the
subform. The subform is dependent on the Authorname field in the
larger
"Authors" table. When I add a new name to the subform, the
"Authors"
table
adds a new record. Good! But the "Books" table is unaffected.

When I add the name to the Author field in the "Books" table the
"Authors"
table is unaffected.

Surely I shouldn't have to enter the author data twice every time I
enter
a
book that has a new author.

What am I doing wrong?

T

in
message Use a form and subform.

The form containing data about the books (or authors) and the
subform
containg data about the authors (or books).

Working with tables directly may seem quick and easy like a
spreadsheet,
but in the end it will be very limiting.

This assumes you have a relation (normalised table) for Books and
one
for
Authors.

--
Slainte

Craig Alexander Morrison
Crawbridge Data (Scotland) Limited

Small Business Solutions Provider

What I need to do should be the simplest thing in the world,

but
I

have
scoured the training pages, the help files, faqs, technical
articles
and
sample databases, and have found nothing. It may be that I am just
using
the "wrong" terminology.

I am review editor for an academic journal. My Access database
keeps
track of books received, and people willing to review these books.
My
job
is to get a reviewer together with a book.

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

What I want to do is this:

When a book arrives, I want to enter its information INCLUDING
AUTHOR(S)
in the "Books" table. I enter the Author information in a ComboBox
that
looks at the Author's table for a list of possible values. I
have
the
"Limit to List" property on this Combo box set to "No." But

when
I

enter
a new Author, the Author table is not updated. The new author is
only
listed with the current book.

There must be some way to add a new "Author" record to my
Authors
table
whenever I enter a new Author to a record in the Books table.

There is probably some simple answer to this question. Thanks
for
any
help.

T

When I enter a new Author

 

[tina]

okay, first: you should compact your database frequently while developing
it, and regularly during normal use. the db you sent me was 2324 KB, but
when i compacted it (before opening it for the first time), it was reduced
to 816 KB.

second: you should immediately turn OFF the Name Autocorrect option in your
database. for more information, see
http://home.att.net/~california.db/tips.html#aTip3.

third: recommend that you set each table's SubdatasheetName property to
[None]. for more information, see
http://home.att.net/~california.db/tips.html#aTip6.

fourth: recommend that, in each table, go to each field with a Text, Memo,
or Hyperlink data type, and set the AllowZeroLength property to No. for more
information, see http://home.att.net/~california.db/tips.html#aTip9.

the above are all standard setup guidelines that i follow in every new
database i build, and recommend to every new user who is not already
following them.

fifth: recommend you open any VBA module, and on the menu bar click Tools |
Options, Editor tab, and checkmark the box next to Require Variable
Declaration. this will enforce that requirement in all *new* modules that
you create (including form and report modules), but it will not affect
*existing* modules. so i further recommend that you open each existing
module and type Option Explicit immediately below the Option Compare
Database line, as

Option Compare Database
Option Explicit

this is a standard setup guideline that i give to every VBA newbie i come
across. note that this setting is *not* database specific - it will apply to
every database you build or work in, using that Access software installation
on that PC.

sixth: recommend that you bind the Authorship subform directly to the
Authorship table. currently the form is bound to a query that includes both
the Books table, and the Authorship table. there is no reason to include the
Books table in the subform's RecordSource, so you shouldn't be including
data fields that you don't need.

New Problem: See the form "frmSelector." I have
restricted values in the first combo box to
categories listed in the second, as per:
http://office.microsoft.com/en-us/assistance/HA011730581033.aspx

I want to be able to add new data to both
boxes, as per:
http://www.mvps.org/access/forms/frm0015.htm

This NotInList event works fine for the
"cboPlaceSelect" box. But consistently returns
an error message when new data is added to the
restricted ("cboPublisherSelect") box. The
error is:

Run-time error '3061':

Too few parameters. Expected 1.

The line of code where the debugger stops is:

Set rs = db.OpenRecordset("qryPublishers", dbOpenDynaset)

The same event associated with cboPlaceSelect
(with reference to "qryPlaces" instead of
"qryPublishers") works fine.

I believe the queries and tables underlying
them are all fine.
Any thoughts pn why this might be happening? Thanks
in advance.

in your code, i changed the recordset from "qryPublishers", to simply open
the table "Publishers". when you're opening a recordset to append data, you
might as well just base the recordset on the table directly, rather than a
query. after changing the recordset reference, i ran the code without an
error message.

but that doesn't solve your main problem: since this is a restricted query,
it will return only those table records where the PlaceID field value
matches the PlaceID chosen in the first combo box control. your code does
nothing to include that value in the new record in the Publishers table, so
the new record will *still* not be available in the combo box control's
droplist - even though it is successfully added to the Publishers table.

to fix that problem, add the following (third) line to your code, as

rs.AddNew
rs!PublisherName = NewData
rs!PlaceID = Me!cboPlaceSelect
rs.Update

hth

if you're using Access97 or newer, you can send me a compacted copy of your
db if you want, and i'll look at it and see if i can tell you what you need
to tweak to get it running. if you'd like me to do so, compact the db as i
said, make a copy and change the copy's .mdb extension to .bak (do NOT open
it after doing that). if you can zip it, that would be great (i'm on dial-up
internet, so it's slow downloading).

to get my email address, go to
http://home.att.net/~california.db/tips.html#aTip11 and decode the Example,
also changing the number 2 to a number 1. pls mention the newsgroups in the
subject line, so i don't delete your email as spam - and refer to this
newsgroup and the subject line of this thread in the message text, so i can
find it again to post back to.
hth

I'm stuck again. I really appreciate your time.

I took the advice of the help files and made the primary key of my junction
table span two fields. So instead of what I told you earlier (below), the
Authorship table now is only the following:

Authorship
BookID, Primary Key <related to BookID in Books>
PersonID, Primary Key <related to PersonID in People>

The relationships window looks fine.

But still, Authors are not associated with Books in the Books form.

I can add Authors to the Authors table via the Books form, but when I leave
the record, and then go back to it, the Author has disappeared. It's there
in the table, but its relationship to that Book has been lost. I don't get
it.

T

Name

is

not

a

book.

RowSource SELECT [PersonID], [Name] FROM [People];
RowSourceType Table/Query
BoundColumn 1
ColumnCount 2
ColumnWidths 0";1.5"
Limit To List yes

the rest of the properties look correct (remember that when you fix the
"Name" fieldname in the People table, you'll need to change the
fieldname
in
the combob box RowSource too, or you'll get an error). fix the first
property as noted above, and try again. if you're still having a
problem,
post the code you're using in the combo box's NotInList event
procedure,
and
we'll see if there's something there to be modified.

hth



Thanks again for any help.



you need to be clear about the relationship between the Authors
table
and
the Books table first, and then design your forms accordingly. from
your
description, it sounds like there is a one-to-many relationship
between
Authors and Books; that is, on Author may have many books, but each
Book
is
written by only one author. if this is a correct description of the
"real
world" relationship, then the relationship should be expressed in
Access
as

Authors.AuthorID 1:n Books.AuthorID

or to put it in relational terms:

ParentTable.PrimaryKeyField 1:n ChildTable.ForeignKeyField

In my "Books" table, each record is a book, and in each record
there
is
a
field for "Authors." I have a related table for "Authors" that
includes
a
list of all authors and their contact details.

your above description sounds correct for the one-to-many
relationship
that
i outlined.

Craig is correct in advising you to use forms for data entry rather
than
tables. let me add to that: if you have a Lookup field in your
Books
table
(or any other table), recommend that you get rid of the Lookup. for
more
information, see http://home.att.net/~california.db/tips.html#aTip8.

okay, now that we've laid out the table design, let's look at forms.
a
mainform/linked subform setup is as follows: main form is bound to
the
*parent* table, and subform is bound to the *child* table. your
setup
is
backward, with the child table Books bound to the main form, and the
parent
table Authors bound to the subform. you cannot force a
mainform/subform
link
to work that way. if you want to enter "Books" data in a form, and
have
the
ability to add data about a new author when the book's author is not
in
the
Authors table, then you need a different setup.

i recommend that you create a single form, bound to the Books table.
use
a
combo box control (with the *RowSource* property set to the Authors
table),
to provide a list of all authors so you can assign an author to each
new
book record. when the book's author is not listed in the combo box
droplist,
you can use the combo box control's NotInList event to run code that
opens
another form which is bound to the Authors table, add the new author
record,
update the droplist with the new record, and assign that author to
the
control.

the above is a standard solution to your data entry issue. if you
need
help
implementing it, post back and i'll walk you through it.

hth


Thank you. I'm sure this is putting me on the right track. But I
still
can't
figure out how to make my "Authors" table update when I enter data
into
the
"Books" form.

The main form is "Books" and the subform is "Author subform." The
"Author"
field in the "Books" form is linked to the "Authorname" field in
the
subform. The subform is dependent on the Authorname field in the
larger
"Authors" table. When I add a new name to the subform, the
"Authors"
table
adds a new record. Good! But the "Books" table is unaffected.

When I add the name to the Author field in the "Books" table the
"Authors"
table is unaffected.

Surely I shouldn't have to enter the author data twice every

time

I

author

is

 

[T Payne]

Thank you so much for this. I don't know how or why you do what you do, but
I wish lawyers and physicians worked the same way!

T.

 

[tina]

well, i do have a day job...
....and you're welcome