Invalid Argument from RunSql

[ImHuman]

Hello,

I know what the problem is, I've encountered it before, but the solution I implimented was too unorthodox to me.

When I make a form, I unbound all the fields and use a generic subroutine to update the records where a single field gets modified. Access has never let me update a field from a form textbox to the table when the length of the field is greater than 255 charcters, but now I get the same thing with 127 characters.

There is no #error in any field of any table, the field I am trying to write to is a Memo field, the database is less than 3Mb. I've even tried importing everything into a new blank db, but it won't do it. The problem is not the code because it works fine on smaller fields.

The problem occurs here:

Private Sub SetData(FieldName As String)
CheckRecordExist 'if necessary, makes a new record and sets fClientId (a combobox) to the appropriate record
DoCmd.SetWarnings False
DoCmd.RunSQL "Update Clients Set " & FieldName & "=[f" & FieldName & "] Where ClientId=[fClientId];"
DoCmd.SetWarnings True
End Sub

Private Sub fContactNotes_AfterUpdate()
SetData "ContactNotes"
End Sub

All the fields on the form are a match to the table field names except I always put an 'f' at the start of the control name on forms so Access and I don't get confused with ambigous names. I also do it all this way so if a user modifies any data, and anything happens (were prone to power outages in my city), the data is saved immediately.

On another client's database, I had the code set the recordset of the form to the table and the code store the text in a variable, clear the textbox, move to another record, move back to this record , write the text to the textbox, move to another record, then back again, and finally clear the RecordSource of the form. So it appeared to the user as a little flash on the screen usually, but it saved the data regardless of size.

I would really like to not have this database flicker like that and any other database I make either. Any ideas?

 

[lameid]

Set the value to a field in a recordset from the form control instead of running SQL to update it. I think Jet has a limitation in dealing with more than 255 characters in SQL.

Alternately in your FE, make a table for the recordsource of the form with all the appropriate fields. Bind the form to this. Add a Yes/No column with a validation rule of equal to Yes to limit the record count to 1. Finally update your table based on the column in the new bound column. Be sure to set form properties to keep the form from going to a new record.

 

[PHV]

You may try this:

DoCmd.RunSQL "UPDATE Clients SET " & FieldName & "='" & Replace(Me(f" & FieldName & "), "'", "''") & " WHERE ClientId=" & Me!fClientId

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ImHuman]

IT WORKS

Thanks PHV

I had some errors with your code, but I made it into:

DoCmd.RunSQL "UPDATE Clients SET " & FieldName & "=""" & Replace(Me("f" & FieldName), """", "'") & """ WHERE ClientId=" & Me!fClientId

So it seems that Access truincates the text when the query pulls it from the control on the form, but now the limitation will be maximum string sizes and I don't plan on going near that.

Thanks again PHV

 

[PHV]

Sorry for the typo:

DoCmd.RunSQL "UPDATE Clients SET " & FieldName & "='" & Replace(Me(f" & FieldName & "), "'", "''") & "[!]'[/!] WHERE ClientId=" & Me!fClientId

 

[lameid]

Inhuman,

You are right the issue has to do with textboxes. It was familiar enough that I thought I remembered. Mea Culpa