Text box get erased when saving record

[Andy74]

Hello,

I have a form for meeting records. One text box contains meeting notes, which is linked to ntext variable (SQL linked table) on the underlying table. The database has about 40 users but this particular form get no more than 10 new records per day, not so much activity. This is the problem: sometimes a user start filling a new record, fill the meeting notes, but when he saves the record the text box gets blank and the record is saved with null value in the text box. This happens randomly but not very frequently. Sometimes frustrating because the text may contain a full page of text and then when the user save the record all the typing is lost! Text box is with RTF formatting. Does anybody has a clue? I suspect it has to do with the Ntext data type of the underlying table. Shall I convert it to varchar(max)?

Thanks for any help

Andrea

 

[RainLover]

Andy.

Be daring. do a back up and then try it.

 

[Andy74]

Hello,

I have converted the data type to nvarchar(max) on the SQL table, it went straight. I still don't know whether the problem will be solved (it doesn't happen all the time), I will see!

thanks

Andrea

 

[Andy74]

After few days the problems is appearing again. Some users complain some fields get cancelled as soon as the save button is pressed. Any idea could be causing this? The code has no event which may cause the fields to be cancelled.

 

[RainLover]

Andy Some of the wording you are using does not fit in with the norm and is therefore difficult to fully come to grips with what you are doing. Fields to be Cancelled. How do you cancel a field. A field is written into the database so it cannot be cancelled. The contents could be saved deleted or edited but the field stays the same. You mention SQL. Now what is that. Are you using MySQL or SQL server. It is only a little thing but it all helps to understand. Could it be possible that two people/users are attempting to save at exactly the same time and therefore one is getting rejecting.

 

[RainLover]

I assume the SQL resides on the main server for all to share or is it located somewhere else. Has this brought to your mind anything that you feel may be causing the error. How is your data saved. Please post a copy of the code you are using to save

 

[Andy74]

Hello,
sorry for not being precise. It should be: contents of the text box get deleted when the user saves the record. So the record is saved with null values. There is no message of writing conflicts or anything like that. The back end is SQL server and tables are linked tables in the Access front ends.

Andy

 

[RainLover]

There is a couple more to answer please.

 

[Andy74]

The SQL resides on the same common server. For all other forms and tables of the same application everything is working fine. There is no much activity on that table, maybe 6-7 new records per day.

The command to save record is a command button on the form and the associated code is a simple:

Public Function saverec()
On Error GoTo Err_lab
DoCmd.RunCommand acCmdSaveRecord

Err_exit:
Exit Function

Err_lab:
MsgBox Err.Description
Resume Err_exit
End Function


Thanks for your help

 

[RainLover]

Run this for a while. You can add the error trapping later.

Public Function saverec() Me.Dirty = False Exit Function

 

[RainLover]

Something wrong with the way the text is coming out. Hope you can fix it.

 

[Andy74]

Thanks, I will try that and see if it solves the issue. It will take some days to see if it solves in "production" environment.

Andy

 

[RainLover]

Andy Give it a test and if it works go live. No need to wait, just go for it because it is so simple.

 

[Andy74]

yes, it's already live. What I meant is that I am not sure it will solve the issue: the issue was not there all the time, i.e. also the previous one was working well in all my tests, but at the end some of the users (and not always the same) complained! I will keep you updated. thanks

 

[RainLover]

Best wishes with that Andy.

 

[Andy74]

Hello,

I have an update to this old post as the present is still there. What happened is the following: a user enter a new record and fill some text boxes like customer, date of meeting, etc; then he moves to a small subform where he fill the meeting attendants (it may be a list box but I prefer a subform); then he moves to a text box on the main form to fill the "meeting notes". Well, if he types and then stay idle let's say for more than 100 seconds or so without hitting the save button (which fires a me.dirty=false statement) then the next time he clicks the save button then the "meeting notes" textbox gets erased. The rest of the text boxes which were filled before going to the subform are ok because the record was saved automatically when clicking on the subform. I find this really weird! I think it has something to do with clicking on the subform and then going back to the parent form.

I solved this by using a form Timer event every 5 secs

Private Sub Form_Timer()
me.dirty=false
End Sub

but I think this maybe a little bit clumsy. Rain, do you have an explanation or a better workaround?

Andy

 

[RainLover]

Do all the users have the latest version of the front end.

They should.

What is the record source this text box. It should be part of the Record source for all the others.

I do not see a need to save. Save is done by Access when you exit the Record.

 

[Andy74]

yes, users have all the same front end.
Record source is always the same select query based on the same table (linked SQL table). The textbox is bound to a field which an nvarchar(max) field.

I normally keep a save button because users like to push it! Moreover they need to conferm changes in case they modified an existing record. I will try remove any Save button and see if it happens again.

 

[RainLover]

yes, users have all the same front end.
Record source is always the same select query based on the same table (linked SQL table). The textbox is bound to a field which an nvarchar(max) field.

I normally keep a save button because users like to push it! Moreover they need to conferm changes in case they modified an existing record. I will try remove any Save button and see if it happens again.

--------------------------------------------------------------------

Just to confirm. users have all the same front end

Users should have their own front end which is a copy of the Master. Users must never share front ends.

Record source is always the same select query based on the same table (linked SQL table).

Please confirm. There just the one query for all fields. There is nothing different for the problem text box.

I normally keep a save button because users like to push it!

I strongly disagree. I have never seen this before. There should be no need to save when using a Database. This is the way they are designed. This is not excel

 

[Andy74]

I confirm all the above: no front end sharing, same select query for all the fields and nothing different for the text box. I will try tonight to remove the save button.