Form/Subform

[Pwyd]

Okay so i built myself a table and a query to power a subform. I wanted a
one to many relationship between the main record table and the subform's
table. Few questions. I want the relationship between the two tables
themselves, not the query that i'm using to feed the form, correct? ( i was
always taught you should use a query to drive a form, not the actual table
the data is in)

Next question. I'd like the subform to only display records that have the
same invoice number in them. So there are two problems ih ave with this. in
one case, i was getting some errors with "automation", where it wasn't
listing the proper records, or any at all. The second was that it wasn't
adding the invoice number (the field that had a one to many relationshp) to
the new records on the subform as they were created, even though i set the
default value to [Forms]![myform]![myinvoicenumberfield].
i thought i understood this concept fairly well, however when i flick over
to the next record on the main form, the subform is still showing the same
set of records, rather than a blank set like it should be, since no records
exist where the subform's invoice number match the second record in the main
form. What am i doing wrong?

 

[Arvin Meyer MVP]

Answers in line:

Okay so i built myself a table and a query to power a subform. I wanted a
one to many relationship between the main record table and the subform's
table. Few questions. I want the relationship between the two tables
themselves, not the query that i'm using to feed the form, correct? ( i
was
always taught you should use a query to drive a form, not the actual table
the data is in)

A query and a table are interchangeable for updating the table, providing,
of course, that the query is updateable. (i.e., not an aggregate, etc.) I
prefer to use queries for forms because I can sort them by any field instead
of the Primary Key.

Next question. I'd like the subform to only display records that have the
same invoice number in them. So there are two problems ih ave with this.
in
one case, i was getting some errors with "automation", where it wasn't
listing the proper records, or any at all. The second was that it wasn't
adding the invoice number (the field that had a one to many relationshp)
to
the new records on the subform as they were created, even though i set the
default value to [Forms]![myform]![myinvoicenumberfield].
i thought i understood this concept fairly well, however when i flick over
to the next record on the main form, the subform is still showing the same
set of records, rather than a blank set like it should be, since no
records
exist where the subform's invoice number match the second record in the
main
form. What am i doing wrong?

You need to check the LinkMaster\LibkChild properties of the subform control
to make sure they have the InvoiceNo as the linking field.

 

[Jeff Boyce]

Tables reflect data that lives in tables, even if you use a query to
select/filter that data.

You haven't described the tables involved, nor their relationship. If you
want to use a main form/subform design, you need data (tables) related
1-to-many.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Pwyd]

Okay.
Main form is powered by a table called VendorRecordTable

subform is powered by a table called LineItemTable

Their is a field in both of them called V_InvoiceNumber
there is a one to many relationship between the two.

The subform sits on the main form. the child/master fields linking them are
set to V_InvoiceNumber.
When i try moving from design view into actually using the form, it gives me
some error about automation.

The subform does not do what it should, ie, display many records for each of
the main records. It displays all of its records regardless of what record
i'm looking at in the main form.

Tables reflect data that lives in tables, even if you use a query to
select/filter that data.

You haven't described the tables involved, nor their relationship. If you
want to use a main form/subform design, you need data (tables) related
1-to-many.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay so i built myself a table and a query to power a subform. I wanted a
one to many relationship between the main record table and the subform's
table. Few questions. I want the relationship between the two tables
themselves, not the query that i'm using to feed the form, correct? ( i
was
always taught you should use a query to drive a form, not the actual table
the data is in)

Next question. I'd like the subform to only display records that have the
same invoice number in them. So there are two problems ih ave with this.
in
one case, i was getting some errors with "automation", where it wasn't
listing the proper records, or any at all. The second was that it wasn't
adding the invoice number (the field that had a one to many relationshp)
to
the new records on the subform as they were created, even though i set the
default value to [Forms]![myform]![myinvoicenumberfield].
i thought i understood this concept fairly well, however when i flick over
to the next record on the main form, the subform is still showing the same
set of records, rather than a blank set like it should be, since no
records
exist where the subform's invoice number match the second record in the
main
form. What am i doing wrong?

 

[Pwyd]

Here it is. The LinkMasterFields property setting has produced this error:
'The object doesn't contain the Automation object VendorRecordsTable.'
which means about zero for me. it says it does, in the fieldname.

Tables reflect data that lives in tables, even if you use a query to
select/filter that data.

You haven't described the tables involved, nor their relationship. If you
want to use a main form/subform design, you need data (tables) related
1-to-many.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Okay so i built myself a table and a query to power a subform. I wanted a
one to many relationship between the main record table and the subform's
table. Few questions. I want the relationship between the two tables
themselves, not the query that i'm using to feed the form, correct? ( i
was
always taught you should use a query to drive a form, not the actual table
the data is in)

Next question. I'd like the subform to only display records that have the
same invoice number in them. So there are two problems ih ave with this.
in
one case, i was getting some errors with "automation", where it wasn't
listing the proper records, or any at all. The second was that it wasn't
adding the invoice number (the field that had a one to many relationshp)
to
the new records on the subform as they were created, even though i set the
default value to [Forms]![myform]![myinvoicenumberfield].
i thought i understood this concept fairly well, however when i flick over
to the next record on the main form, the subform is still showing the same
set of records, rather than a blank set like it should be, since no
records
exist where the subform's invoice number match the second record in the
main
form. What am i doing wrong?

 

[John W. Vinson]

Here it is. The LinkMasterFields property setting has produced this error:
'The object doesn't contain the Automation object VendorRecordsTable.'
which means about zero for me. it says it does, in the fieldname.

Please post the SQL view of the main form's Recordsource property and that of
the Subform.

Just to be clear - the main and subforms can be based either on a table or a
query, but *neither of them* should be based on a two-table query joining the
VendorRecordsTable and the LineItemTable. The main form should be based on
VendorRecordsTable (or a query based on that table); the subform should be
based on the LineItemTable (or...). The Master Link Field should be the
Primary Key of the VendorRecordsTable, or (rarely) some other field which has
a unique Index; the child link field should be the corresponding foreign key
field in the LineItemTable.

 

[Pwyd]

♦SELECT VendorRecordsTable.*, VendorRecordsTable.ID
FROM VendorRecordsTable
ORDER BY VendorRecordsTable.ID;

that's the query that powers the main form


SELECT LineItemsSubTable.*
FROM LineItemsSubTable;

that query powers the subform.




i understand the requirements -- neither of the table's is powered by a
query that links the tables together. they are each gathering from only one
unique table each.

 

[John W. Vinson]

SELECT VendorRecordsTable.*, VendorRecordsTable.ID
FROM VendorRecordsTable
ORDER BY VendorRecordsTable.ID;

that's the query that powers the main form


SELECT LineItemsSubTable.*
FROM LineItemsSubTable;

that query powers the subform.




i understand the requirements -- neither of the table's is powered by a
query that links the tables together. they are each gathering from only one
unique table each.

I'd suggest removing the * field and just copy all of the fields into the
query grid: part of the problem is that you have VendorRecordsTable.ID in the
query TWICE, once explicitly and also as one of the "all fields".

What are the Master and Child Link Fields? How are the tables related? What is
the Primary Key of each table?

 

[Pwyd]

Okay so i've done as you said, its still giving the same error though
(they're all explicitly stated, i removed the .* field)

Link child fields: V_InvoiceNumber

Link master fields: VendorRecordsTable.ID

 

[Arvin Meyer MVP]

The master field is the Primary key from the 1-side table. The child key is
the matching Foreign Key from the many-side table. It doesn't appear as if
you've done that. Also, VendorRecordsTable.ID is not the correct way of
showing the field. The field name is what is required.

 

[Pwyd]

It doesn't allow me to put hte field name in. It only allows me to put
tablename.fieldname in that property.

Its not even listed in the available properties as a master field.


Two related problems: How come its not automatically making the linked
field show the correct number when a new record in the subform is created,
and, how can i make it start re-numbering the records in the subform from "1"
for each main record they're associated with?

 

[Pwyd]

Arvin if you'll email me at (e-mail address removed) i'll send you a copy of
the database. I have several other problems i cannot articulate easily here
without showing you.

 

[Pwyd]

The problem seems to have been the query design that was powering the form
and subform. After i removed the .* field and just placed all of the fields
on the query explicitly, it seems to work fine, and its populating the linked
fields properly on the subform. The only reason i chose to use the .* was
because i was tired of going back and adding new fields to the queries as i
changed the structure/names on the tables. The update property only updates
the forms, not the queries powering those forms so i was using .* instead.

Arvin if you'll email me at (e-mail address removed) i'll send you a copy of
the database. I have several other problems i cannot articulate easily here
without showing you.

 

[John W. Vinson]

The problem seems to have been the query design that was powering the form
and subform. After i removed the .* field and just placed all of the fields
on the query explicitly, it seems to work fine, and its populating the linked
fields properly on the subform. The only reason i chose to use the .* was
because i was tired of going back and adding new fields to the queries as i
changed the structure/names on the tables. The update property only updates
the forms, not the queries powering those forms so i was using .* instead.

Ah! You should certainly fill in all the fields rather than using * in that
case. If you change the structure of a table, all the queries, forms, and
reports referencing that table need to be refreshed (by opening the query at
least).