Problem with form--subform

[lcc66604]

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.

Regards,
Leslie Charles

 

[John W. Vinson]

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 and methods it makes available for Automation operations.
-----
I click OK and resume typing. Then the ‘Element ID” box in the subform populates 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 the subform it will automatically populate the “Element” and “Element ID” fields from the data in the corresponding fields in the main field.

Regards,
Leslie Charles

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:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Leslie Coover]

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 thetwo.

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:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

------

John,

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

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

Table_Notecards
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.

Cordially,
Leslie Charles

 

[John W. Vinson]

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

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

Table_Notecards
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 to many 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 the subform 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.

Cordially,
Leslie Charles

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

=[Forms]![NameOfYourForm]![ELM_ID]

on the Elm_ID field.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Leslie Coover]

Thank you so much for your response. I followed your suggestions but still can'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 oneto many relationship.

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

Your suggestion concerning basing the main form on the parent table and the subform 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 outevery 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

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



=[Forms]![NameOfYourForm]![ELM_ID]



on the Elm_ID field.

--



John W. Vinson [MVP]

Microsoft's replacements for these newsgroups:

http://social.msdn.microsoft.com/Forums/en-US/accessdev/

http://social.answers.microsoft.com/Forums/en-US/addbuz/

and see also http://www.utteraccess.com

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.

SIMPLE 2-TABLE DATABASE WITH FORMS AND A REPORT:

(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.

Les

 

[John W. Vinson]

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.

Well... sorry. But I have some real quibbles with your checklist.

SIMPLE 2-TABLE DATABASE WITH FORMS AND A REPORT:

(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.

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.

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

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.

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

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.

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

That's fine, though you can construct subforms without using the wizard if you
prefer (I often do).

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

(6) DO NOT BASE YOUR REPORT ON TABLES—first build a Query with the fields you 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.
Absolutely!

(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 AS Table_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];

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.

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.

Very handy. It can be done manually of course, but hey, this is one case where
the wizard does a good job.

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 All tab list in the Property Sheet) to "Yes".

Now that's interesting - I don't recall that I've ever used that! So I've
learned something today thanks to you!

Thank you again for your help.

You're welcome, and good luck with your database!
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com