Multiple table subform help

  • Thread starter Thread starter Nathan Guill
  • Start date Start date
N

Nathan Guill

Basically, I have infromation from 3 tables I would like to show in a
subform on a from from another table.

I have a form that shows me all of the Part Numbers, and its corresponding
information, on a form. On this form is a subform that will show me the
Drawings associated with this part number. The information for the drawings
are kept in three table (Customers, CSRs, and Drawings). I need to show
Drawings.Sketch, Customers.Name (from Customers.ID = Drawings.CustNum),
CSRs.Name (CSRs.ID = Customers.CSRID), and CSRs.Current (CSRs.ID =
Customers.CSRID).

This all seems pretty simple, yet when I try to create a query that has all
the elements needed to show the fields involved, I get an error (Type
mismatch in expression). What am I doing wrong?

Here is the SQL just in case it's a syntax problem:
SELECT CSRs.CSRID AS CSRs_CSRID, CSRs.Name, CSRs.Current, Customers.CustNum,
Customers.Company, Customers.CSRID AS Customers_CSRID, Drawings.[Index #],
Drawings.CustNumber, Drawings.PartNumber AS Drawings_PartNumber,
PartNumbers.PartNumber AS PartNumbers_PartNumber
FROM (CSRs INNER JOIN Customers ON CSRs.CSRID = Customers.CSRID) INNER JOIN
(PartNumbers INNER JOIN Drawings ON PartNumbers.PartNumber =
Drawings.PartNumber) ON Customers.CustNum = Drawings.CustNumber;
 
Nathan,

Check your datatypes for all of you linking field pairs in
the table designs. This sounds like you may be trying to
link a numeric field with a text field.


Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
You were right. I corrected that and the form opened up. Now, the problem
is that no information populates the form. Any idea?

Gary Miller said:
Nathan,

Check your datatypes for all of you linking field pairs in
the table designs. This sounds like you may be trying to
link a numeric field with a text field.


Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Nathan Guill said:
Basically, I have infromation from 3 tables I would like to show in a
subform on a from from another table.

I have a form that shows me all of the Part Numbers, and its corresponding
information, on a form. On this form is a subform that will show me the
Drawings associated with this part number. The information for the drawings
are kept in three table (Customers, CSRs, and Drawings). I need to show
Drawings.Sketch, Customers.Name (from Customers.ID = Drawings.CustNum),
CSRs.Name (CSRs.ID = Customers.CSRID), and CSRs.Current (CSRs.ID =
Customers.CSRID).

This all seems pretty simple, yet when I try to create a query that has all
the elements needed to show the fields involved, I get an error (Type
mismatch in expression). What am I doing wrong?

Here is the SQL just in case it's a syntax problem:
SELECT CSRs.CSRID AS CSRs_CSRID, CSRs.Name, CSRs.Current, Customers.CustNum,
Customers.Company, Customers.CSRID AS Customers_CSRID, Drawings.[Index #],
Drawings.CustNumber, Drawings.PartNumber AS Drawings_PartNumber,
PartNumbers.PartNumber AS PartNumbers_PartNumber
FROM (CSRs INNER JOIN Customers ON CSRs.CSRID = Customers.CSRID) INNER JOIN
(PartNumbers INNER JOIN Drawings ON PartNumbers.PartNumber =
Drawings.PartNumber) ON Customers.CustNum = Drawings.CustNumber;
Click to expand...
Click to expand...
 
Do you have any records if you just open the query by
itself? You have no records on the Parent form, the subform
or both? Do you have your Master/Child relationships correct
on the subform container control. Should be PartNumber I
would think. Are they the same data type.

Those are a few things to check.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Nathan Guill said:
You were right. I corrected that and the form opened up. Now, the problem
is that no information populates the form. Any idea?

Nathan,

Check your datatypes for all of you linking field pairs in
the table designs. This sounds like you may be trying to
link a numeric field with a text field.


Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Basically, I have infromation from 3 tables I would
Click to expand...
like
to show in a
subform on a from from another table.

I have a form that shows me all of the Part Numbers,
Click to expand...
and
its corresponding
information, on a form. On this form is a subform
Click to expand...
that
will show me the
Drawings associated with this part number. The information for the drawings
are kept in three table (Customers, CSRs, and
Click to expand...
Drawings).
I need to show
Drawings.Sketch, Customers.Name (from Customers.ID = Drawings.CustNum),
CSRs.Name (CSRs.ID = Customers.CSRID), and
Click to expand...
CSRs.Current
(CSRs.ID =
Customers.CSRID).

This all seems pretty simple, yet when I try to create
Click to expand...
a
query that has all
the elements needed to show the fields involved, I get
Click to expand...
an
error (Type
mismatch in expression). What am I doing wrong?

Here is the SQL just in case it's a syntax problem:
SELECT CSRs.CSRID AS CSRs_CSRID, CSRs.Name,
Click to expand...
CSRs.Current,
Customers.CustNum,
Customers.Company, Customers.CSRID AS Customers_CSRID, Drawings.[Index #],
Drawings.CustNumber, Drawings.PartNumber AS Drawings_PartNumber,
PartNumbers.PartNumber AS PartNumbers_PartNumber
FROM (CSRs INNER JOIN Customers ON CSRs.CSRID = Customers.CSRID) INNER JOIN
(PartNumbers INNER JOIN Drawings ON
Click to expand...
PartNumbers.PartNumber
=
Drawings.PartNumber) ON Customers.CustNum = Drawings.CustNumber;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top