I just looked at some of the changes I made while resolving similar issues
and saw that in addition to my suggestion to include Set NoCount On in any
stored procedures being used, I went back and added that same setting to all
triggers. Since that was the last thing I changed, maybe that was the key?

"Paul Shapiro" <(E-Mail Removed)> wrote in message news:...
> Sorry, I know this is a real pain. This kind of hidden inner working is
> part of why I plan on shifting my development out of Access. I've fought
> through this one before too.
>
> Searching for the error message on google yields plenty of hits, with
> suggestions for:
> 1. Specify the Unique Table in the form properties, with or without a
> resync command.
> 2. If using any stored procedures, include Set NoCount On in the beginning
> of the SP since Access seems to get confused when affected record counts
> are returned.
> 3. A trigger on any affected tables can return the incorrect ID value,
> similar to what you're seeing, since Access uses @@identity instead of the
> scope identity value. This might have been changed in Access 2007. Check
> the details with SQL Profiler.
> 4. Replicated databases are more subject to this issue since they always
> have system triggers.
> 5. http://support.microsoft.com/kb/951937 has a hotfix for "On a computer
> that is running Windows Vista, Windows Server 2008, or Windows XP, an
> incorrect value is returned when an application queries the identity
> column value of a newly inserted row in various versions of SQL Server
> 2005 and of SQL Server 2000".
> 6. From another discussion, "We now have updates to correct this problem
> available on the Microsoft Download Center. Windows Vista and Windows
> Server2008 link is:"
> http://www.microsoft.com/downloads/d...displaylang=en
> 7. The issue is also discussed in http://support.microsoft.com/kb/q248095/
>
> "Helmut Herrmann" <(E-Mail Removed)> wrote in
> message news:C3901BE0-A423-42E6-AA26-(E-Mail Removed)...
>>I followed your hint and removed the "TOP 100 PERCENT" clause. The subform
>> record source is now
>>
>> SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos
>>
>> and everything works as before. Then I removed the resync command in
>> order
>> to see what happens (sure, I tested this before but could not remember
>> the
>> details). Without the resync command I get:
>>
>> 1. SQL Profiler trace (note that the value for @P1 is correct):
>> exec sp_executesql N'SELECT * FROM (SELECT dbo.t_RECHNUNGS_POS.* FROM
>> dbo.t_RECHNUNGS_POS) AS DRVD_TBL WHERE "Re-PosID" = @P1 ORDER BY
>> Pos',N'@P1
>> int',22411
>>
>> 2. Error message in Access:
>> 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 is the reason why I added the resync command after upgrading from
>> Access 2003/SQL 2000 to Access 2007/SQL 2005. Now I inserted the
>> simplified
>> resync command which now reads
>>
>> SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE ([Re-PosID] = ?)
>>
>> Unfortunately, this results in the same error described in my first post.
>> SQL Profiler trace (note that the value for @P1 is 0):
>>
>> exec sp_executesql N'SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS)
>> WHERE
>> ([Re-PosID] = @P1)',N'@P1 int',0
>>
>> By the way, I found a similar problem report at
>> http://forums.microsoft.com/TechNet/...3643&SiteID=17
>> but the solution affects Vista or Server 2008 only. Our problem is with
>> Vista and XP and with Server 2003.
>>
>> "Paul Shapiro" wrote:
>>
>>> Since you're only selecting a single row, you can simplify the resync
>>> command. I believe it has to use the PK attributes, and cannot use any
>>> other
>>> attributes. What happens if you set the resync command to:
>>>
>>> SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?
>>>
>>> Also, sometimes Access seems to do better when the resync command is
>>> left
>>> blank. I think though your initial post said that's how it was in Access
>>> 2003, and now it stopped working in Access 2007, which is why you're
>>> specifying the resync command?
>>>
>>> If you're entering the sql directly into the subform's record source
>>> setting, you don't need the TOP 100 PERCENT clause. That's only needed
>>> in a
>>> view definition, where it was never a recommended syntax. I believe
>>> sorting
>>> in a view is ignored in SQL 2005 and later even with that clause. Access
>>> seems to do better with simpler record source settings. You could try
>>> simplifying the subform record source to:
>>>
>>> SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos
>>>
>>> "Helmut Herrmann" <(E-Mail Removed)> wrote in
>>> message news:10D3BD52-DE19-4241-A862-(E-Mail Removed)...
>>> > Sorry to post this twice, but the first time I replied to the wrong
>>> > post.
>>> > ---
>>> > The SQL Profiler trace contains only subform messages because nothing
>>> > happens in the main form. The record source in the subform settings is
>>> > as
>>> > follows:
>>> >
>>> > SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS
>>> > ORDER
>>> > BY Pos
>>> >
>>> > The resync command is this:
>>> >
>>> > SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
>>> > BY
>>> > Pos) WHERE (ReID = ?)
>>> > or
>>> > SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS ORDER
>>> > BY
>>> > Pos) WHERE ([Re-PosID] = ?)
>>> >
>>> > As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS" (subform)
>>> > and
>>> > "ReID" is one of the fields in this table and is linked to the PK
>>> > "ReID"
>>> > of
>>> > "t_Rechnungen" in the main form.
>>> >
>>> > "Paul Shapiro" wrote:
>>> >
>>> >> Can you provide the record source and resync commands you're using in
>>> >> the
>>> >> subform? You earlier stated that the SQL Profiler trace was from the
>>> >> successful subform insertion. Is that correct, or is that the trace
>>> >> from
>>> >> the
>>> >> main form insert? Both the successful insert and the failed select
>>> >> seem
>>> >> to
>>> >> reference the same table name, so it would seem that either both are
>>> >> from
>>> >> the main form or both from the subform? In which case the PK
>>> >> attribute
>>> >> name
>>> >> would have to be the same?
>>> >>
>>> >> "Helmut Herrmann" <(E-Mail Removed)> wrote in
>>> >> message news:617778CF-4783-4839-BF59-(E-Mail Removed)...
>>> >> > First of all, thank you for your reply.
>>> >> > Yes, the SELECT command is the resync command, namely of the
>>> >> > subform.
>>> >> > "ReID"
>>> >> > is the primary key of the unique table of the main form and it is
>>> >> > the
>>> >> > link
>>> >> > of
>>> >> > the records in the subform to the record in the main form. The
>>> >> > primary
>>> >> > key
>>> >> > of
>>> >> > the table in the subform is "Re-PosID". This is why I tried both
>>> >> > "ReID"
>>> >> > and
>>> >> > "Re-posID". In either case Access does not fill in the correct
>>> >> > value
>>> >> > for
>>> >> > "@P1". This value is always 0.
>>> >> >
>>> >> >
>>> >> > "Paul Shapiro" wrote:
>>> >> >
>>> >> >> The successful insert statement uses "ReID" as the primary key
>>> >> >> attribute,
>>> >> >> but the select (which I guess is the resync) uses [Re-PosID] in
>>> >> >> the
>>> >> >> where
>>> >> >> clause. It also uses a value of 0 for that attribute, which might
>>> >> >> be
>>> >> >> because
>>> >> >> it has the wrong attribute name?
>>> >> >>
>>> >> >> "Helmut Herrmann" <(E-Mail Removed)> wrote
>>> >> >> in
>>> >> >> message news:EF41DFCE-3808-43E6-876A-(E-Mail Removed)...
>>> >> >> > After upgrading from Access 2003/SQL Server 2000 to Access
>>> >> >> > 2007/SQL
>>> >> >> > Server
>>> >> >> > 2005 the following problem occurred:
>>> >> >> >
>>> >> >> > In some forms with subforms the data source of the main form is
>>> >> >> > a
>>> >> >> > view
>>> >> >> > and
>>> >> >> > the data source of the subform is a table with a field
>>> >> >> > containing
>>> >> >> > the
>>> >> >> > ID
>>> >> >> > of
>>> >> >> > the record in the main form. When I insert a new record in the
>>> >> >> > subform
>>> >> >> > this
>>> >> >> > works well. But when I want to edit this new record, I get the
>>> >> >> > error
>>> >> >> > message
>>> >> >> > that another user has changed the record which is definitely not
>>> >> >> > true.
>>> >> >> > It
>>> >> >> > is
>>> >> >> > not possible to save the edited record. I have to leave the
>>> >> >> > record
>>> >> >> > in
>>> >> >> > the
>>> >> >> > main form and reenter it in order to be able to edit the new
>>> >> >> > record
>>> >> >> > in
>>> >> >> > the
>>> >> >> > subform.
>>> >> >> >
>>> >> >> > I used SQL profiler to see what is going on. Regarding insertion
>>> >> >> > of
>>> >> >> > the
>>> >> >> > new
>>> >> >> > record in the subfrom the profiler recorded the following:
>>> >> >> > ---------
>>> >> >> > exec sp_executesql N'INSERT INTO
>>> >> >> > "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
>>> >> >> > ("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStSatz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
>>> >> >> > VALUES
>>> >> >> > (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
>>> >> >> > int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
>>> >> >> > nvarchar(5),@P7
>>> >> >> > int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12
>>> >> >> > float,@P13
>>> >> >> > datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0000,N'EUR',1,0,'2008-08-27
>>> >> >> > 00:00:00:000'
>>> >> >> >
>>> >> >> > Error: 156, Severity: 15, State: 1
>>> >> >> >
>>> >> >> > Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
>>> >> >> > (in English something like: Wrong syntax near 'WHERE' keyword.)
>>> >> >> >
>>> >> >> > exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
>>> >> >> > dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos)
>>> >> >> > WHERE
>>> >> >> > dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
>>> >> >> > ---------
>>> >> >> >
>>> >> >> > I conlude from this that the resync command of the subform has a
>>> >> >> > problem
>>> >> >> > and
>>> >> >> > that this is the reason why subsequent attempts to update the
>>> >> >> > new
>>> >> >> > record
>>> >> >> > fail. The resync command was not necessary with Access 3003/SQL
>>> >> >> > Server
>>> >> >> > 2000.
>>> >> >> > Can anyone please help me with the resync command? I could not
>>> >> >> > figure
>>> >> >> > out
>>> >> >> > what I am doing wrong.

"Paul Shapiro" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> I just looked at some of the changes I made while resolving similar issues
> and saw that in addition to my suggestion to include Set NoCount On in any
> stored procedures being used, I went back and added that same setting to
all
> triggers. Since that was the last thing I changed, maybe that was the key?
>
> "Paul Shapiro" <(E-Mail Removed)> wrote in message news:...
> > Sorry, I know this is a real pain. This kind of hidden inner working is
> > part of why I plan on shifting my development out of Access. I've fought
> > through this one before too.
> >
> > Searching for the error message on google yields plenty of hits, with
> > suggestions for:
> > 1. Specify the Unique Table in the form properties, with or without a
> > resync command.
> > 2. If using any stored procedures, include Set NoCount On in the
beginning
> > of the SP since Access seems to get confused when affected record counts
> > are returned.
> > 3. A trigger on any affected tables can return the incorrect ID value,
> > similar to what you're seeing, since Access uses @@identity instead of
the
> > scope identity value. This might have been changed in Access 2007. Check
> > the details with SQL Profiler.
> > 4. Replicated databases are more subject to this issue since they always
> > have system triggers.
> > 5. http://support.microsoft.com/kb/951937 has a hotfix for "On a
computer
> > that is running Windows Vista, Windows Server 2008, or Windows XP, an
> > incorrect value is returned when an application queries the identity
> > column value of a newly inserted row in various versions of SQL Server
> > 2005 and of SQL Server 2000".
> > 6. From another discussion, "We now have updates to correct this problem
> > available on the Microsoft Download Center. Windows Vista and Windows
> > Server2008 link is:"
> >
http://www.microsoft.com/downloads/d...displaylang=en
> > 7. The issue is also discussed in
http://support.microsoft.com/kb/q248095/
> >
> > "Helmut Herrmann" <(E-Mail Removed)> wrote in
> > message news:C3901BE0-A423-42E6-AA26-(E-Mail Removed)...
> >>I followed your hint and removed the "TOP 100 PERCENT" clause. The
subform
> >> record source is now
> >>
> >> SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos
> >>
> >> and everything works as before. Then I removed the resync command in
> >> order
> >> to see what happens (sure, I tested this before but could not remember
> >> the
> >> details). Without the resync command I get:
> >>
> >> 1. SQL Profiler trace (note that the value for @P1 is correct):
> >> exec sp_executesql N'SELECT * FROM (SELECT dbo.t_RECHNUNGS_POS.* FROM
> >> dbo.t_RECHNUNGS_POS) AS DRVD_TBL WHERE "Re-PosID" = @P1 ORDER BY
> >> Pos',N'@P1
> >> int',22411
> >>
> >> 2. Error message in Access:
> >> 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 is the reason why I added the resync command after upgrading from
> >> Access 2003/SQL 2000 to Access 2007/SQL 2005. Now I inserted the
> >> simplified
> >> resync command which now reads
> >>
> >> SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS) WHERE ([Re-PosID] =
?)
> >>
> >> Unfortunately, this results in the same error described in my first
post.
> >> SQL Profiler trace (note that the value for @P1 is 0):
> >>
> >> exec sp_executesql N'SELECT * FROM (SELECT * FROM dbo.t_RECHNUNGS_POS)
> >> WHERE
> >> ([Re-PosID] = @P1)',N'@P1 int',0
> >>
> >> By the way, I found a similar problem report at
> >>
http://forums.microsoft.com/TechNet/...3643&SiteID=17
> >> but the solution affects Vista or Server 2008 only. Our problem is with
> >> Vista and XP and with Server 2003.
> >>
> >> "Paul Shapiro" wrote:
> >>
> >>> Since you're only selecting a single row, you can simplify the resync
> >>> command. I believe it has to use the PK attributes, and cannot use any
> >>> other
> >>> attributes. What happens if you set the resync command to:
> >>>
> >>> SELECT * FROM dbo.t_RECHNUNGS_POS WHERE ([Re-PosID] = ?
> >>>
> >>> Also, sometimes Access seems to do better when the resync command is
> >>> left
> >>> blank. I think though your initial post said that's how it was in
Access
> >>> 2003, and now it stopped working in Access 2007, which is why you're
> >>> specifying the resync command?
> >>>
> >>> If you're entering the sql directly into the subform's record source
> >>> setting, you don't need the TOP 100 PERCENT clause. That's only needed
> >>> in a
> >>> view definition, where it was never a recommended syntax. I believe
> >>> sorting
> >>> in a view is ignored in SQL 2005 and later even with that clause.
Access
> >>> seems to do better with simpler record source settings. You could try
> >>> simplifying the subform record source to:
> >>>
> >>> SELECT * FROM dbo.t_RECHNUNGS_POS ORDER BY Pos
> >>>
> >>> "Helmut Herrmann" <(E-Mail Removed)> wrote in
> >>> message news:10D3BD52-DE19-4241-A862-(E-Mail Removed)...
> >>> > Sorry to post this twice, but the first time I replied to the wrong
> >>> > post.
> >>> > ---
> >>> > The SQL Profiler trace contains only subform messages because
nothing
> >>> > happens in the main form. The record source in the subform settings
is
> >>> > as
> >>> > follows:
> >>> >
> >>> > SELECT TOP 100 PERCENT dbo.t_RECHNUNGS_POS.* FROM
dbo.t_RECHNUNGS_POS
> >>> > ORDER
> >>> > BY Pos
> >>> >
> >>> > The resync command is this:
> >>> >
> >>> > SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS
ORDER
> >>> > BY
> >>> > Pos) WHERE (ReID = ?)
> >>> > or
> >>> > SELECT * FROM (SELECT TOP 100 PERCENT * FROM dbo.t_RECHNUNGS_POS
ORDER
> >>> > BY
> >>> > Pos) WHERE ([Re-PosID] = ?)
> >>> >
> >>> > As I wrote before, "Re-PosID" is the PK of "t_RECHNUNGS_POS"
(subform)
> >>> > and
> >>> > "ReID" is one of the fields in this table and is linked to the PK
> >>> > "ReID"
> >>> > of
> >>> > "t_Rechnungen" in the main form.
> >>> >
> >>> > "Paul Shapiro" wrote:
> >>> >
> >>> >> Can you provide the record source and resync commands you're using
in
> >>> >> the
> >>> >> subform? You earlier stated that the SQL Profiler trace was from
the
> >>> >> successful subform insertion. Is that correct, or is that the trace
> >>> >> from
> >>> >> the
> >>> >> main form insert? Both the successful insert and the failed select
> >>> >> seem
> >>> >> to
> >>> >> reference the same table name, so it would seem that either both
are
> >>> >> from
> >>> >> the main form or both from the subform? In which case the PK
> >>> >> attribute
> >>> >> name
> >>> >> would have to be the same?
> >>> >>
> >>> >> "Helmut Herrmann" <(E-Mail Removed)> wrote
in
> >>> >> message news:617778CF-4783-4839-BF59-(E-Mail Removed)...
> >>> >> > First of all, thank you for your reply.
> >>> >> > Yes, the SELECT command is the resync command, namely of the
> >>> >> > subform.
> >>> >> > "ReID"
> >>> >> > is the primary key of the unique table of the main form and it is
> >>> >> > the
> >>> >> > link
> >>> >> > of
> >>> >> > the records in the subform to the record in the main form. The
> >>> >> > primary
> >>> >> > key
> >>> >> > of
> >>> >> > the table in the subform is "Re-PosID". This is why I tried both
> >>> >> > "ReID"
> >>> >> > and
> >>> >> > "Re-posID". In either case Access does not fill in the correct
> >>> >> > value
> >>> >> > for
> >>> >> > "@P1". This value is always 0.
> >>> >> >
> >>> >> >
> >>> >> > "Paul Shapiro" wrote:
> >>> >> >
> >>> >> >> The successful insert statement uses "ReID" as the primary key
> >>> >> >> attribute,
> >>> >> >> but the select (which I guess is the resync) uses [Re-PosID] in
> >>> >> >> the
> >>> >> >> where
> >>> >> >> clause. It also uses a value of 0 for that attribute, which
might
> >>> >> >> be
> >>> >> >> because
> >>> >> >> it has the wrong attribute name?
> >>> >> >>
> >>> >> >> "Helmut Herrmann" <(E-Mail Removed)>
wrote
> >>> >> >> in
> >>> >> >> message
news:EF41DFCE-3808-43E6-876A-(E-Mail Removed)...
> >>> >> >> > After upgrading from Access 2003/SQL Server 2000 to Access
> >>> >> >> > 2007/SQL
> >>> >> >> > Server
> >>> >> >> > 2005 the following problem occurred:
> >>> >> >> >
> >>> >> >> > In some forms with subforms the data source of the main form
is
> >>> >> >> > a
> >>> >> >> > view
> >>> >> >> > and
> >>> >> >> > the data source of the subform is a table with a field
> >>> >> >> > containing
> >>> >> >> > the
> >>> >> >> > ID
> >>> >> >> > of
> >>> >> >> > the record in the main form. When I insert a new record in the
> >>> >> >> > subform
> >>> >> >> > this
> >>> >> >> > works well. But when I want to edit this new record, I get the
> >>> >> >> > error
> >>> >> >> > message
> >>> >> >> > that another user has changed the record which is definitely
not
> >>> >> >> > true.
> >>> >> >> > It
> >>> >> >> > is
> >>> >> >> > not possible to save the edited record. I have to leave the
> >>> >> >> > record
> >>> >> >> > in
> >>> >> >> > the
> >>> >> >> > main form and reenter it in order to be able to edit the new
> >>> >> >> > record
> >>> >> >> > in
> >>> >> >> > the
> >>> >> >> > subform.
> >>> >> >> >
> >>> >> >> > I used SQL profiler to see what is going on. Regarding
insertion
> >>> >> >> > of
> >>> >> >> > the
> >>> >> >> > new
> >>> >> >> > record in the subfrom the profiler recorded the following:
> >>> >> >> > ---------
> >>> >> >> > exec sp_executesql N'INSERT INTO
> >>> >> >> > "zentraleSQL"."dbo"."t_RECHNUNGS_POS"
> >>> >> >> >
("ReID","Pos","Anzahl","Bezeichnung","Einzelpreis","Einheit","Rabatt","MwStS
atz","EinzelpreisEuro","Währung","Kurs","Betrag","DatumEingabe")
> >>> >> >> > VALUES
> >>> >> >> >
(@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P12,@P13)',N'@P1
> >>> >> >> > int,@P2 smallint,@P3 real,@P4 nvarchar(10),@P5 money,@P6
> >>> >> >> > nvarchar(5),@P7
> >>> >> >> > int,@P8 real,@P9 money,@P10 nvarchar(3),@P11 float,@P12
> >>> >> >> > float,@P13
> >>> >> >> >
datetime',9281,4,1,N'BBBBBBBBBB',$0.0000,N'Stück',0,0,18999999761581421,$0.0
000,N'EUR',1,0,'2008-08-27
> >>> >> >> > 00:00:00:000'
> >>> >> >> >
> >>> >> >> > Error: 156, Severity: 15, State: 1
> >>> >> >> >
> >>> >> >> > Falsche Syntax in der Nähe des 'WHERE'-Schlüsselwortes.
> >>> >> >> > (in English something like: Wrong syntax near 'WHERE'
keyword.)
> >>> >> >> >
> >>> >> >> > exec sp_executesql N'SELECT * FROM (SELECT TOP 100 PERCENT
> >>> >> >> > dbo.t_RECHNUNGS_POS.* FROM dbo.t_RECHNUNGS_POS ORDER BY Pos)
> >>> >> >> > WHERE
> >>> >> >> > dbo.t_RECHNUNGS_POS.[Re-PosID] = @P1',N'@P1 int',0
> >>> >> >> > ---------
> >>> >> >> >
> >>> >> >> > I conlude from this that the resync command of the subform has
a
> >>> >> >> > problem
> >>> >> >> > and
> >>> >> >> > that this is the reason why subsequent attempts to update the
> >>> >> >> > new
> >>> >> >> > record
> >>> >> >> > fail. The resync command was not necessary with Access
3003/SQL
> >>> >> >> > Server
> >>> >> >> > 2000.
> >>> >> >> > Can anyone please help me with the resync command? I could not
> >>> >> >> > figure
> >>> >> >> > out
> >>> >> >> > what I am doing wrong.
>