I have a form in datasheet view with an associated recordsource which looks something like this:
[tt]
SELECT COP.CompanyOrderId, COP.ProductId,
COP.Quantity,
COP.UnitPrice, COP.BillTo, COP.SendTo
FROM tblCompanyOrder CO
INNER JOIN tblCompanyOrderProduct COP
ON CO.CompanyOrderId = COP.CompanyOrderId
INNER JOIN tblProduct P
ON COP.ProductId = P.ProductId
WHERE CO.CompanyId = 262
AND P.ServiceId = 1
AND CO.OrderDate IS NULL
[/tt]
This all works OK, and populates the form as required. However, when I attempt to add a new record, I get the following message:
"The data was added to the database but the data wont be displayed in the form because it does not satisfy the criteria in the underlying record source"
The new record is then removed from display,l although the new record is generated into the COP record (which I have designated in the form as the 'unique table').
My concern here is that I am unable to use a multi table recordsource without this problem. I'm currently re-writing the recordsource to use a single table query with correlated subqueries, but I shouldnt have to do this.
Anyone come across this problem before? Perhaps I'm missing something.
Any help would be greatly appreciated,
Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
[tt]
SELECT COP.CompanyOrderId, COP.ProductId,
COP.Quantity,
COP.UnitPrice, COP.BillTo, COP.SendTo
FROM tblCompanyOrder CO
INNER JOIN tblCompanyOrderProduct COP
ON CO.CompanyOrderId = COP.CompanyOrderId
INNER JOIN tblProduct P
ON COP.ProductId = P.ProductId
WHERE CO.CompanyId = 262
AND P.ServiceId = 1
AND CO.OrderDate IS NULL
[/tt]
This all works OK, and populates the form as required. However, when I attempt to add a new record, I get the following message:
"The data was added to the database but the data wont be displayed in the form because it does not satisfy the criteria in the underlying record source"
The new record is then removed from display,l although the new record is generated into the COP record (which I have designated in the form as the 'unique table').
My concern here is that I am unable to use a multi table recordsource without this problem. I'm currently re-writing the recordsource to use a single table query with correlated subqueries, but I shouldnt have to do this.
Anyone come across this problem before? Perhaps I'm missing something.
Any help would be greatly appreciated,
Cheers,
Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)