473,883 Members | 1,602 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update long memo field in tables via VBA

thewesties
2 New Member
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_Descriptio n2) 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
1 2500
thewesties
2 New Member
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_Wo rk] & ' 3.4 Excavate 5' deep tree pits'.

Additionally, just to elaborate better. The end goal is to have the memo field tbl_Proposal.Bi d_Description2 copied in to the field tbl_Job.Job_Sco pe_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

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

Similar topics

1
3142
by: Rose | last post by:
I have an access 2000 database. In it I have a table OPEN Items which has a field Issue that is setup as a MEMO field. I am trying to append information from another table with the same setup, but it does not bring in all of my memo field - it cuts off the data. I have other queries setup to do the same thing from different tables and they work fine. I cannot see anything different in my tables or queries. Anyone have any ideas of...
1
2274
by: Andrew Donnelly | last post by:
I am trying to update a memo field in a table from a form that I have created. The form is unbound, and once the user makes their changes, they have to click the continue button. Once the button is click then, there is some VB code that uses SQL Update to take the information on the form and update the table with the new changes. However, I am having issues with the Memo fields. I have looked at several different posts and have not been...
3
3699
by: David W. Fenton | last post by:
A very old app of mine that's been in production use, and largely unchanged since about 1998 has started recently throwing error 3188 (can't update, locked by another session on this machine) when saving edits to some memo fields. I have found a number of things: 1. it happens only with long memo fields, starting somewhere above 255 characters (though I don't know exactly where.
6
2723
by: Matt | last post by:
I'm having difficulty with trying to update a Access memo field through an SQL statement where the value I'm trying to pass is longer than 255 characters. The field is being truncated. I'm using a simple INSERT INTO sql command. Anyone know a way around this? Sample strSQL = "INSERT INTO tblListings " & _ "(Dir, Street, City, State, " & _ "AFirst, Price, ALast, MainPhoto, Summary, Details,
0
5836
by: M. David Johnson | last post by:
I cannot get my OleDbDataAdapter to update my database table from my local dataset table. The Knowledge Base doesn't seem to help - see item 10 below. I have a Microsoft Access 2000 database which indexes computer magazine articles for personal reference. I am developing a Visual Basic.NET program whose sole purpose is to enter new records into the database. No updates to existing entries, no deletions, and no display
1
8505
by: Mark Reed | last post by:
Hi All, I'm having a problem with the following code. I've read quite a lot of old posts regarding the issue but none seem to affer a solution. The scenario is. I have a bound form which contains a couple of memo fields. I need to keep some sort of log as to when each update of the memo field occurs so I have locked bot the memo fields on the main form. To edit them, the user double clicks the ememo field which then opens an unbound...
0
3243
by: Access Programming only with macros, no code | last post by:
ERROR MESSAGE: Could not update; currently locked by another session on this machine. BACKGROUND I have the following objects: Table1 - HO (which has about 51,000+ records) Table2 - Contact (which has 68,000+ records)
2
3394
by: Roger | last post by:
I've got two tables in sql2005 which have an 'ntext' field when I linked the first table in access97 last week using an odbc data source the access-field type was 'memo' when I link the 2nd table today, it is linked as a text(255) field, ditto for the first table if I link it today if I link the 2nd table using access2003 (and the same odbc data source) it is linked as a memo field
3
9394
by: Kunal Desale | last post by:
Hi, How to insert/update data in foxpro table field having datatype MEMO using Linked Server? I have written sql insert queries in which i have used linked server to insert data into foxpro tables. Some fields have datatype Memo and in these fields my data is not getting inserted/updated. My queries contains local tables & dbf tables (Both). I try simple insert query which insert data into one memo field in foxpro editor and sql...
0
9781
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11118
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10732
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10407
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9564
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7960
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5791
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
3230
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.