Error Help

[rkrause]

I have attached two documents, the word document is an error I am getting, and the txt file is the code behind the button, could someone point me to the right direction for a fix.

Thanks

 

[pbaldy]

What line does it error on? The error is fairly common, and as it implies generally occurs when you're trying to put data into a field that's more than the field is capable of handling. For instance, putting 25 characters into a text field limited to 20. Check the values you're trying to insert against the data types in the table.

 

[rkrause]

how can I tell what line it errors out on?

 

[pbaldy]

Comment out the

On Error...

lines.

 

[rkrause]

let me know if i did this right.

I put a break point on line one and and End Sub, used F5 to step through and it errored out right away.

 

[pbaldy]

On what line? F5 will not step, it just runs the code. You have more than one On Error line, so if it didn't break into the code and show you the line, you need to comment out the others.

 

[rkrause]

I have all the On Error lines commented out, and it is still breaking right away, can you give me a better way to debug, to see what line its bugging on.

Thanks

 

[Rabbie]

let me know if i did this right.

I put a break point on line one and and End Sub, used F5 to step through and it errored out right away.

Use F8 to step through your code one line at a time

 

[rkrause]

It is erroring out on the bold line, can anyone help on a fix?

Do While Not rsSortTemp_In.EOF
blnCopyData = True
rsSortTemp_Out.AddNew
rsSortTemp_Out!TableID = 0
rsSortTemp_Out!ClientCode = Left(rsSortTemp_In!CustID, intClCodeLen)
rsSortTemp_Out!CustCode = CInt(Right(rsSortTemp_In!CustID, Len(rsSortTemp_In!CustID) - intClCodeLen))
rsSortTemp_Out!SortID = 0
rsSortTemp_Out!CustName = rsSortTemp_In!CustName
rsSortTemp_Out!Contact = rsSortTemp_In!Contact
rsSortTemp_Out!Phone = rsSortTemp_In!Phone
rsSortTemp_Out!dbCustID = rsSortTemp_In!CustID
rsSortTemp_Out.Update
rsSortTemp_In.MoveNext
Loop

 

[Rabbie]

In that case you need to check the lengths of rsSortTemp_Out!CustName and rsSortTemp_In!CustName

The error message if it is still as in your first post is saying there isn't enough room in rsSortTemp_out!CustName to store the contents of rsSortTemp_In!CustName.

 

[JANR]

I think it stops there because you got a empty recordset. In your code:

If Not rsSortTemp_In.EOF Then
      rsSortTemp_In.MoveFirst
      DoCmd.RunSQL "DELETE * FROM tblCID_Out_TEMP;"         ' Clear out any old data
      strSQL = "[COLOR=red]SELECT * FROM tblCID_Out_TEMP[/COLOR];"             ' Open Output recordset
      Set rsSortTemp_Out = New ADODB.Recordset
      rsSortTemp_Out.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If

It looks a bit odd don't you think.

Try:

"SELECT [COLOR=red]tblCID_Out_TEMp.*[/COLOR] FROM tbl_Out_TEMP;"

JR

 

[Rabbie]

I think it stops there because you got a empty recordset. In your code:

If Not rsSortTemp_In.EOF Then
      rsSortTemp_In.MoveFirst
      DoCmd.RunSQL "DELETE * FROM tblCID_Out_TEMP;"         ' Clear out any old data
      strSQL = "[COLOR=red]SELECT * FROM tblCID_Out_TEMP[/COLOR];"             ' Open Output recordset
      Set rsSortTemp_Out = New ADODB.Recordset
      rsSortTemp_Out.Open strSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    End If

It looks a bit odd don't you think.

Try:

"SELECT [COLOR=red]tblCID_Out_TEMp.*[/COLOR] FROM tbl_Out_TEMP;"

JR

That code may look a bit odd but it will work. You only need the longer name if there would be ambiguity with out out it.

Select * FROM tbl_Out_TEMP;

just tells the system you want all fields and records from tbl_Out_TEMP in your recordset.

 

[rkrause]

that didnt work, any other tips?

 

[Rabbie]

Have you checked the field sizes in your two recordsets as advised in post 10?

 

[rkrause]

Yes and I cannot find anywhere that the data would be over the size of the field limits.

When I commented out the line of code dealing with custname, everything went smooth, but I cannot find anything wrong with any customer in any table that would cause this to error out.

 

[Rabbie]

Well i am afraid without being able to see your datasets I'm fresh out of ideas about this

 

[kiwi]

What do you get if you insert something like

debug.print rsSortTemp!In!Custname , Len(rsSortTemp!In!Custname )

before the error.

 

[Rabbie]

What do you get if you insert something like

debug.print rsSortTemp!In!Custname , Len(rsSortTemp!In!Custname )

before the error.

It will work even better with the correct names

try

debug.print rsSortTemp_In!Custname , Len(rsSortTemp_In!Custname )

Then check that
rsSortTemp_out!Custname is large enough to hold that length