Re: "Overflow Error" - need help please.

Tech-Archive recommends: Fix windows errors by optimizing your registry

Allen:
Just wanted to drop a line and say thanks for taking the time to help me
solve this problem. I found out what the problem was and was able to fix it.
It turned out that in a "global" procedure the person who designed the
database and code declared a variable as a integer and assigend it a value
from the table that was a long integer. The field was the "contactID" and
this explains why the error only showed up on certain records...those that
exceeded the size of the varialbe declared as integer. I assume becuase it
was in a "global" procedure and not part of the form's procedures is why when
we commented out the form's code it still showed up.

Anyway, I just wanted to say a big THANK YOU for taking your time to help
me and say sorry for not responding sooner. I hope if I have any
problems/questions that there will be someone like you Allen to help me
through it.

Thanks,
FatMan

"Allen Browne" wrote:

Sorry: by "post back", I just meant post a reply, which you did.

Okay, so the Overflow Error happens in the form, even when it has NO code.
Name AutoCorrupt is turned off, and you have done the compact/repair.

Do you get the error when you scroll through the records in the table?
No: Try changing the Record Source of the form to the name of the table
(instead of the query statement.)

Yes: Send me an email, using the address in the signature below. (Don't just
"reply": that address won't work.) Include the text of this message in your
email.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"FatMan" <FatMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:24EE3562-065A-46D0-BC1A-64DA6808C66C@xxxxxxxxxxxxxxxx
Allen:
Thanks for the help but still no luck. I "block commented" ALL the code
out
for the form and still got the "overflow error". So I tried suggestion
"B"
(Name AutoCorrect and then compact and repair) but had no luck. I have to
admit I did not understand what you ment by "Post back". Is this a
function
in Access or do you mean I should delete all the records that have been
appended to the tblContacts and then rerun my macro that does the
following:

a) sets warnings off

b) displays a message box that the data is being imported

c) deletes the table (tblPeopleSoft)

d) transfer text (Transfer Type - import delimited, Table Name -
tblImpPeopleSoft, File Name - c:\nlwis\nlwis.txt - Field Names - No, HTML
Table Name - nothing, Code Page - nothing)

e) Opens Query - qryImpPeopleSoftTotblContacts (SQL statement below)

f) displays a message box that the data has been imported

g) set warnings back on

Any ideas what I should do next? I do appreciate any help you can offer
me.

Thanks,
FatMan



"Allen Browne" wrote:

Okay, first step is to identify what is causing the problem.
First suspect is the code in the form. Given the timing of the problem,
Form_Load and Form_Current are the main suspects.

Copy all the code, paste into Notepad, and save it as a text file.
Then delete all the code from the form.
(a) If the problem ceases, we now know that the issue is with the code.
(b) If it doesn't cease, the problem is with the data.

Assuming (a), it bothers me that you are setting the value of 2 controls
in
Form_Load:
- fraIntExt
- ctCompanyName
That's particularly suspect.

IME, input masks are weird beasts too, so you could see if it makes any
difference to omit that code from Form_Current.

We don't know what gbOK is (probably a global boolean?)

If removing all the code does not solve the problem, we need to examine
alternatives for (b). Post back, but first make sure the Name AutoCorrect
boxes are unchecked under:
Tools | Options | General | Name AutoCorrect
Then compact the database:
Tools | Database Utilities | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"FatMan" <FatMan@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1312E426-622E-4A74-AEAE-8DE6E1981E2F@xxxxxxxxxxxxxxxx
Allen:
Thanks for the words of wisdom. I know it is like remove the bulb from
a
warrning light but I am that fruastrated that I can not solve this
problem.
I hope maybe you will be able to help me.

I will start out by first saying I am sorry for all the detail but I
know
no
other way to explain my problem. I worry that the detail may be too
much
and
scare you away from responding. Please do not let this happen as I am
at
wits end and need help badly. Please help.

Here it goes......

To answer your questions:
1) the table (tblccontacts) is a linked table to the "back end"
database
(i.e. the database is split)

2) the table contains data that was entered directly into the table via
a
form (frmCustomers) while other records have been added by an append
querry.
The overflow error only shows when the form is opend for the records
that
have been appended to the table. The appending records come from a
table
(tblImpPeopleSoft - all fields are text with a size of 255 by default
and
have not been changed) that has been created from a text file. It
appears
to
me that the append querry works as the records are added to
tblccontacts
and
I can open the table and view all the records.

SQL for append query:
INSERT INTO tblContacts ( ctEnteredDate, ctPeopleSoftID, ctLastName1,
ctFirstName1, ctEnglishLang, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT Date() AS DateEntered, tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, IIf([F4]="Y",-1,0) AS EnglishLang,
IIf([F4]="Y",-1,0)
AS
FrenchLang, tblImpPeopleSoft.F5, tblImpPeopleSoft.F6,
tblImpPeopleSoft.F7,
tblImpPeopleSoft.F8, tblImpPeopleSoft.F13, IIf([f22] Is
Null,[F21],IIf([f23]
Is Null,[f21] & ", " & [F22],[f21] & ", " & [F22] & ", " & [f23])) AS
Address, tblImpPeopleSoft.F24, "Canada" AS Country,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;

Here are the details of the fields and largest size of data store into
each:

Field Name - Data Type - Field Size - longest record for field
(len([FieldName])
ctEnteredDate - date/time - n/a - 10
ctPeopleSoftID - text - 10 - 5
ctLastName1 - text - 50 - 21
ctFirstName1 - text - 20 - 14
ctEnglishLang - yes/no
ctFrenchLang - yes/no
ctEmailName - text - 50 - 27
ctPhone1 - text - 30 - 22
ctFax - text - 30 - 14
ctCellPhone - text - 30 - 22
ctCompanyName - text - 80 - 30
ctLcAddress - text - 100 - 88
ctLcCity - text - 30 - 28
ctLcCountry - text - 20 - 6
ctLcPostalCode - text - 20 - 7
ctLcProvState - text - 20 - 2



3) The forms is based on the table (tblContacts) and uses the following
as
the control source for the form: SELECT tblContacts.* FROM tblContacts;

4) As mentioned above when I open the table and scroll to the end of
the
table no error is displayed and if needed I can add a new record.

5) As mentioned above the form is based on a table and there are not
calculated fields.

6) There are no calculated controls on the form. The closest thing is
a
couple of combo boxes.

7) I have included ALL the code on the form. Starting with the current
and
open events, followed by any code to do with the fields and then the
code
for
any command buttons.

Code on the form:
Private Sub Form_Current()

gbOK = False

If Me!LcCountry = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If


End Sub

---------------------------------------------------------------------------------------------
Private Sub Form_Load()

If OpenArgs = "New" Then
Me!fraIntExt = 0
Me!lblIntExt.Visible = False
Me!fraIntExt.Visible = True
Me!lblOrgCo.Caption = "Organization"


If Me.fraIntExt = 0 Then
Me.ctCompanyName = "Agriculture and Agri-Food Canada"
End If

Else
If Me!fraIntExt Then
Me!lblIntExt.Caption = "External"
Me!lblOrgCo.Caption = "Company"
Else
Me!lblIntExt.Caption = "Internal"
Me!lblOrgCo.Caption = "Organization"

End If
Me!lblIntExt.Visible = True
Me!fraIntExt.Visible = False
End If
If Me!LcCountry = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub fraIntExt_BeforeUpdate(Cancel As Integer)
If DCount("ContactID", "tblComteeCont", "ContactID = " &
Forms!frmCustomers!ContactID) > 0 Or DCount("ContactID", "tblExpCont",
"ContactID = " & Forms!frmCustomers!ContactID) > 0 Then
If MsgBox("By changing the customer's Internal/External designation
review all drop downs and Expertise and Committee selections." & vbCrLf
&
"Do
you wish to continue?", vbYesNo, "Confirm") = vbNo Then
Cancel = 1
Me!fraIntExt.Undo
End If
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub fraIntExt_AfterUpdate()
Dim db As Database
Set db = CurrentDb
Me!cmbClass.Requery
Me!cmbOrg.Requery

If fraIntExt Then
'from Int to Ext
db.Execute "DELETE tblExpCont.*, tblCodes.cdInt, tblCodes.cdExt,
tblCodes.cdTitleNo, tblExpCont.ContactID " _
& "FROM tblExpCont INNER JOIN tblCodes ON tblExpCont.ExpertiseID
=
tblCodes.cdValue " _
& "WHERE (((tblCodes.cdInt)=Yes) AND ((tblCodes.cdExt)=No) AND
((tblCodes.cdTitleNo)=17) AND ((tblExpCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
db.Execute "DELETE tblComteeCont.*, tblCodes.cdInt, tblCodes.cdExt,
tblCodes.cdTitleNo, tblComteeCont.ContactID " _
& "FROM tblComteeCont INNER JOIN tblCodes ON
tblComteeCont.CommitteeID = tblCodes.cdValue " _
& "WHERE (((tblCodes.cdInt)=Yes) AND ((tblCodes.cdExt)=No) AND
((tblCodes.cdTitleNo)=16) AND ((tblComteeCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
Me!lblOrgCo.Caption = "Company"
Me.[ctCompanyName] = ""
Else
'from Ext to Int
db.Execute "DELETE tblExpCont.*, tblCodes.cdInt, tblCodes.cdExt,
tblCodes.cdTitleNo, tblExpCont.ContactID " _
& "FROM tblExpCont INNER JOIN tblCodes ON tblExpCont.ExpertiseID
=
tblCodes.cdValue " _
& "WHERE (((tblCodes.cdInt)=No) AND ((tblCodes.cdExt)=Yes) AND
.

Quantcast