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_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 -
'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
1 2491
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
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...
|
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...
|
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.
|
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,
|
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
| |
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...
|
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)
|
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
|
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...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
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,...
| |
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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...
|
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...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |