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.

Thank you in advance.

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.

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.

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.
>

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.


"Helmut Herrmann" wrote:

> 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.
>
> Thank you in advance.

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.
> >
>
>

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.
>> >
>>
>>

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.
> >> >
> >>
> >>
>
>

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.
>> >> >
>> >>
>> >>
>>
>>

None of these suggestions seems to apply to our problem. Fortunately, we
found a solution by chance when we tackled another problem in another form.
For ordering we do no longer use SELECT statements in the RecordSource form
property. Instead we set the form properties OrderBy and OrderByOnLoad
appropriately.

Thank you very much for your time.

"Paul Shapiro" wrote:

> 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.
> >> >> >
> >> >>
> >> >>
> >>
> >>
>
>