Changing Record Source and Subform Disapears

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I changed the record source from a table to a query so I could use fields
from multiple tables, when I did this the subform I have attached disapears,
when I change back to the table it reappears. Is there something I need to do
so the subform shows when I change the record source to my query?

Thanks
Joe
 
Joe Gieder said:
I changed the record source from a table to a query so I could use
fields from multiple tables, when I did this the subform I have
attached disapears, when I change back to the table it reappears. Is
there something I need to do so the subform shows when I change the
record source to my query?
Click to expand...

This is the recordsource of the subform that you're talking about?
Probably the query you changed it to is not updatable. If there are no
records to display and no records can be added, then the form's detail
section is completely blank.

You can check the updatability of the query by opening it directly as a
datasheet and seeing if the "new record" row appears. If it's not
updatable, check the help topic "When can I update data from a query?"
to see what may be wrong and what you can do about it.
 
It's the record source of the main form. What seems to happen is when I
delete one of my tables from the query it works fine but when I add it back
in it doesn't. Can you have three or more tables in a query linked and have
the main form use it (I link by SupplierID)? In my Suppliers table I defined
SupplierID as an autonumber but in the other two tables this field is just
defined as a number. When adding information into the form and an autonumber
being assigned how does it get added into the other two tables? I'm trying to
create a Vendor Quote database that uses three main tables, Material,
Suppliers and RFQHeaderData. Where the main form uses the Suppliers table to
look up that information I then add the reference number, date and due date
which I wat then have this data written to the RFQHeaderData table, then I
will add the material (part numbers) into the subform and want it to add that
data into the Material table and also add the SuppliedID and reference number
from the main form. Am I trying to do too many things from too many places?
Is this even achievable?
Sorry fo the long discussion and thank you
Joe

Joe
 
Joe Gieder said:
It's the record source of the main form. What seems to happen is when
I delete one of my tables from the query it works fine but when I add
it back in it doesn't. Can you have three or more tables in a query
linked and have the main form use it (I link by SupplierID)? In my
Suppliers table I defined SupplierID as an autonumber but in the
other two tables this field is just defined as a number. When adding
information into the form and an autonumber being assigned how does
it get added into the other two tables? I'm trying to create a Vendor
Quote database that uses three main tables, Material, Suppliers and
RFQHeaderData. Where the main form uses the Suppliers table to look
up that information I then add the reference number, date and due
date which I wat then have this data written to the RFQHeaderData
table, then I will add the material (part numbers) into the subform
and want it to add that data into the Material table and also add the
SuppliedID and reference number from the main form. Am I trying to do
too many things from too many places? Is this even achievable?
Sorry fo the long discussion and thank you
Joe
Click to expand...

You may well be trying to do too many things in *one* place, but there's
no reason what you want to do should be unachievable, or even hard.
It's hard to say what you should be doing, though, without knowing more
about the design of your tables -- what fields are in them, which fields
relate one table to another, and so on. Probably you need to have a
main form based only on the Suppliers table (or a simple query of it),
with a subform on that form based only on the RFQHeaderData table (or a
simple query of it), and a subform on *that* form (the RFQHeaderData
subform) based only on the Materials table. Each subform would be
linked to its parent form via the Link Master/Child Fields properties
such that all the necessary information to link the records in the
tables is filled in automatically. The Materials subform would probably
be in continuous forms view, but the others would have to be in
single-form view, because you can't put a subform on a continuous form.
 
Thanks for the insite. I'll try the approach you laid out here. Would there
be a problem with putting the two subforms Material and RFQHeaderData onto
the Suppliers main form? What would the advantage be by putting the Material
subform on the RFQHeaeder subform? What is a continuous form?

Thanks
Joe
 
Joe Gieder said:
Thanks for the insite. I'll try the approach you laid out here. Would
there be a problem with putting the two subforms Material and
RFQHeaderData onto the Suppliers main form?
Click to expand...

That's doable, but more complicated because the form layout doesn't
mirror the hierarchical relationship of the tables (if I've understood
it correctly) and you have to use a trick to get the subforms to link
properly. My understanding is that the tables are related like this:

Suppliers is related one-to-many with RFQHeaderData
RFQHeaderData is related one-to-many with Materials

In other words, for each supplier there could be multiple RFQs (each
being represented by a single record in RFQHeaderData), and for each RFQ
there could be multiple materials related to that RFQ. If this isn't a
correct description of the relationships, then my suggestions are
probably wrong.
What would the advantage
be by putting the Material subform on the RFQHeaeder subform?
Click to expand...

With the proper linking fields set in the suform properties, Access
automatically ensures that only related records are shown on the
subform, and automatically inserts values in the linking fields in the
subform from the parent form, for new records, so that the relationship
between the parent and "child" records is maintained. If the records
added to Materials are "child" records to the current RFQHeaderRecord,
this is by far the easiest way to manage it.
What is a continuous form?
Click to expand...

A form that shows multiple records on screen at once; rather like a
datasheet only with more layout and format properties to work with.

It sounds like you need to do some reading in the help file about forms
and subforms, and how they work.
 
Thank you for all your help.
You're probably right on the reading. As for how the tables are related
you're correct, the Suppliers is one-to-many with RFQ and RFQ is one-to-many
with Material. I'll start reading and see where it takes me along with your
expert guidance.

Thanks again
Joe
 
Back
Top