Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    2

    Unanswered: Form with subform and subsubform

    Hello

    (First: my apologies if this post appears twice - I had a bit of trouble getting started here.)

    I am trying to learn Access 2007 and have set myself a small project as a vehicle by which to do so, but I have run into trouble early.

    My project is to develop a database to manage my collection of books. I have three main tables, as follows:

    Table 1 Authors has fields Author_ID (AutoNumber, Primary Key), First_Name and Last_Name.
    Table 2 Books has fields Book_ID (AutoNumber, Primary Key) and Title.
    Table 3 Volumes has fields Volume_ID (Autonumber, Primary Key), Title, Publisher, etc.

    I am using the concept of Volume to represent a physical book which may contain one or more Books. For example, a Volume might be a compendium containing several Books by one Author ("The Complete Works of William Shakespeare") or a collection of Books by different Authors (like the sets of Readers Digests Condensed Books), or it may just be a volume with one title (ie a regular normal book!).

    I also have two junction tables, as follows:
    Table 4 Books_Authors with fields ID (Autonumber), Book_ID and Author_ID (both Primary Keys)
    Table 5 Volumes_Books with fields ID (Autonumber), Volume_ID and Book_ID (both Primary Keys)

    These tables are to enable relevant many-to-many relationships between tables. For example, Table 4 is there to enable a many-to-many relationship between Books and Authors, as one Author can have several Books and one Book may have several Authors. I have created a one-to-many relationship between Books and Books_Authors on the Book_ID field, and a similar one-to-many relationship between Authors and Books_Authors on the Author_ID field.

    Similarly with Table 5, which allows one Volume to have many Books and one Book to be in many Volumes.

    I want to develop a data entry form which is based on Volumes, with a subform to enter a variable number of Books, with a further subform (a subsubform??) to enter a variable number of Authors for each Book.

    Because I have several Books by the same Author I don't want to have to enter the Author details every time. Instead when I get to the Author subform I would like to be able to use a lookup table in a Combo box, (based on my Authors table) using the Author's full name { [First_Name] & " " & [Last_Name]} so that I can just click on the Author I want. I think I can handle taht part OK. If the Author I want is not on the list already I will use a button on the form to take me to another form to enter a new Author. That part is also OK.

    I have tried several times to develop my form but can't get it to work properly - some of the links don't work and the data gets lost. While I understand (I think!) the theory of the relationships, I am having difficulty putting that theory into practice.

    I have looked at several on-line tutorials and tips, as well as a few sample databases which look similar to what I want, but when I try to recreate these from scratch I cannot manage it. I would, therefore, be grateful if someone could help by walking me through the steps required to create my form.

    Thanks in advance.

    Robert

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    When developing forms (as data entry), as a general rule, I usually keep 1 table to 1 form and use sub-forms for the relational tables (1 table for 1 form) or I'll use a popup form, but you can utilize 2 tables as the source for 1 form bringing in the fields from the 2nd relational table onto the main form (but not 3+ tables for 1 data entry form). I've also utilized sub-sub forms but when I get to this point, I look at the limitation of making a sub form continuous as you can't make the sub form continuous when utilizing a sub-sub form (nothing wrong though with having a sub-sub form). I don't quite understand what you mean though by "- some of the links don't work and the data gets lost." - could you please elaborate?

    You may also want to take a look at the Northwinds database that ships with MSAccess (and maybe look at some of the examples in the MSAccess code bank on this forum.) And the Access Developers book by Sybex is a great book to have on your shelf.

    Try using the Form Wizard for creating your form(s) (select Form Wizard after you click new form) and after your done, then re-arranging the fields in design view to the look you want. The wizard also works very good for creating sub forms (by simply clicking on the subform icon in the toolbox toolbar). Once you get the hang of creating your forms using the Form wizard, then I'd start designing the forms without the wizard.

    Good luck Robert and let us know how you come along. If you get stuck, you can always post your code or even upload your zipped mdb and we'll help you along.
    Last edited by pkstormy; 04-08-08 at 20:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Apr 2008
    Posts
    2
    Thanks pkstormy.

    I started again on my form and this time it worked. I haven't put the lookup feature in yet, but all the data entered on the form found its way to the relevant tables.

    In my original post when I said "- some of the links don't work and the data gets lost" I was referring to the situation in which after I entered the data into the form not all of it found its way to the relevant table, but this seems OK now.

    However while using the Wizard to add the subsubform I got an error message saying:
    "A form with a subform object can't have its Default View property set to Continuous forms.
    You tried to add a subform to a form in Design View.
    Microsoft Access will reset the property to Single Form"

    --- which it did, presumably, and everything was fine.

    Anyway, thanks again

    Robert

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You should get this error (or actually message box)...

    However while using the Wizard to add the subsubform I got an error message saying:
    "A form with a subform object can't have its Default View property set to Continuous forms.
    You tried to add a subform to a form in Design View.
    Microsoft Access will reset the property to Single Form"

    If you are adding a subform within a subform.

    This always comes up when adding a subform within a subform and you have a subform set to continuous forms.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •