By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,849 Members | 2,361 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,849 IT Pros & Developers. It's quick & easy.

Update long memo field in tables via VBA

thewesties
P: 2
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:


Expand|Select|Wrap|Line Numbers
  1.   g_NewJob = True        ' Flag to control a caption in the form popup_Date
  2.   DoCmd.OpenForm "popup_Date", acNormal, , , , acDialog
  3.  
  4.   If IsNull(g_Accepted_Date) Then
  5.     Exit Sub
  6.   End If
  7.  
  8.   If Trim(g_Accepted_Date & "x") = "x" Then
  9.     ' User entered a blank or hit cancel
  10.     DoCmd.SetWarnings True
  11.     Exit Sub
  12.   End If
  13.  
  14.   'With Bid Description 2
  15.   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 ) " & _
  16.                 "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 & "')")
  17.  

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


Expand|Select|Wrap|Line Numbers
  1. 'Without Bid Description 2
  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 ) " & _
  3.                 "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 & "')")
  4.  
  5.   Dim myString
  6.   Dim x As Integer
  7.   Dim myArray() As String
  8.  
  9.   g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID & "));"
  10.  
  11.   Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
  12.    With g_myRS1
  13.     If .EOF And .BOF Then
  14.         MsgBox "No Scope of Work present"
  15.         GoTo LetsMOveOn
  16.       Else
  17.         myString = g_myRS1("Bid_Description2")
  18.     End If
  19.    End With
  20.   g_myRS1.Close
  21.  
  22.  
  23.   myArray = Split(myString, Chr(13) & Chr(10), -1, 1)
  24.  
  25.   For x = LBound(myArray) To UBound(myArray)
  26.     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 & "'));")
  27.     '  Somewhere in here it died
  28.   Next x
  29.  

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


Expand|Select|Wrap|Line Numbers
  1. 'Without Bid Description 2
  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 ) " & _
  3.                 "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 & "')")
  4.  
  5.   g_SQLStmt1 = "SELECT tbl_Proposal.Bid_Description2 FROM tbl_Proposal WHERE (((tbl_Proposal.ProposalID)=" & Me.ProposalID & "));"
  6.  
  7.   Dim sDriver As String
  8.   Dim workdb As Database
  9.   Dim worktable As DAO.TableDef
  10.   Dim workset As DAO.Recordset
  11.  
  12.   Set workdb = CurrentDb
  13.   Set worktable = workdb.TableDefs("tbl_Job")
  14.   Set workset = worktable.OpenRecordset(dbOpenTable)
  15.  
  16.   Set g_myRS1 = g_myDB.OpenRecordset(g_SQLStmt1)
  17.     With g_myRS1
  18.   If Not workset.EOF Then
  19.     sDriver = CStr(g_JobNumber)
  20.     workset.Index = "PrimaryKey"
  21.     workset.Seek "=", sDriver
  22.     If workset.NoMatch Then
  23.       MsgBox ("Entry not found")
  24.       Else
  25.       MsgBox ("Entry found")
  26.     End If
  27.   Else
  28.     MsgBox "No records."
  29.   End If
  30.  
  31.   End With
  32.   g_myRS1.Close
  33.  

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
Jan 30 '08 #1
Share this Question
Share on Google+
1 Reply


thewesties
P: 2
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.
Jan 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.