Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

HELP!!!!!! RUNTIME 6 ERROR "overflow"

Status
Not open for further replies.
madetoheal13

madetoheal13

MIS
Aug 23, 2005
43
US
Hey all! I am getting a runtime 6 error message "overflow". My database was working fine, and then I replicated it. Now when logging out on a replica...before closing...this message pops up. All updated info..is updated and kept in the tables. But updated info does not show up in reports. Any clues?

Thanks.
Madetoheal13
 
Sort by date Sort by votes
Its me again. Hey when I get this message the window lets me end or debug. When I debug...it goes to a VBA code screen where some written code is highlighted yellow.
Thanks

Madetoheal13
 
Upvote 0 Downvote
Sounds like a variable misdeclaration. Data type too small for desired data.

Show the line that's highlighted please, very significant.
 
Upvote 0 Downvote
OK Here is the code..... the line below....under 'Do While Not .EOF'....it says....'intFace = .Fields(0)' this line is higlighted yellow...

CODE

'Update Face2FaceReports1 table
Set rstFace2Face = DB.OpenRecordset("Face2FaceReports1", dbOpenDynaset)
Set rstFace2FaceUser = DB.OpenRecordset(Trim(strGlobalUser) & "Face2Face", dbOpenDynaset)
Dim intFace As Integer

With rstFace2FaceUser
.MoveFirst
Do While Not .EOF
intFace = .Fields(0)
rstFace2Face.MoveFirst

rstFace2Face.FindFirst "Face2FaceReportsID = " & CStr(intFace)
rstFace2Face.Edit
If rstFace2Face.NoMatch Then
'This is a record to be appended for the existing customers
intCustCont = .Fields(1)

If intCustCont <= intLastCustID Then
rstFace2Face.AddNew
rstFace2Face.Fields(1) = .Fields(1)
rstFace2Face.Fields(2) = .Fields(2)
rstFace2Face.Fields(3) = .Fields(3)
rstFace2Face.Fields(4) = .Fields(4)
rstFace2Face.Fields(5) = .Fields(5)
rstFace2Face.Fields(6) = .Fields(6)
rstFace2Face.Fields(7) = .Fields(7)
rstFace2Face.Fields(8) = .Fields(8)
rstFace2Face.Fields(9) = .Fields(9)
rstFace2Face.Fields(10) = .Fields(10)
rstFace2Face.Fields(11) = .Fields(11)
rstFace2Face.Fields(12) = .Fields(12)
rstFace2Face.Fields(13) = .Fields(13)
rstFace2Face.Fields(14) = .Fields(14)
rstFace2Face.Fields(15) = .Fields(15)
rstFace2Face.Fields(16) = .Fields(16)
rstFace2Face.Fields(17) = .Fields(17)
rstFace2Face.Fields(18) = .Fields(18)
rstFace2Face.Fields(19) = .Fields(19)
rstFace2Face.Fields(20) = .Fields(20)
rstFace2Face.Fields(21) = .Fields(21)
rstFace2Face.Fields(22) = .Fields(22)
rstFace2Face.Fields(23) = .Fields(23)
rstFace2Face.Fields(24) = .Fields(24)
rstFace2Face.Fields(25) = .Fields(25)
rstFace2Face.Fields(26) = .Fields(26)
rstFace2Face.Fields(27) = .Fields(27)
rstFace2Face.Fields(28) = .Fields(28)
rstFace2Face.Fields(29) = .Fields(29)
rstFace2Face.Fields(30) = .Fields(30)
rstFace2Face.Fields(31) = .Fields(31)
rstFace2Face.Fields(32) = .Fields(32)
Else
'The records for created in this session customers
'but first we need to know the customer ID
End If
Else
'The records to be updated
intCustCont = .Fields(1)
If intCustCont <= intLastCustID Then
rstFace2Face.Fields(1) = .Fields(1)
rstFace2Face.Fields(2) = .Fields(2)
rstFace2Face.Fields(3) = .Fields(3)
rstFace2Face.Fields(4) = .Fields(4)
rstFace2Face.Fields(5) = .Fields(5)
rstFace2Face.Fields(6) = .Fields(6)
rstFace2Face.Fields(7) = .Fields(7)
rstFace2Face.Fields(8) = .Fields(8)
rstFace2Face.Fields(9) = .Fields(9)
rstFace2Face.Fields(10) = .Fields(10)
rstFace2Face.Fields(11) = .Fields(11)
rstFace2Face.Fields(12) = .Fields(12)
rstFace2Face.Fields(13) = .Fields(13)
rstFace2Face.Fields(14) = .Fields(14)
rstFace2Face.Fields(15) = .Fields(15)
rstFace2Face.Fields(16) = .Fields(16)
rstFace2Face.Fields(17) = .Fields(17)
rstFace2Face.Fields(18) = .Fields(18)
rstFace2Face.Fields(19) = .Fields(19)
rstFace2Face.Fields(20) = .Fields(20)
rstFace2Face.Fields(21) = .Fields(21)
rstFace2Face.Fields(22) = .Fields(22)
rstFace2Face.Fields(23) = .Fields(23)
rstFace2Face.Fields(24) = .Fields(24)
rstFace2Face.Fields(25) = .Fields(25)
rstFace2Face.Fields(26) = .Fields(26)
rstFace2Face.Fields(27) = .Fields(27)
rstFace2Face.Fields(28) = .Fields(28)
rstFace2Face.Fields(29) = .Fields(29)
rstFace2Face.Fields(30) = .Fields(30)
rstFace2Face.Fields(31) = .Fields(31)
rstFace2Face.Fields(32) = .Fields(32)
Else
'The records for created in this session customers
'but first we need to know the customer ID
End If
End If
rstFace2Face.Update
.MoveNext
Loop
.Close
rstFace2Face.Close

End With

'Append new customers to main table with this query
DoCmd.OpenQuery strGlobalUser & "AppendCustomerInfo"

'Find Cust Refrence to the user's tables
Dim rstCustRef As Recordset
Dim strSelect As String
strSelect = "SELECT CustomerID, RefCustID FROM CustomerInfo1 " & _
" WHERE RefCustID <> 0 And SalesID = '" & Trim(strGlobalUser) & "';"
Set rstCustRef = DB.OpenRecordset(strSelect, dbOpenForwardOnly)

Set rstContactUser = DB.OpenRecordset(Trim(strGlobalUser) & "ContactInfo", dbOpenDynaset)

Set rstContact = DB.OpenRecordset("ContactInfo1", dbOpenDynaset)

Dim rstFace As Recordset
Dim rstFaceUser As Recordset

Set rstFaceUser = DB.OpenRecordset(Trim(strGlobalUser) & "Face2Face", dbOpenDynaset)
Set rstFace = DB.OpenRecordset("Face2FaceReports1", dbOpenDynaset)

'For the new customers we need to move records from the user tables to the main ones
Do While Not rstCustRef.EOF

rstContactUser.FindFirst "CustomerID = " & CStr(rstCustRef.Fields(1))
'This loop appends records of new customers to ContactInfo1 table
Do While Not rstContactUser.EOF

If rstContactUser.NoMatch Then
Exit Do
Else

rstContact.AddNew
With rstContactUser

'Append record to the main contact table
rstContact.Fields(1) = rstCustRef.Fields(0)
rstContact.Fields(2) = .Fields(2)
rstContact.Fields(3) = .Fields(3)
rstContact.Fields(4) = .Fields(4)
rstContact.Fields(5) = .Fields(5)
rstContact.Fields(6) = .Fields(6)
rstContact.Fields(7) = .Fields(7)
rstContact.Fields(8) = .Fields(8)
rstContact.Fields(9) = .Fields(9)
rstContact.Fields(10) = .Fields(10)
rstContact.Fields(11) = .Fields(11)
rstContact.Fields(12) = .Fields(12)
rstContact.Fields(13) = .Fields(13)
End With
rstContact.Update

rstContactUser.FindNext "CustomerID = " & CStr(rstCustRef.Fields(1))
End If

Loop

rstFaceUser.FindFirst "CustomerID = " & CStr(rstCustRef.Fields(1))
'This next loop will append records to the Face2FaceReports1 table
Do While Not rstFaceUser.EOF

If rstFaceUser.NoMatch Then
Exit Do
Else
rstFace.AddNew
With rstFaceUser

'Append record to the main contact table
rstFace.Fields(1) = rstCustRef.Fields(0)
rstFace.Fields(2) = .Fields(2)
rstFace.Fields(3) = .Fields(3)
rstFace.Fields(4) = .Fields(4)
rstFace.Fields(5) = .Fields(5)
rstFace.Fields(6) = .Fields(6)
rstFace.Fields(7) = .Fields(7)
rstFace.Fields(8) = .Fields(8)
rstFace.Fields(9) = .Fields(9)
rstFace.Fields(10) = .Fields(10)
rstFace.Fields(11) = .Fields(11)
rstFace.Fields(12) = .Fields(12)
rstFace.Fields(13) = .Fields(13)
rstFace.Fields(14) = .Fields(14)
rstFace.Fields(15) = .Fields(15)
rstFace.Fields(16) = .Fields(16)
rstFace.Fields(17) = .Fields(17)
rstFace.Fields(18) = .Fields(18)
rstFace.Fields(19) = .Fields(19)
rstFace.Fields(20) = .Fields(20)
rstFace.Fields(21) = .Fields(21)
rstFace.Fields(22) = .Fields(22)
rstFace.Fields(23) = .Fields(23)
rstFace.Fields(24) = .Fields(24)
rstFace.Fields(25) = .Fields(25)
rstFace.Fields(26) = .Fields(26)
rstFace.Fields(27) = .Fields(27)
rstFace.Fields(28) = .Fields(28)
rstFace.Fields(29) = .Fields(29)
rstFace.Fields(30) = .Fields(30)
rstFace.Fields(31) = .Fields(31)
rstFace.Fields(32) = .Fields(32)
End With
rstFace.Update

rstFaceUser.FindNext "CustomerID = " & CStr(rstCustRef.Fields(1))
End If
Loop
rstCustRef.MoveNext
Loop
 
Upvote 0 Downvote
THis line higlighted below:

intFace = .Fields(0)


CODE

'Update Face2FaceReports1 table
Set rstFace2Face = DB.OpenRecordset("Face2FaceReports1", dbOpenDynaset)
Set rstFace2FaceUser = DB.OpenRecordset(Trim(strGlobalUser) & "Face2Face", dbOpenDynaset)
Dim intFace As Integer

With rstFace2FaceUser
.MoveFirst
Do While Not .EOF
intFace = .Fields(0)
rstFace2Face.MoveFirst

rstFace2Face.FindFirst "Face2FaceReportsID = " & CStr(intFace)
rstFace2Face.Edit
If rstFace2Face.NoMatch Then
'This is a record to be appended for the existing customers
intCustCont = .Fields(1)
 
Upvote 0 Downvote
Replace "Dim intFace as Integer" with "Dim intFace as Long".

A quick and superficial look at your issue but I have found in the past that Integer declarations are often too short (actually the value exceeds 32k).
 
Upvote 0 Downvote
Just a suggestion, too...

You could shorten the code significantly. Every time you do a field to field copy, you could do a loop on an integer variable...

Code: 
Dim a as integer

Here is an example of the loop, based on the first mass field-copy going on...

Code: 
If intCustCont <= intLastCustID Then
   rstFace2Face.AddNew
      For a = 1 to 32
         rstFace2Face.Fields(a) = .Fields(a)
      Next a
   rsFace2Face.Update
Else

You would just need to be careful on the other mass copies... some of them don't copy the first field from the read rset to the write rset. In that case, put the first field outside of the loop, and loop from a = [red]2[/red] To 32 (or whatever your upper limit is.

HTH
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Moss100
  • Locked
  • Question
Access prompts &quot;open or save&quot; when I preview an excel file 1
Replies
1
Views
114
combo
combo
Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
615
Andrzejek
Andrzejek
IrishJim
  • Locked
  • Question
How do I capture the &quot;digital signature&quot;, for display in my Form header?
Replies
0
Views
140
IrishJim
IrishJim
DarkOne72
  • Locked
  • Question
Message Box Popup on certain days help !
2
Replies
23
Views
1K
Norman Sherlock
Norman Sherlock
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
532
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top