Update long memo field in tables via VBA

[thewesties]

Could somebody please help me before I dump a gallon of water on my pc!

I am very happy to have come across this site on the internet. TheScripts has helped me through so many issues to this point. I have now spent the last 2 days scouring the net for a resolution to my problem.

I have a DB with MANY tables, but a transfer of 1 memo field between 2 in particular (tbl_Proposal to tbl_Job) is where my problem is. Nothing is working. The field is a proposal which can have next to nothing up to several pages (including carriage returns).

I have left out all the code around this that I know works. The database has been in effect for several years, but is in the process of a major enhancement so there have been no issues with any of the existing globals (g_variable).

Any items that are a global (g_*) are functioning properly. They are passing the proper values in the proper formats.


Here is a snippet of feeble attempt number 1:

g_NewJob = True        ' Flag to control a caption in the form popup_Date
  DoCmd.OpenForm "popup_Date", acNormal, , , , acDialog
 
  If IsNull(g_Accepted_Date) Then
    Exit Sub
  End If
 
  If Trim(g_Accepted_Date & "x") = "x" Then
    ' User entered a blank or hit cancel
    DoCmd.SetWarnings True
    Exit Sub
  End If
 
  'With Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job (Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date, Proposal_Revision_Number, Job_Scope_Of_Work, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date, Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" & Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Bid_Description2 & "', '" & Me.Job_Name & "', '" & Me.Job_Address & "', '" & Me.Job_City & "', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date & "#, '" & Me.Pictures_Directory & "', '" & Me.Excel_Link & "')")

THIS RESULTED IN
Run-time error '3075':

So, I came to the conclusion that because some of these memo fields (Bid_Description2) can be large, Access is having a serious issue with passing text of that size in a single query.

Next, I found a solution that recommended parsing the data using an array. I used the carriage returns as a delimeter to parse the memo in to smaller chunks (fingers crossed).

Feeble attempt number 2

'Without Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job ( Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date, Proposal_Revision_Number, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date, Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" & Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Job_Name & "', '" & Me.Job_Address & "', '" & Me.Job_City & "', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date & "#, '" & Me.Pictures_Directory & "', '" & Me.Excel_Link & "')")
 
  Dim myString
  Dim x As Integer
  Dim myArray() As String
 
  g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID & "));"
 
  Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
   With g_myRS1
    If .EOF And .BOF Then
        MsgBox "No Scope of Work present"
        GoTo LetsMOveOn
      Else
        myString = g_myRS1("Bid_Description2")
    End If
   End With
  g_myRS1.Close
 
 
  myArray = Split(myString, Chr(13) & Chr(10), -1, 1)
 
  For x = LBound(myArray) To UBound(myArray)
    DoCmd.RunSQL ("UPDATE tbl_Job SET tbl_Job.Job_Scope_Of_Work = [tbl_Job]![Job_Scope_Of_Work] & '" & myArray(x) & vbCrLf & "' WHERE (((tbl_Job.Job_Number)='" & g_JobNumber & "'));")
    '  Somewhere in here it died
  Next x

This was working sweet UNTIL about half way through the line of text in the 9th element in the arrray when I received yeat another Run-time 3075 error. This was about 571 characters (including spaces and the CHR's). So, it's not based on the 256 character thing.

OK. Breath deep. Fingers no longer crossed, only one is now extended.
Let's try something else. Feeble attempt number 3

'Without Bid Description 2
  DoCmd.RunSQL ("INSERT INTO tbl_Job ( Job_Number, CustomerID, ContactID, ProposalID, Proposal_Date, Proposal_Revision_Number, Job_Name, Job_Address, Job_City, Job_State, Job_Brief_Description, Job_Accepted_Date, Job_Pictures_Directory, Job_Excel_Link ) " & _
                "VALUES('" & g_JobNumber & "', " & Me.CustomerID & ", " & Me.ContactID & ", " & Me.ProposalID & ", #" & Me.Proposal_Date & "#, '" & Me.Prop_Revision_Number & "', '" & Me.Job_Name & "', '" & Me.Job_Address & "', '" & Me.Job_City & "', '" & Me.Job_State & "', '" & Me.Job_Brief_Description & "', #" & g_Accepted_Date & "#, '" & Me.Pictures_Directory & "', '" & Me.Excel_Link & "')")
 
  g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID & "));"
 
  Dim sDriver As String
  Dim workdb As Database
  Dim worktable As DAO.TableDef
  Dim workset As DAO.Recordset
 
  Set workdb = CurrentDb
  Set worktable = workdb.TableDefs("tbl_Job")
  Set workset = worktable.OpenRecordset(dbOpenTable)
 
  Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
    With g_myRS1
  If Not workset.EOF Then
    sDriver = CStr(g_JobNumber)
    workset.Index = "PrimaryKey"
    workset.Seek "=", sDriver
    If workset.NoMatch Then
      MsgBox ("Entry not found")
      Else
      MsgBox ("Entry found")
    End If
  Else
    MsgBox "No records."
  End If
   
  End With
  g_myRS1.Close

Alas, a new run time error : 3219

Can anyone please help me? I am at my wit's end and I have a bottle of Poland Spring and I'm not afraid to use it...
Laughter is the only thig that keeps me going at this point.

Regards,
Westy

 

[MarkK]

Hey, welcome to the forum!

My first suggestion is that you post the error description instead of, or in addition to, the error number. That a line of your code generates error 3219 just doesn't do that much to describe the problem.

A possible issue when working with string data is that a user has typed in a single or double quote character. This is then interpreted as a delimiter by an update or insert query where you've concatenated the strings in the VALUES or SELECT clause of your SQL statement.

 

[thewesties]

Sorry about leaving the error code descriptions out. Just googled so much, I got used to just the partial.
The Error Code explanations are as follows
Run-time error '3219'
Invalid Operation

Run-time error '3075'
Syntax error (missing operator) in query expression
'[tbl_Job]![Job_Scope_of_Work] & ' 3.4 Excavate 5' deep tree pits'.

Additionally, just to elaborate better. The end goal is to have the memo field tbl_Proposal.Bid_Description2 copied in to the field tbl_Job.Job_Scope_Of_Work.

Since these are construction proposals and jobs, there will almost always be an ' and/or a " present in these bodies of text and there presence becomes a necessary evil.

 

[CyberLynx]

uuuuhhh...looks like it's possibly the Apostrophe dilemma, in particular where you have 5' deep or the variable or control holding as such. There is (or least always has been) a problem with using apostrophe's in query statements. They like to see it as a text delimiter.

There is of course a way around this. Place your memo Control contents into a string Variable (MemStrg perhaps) then use the following code line following before implementing the Variable into query string:


MemStrg = Replace(MemStrg, "'", "''")

What is is doing?

Well, it's replacing a single apostrophe with two apostrophe's so that the Engine running the Query will accept it. Now, we're not changing the Data here, just simply dealing with apostrophe's when using the data for a specific purpose.

Good place to start anyways.

.

 

[MarkK]

Yes, in construction you'll constantly need to retain the user's entries that include either single or double quotes, or both. I commonly solve this problem by using a recordset to update or insert records rather than dynamically creating a SQL statement, which will require delimiters.

Say you have a control, txtDescription, on a form and the user types in 2" x 4" x 8'. If this control is bound to a field in a table you don't have a problem. The data will save correctly.
If the control is not bound and you want to save this text to a table, you can't use

CurrentDB.Execute _
  "UPDATE tMaterial " & _
  "SET Description = '" & Me.txtDescription & "' " & _
  "WHERE MaterialID = 123"

because of the delimiter conflict you already know about. But you can use a recordset.

Dim rst as dao.recordset
Set rst = CurrentDB.OpenRecordset( _
  "SELECT Description " & _
  "FROM tMaterial " & _
  "WHERE MaterialID = 123")
With rst
  .Edit
  !Description = me.txtDescription
  .Update
  .Close
End With

Using recordsets, and possibly hidden controls on forms rather than declared string variables, you should be able to manipulate any fields that contain either or both types of quotes.
I also find that when saving or updating multiple fields in a record the recordset technique can make your code more readable since each value assignment to a recordset field occupies it's own line.
The drawback is that it is considerably slower, but hardly noticeable on a newer computer.