Update Query with Memo

[tonyrusin]

Hi Access MVPs,

I'm using Access 2003. I have a bound form with subforms that's used
for creating and revising records.

My issue is on the side of revising existing records. Once the user
is finished revising a record, there is a submit button that finalizes
the changes. If the user decides to cancel, they simply close the
form without submitting and the idea is to revert the record back to
its original state.

The way I accomplish this is somewhat convoluted. When the user
chooses to revise the record, I append the original record to an
archive table, update the live record with system changes, including a
timestamp, and open it up for user changes. At this time, the record
is updated which would happen anyways if they were to change focus to
a subform. If the user then decides to cancel after this update, I
have an update query that copies the latest version of that record
from archive table back over the canceled changes. Here is the problem
query (containing only the necessary pieces to present the issue):

UPDATE tblCCN SET tblCCN.Description = (SELECT TOP 1
tblCCNArchive.Description FROM tblCCNArchive WHERE
(((tblCCNArchive.CCN)=[Forms]![frmCCNManager]![txtCCN])) ORDER BY
tblCCNArchive.LastUpdated DESC)
WHERE (((tblCCN.CCN)=[Forms]![frmCCNManager]![CCN]));

Each field is updated using a subquery and my timestamp field is
'LastUpdated'. The entire query works great except the
'tblCCN.Description' field in the example above is the one I run into
an error with because it is a memo field (Ref Error 3342). Any ideas
on a work around?

Thanks in advance for any help,

- Tony

 

[Pieter Wijnen]

You have to use the GetChunck & AppendChunck methods of a recordset, I think
Group by etc doesn't compute for memo fields

Pieter

 

[tonyrusin]

Can GetChunk & AppendChunk be used in the Access query builder in some
way or is my only option to create a recordset?

- Tony

You have to use the GetChunck & AppendChunck methods of a recordset, I think
Group by etc doesn't compute for memo fields

Pieter

Hi Access MVPs,

I'm using Access 2003. I have a bound form with subforms that's used
for creating and revising records.

My issue is on the side of revising existing records. Once the user
is finished revising a record, there is a submit button that finalizes
the changes. If the user decides to cancel, they simply close the
form without submitting and the idea is to revert the record back to
its original state.

The way I accomplish this is somewhat convoluted. When the user
chooses to revise the record, I append the original record to an
archive table, update the live record with system changes, including a
timestamp, and open it up for user changes. At this time, the record
is updated which would happen anyways if they were to change focus to
a subform. If the user then decides to cancel after this update, I
have an update query that copies the latest version of that record
from archive table back over the canceled changes. Here is the problem
query (containing only the necessary pieces to present the issue):

UPDATE tblCCN SET tblCCN.Description = (SELECT TOP 1
tblCCNArchive.Description FROM tblCCNArchive WHERE
(((tblCCNArchive.CCN)=[Forms]![frmCCNManager]![txtCCN])) ORDER BY
tblCCNArchive.LastUpdated DESC)
WHERE (((tblCCN.CCN)=[Forms]![frmCCNManager]![CCN]));

Each field is updated using a subquery and my timestamp field is
'LastUpdated'. The entire query works great except the
'tblCCN.Description' field in the example above is the one I run into
an error with because it is a memo field (Ref Error 3342). Any ideas
on a work around?

Thanks in advance for any help,

- Tony- Hide quoted text -

- Show quoted text -

 

[Pieter Wijnen]

you have to use code

Pieter

Can GetChunk & AppendChunk be used in the Access query builder in some
way or is my only option to create a recordset?

- Tony

You have to use the GetChunck & AppendChunck methods of a recordset, I
think
Group by etc doesn't compute for memo fields

Pieter

Hi Access MVPs,

I'm using Access 2003. I have a bound form with subforms that's used
for creating and revising records.

My issue is on the side of revising existing records. Once the user
is finished revising a record, there is a submit button that finalizes
the changes. If the user decides to cancel, they simply close the
form without submitting and the idea is to revert the record back to
its original state.

The way I accomplish this is somewhat convoluted. When the user
chooses to revise the record, I append the original record to an
archive table, update the live record with system changes, including a
timestamp, and open it up for user changes. At this time, the record
is updated which would happen anyways if they were to change focus to
a subform. If the user then decides to cancel after this update, I
have an update query that copies the latest version of that record
from archive table back over the canceled changes. Here is the problem
query (containing only the necessary pieces to present the issue):

UPDATE tblCCN SET tblCCN.Description = (SELECT TOP 1
tblCCNArchive.Description FROM tblCCNArchive WHERE
(((tblCCNArchive.CCN)=[Forms]![frmCCNManager]![txtCCN])) ORDER BY
tblCCNArchive.LastUpdated DESC)
WHERE (((tblCCN.CCN)=[Forms]![frmCCNManager]![CCN]));

Each field is updated using a subquery and my timestamp field is
'LastUpdated'. The entire query works great except the
'tblCCN.Description' field in the example above is the one I run into
an error with because it is a memo field (Ref Error 3342). Any ideas
on a work around?

Thanks in advance for any help,

- Tony- Hide quoted text -

- Show quoted text -