D
Donald King
Hi,
I am attempting to create a form that contains a subform with the control
source of both the Form and the Subform being a Query. The whole system has
linked tables to a SQL Server database. This works fine except for some
reason 3 of 25 test records are duplicated????? I tried using 2 queries, one
for the Form and a different query for the Subform. This works fine but you
can not add child records to the subform. The only way I have been able to
make the form work properly is to set the control source of the Form to a
query and the control source of the subform to the related child table. In
all cases, I have defined which fields are the linked fields. Here's the
queries:
Main Form Query:
SELECT dbo_Products.StockNo, dbo_Products.Condition,
dbo_Products.ProductDescription, dbo_Products.Category,
dbo_Products.CategoryNo, dbo_Products.PropertyNo, dbo_Products.Location,
dbo_Products.LeadTime, dbo_Products.ReorderLevel, dbo_Products.UnitPrice,
dbo_Products.ProductType
FROM dbo_Products
WHERE (((dbo_Products.ProductType)="Non-Tubulars"))
ORDER BY dbo_Products.StockNo;
Subform Query: (Can't add records to subform, just view/edit)
SELECT dbo_InventoryTransactions.TransactionDate,
dbo_InventoryTransactions.TransactionDescription,
dbo_InventoryTransactions.SerialNo, dbo_InventoryTransactions.UnitsOrdered,
dbo_InventoryTransactions.UnitsReceived,
dbo_InventoryTransactions.JointsXferred, dbo_InventoryTransactions.Cost,
dbo_InventoryTransactions.MTInvNo, dbo_InventoryTransactions.StockID,
dbo_InventoryTransactions.PropertyID
FROM dbo_InventoryTransactions
ORDER BY dbo_InventoryTransactions.TransactionDate;
Both Form & Subform using same query (3 parent records duplicated?)
SELECT dbo_Products.StockNo, dbo_Products.Condition,
dbo_Products.ProductDescription, dbo_Products.PropertyNo,
dbo_Products.Location, dbo_Products.Category, dbo_Products.CategoryNo,
dbo_Products.ProductType, dbo_Products.ReorderLevel, dbo_Products.LeadTime,
dbo_Products.UnitPrice, dbo_InventoryTransactions.*
FROM dbo_Products INNER JOIN dbo_InventoryTransactions ON
(dbo_Products.StockNo = dbo_InventoryTransactions.StockID) AND
(dbo_Products.PropertyNo = dbo_InventoryTransactions.PropertyID)
WHERE (((dbo_Products.ProductType)="Non-Tubulars"))
ORDER BY dbo_InventoryTransactions.TransactionDate;
Help,
Don
I am attempting to create a form that contains a subform with the control
source of both the Form and the Subform being a Query. The whole system has
linked tables to a SQL Server database. This works fine except for some
reason 3 of 25 test records are duplicated????? I tried using 2 queries, one
for the Form and a different query for the Subform. This works fine but you
can not add child records to the subform. The only way I have been able to
make the form work properly is to set the control source of the Form to a
query and the control source of the subform to the related child table. In
all cases, I have defined which fields are the linked fields. Here's the
queries:
Main Form Query:
SELECT dbo_Products.StockNo, dbo_Products.Condition,
dbo_Products.ProductDescription, dbo_Products.Category,
dbo_Products.CategoryNo, dbo_Products.PropertyNo, dbo_Products.Location,
dbo_Products.LeadTime, dbo_Products.ReorderLevel, dbo_Products.UnitPrice,
dbo_Products.ProductType
FROM dbo_Products
WHERE (((dbo_Products.ProductType)="Non-Tubulars"))
ORDER BY dbo_Products.StockNo;
Subform Query: (Can't add records to subform, just view/edit)
SELECT dbo_InventoryTransactions.TransactionDate,
dbo_InventoryTransactions.TransactionDescription,
dbo_InventoryTransactions.SerialNo, dbo_InventoryTransactions.UnitsOrdered,
dbo_InventoryTransactions.UnitsReceived,
dbo_InventoryTransactions.JointsXferred, dbo_InventoryTransactions.Cost,
dbo_InventoryTransactions.MTInvNo, dbo_InventoryTransactions.StockID,
dbo_InventoryTransactions.PropertyID
FROM dbo_InventoryTransactions
ORDER BY dbo_InventoryTransactions.TransactionDate;
Both Form & Subform using same query (3 parent records duplicated?)
SELECT dbo_Products.StockNo, dbo_Products.Condition,
dbo_Products.ProductDescription, dbo_Products.PropertyNo,
dbo_Products.Location, dbo_Products.Category, dbo_Products.CategoryNo,
dbo_Products.ProductType, dbo_Products.ReorderLevel, dbo_Products.LeadTime,
dbo_Products.UnitPrice, dbo_InventoryTransactions.*
FROM dbo_Products INNER JOIN dbo_InventoryTransactions ON
(dbo_Products.StockNo = dbo_InventoryTransactions.StockID) AND
(dbo_Products.PropertyNo = dbo_InventoryTransactions.PropertyID)
WHERE (((dbo_Products.ProductType)="Non-Tubulars"))
ORDER BY dbo_InventoryTransactions.TransactionDate;
Help,
Don