Form data lost when edited and saved

[John =?ISO-8859-1?Q?=D8llg=E5rd?= Jensen]

Hi,

Using an ADP Access 2003 front end application with SQL Server backend:

An application form is "populated" with several controls (textboxes,
dropdown etc).
Some text fields have recordSource datatype nvarchar, other fields have
datatype ntext.

If "old" data (existing record) in a ntext based field is
corrected/edited and saved (updated) the field data become empty.
nvarchar based fields are working as normal.

Do as follows:
Put cursor in the midle of a word in a ntext based field
Press 'Backspace' for deleting the character to the left og the cursor
position
Enter another character for replacing the old character (entering same
character does not result in the same problem)
Save record (using the standard save-button on an Access form)
Now all data disappears. Data is gone.

Trace:
Data disappears when returning from the before_update handler.
I have tried removing all code from the event-handlers for tracing the
cause of this behaviour, but still data is lost after doing as described.

Any clue for this behaviour?


regards John

 

[John =?ISO-8859-1?Q?=D8llg=E5rd?= Jensen]

Solved by creating a new form object followed by copy/pasting all
controls + code from the faulty form-object to the new one.


- John

 

[John =?ISO-8859-1?Q?=D8llg=E5rd?= Jensen]

Problem still not solved.
Saving a record was only succeeding when no Unique Table property defined.
The RecordSource of the form is a SQL Server View.


Specifying a Unique Table is required if you want to add a new record,
but if I do specify a Unique Table, trying saving data after replacing a
single character of a ntext based field will cause all data of that
field to be lost ( as described ealier).

Why this behaviour? What can be done?????


- John

 

[John =?ISO-8859-1?Q?=D8llg=E5rd?= Jensen]

A work-around "solution" might be using the Unique Table property ONLY
when user wants to add a new record.
By code before creating new record, the Unique Table property could be
set: Me.UniqueTable = "myUniqueTable", else when just editing existing
record this proterty should be cleared/deactivated. I have tried
clearing the property in this maners: Me.UniqueTable = Null or
Me.UniqueTable = "". None of these will accepted be Access. How is the
syntax? Is it possible?

In general: Is it safe omitting the UniqueTable property, when the
recordSource is a multi-table view? Is it mandatory for addressing the
"correct" table to be updated?

Summery: The Access front end is Access 2003 and the backend is SQL
Server 2000.

- John