String longer then 256 in string does not insert or update

[CodeJane]

Hiya everybody,

I have a memo field in a table. In my code I do an insert with variables coming from the form. However, the system cuts the long string (memo). Here is what I have done (rather simple actually):

'first I do an insert
addDVDSql = "INSERT INTO DVD ( Title, Actor1, Actor2, Actor3, Actor4, [Rel Year], Director, Format, Category, Duration, Comment, Country ) VALUES ('" & titlestr & " ', '" & act1str & " ', '" & act2str & " ', '" & act3str & " ', '" & act4str & " ', '" & relstr & " ', '" & dirstr & " ', '" & formstr & " ', '" & catstr & " ', '" & durstr & " ', '" & comstr & " ', '" & countrystr & "')"
DoCmd.SetWarnings False
DoCmd.RunSQL addDVDSql

'as the insert with the long string storystr didn't work I thought I'd do an update afterwards
addDVDSql = ""
addDVDSql = "UPDATE DVD SET DVD.Story = '" & storystr & "' WHERE DVD.Title = '" & titlestr & " '"
DoCmd.RunSQL addDVDSql

It still cuts it. Any ideas? Thanks so much for your help.
Jane

 

[rjoubert]

Your storystr variable (which I'm assuming is a String) will only hold a limited number of characters. You are assigning the data input from the form to the storystr variable, right? Why not just take the storystr variable out of the code, and use (replacing memofieldonform with the name of the form field you're using)...

addDVDSql = "UPDATE DVD SET DVD.Story = '" & Me.memofieldonform.value & "' WHERE DVD.Title = '" & titlestr & " '"

 

[CautionMP]

CodeJane,
Instead of using [tt]DoCmd.RunSQL()[/tt] you might try inserting the new record suing a DAO Recordset. Here is a sample routine. I tested it (A2K SR-1) and was able to insert 512 characters into a memo field with no issues.

Sub DAOInsert()
Dim rstTarget As DAO.Recordset
Set rstTarget = CurrentDb.OpenRecordset("DVD", dbOpenDynaset)
With rstTarget
  .AddNew
  .Fields("Title") = titlestr
  .Fields("Actor1") = act1str
  .Fields("Actor2") = act2str
  .Fields("Actor3") = act3str
  .Fields("Actor4") = act4str
  .Fields("Rel Year") = relstr
  .Fields("Director") = dirstr
  .Fields("Format") = formstr
  .Fields("Category") = satstr
  .Fields("Duration") = durstr
  .Fields("Comment") = comstr
  .Fields("Country") = countrystr
  .Update
  .Close
End With
Set rstTarget = Nothing
End Sub

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

Hi,

How unimaginative! Why do almost ALL instructors use a MOVIE database example? I guess it's to hold the interest of the majority.

So, what's the actual assignment?

Skip,

_[red][/red]

 

[CodeJane]

Hiya CautionMP,

I used your suggestion and it works (2003). Thanks a lot.
rjoubert tried yours and it didn't work. Thought I'd let you know.
FYI I'm creating my own DVD database as I have a huge collection and have lost the overview.
Again guys/girls thanks a lot.
Jane

 

[PHV]

And why not simply use a BOUND form ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

Why reinvent the wheel.

Look at

http://www.dvdprofiler.com/

for one of the best DVD programs available. Free, or if you wish to have the premium version you pay xxx.

Works great. Over 400 DVD's myself, and know of people with more than 900 stored on this program.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com