Outer join subform problem

[bobby]

Here is the situation:

I am making a classic subform on a form with the main form holding
information about an order. The subform is a continous form holding all
OrderDetail. On the subform the user selects a product and quantity.
However, there is a little twist.

The company only sells about 15 different products and it is therefore a
requirement that the user should have all products listed on the subform and
not select from a combo. The user will then locate the right product and
only enter quantity. I expect to catch the update in the beforeupdate event
and check if OrderDetailNo is null. If so, it is a new entry into
tblOrderDetail, otherwise it is an update.

CREATE TABLE [dbo].[tblOrder] (
[OrderNo] [int] NOT NULL ,
[CustNo] [int] NOT NULL ,
[OrderDate] [datetime] NULL)

CREATE TABLE [dbo].[tblOrderDetail] (
[OrderDetailNo] [bigint] IDENTITY (1, 1) NOT NULL ,
[OrderNo] [int] NOT NULL ,
[ProductNo] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[Styk] [int] NOT NULL ,
[TidsforbrugForLinje] [int] NOT NULL ,
[PrisForLinje] [decimal](18, 2) NOT NULL)

CREATE TABLE [dbo].[tblProduct] (
[ProductNo] [varchar] (20) COLLATE Latin1_General_CI_AS NOT NULL ,
[Active] [bit] NOT NULL ,
[Price] [decimal](19, 2) NOT NULL )

I can't seem to get my head around this one. It smells like an outer join
for the recordsource on the subform but that doesn't quite give me the
correct resultset the way I have constructed it so far.

SELECT tblOrderDetail.OrderDetailNo, tblOrderDetail.OrderNo,
tblOrderDetail.ProductNo, tblOrderDetail.Styk, tblProduct.ProductNo AS
PriProductNo FROM tblOrderDetail RIGHT OUTER JOIN tblProduct ON
tblOrderDetail.ProductNo = tblProduct.ProductNo WHERE (tblProduct.Active =
1)

I am aware I will probably need to do all inserts, updates and deletes using
stored procedures but that all ok.

Any ideas much appreciated.