Problem with form--subform

Discussion in 'Access General' started by lcc66604, May 30, 2013.

  1. lcc66604

    lcc66604 Guest

    Microsoft Access 2010
    Form with subform; following SQL in subform form-properties

    SELECT Table_Notecards.*, Table_Main.Elemental_, Table_Main.ELM_ID AS ELM_ID_Table_Main FROM Table_Main LEFT JOIN Table_Notecards ON Table_Main.ELM_ID= Table_Notecards.ELM_ID;

    When I start a record in the main form the subform does nothing.
    If I click into the first field and start to type in the “Element” field exactly the same data that is in the Main form I get:

    The object doesn’t contain the Automation object ‘Table_Notecards.’
    You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn’t make the property or method available for Automation operations.
    Check the component’s documentation for information on the properties andmethods it makes available for Automation operations.
    I click OK and resume typing. Then the ‘Element ID” box in the subformpopulates with the same value that is in the ‘Element ID” in the mainform.
    Could someone explain how to make this less clunky so that when I enter thesubform it will automatically populate the “Element” and “Element ID” fields from the data in the corresponding fields in the main field.

    Leslie Charles
    lcc66604, May 30, 2013
    1. Advertisements

  2. Consider basing the main form on the parent table, and the subform on the
    child table (Table_Main and Table_Notecards respectively, I'm guessing).

    It's just additional complexity to base either one on a query joining the two.
    Put controls on the mainform for the main table's fields, and on the subform
    for the subform's fields; use ElementID as the Master/Child field link. If
    it's an autonumber on the main form, it typically would not be displayed on
    EITHER form - it works "under cover", just linking the tables together.

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, May 30, 2013
    1. Advertisements

  3. ------


    Thank you so much for your response. I followed your suggestions but stillcan't resolve the problem. Here is what I have:

    ELM-ID (Auto Number and Primary key)
    Elemental (text)

    Notecard_ID (AutoNumber)
    Elemental (Text--foreign)
    ELM_ID (Number--foreign)
    Unit (Text)
    Source_Code (Text)
    Source_Code_Desc (Text)
    VID (Text)
    Memo_ (Memo)

    Relationships: Table_Main linked to Table_Notecards via ELM-ID in a one tomany relationship.

    Form_mymain and subform_mynotecards. On the subform is a command button that opens Rpt_Notecard in Print Preview

    Your suggestion concerning basing the main form on the parent table and thesubform on the child table seemed to work OK. But then the Print Preview function would not work--instead of printing only the current record in subform-mynotecards (as it did with my "clunker" version) it now prints out every record in the subform.

    The reasons for this database is that I need a way to coordinate my research notecards with source material. Any suggestions welcomed.

    Leslie Charles
    Leslie Coover, May 30, 2013
  4. A basic principle of relational databases is that data should be stored once
    and once only; you would use queries and appropriate form tools to display
    data from related tables. In this case, the Elemental text field should exist
    ONLY in Table_Main, and not be copied into Table_Notecards.

    And it's usually not very practical to print Forms. Forms are for onscreen use
    and for interaction with data; to print data you would use a Report. The
    Report would be based on a query appropriately referencing a form control. If
    you want to see all of the Notecards for a given ELM-ID you would use a query
    joining the two tables, with a criterion such as


    on the Elm_ID field.

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, May 31, 2013
  5. John, thank you again. I finally got it to work. I incorporated your suggestions into the following checklist where I added a few things that might help novices like me build simple databases.


    (1) AutoNumber primary keys for both tables. Any foreign key used in Second (child table) must exactly match the formatting of the germane field in First Table.

    (2) Use “Require = yes” and “Indexed=yes” as sparingly as possible in the Second Table.

    (3) Establish relationships as One (parent table) to Many (child table) andmake the Join Type 3 (Include ALL records from Second Table and only thoserecords from First Table where joined fields are equal).

    (4) Set the Record Source for the Main Form to First Table and Default Viewto Single form, then build the subform using the Subform/Subreport tool inthe Design Tools group. TIP: make sure you select “Use Control Wizards.”

    (5) While “tidying up” the subform, make sure Default View is set to “Continuous Forms.” The Record source should be the Second Table.

    (6) DO NOT BASE YOUR REPORT ON TABLES—first build a Query with the fieldsyou need from the First Table, adding them to the query grid (going from left to right) followed by the fields you need from the Second Table.

    (7) Base your report on your query. The SQL that goes with the Record Source (which was set to the query) was as follows:

    SELECT Table_Main.ELM_ID AS Table_Main_ELM_ID, Table_Main.Elemental_ AS Table_Main_Elemental_, Table_Notecards.Notecard_ID, Table_Notecards.ELM_ID ASTable_Notecards_ELM_ID, Table_Notecards.Elemental_ AS Table_Notecards_Elemental_, Table_Notecards.Unit, Table_Notecards.Source_Code, Table_Notecards.Source_Code_Desc, Table_Notecards.VID, Table_Notecards.Memo_
    FROM Table_Main LEFT JOIN Table_Notecards ON Table_Main.[ELM_ID] = Table_Notecards.[ELM_ID];

    I used this as a check, my Query (not I) made the syntax.

    (8) On the subform place a command button using the Design, Button tool with “Use Control Wizards” turned on. This command button opens the report to the same record that you are viewing in your form.

    John, you probably have other things to add or changes to suggest concerning this “checklist,” but I hope it will provide a cut-to-the-chase method for novices who only delve into the development of simple databases on rare occasions. One last thing: the report I made prints the information I put into my form on 5-inch × 3-inch notecards. The basic trick was to set the Field Property “Vertical” (found almost at the bottom of the Alltab list in the Property Sheet) to "Yes".

    Thank you again for your help.

    Leslie Coover, May 31, 2013
  6. Well... sorry. But I have some real quibbles with your checklist.
    The Format of a field only affects the display. It is irrelevant for joining.
    The DATATYPE - which is not the same as the format! - must match; the Foreign
    Key linked to an Autonumber primary key must be a Long Integer.
    To quote Einstein, "A theory must be as simple as possible - BUT NOT SIMPLER!"
    Fields should be marked Required if there is a business reason to make them
    required. Foreign keys probably should be - you don't want to be able to
    create orphan records by just leaving the foreign key field NULL. Creating a
    relationship will automatically (and correctly, and essentially) create an
    index on the foriegn key field.
    The Join Type doesn't make any difference (it just sets the default for the
    join type of queries you create involving the table). It has no effect
    whatsoever on the data.
    That's fine, though you can construct subforms without using the wizard if you
    prefer (I often do).
    The Left Join is appropriate if you want to see all records from Table_Main
    even if there are no Notecards records. Sometimes you might want an Inner Join
    if you only want to see those records which DO have notecards. Left Join,
    Right Join, Inner Join all have valuable uses.
    Very handy. It can be done manually of course, but hey, this is one case where
    the wizard does a good job.
    Now that's interesting - I don't recall that I've ever used that! So I've
    learned something today thanks to you!
    You're welcome, and good luck with your database!

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    and see also
    John W. Vinson, May 31, 2013
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.