I have a form that is returning a bizarre message and I can't figure out why.
The form's record source is the following SQL statement:

SELECT c.*, i.[Advertiser Name]
FROM [CMR Client Numbers] c LEFT OUTER JOIN [Imported CMR Client Numbers]  i
ON c.[CMR Client Number ID] = i.[CMR Client Number ID]
WHERE ((c.[Advertiser ID] IS NULL) OR (c.[Current CMR Office ID] = 113))
ORDER BY c.[CMR Client Number];

Whenever I edit the [Advertiser ID] or [Current CMR Office ID] fields, the
following message pops up:

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record source."

This isn't correct, since there's an OR in the WHERE condition....i.e. the
SQL should return all records that match either criteria, so changing just
one shouldn't remove the record from the recordset. Sure enough, if I close
the form and reopen it, the record I changed will show up again as long as it
continues to meet one of the criteria. Curiously, I get the same error
message even when I change a record so that it meets both criteria (i.e. if
the [Advertiser ID] is not null and the [Current CMR Office ID] is 113 the
record will appear on the form. If I then change the [Advertiser ID] to null,
I get the error message saying it doesn't meet the record source's criteria,
even though the change ensures that the record meets *both* criteria instead
of just one). Also note that the message appears when the changes are written
to the database.

I'm working with an Access 2002 front end and a SQL Server back-end. When I
run update or insert queries on the SQL, everything works fine. It's only
when I make changes in the Access form that I get this odd behavior.

Finally, here's the table structures for the appropriate tables. Note that
the [CMR Client Numbers] table has a calculated field ([Status Flag]) and
constraint (IX_CMR Client Numbers) that were recently added. It was after
these table changes that the odd behavior started.

***********************
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[CMR Client Numbers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [CMR Client Numbers] (
    [CMR Client Number ID] [int] IDENTITY (1, 1) NOT NULL ,
    [Original CMR Office ID] [int] NULL ,
    [Current CMR Office ID] [int] NULL ,
    [Advertiser ID] [int] NULL CONSTRAINT [DF__CMR Clien__Adver__534D60F1]
DEFAULT (0),
    [CMR Client Number] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
    [Rollout Qtr] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Rollout Date] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Current Status] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Pub Assignment] [nvarchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
CONSTRAINT [DF_CMR Client Numbers_Pub Assignment] DEFAULT ('ALL'),
    [Status Flag] AS (case when ([Current Status] = 'X') then [CMR Client
Number ID] end) ,
    CONSTRAINT [aaaaaCMR Client Numbers_PK] PRIMARY KEY  NONCLUSTERED
    (
        [CMR Client Number ID]
    )  ON [PRIMARY] ,
    CONSTRAINT [IX_CMR Client Numbers] UNIQUE  NONCLUSTERED
    (
        [CMR Client Number],
        [Status Flag]
    )  ON [PRIMARY] ,
    CONSTRAINT [CMR Client Numbers_FK000] FOREIGN KEY
    (
        [Advertiser ID]
    ) REFERENCES [Advertisers] (
        [Advertiser ID]
    ) ON DELETE CASCADE  ON UPDATE CASCADE ,
    CONSTRAINT [FK_CMR Client Numbers_CMR Offices01] FOREIGN KEY
    (
        [Original CMR Office ID]
    ) REFERENCES [CMR Offices] (
        [CMR Office ID]
    ),
    CONSTRAINT [FK_CMR Client Numbers_CMR Offices02] FOREIGN KEY
    (
        [Current CMR Office ID]
    ) REFERENCES [CMR Offices] (
        [CMR Office ID]
    )
) ON [PRIMARY]
END

GO


*************************************
if not exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Imported CMR Client Numbers]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
BEGIN
CREATE TABLE [Imported CMR Client Numbers] (
    [Imported CMR Client Number ID] [int] IDENTITY (1, 1) NOT NULL ,
    [CMR Client Number ID] [int] NULL ,
    [Advertiser Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Import Status] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    CONSTRAINT [PK_Imported CMR Client Numbers] PRIMARY KEY  CLUSTERED
    (
        [Imported CMR Client Number ID]
    )  ON [PRIMARY]
) ON [PRIMARY]
END

GO