Createing a Form and Subform

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

Guest

I am using Access 2002 and there are no disabled items. In trying to create a
subform using the Forms Wizard. First I created a query from three related
tables. (One of the tables is the same table that the basic Form is being
created from.) After the data elements from both the table and query have
been added, I get the following warning when I click "Next". "You have chosen
fields from record sources which the wizard can't connect. You may have
chosen fields from a table and from a query base on that table. If so, try
choosing fields from only the table or only the query." I then tried to
cleate a Form and Subform by first creating a Form from the table and then
adding a Subform using the toolbox control for subforms in the form design
view. When I did that, the query was not even available to build a subform
from. However, the query is present when I look at my query objects. (Hmmm.
I wonder what is wrong with my relationships?) Oh well, I am continuing to
solve this problem. Any help anyone can give me would be appreciated.
 
Question Mark said:
I am using Access 2002 and there are no disabled items. In trying to
create a subform using the Forms Wizard. First I created a query from
three related tables. (One of the tables is the same table that the
basic Form is being created from.) After the data elements from both
the table and query have been added, I get the following warning when
I click "Next". "You have chosen fields from record sources which the
wizard can't connect. You may have chosen fields from a table and
from a query base on that table. If so, try choosing fields from only
the table or only the query." I then tried to cleate a Form and
Subform by first creating a Form from the table and then adding a
Subform using the toolbox control for subforms in the form design
view. When I did that, the query was not even available to build a
subform from. However, the query is present when I look at my query
objects. (Hmmm. I wonder what is wrong with my relationships?) Oh
well, I am continuing to solve this problem. Any help anyone can
give me would be appreciated.
Click to expand...

I'm not sure what you did wrong (or what may have gone wrong without
your help <g>), but have you defined the relationships between the
tables in the Relationships window? Also, it sounds as though you may
be trying to include fields that belong to the main form and its
recordsource in the subform itself. That would not be the way to do it.

Would you like to post the details of the tables involved, their
relationships, and what you want to see on the main form and the
subform? Maybe the answer will then become clear.
 
Here are the germain details.
A database with relateded tables - tblCustomer (CustID, Name, Address,
Phone), tblOrder (OrdID, CustID, OrdDate, ShipDate + other fields),
tblOrderDetails (DetailID, OrdID, tblProdID, Quant, UPrice), and
tblProduct (ProdID, Descirp, Price, VendID).

SubformQuery: tblOrder (CustID), tblProduct (Descrip), tblOrdDetails
(OrdDate, ShipDate) tblOrderDetails (Quant, UPrice). No sort or crieteria,
all show.

Form Wizard using tblCustomer >> all fields, switch to SubformQuery >> all
fields, < CustID. Click "Next" or "Finish" and get the warning I posted.

Thanks for your help.
 
Question Mark said:
Here are the germain details.
A database with relateded tables - tblCustomer (CustID, Name, Address,
Phone), tblOrder (OrdID, CustID, OrdDate, ShipDate + other fields),
tblOrderDetails (DetailID, OrdID, tblProdID, Quant, UPrice), and
tblProduct (ProdID, Descirp, Price, VendID).

SubformQuery: tblOrder (CustID), tblProduct (Descrip), tblOrdDetails
(OrdDate, ShipDate) tblOrderDetails (Quant, UPrice). No sort or
crieteria, all show.

Form Wizard using tblCustomer >> all fields, switch to SubformQuery
I posted.
Click to expand...

If you take out SubformQuery.CustID, there is no field to link the main
form to the subform. That field *must* be included in the subform's
recordsource, though you can make it invisible on the subform itself if
you want (I usually do).

What is this subform going to be used for? It seems to me you don't
include the necessary key fields in SubformQuery to make it updatable,
nor to distinguish the details of one order from those of another. Is
this just for a quick look at the products a particular customer has
ordered?
 
Dirk, you say SubformQuery.CustID must stay. It is redundant to
tblOrder.CustID. Besides, I already tried and it makes no difference. Subform
will be used to review the customers order history. It should be updateable,
all the key fields are there. Any more thoughts?
 
Question Mark said:
Dirk, you say SubformQuery.CustID must stay. It is redundant to
tblOrder.CustID. Besides, I already tried and it makes no difference.
Click to expand...

Maybe we aren't understanding one another. There are two recordsources
involved here: the one for the main form, which you tell me is
tblCustomer, and the one for the subform, which you tell me is
SubformQuery. The linking field between them is CustID, which must be
present in both recordsources. If you say you already tried that with
no success, I can only think that something else must have been wrong as
well, but it's hard for me to say what with the information available.
Subform will be used to review the customers order history. It
should be updateable, all the key fields are there. Any more
thoughts?
Click to expand...

In your description of SubformQuery, all the key fields were not
present. Maybe that was an incomplete description of the query. Please
copy the SQL of the query and post it in a reply, so I can see what's
really in there.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
 
Dirk, sorry to be at the end of the day to reply. Here is the SQL View you
asked for.

SELECT Orders.CustomerID, Products.Description, Orders.OrderDate,
Orders.ShipDate, Orders.ShipVia, Orders.ShipCost, OrderDetail.Quantity,
OrderDetail.UnitPrice
FROM (Orders INNER JOIN OrderDetail ON Orders.OrderID = OrderDetail.OrderID)
INNER JOIN Products ON OrderDetail.ProductID = Products.ProductID;
 
Question Mark said:
Dirk, sorry to be at the end of the day to reply. Here is the SQL
View you asked for.

SELECT Orders.CustomerID, Products.Description, Orders.OrderDate,
Orders.ShipDate, Orders.ShipVia, Orders.ShipCost,
OrderDetail.Quantity, OrderDetail.UnitPrice
FROM (Orders INNER JOIN OrderDetail ON Orders.OrderID =
OrderDetail.OrderID) INNER JOIN Products ON OrderDetail.ProductID =
Products.ProductID;
Click to expand...

If you look at that, you'll see that neither the primary key field nor
the foreign key fields of *any* of the tables is included in the query's
select list, with the exception of the foreign key field
Orders.CustomerID, which is not involved in the joins. Somewhat to my
surprise, it appears that such a query will in fact be updatable, but I
think you won't be able to add records in any but the Orders table.
Since this query is to display history, I don't expect that's important.

So, if you use this query for your subform, including all the fields,
does the wizard still not think there's a suitable field for linking the
subform with the main form?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 
Back
Top