Tailor a form to enter vriable records

[Kim]

I am making a database of literature references with Access 2003. My fields
include author, publication year, journal title, abstract (which can be a
paragraph), and then graphs/chart images can be added. I want a "user
friendly" form to input the data for each publication. The end goal is to be
able to produce reports that will neatly list the info about each reference
along with the applicable graphs corresponding to each paper in a tabular
display.

The issue is that it will be difficult to set up a "one size fits all" form
since each paper will differ. I cannot predict how many authors each paper
will have, for example. I can guess that I will rarely have a publication
with more than 10 authors. So I could:
a) design a form with 10 fields for authors. But, using this method there
will always be unused fields and what if I have a paper with 11 authors? I
will have to make a new form or enter the data directly into the table...
which defeats the purpose of trying to have a user friendly method of
entering data.

Q: Is there a way to design a form that is modifiable? For example, could I
design a form with one "author" field and a button next to it which says "add
author". When I push this button an additional author field will be inserted
into the form... thereby extending the length of the form.

or could I set up a dialog box where I could answer a couple of questions
about the publication I want to enter into the database and a form
automatically pops up with the appropriate fields for that publication I am
entering? So, I could have a box that asks how many authors this paper has,
etc. Then a form will pop up with exactly that many 'author" fields among the
other fields I need that apply to that type of paper.

Thank you

 

[Mr. B]

Kim,

You are dealing with a clasic one to many situation here. All you need to
do is to have another table that will hold as many authors for each
publicaiton as you need. This type of thing is done all the time. Create
the new table and include a foreign key field (numeric, long type) that will
line specific authors to the publication. You will then be able to have your
form to manage the publicaition inforamtion and a sub form to manage all of
the authors, as many as you need for any publicaiton.

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm

 

[Kim]

Thank you for you reply, but I'm not sure it will solve my issue.

I'm not using the forms to manage the data. I'm using the form as a way to
enter the data into table(s)so that i can make reports. I want to enter the
data into one place... the form. It sounds like with your solution I will
have to establish relationships between each publication and its authors for
every record I enter. That seems more complicated and time consuming.

Let's say an user of this database wants to enter information for 10
publications. I want them to open the database and be able to enter all the
info for one publication on one form. So, essentially, they have to fill out
10 forms to input 10 records. It will get complicated if they have to know
how to manage relationships between data in tables.

 

[John W. Vinson]

I am making a database of literature references with Access 2003. My fields
include author, publication year, journal title, abstract (which can be a
paragraph), and then graphs/chart images can be added. I want a "user
friendly" form to input the data for each publication. The end goal is to be
able to produce reports that will neatly list the info about each reference
along with the applicable graphs corresponding to each paper in a tabular
display.

The issue is that it will be difficult to set up a "one size fits all" form
since each paper will differ. I cannot predict how many authors each paper
will have, for example. I can guess that I will rarely have a publication
with more than 10 authors. So I could:
a) design a form with 10 fields for authors. But, using this method there
will always be unused fields and what if I have a paper with 11 authors? I
will have to make a new form or enter the data directly into the table...
which defeats the purpose of trying to have a user friendly method of
entering data.

Q: Is there a way to design a form that is modifiable? For example, could I
design a form with one "author" field and a button next to it which says "add
author". When I push this button an additional author field will be inserted
into the form... thereby extending the length of the form.

or could I set up a dialog box where I could answer a couple of questions
about the publication I want to enter into the database and a form
automatically pops up with the appropriate fields for that publication I am
entering? So, I could have a box that asks how many authors this paper has,
etc. Then a form will pop up with exactly that many 'author" fields among the
other fields I need that apply to that type of paper.

Thank you

Start with your tables!!! Forms are secondary; you should have a normalized
set of tables first, and then design your forms to fit them, NOT vice versa.

I see several one-to-many and many-to-many relationships here, so you'll need
several tables. Each Publication can have zero, one, or many images, so you'll
need a table of Publications related one to many to a table of Images. Each
Publication can have one or more Authors, and each Author may write one or
more Publications, so you need *three* tables:

Publications
PublicationID
Title
PublicationDate
<etc etc>

Authors
AuthorID <Primary Key, probably autonumber>
LastName
FirstName
<other biographical data>

Authorship
PublicationID <link to Publications>
AuthorID <link to Authors>
<any information about this author with respect to this publication, such as
a sequential author number so you can identify first author, sixth author etc>

You'ld use a Form based on Publications with two subforms, one based on the
images table and the other based on authorship.

 

[John W. Vinson]

Thank you for you reply, but I'm not sure it will solve my issue.

I'm not using the forms to manage the data. I'm using the form as a way to
enter the data into table(s)so that i can make reports. I want to enter the
data into one place... the form. It sounds like with your solution I will
have to establish relationships between each publication and its authors for
every record I enter. That seems more complicated and time consuming.

Let's say an user of this database wants to enter information for 10
publications. I want them to open the database and be able to enter all the
info for one publication on one form. So, essentially, they have to fill out
10 forms to input 10 records. It will get complicated if they have to know
how to manage relationships between data in tables.

They don't need to know about relationships... but YOU do.

That's what good database design is all about - you manage the relationships
so the user doesn't need to!

See my other post in the thread. This will become very easy if you use a
form/subform arrangement for the tables (four tables, in my suggested
example).

Your line "I want to enter the data into one place... the form" evinces a
misconception. You're not entering data "into the Form" - you're using the
form AS A TOOL to enter data into tables. The user sees and interacts with
only one form, but that form does NOT store data; it's just a conduit.

 

[Mr. B]

Kim,

As John and i are attempting to point out to you is that one form with one
or more sub forms can allow your users to enter data into what apears to them
to be one form and the data they enter would actually be being save in
multiple tables.

Access is quite good at managing data this way.

As for your comments: You may not be using forms to manage the data, but
you should be. If you first design your database correctly as John has so
graciously designed for you, you will then be able to allow users to enter
data in a very efficient and fluid manner. Once designed, you will not have
to do anything for each record.


I realize that it may not seem to you that you need everything that we have
proposed, but please believe me when I say, "Both John and I are trying to
direct you in a way that will produce that very best database design
possible."

-----
HTH
Mr. B
http://www.askdoctoraccess.com/
Doctor Access Downloads Page:
http://www.askdoctoraccess.com/DownloadPage.htm