[RESOLVED] The field is too small to accept the amount of data you attempted to add.

[aikidokid]

I don't understand this problem, or rather why I am getting it!

I use field values:

• Text
• Memo
• Date/Time
• Autonumber

All the text fields are set to 50 chars, and the longest string I am inputting is 14 chars!
The memo field is less than 200 chars.
The date/time fields are set to ShortDate (Format - 00/00/0000;0;#)

The Execute code works with other records, so I cannot see what is wrong with this record.

This is only a test DB with made up details, but should still work.
The last record added is inot the memo field.

strsql2
INSERT INTO tbl_EX_Membership Values  (16,
'Paul',
'Earl',
'Limit',
'16 Forever Ave',
'Filton',
'Wiltshire',
'33W E4',
'36562326',
'3542365',
'Help@please',
#09/09/1984#,
#03/06/1966#,
#12/12/2007#,
'2ND DAN',
#23/06/2004#,
'6788',
'manager',
'Male',
'We have now amended our guidance to reflect the increase in the holiday entitlement: 
')

[Hack]

Which of the fields is it giving the error on?

[aikidokid]

I'm not sure Hack.
That's why I showed the Debug.Print.

I did read a post of yours when searching, and you asked the same question, but the whole of the strSQL2 is highlighted in yellow when stepping through the code and when it reaches the Execute line, it goes to the errhandler.

My code (Which has worked with every other record I have used it for)

strSQL = "SELECT * FROM tbl_Membership"
            strSQL = strSQL & " WHERE Memb_ID = " & lngRecToDelete
            
            rs.Open strSQL, cn, adOpenKeyset, adLockPessimistic, adCmdText
            
            Dim strDOB As String
            Dim strDate_Joined As String
            Dim strInsurance_Due As String
            Dim strLast_Graded As String
            
            strDOB = "NULL"
            strDate_Joined = "NULL"
            strInsurance_Due = "NULL"
            strLast_Graded = "NULL"
            
            If Not IsNull(rs!DOB) Then
               strDOB = "#" & rs!DOB.Value & "#"
            End If
            If Not IsNull(rs!Date_Joined) Then
               strDate_Joined = "#" & rs!Date_Joined.Value & "#"
            End If
            If Not IsNull(rs!Insurance_Due) Then
               strInsurance_Due = "#" & rs!Insurance_Due.Value & "#"
            End If
            If Not IsNull(rs!Last_Graded) Then
               strLast_Graded = "#" & rs!Last_Graded.Value & "#"
            End If
            
            Dim strSQL2 As String
            strSQL2 = "INSERT INTO tbl_EX_Membership"
            strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                         "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Surname.Value & "'," & vbCrLf & _
                                         "'" & rs!Address.Value & "'," & vbCrLf & _
                                         "'" & rs!Town.Value & "'," & vbCrLf & _
                                         "'" & rs!County.Value & "'," & vbCrLf & _
                                         "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                         "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Email.Value & "'," & vbCrLf & _
                                         "" & strDOB & "," & vbCrLf & _
                                         "" & strDate_Joined & "," & vbCrLf & _
                                         "" & strInsurance_Due & "," & vbCrLf & _
                                         "'" & rs!Grade.Value & "'," & vbCrLf & _
                                         "" & strLast_Graded & "," & vbCrLf & _
                                         "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                         "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                         "'" & rs!Gender.Value & "'," & vbCrLf & _
                                         "'" & rs!Notes.Value & "')"
            
            cn.Execute strSQL2

[Hack]

If you aren't sure, and this is just a test record, then do an Insert with just one text field. If that works, try another one. You should be able to find out which one it is by a simple (although potentially time consuming) process of eliminiation.

[aikidokid]

Good idea I will give it a go.

Just another question, remind me how to adapt this to just insert one record

This code is in a menu item to remove a record, which is a record selected from a listview.

[Hack]

sSQL = "INSERT INTO tablename (field1, field2, field3) VALUES ('Hack', 'aikidokid', 'si_the_geek') "
cn.Execute sSQL

Is this what you mean?

[aikidokid]

Hmm

I have added all fields individually and it adds all of them.
I tried this twice and I also used this code to remove a different record and it worked!!!!

[aikidokid]

Is trying to put too many characters into a string field the only reason for this type of error?

I just cannot see what is causing it!!

[Hack]

Maybe it is the date/time field. What are you adding to it?

[aikidokid]

I did check these as well.

They are all validated before they are added to strings for the Insert, as in my code above.

            If Not IsNull(rs!Date_Joined) Then
               strDate_Joined = "#" & rs!Date_Joined.Value & "#"
            End If

Insert statement

"" & strDate_Joined & "," & vbCrLf & _

The format is UK format - 05/11/2007 - but I'm not having any problems with ALL the other test records (approx 25)

[si_the_geek]

One thing that is missing from your original code is a field list - I suspect that is causing the problem, as the fields are not quite in the order your code expects (so the wrong fields are being used).

Try adding the field list like this:

INSERT INTO tbl_EX_Membership (Field1, Field2, ...) Values

The format is UK format - 05/11/2007 - but ...

But nothing - do not ever, under any circumstances, use UK format dates in SQL statements - always use US or ISO formats.

Using a UK format will damage your data and/or give you the wrong query results at some point.

It's annoying, but something that is very important for you to remember and stick to.

[aikidokid]

One thing that is missing from your original code is a field list - I suspect that is causing the problem, as the fields are not quite in the order your code expects (so the wrong fields are being used).

Perhaps you could clarify this point for me please.
I was told, on this forum, some time ago now, that as the two tables have identical fields (one being for current members details and the other being for lapsed members details) I could use the above code to copy from one table to the other.
Is this not correct?

Using a UK format will damage your data and/or give you the wrong query results at some point.

Hmm, I did know this - probably you that told me along with many others.
This one must have got past me

I will have to change this.

[si_the_geek]

Perhaps you could clarify this point for me please.
I was told, on this forum, some time ago now, that as the two tables have identical fields (one being for current members details and the other being for lapsed members details) I could use the above code to copy from one table to the other.
Is this not correct?

If that was what you were doing, you could do it like that - but I would not recommend it, as it will fail if the design of either of the tables change (such as the order of columns changes).

The "if" is because you are not doing that - you are simply running a single insert statement (you happen to be creating it based on data from a table, but that is irrelevant - as they aren't part of the same SQL statement, they are separate things).

[aikidokid]

Is this what you mean?

            strSQL2 = "INSERT INTO tbl_EX_Membership"
            strSQL2 = strSQL2 & "(Memb_ID, First_Name, Middle_Name, Surname, Address, Town, County, " & _
                                 "Post_Code, Home_Tel, Mobile_Tel, Email, DOB, Date_Joined, Insurance_Due, " & _
                                 "Grade, Last_Graded, Membership_Number, Occupation, Gender, Notes)"
            strSQL2 = strSQL2 & " Values  (" & rs!Memb_ID.Value & "," & vbCrLf & _
                                         "'" & rs!First_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Middle_Name.Value & "'," & vbCrLf & _
                                         "'" & rs!Surname.Value & "'," & vbCrLf & _
                                         "'" & rs!Address.Value & "'," & vbCrLf & _
                                         "'" & rs!Town.Value & "'," & vbCrLf & _
                                         "'" & rs!County.Value & "'," & vbCrLf & _
                                         "'" & rs!Post_Code.Value & "'," & vbCrLf & _
                                         "'" & rs!Home_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Mobile_Tel.Value & "'," & vbCrLf & _
                                         "'" & rs!Email.Value & "'," & vbCrLf & _
                                         "" & strDOB & "," & vbCrLf & _
                                         "" & strDate_Joined & "," & vbCrLf & _
                                         "" & strInsurance_Due & "," & vbCrLf & _
                                         "'" & rs!Grade.Value & "'," & vbCrLf & _
                                         "" & strLast_Graded & "," & vbCrLf & _
                                         "'" & rs!Membership_Number.Value & "'," & vbCrLf & _
                                         "'" & rs!Occupation.Value & "'," & vbCrLf & _
                                         "'" & rs!Gender.Value & "'," & vbCrLf & _
                                         "'" & rs!Notes.Value & "')"

[si_the_geek]

That's it.

Does it solve the error?

[aikidokid]

That's it.

Does it solve the error?

YES!!!! Thanks I was begining to think this one was going to beat me!