ADODB Update Problem

[devonknows]

I was wondering if anyone could help me on this, im having trouble with the last elseif, for some reason when i try and use MEMO in the access database it only lets me put in like 20 characters or something so im having to put in 3 description boxes in the database to allow me to put in 750 characters, but when im trying to split the text up i can get

                If Len(Text4.Text) <= 250 Then
                    rs!Description = Text4.Text
                    rs!Description2 = ""
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 250 And Len(Text4.Text) <= 500 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = ""
 

to work but when i try and add the 2nd elseif it just keeps crashing on me

                ElseIf Len(Text4.Text) > 500 And Len(Text4.Text) <= 750 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = Right(Text4.Text, Len(Text4.Text) - 500)
                End If
 

If anyone can show me a better way of doing this then i would be most greatful or tell me how to make the memo section on the access database accept more than a few characters, i always beleived that a memo field was unlimited in characters or something... a full view of the desciption code is below if anyone could help i would be most greatful

Kind Regards as always
Devon.

        Do While Not rs.EOF
            If rs!filmname = Label7.Caption And rs!cdnumber = Label5.Caption And _
            rs!filmgenre = Label9.Caption And rs!filmtype = Label11.Caption Then
                rs!cdnumber = Text2.Text
                rs!filmname = Text3.Text
                rs!filmgenre = Combo1.Text
                rs!filmtype = Combo2.Text
                rs!filmrating = Text6.Text
                rs!dateadded = Text7.Text
                If Len(Text4.Text) <= 250 Then
                    rs!Description = Text4.Text
                    rs!Description2 = ""
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 250 And Len(Text4.Text) <= 500 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 500 And Len(Text4.Text) <= 750 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = Right(Text4.Text, Len(Text4.Text) - 500)
                End If
                 rs.Update
            End If
 

[vijaydiwakar]

I was wondering if anyone could help me on this, im having trouble with the last elseif, for some reason when i try and use MEMO in the access database it only lets me put in like 20 characters or something so im having to put in 3 description boxes in the database to allow me to put in 750 characters, but when im trying to split the text up i can get

                If Len(Text4.Text) <= 250 Then
                    rs!Description = Text4.Text
                    rs!Description2 = ""
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 250 And Len(Text4.Text) <= 500 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = ""
 

to work but when i try and add the 2nd elseif it just keeps crashing on me

                ElseIf Len(Text4.Text) > 500 And Len(Text4.Text) <= 750 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = Right(Text4.Text, Len(Text4.Text) - 500)
                End If
 

If anyone can show me a better way of doing this then i would be most greatful or tell me how to make the memo section on the access database accept more than a few characters, i always beleived that a memo field was unlimited in characters or something... a full view of the desciption code is below if anyone could help i would be most greatful

Kind Regards as always
Devon.

        Do While Not rs.EOF
            If rs!filmname = Label7.Caption And rs!cdnumber = Label5.Caption And _
            rs!filmgenre = Label9.Caption And rs!filmtype = Label11.Caption Then
                rs!cdnumber = Text2.Text
                rs!filmname = Text3.Text
                rs!filmgenre = Combo1.Text
                rs!filmtype = Combo2.Text
                rs!filmrating = Text6.Text
                rs!dateadded = Text7.Text
                If Len(Text4.Text) <= 250 Then
                    rs!Description = Text4.Text
                    rs!Description2 = ""
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 250 And Len(Text4.Text) <= 500 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = ""
                ElseIf Len(Text4.Text) > 500 And Len(Text4.Text) <= 750 Then
                    rs!Description = Left(Text4.Text, 250)
                    rs!Description2 = Right(Text4.Text, Len(Text4.Text) - 250)
                    rs!Description3 = Right(Text4.Text, Len(Text4.Text) - 500)
                End If
                 rs.Update
            End If
 

there is a problem in memo that it can accept only first line i.e. whenevr it encounters vbcrlf then it leaves remaining value so try to use the code

 
Conn.Execute "insert into temp(ff) values('" & Replace(Text1.Text, vbCrLf, "##") & "')"
 

where conn is thy connection (ADODB) ff is memo filed in temp tbl
here i'm replacing vbcrlf with ## so the large value can be inserted without vbcrlf
whenevr u 'll retriuve this value just replace ## with vbcrlf and thy problem will be get solved
u need not to use three decription fileds
Try it
Good Luck

[devonknows]

Im afraid that its not working, ive tried it but its still only allowing a total of 50 characters into it, the text is mainly on one line anyway as it is a description field of a film, sometimes enter is pressed to give space but not often, but anything i try to enter in the Microsoft Access database under memo only allows me 50 chars, here the connection string im using.

        Set rs = New ADODB.Recordset
        rs.Open Source:="filmdatabase", _
            ActiveConnection:=conn, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockOptimistic
        Do While Not rs.EOF
            If rs!filmname = Label7.Caption And rs!cdnumber = Label5.Caption And _
            rs!filmgenre = Label9.Caption And rs!filmtype = Label11.Caption Then
                rs!cdnumber = Text2.Text
                rs!filmname = Text3.Text
                rs!filmgenre = Combo1.Text
                rs!filmtype = Combo2.Text
                rs!filmrating = Text6.Text
                rs!dateadded = Text7.Text
                rs!maindescription = Replace(Text4.Text, vbCrLf, "##")
                rs.Update
            End If
 
            rs.MoveNext
        Loop
 

ive replaced the Description, Description2, Description3 with maindescription (maindescription is the memo field) any help as always will be greatly appreciated.

Kind Regards
Devon

[vijaydiwakar]

Im afraid that its not working, ive tried it but its still only allowing a total of 50 characters into it, the text is mainly on one line anyway as it is a description field of a film, sometimes enter is pressed to give space but not often, but anything i try to enter in the Microsoft Access database under memo only allows me 50 chars, here the connection string im using.

        Set rs = New ADODB.Recordset
        rs.Open Source:="filmdatabase", _
            ActiveConnection:=conn, _
            CursorType:=adOpenDynamic, _
            LockType:=adLockOptimistic
        Do While Not rs.EOF
            If rs!filmname = Label7.Caption And rs!cdnumber = Label5.Caption And _
            rs!filmgenre = Label9.Caption And rs!filmtype = Label11.Caption Then
                rs!cdnumber = Text2.Text
                rs!filmname = Text3.Text
                rs!filmgenre = Combo1.Text
                rs!filmtype = Combo2.Text
                rs!filmrating = Text6.Text
                rs!dateadded = Text7.Text
                rs!maindescription = Replace(Text4.Text, vbCrLf, "##")
                rs.Update
            End If
 
            rs.MoveNext
        Loop
 

ive replaced the Description, Description2, Description3 with maindescription (maindescription is the memo field) any help as always will be greatly appreciated.

Kind Regards
Devon

see i've checked this code on more than 200 chars with vbcrlf as line seperetor and its working fine

[vijaydiwakar]

see i've checked this code on more than 200 chars with vbcrlf as line seperetor and its working fine

try to use connection object as i've given in example

[devonknows]

But the problem being im not inserting, im updating and you have to use a different type of connection, plus ive even tried to use visdata to manually edit the field and even in visdata it doesnt let me go over 50 characters, the insert into clause isnt required here that is why im using rs!update, would it be worth using another database type or instead of using ADODB ? i only use it as its the only one i know how to use. And there isnt so much a vbcrlf as line seperator, its mainly one line of text in a text box wordwrapped.


Apologies for being a pain
Kind Regards
Devon.