"Overflow Error" - need help please.

[FatMan]

Hi all:
I am getting an "Overflow Error" when I open a form on certain data. This
data has been appended to my database. The append query appears to work as
my data is added to the table but when the form is opened I get an "Overflow
Error" and no further explanation.

I have gotten help under the "Forms: Programming" section of this discussion
group and have tried everything suggested but NOTHING works. What I believe
I am faced with is to put in some code telling Access to ignore the error for
this form. What I need is some help with the code. I have added the below
code to the forms "On error" event and NOTHING happens not even the Msgbox
statements.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
MsgBox DataErr
MsgBox Response
MsgBox Err.Number
Select Case DataErr
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select
End Sub

Can someone please help me with the code? I will need to know where to
place the code (in one of the forms events or part of a global module). My
knowledge of programing is primative.

All help is appreciated.

Thanks,
FatMan

 

[Allen Browne]

FatMan, I doubt that approach will be productive. The form's Error message
fires when there is an engine-level error. Even if it did manage to suppress
the error message, it doesn't solve the engine-level problem. Kinda-like
unscrewing the warning light from the dashboard of your car: it doesn't
solve the problem.

You will need to track down what is causing this error:
Is this an attached table?

Or is the data imported and stored in Access?

Is the form based on a query?

Do you see the error when you open the table/query and scroll to the end?

Are there any calculated fields in the source query?

Are there any calculated controls on your form (i.e. the Control Source
starts with equals)?

Is there any other code in the form?

What version of Access is this?

 

[SurendranSNV via AccessMonster.com]

In addition to Allen Browne message, visit the following links where in Allen
Browne and others have explained the cause for overflow with examples.

http://www.accessmonster.com/Uwe/Forum.aspx/databases-ms-access/24805/Overflow


http://www.accessmonster.com/Uwe/Forum.aspx/access-modulesdaovba/2168/Overflow


An overflow results when you try to make an assignment that exceeds the
limitations of the target of the assignment.

Surendran

 

[FatMan]

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
((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)=No) AND ((tblCodes.cdExt)=Yes) AND
((tblCodes.cdTitleNo)=16) AND ((tblComteeCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
Me!lblOrgCo.Caption = "Organization"
Me.[ctCompanyName] = "Agriculture and Agri-Food Canada"
End If
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_AfterUpdate()
Me!LcCountry = Me!cmbLcProvState.Column(2)
If Me!cmbLcProvState.Column(2) = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_DblClick(Cancel As Integer)
Me!cmbLcProvState.Undo
DoCmd.OpenForm "frmPrvStMgmt", , , , acFormAdd, , "cmbLcProvState"
End Sub
---------------------------------------------------------------------------------------------
Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
MsgBox "Contact Information is incomplete." & vbCrLf & "All areas marked
in red need to be completed."
Exit Sub
End If
If OpenArgs = "New" Then
giNumber = Me!ctContactID
DoCmd.Close acForm, "frmCustomers"
DoCmd.Close acForm, "frmCustList"
gbFindRec = True
DoCmd.OpenForm "frmCustList"
DoCmd.GoToControl "ContID"
DoCmd.FindRecord giNumber
DoCmd.GoToControl "btnEdit"
Else
DoCmd.Close acForm, "frmCustomers"
If Not IsLoaded("frmCustList") Then
gbFindRec = True
DoCmd.OpenForm "frmCustList"
End If
'DoCmd.GoToControl "ContID"

Forms!frmCustList!ContID.SetFocus
DoCmd.FindRecord giNumber, acStart, False, , False, acCurrent, True
DoCmd.GoToControl "btnEdit"
End If


Exit_btnDone_Click:
Exit Sub

Err_btnDone_Click:
MsgBox Err.Description
Resume Exit_btnDone_Click

End Sub
---------------------------------------------------------------------------------------------

Private Sub btnCont_Click()
On Error GoTo Err_btnCont_Click
Dim db As Database
Dim rs As Recordset
Dim stDocName As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
If MsgBox("Contact Information is incomplete." & vbCrLf & "All areas
marked in red should be completed." & vbCrLf & "Select 'Cancel' to complete
the Contact Information.", vbOKCancel, "Incomplete Contact Info") = vbCancel
Then
Exit Sub
End If
End If
Set rs = db.OpenRecordset("SELECT tblServAreaName.*,
tblServAreaName.snInactive " _
& "FROM tblServAreaName " _
& "WHERE (((tblServAreaName.snInactive)=No));")
If rs.RecordCount = 0 Then
MsgBox "Please set up active service areas before making a contact."
Exit Sub
End If
DoCmd.Close acForm, "frmCustList"
Call CreateTmpContList
stDocName = "frmCustCare"
DoCmd.OpenForm stDocName, , , , , , Me!ContactID
DoCmd.Close acForm, "frmCustomers"

Exit_btnCont_Click:
Exit Sub

Err_btnCont_Click:
MsgBox Err.Description
Resume Exit_btnCont_Click

End Sub

8) The version of access being used is Access 2000.

Allen, I hope I have answered your questions and hope the details will aid
you in finding why the "overflow error" is displayed when the form is opened.

Thanking you in advance for any help you can offer.

Thanks,
FatMan

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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
((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)=No) AND ((tblCodes.cdExt)=Yes) AND
((tblCodes.cdTitleNo)=16) AND ((tblComteeCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
Me!lblOrgCo.Caption = "Organization"
Me.[ctCompanyName] = "Agriculture and Agri-Food Canada"
End If
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_AfterUpdate()
Me!LcCountry = Me!cmbLcProvState.Column(2)
If Me!cmbLcProvState.Column(2) = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_DblClick(Cancel As Integer)
Me!cmbLcProvState.Undo
DoCmd.OpenForm "frmPrvStMgmt", , , , acFormAdd, , "cmbLcProvState"
End Sub
---------------------------------------------------------------------------------------------
Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
MsgBox "Contact Information is incomplete." & vbCrLf & "All areas
marked
in red need to be completed."
Exit Sub
End If
If OpenArgs = "New" Then
giNumber = Me!ctContactID
DoCmd.Close acForm, "frmCustomers"
DoCmd.Close acForm, "frmCustList"
gbFindRec = True
DoCmd.OpenForm "frmCustList"
DoCmd.GoToControl "ContID"
DoCmd.FindRecord giNumber
DoCmd.GoToControl "btnEdit"
Else
DoCmd.Close acForm, "frmCustomers"
If Not IsLoaded("frmCustList") Then
gbFindRec = True
DoCmd.OpenForm "frmCustList"
End If
'DoCmd.GoToControl "ContID"

Forms!frmCustList!ContID.SetFocus
DoCmd.FindRecord giNumber, acStart, False, , False, acCurrent, True
DoCmd.GoToControl "btnEdit"
End If


Exit_btnDone_Click:
Exit Sub

Err_btnDone_Click:
MsgBox Err.Description
Resume Exit_btnDone_Click

End Sub
---------------------------------------------------------------------------------------------

Private Sub btnCont_Click()
On Error GoTo Err_btnCont_Click
Dim db As Database
Dim rs As Recordset
Dim stDocName As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
If MsgBox("Contact Information is incomplete." & vbCrLf & "All areas
marked in red should be completed." & vbCrLf & "Select 'Cancel' to
complete
the Contact Information.", vbOKCancel, "Incomplete Contact Info") =
vbCancel
Then
Exit Sub
End If
End If
Set rs = db.OpenRecordset("SELECT tblServAreaName.*,
tblServAreaName.snInactive " _
& "FROM tblServAreaName " _
& "WHERE (((tblServAreaName.snInactive)=No));")
If rs.RecordCount = 0 Then
MsgBox "Please set up active service areas before making a contact."
Exit Sub
End If
DoCmd.Close acForm, "frmCustList"
Call CreateTmpContList
stDocName = "frmCustCare"
DoCmd.OpenForm stDocName, , , , , , Me!ContactID
DoCmd.Close acForm, "frmCustomers"

Exit_btnCont_Click:
Exit Sub

Err_btnCont_Click:
MsgBox Err.Description
Resume Exit_btnCont_Click

End Sub

8) The version of access being used is Access 2000.

Allen, I hope I have answered your questions and hope the details will aid
you in finding why the "overflow error" is displayed when the form is
opened.

Thanking you in advance for any help you can offer.

FatMan, I doubt that approach will be productive. The form's Error
message
fires when there is an engine-level error. Even if it did manage to
suppress
the error message, it doesn't solve the engine-level problem. Kinda-like
unscrewing the warning light from the dashboard of your car: it doesn't
solve the problem.

You will need to track down what is causing this error:
Is this an attached table?

Or is the data imported and stored in Access?

Is the form based on a query?

Do you see the error when you open the table/query and scroll to the end?

Are there any calculated fields in the source query?

Are there any calculated controls on your form (i.e. the Control Source
starts with equals)?

Is there any other code in the form?

What version of Access is this?

 

[FatMan]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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
((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)=No) AND ((tblCodes.cdExt)=Yes) AND
((tblCodes.cdTitleNo)=16) AND ((tblComteeCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
Me!lblOrgCo.Caption = "Organization"
Me.[ctCompanyName] = "Agriculture and Agri-Food Canada"
End If
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_AfterUpdate()
Me!LcCountry = Me!cmbLcProvState.Column(2)
If Me!cmbLcProvState.Column(2) = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_DblClick(Cancel As Integer)
Me!cmbLcProvState.Undo
DoCmd.OpenForm "frmPrvStMgmt", , , , acFormAdd, , "cmbLcProvState"
End Sub
---------------------------------------------------------------------------------------------
Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
MsgBox "Contact Information is incomplete." & vbCrLf & "All areas
marked
in red need to be completed."
Exit Sub
End If
If OpenArgs = "New" Then
giNumber = Me!ctContactID
DoCmd.Close acForm, "frmCustomers"
DoCmd.Close acForm, "frmCustList"
gbFindRec = True
DoCmd.OpenForm "frmCustList"
DoCmd.GoToControl "ContID"
DoCmd.FindRecord giNumber
DoCmd.GoToControl "btnEdit"
Else
DoCmd.Close acForm, "frmCustomers"
If Not IsLoaded("frmCustList") Then
gbFindRec = True
DoCmd.OpenForm "frmCustList"
End If
'DoCmd.GoToControl "ContID"

Forms!frmCustList!ContID.SetFocus
DoCmd.FindRecord giNumber, acStart, False, , False, acCurrent, True
DoCmd.GoToControl "btnEdit"
End If


Exit_btnDone_Click:
Exit Sub

Err_btnDone_Click:
MsgBox Err.Description
Resume Exit_btnDone_Click

End Sub
---------------------------------------------------------------------------------------------

Private Sub btnCont_Click()
On Error GoTo Err_btnCont_Click
Dim db As Database
Dim rs As Recordset
Dim stDocName As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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
((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)=No) AND ((tblCodes.cdExt)=Yes) AND
((tblCodes.cdTitleNo)=16) AND ((tblComteeCont.ContactID)= " &
[Forms]![frmCustomers]![ContactID] & "));"
Me!lblOrgCo.Caption = "Organization"
Me.[ctCompanyName] = "Agriculture and Agri-Food Canada"
End If
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_AfterUpdate()
Me!LcCountry = Me!cmbLcProvState.Column(2)
If Me!cmbLcProvState.Column(2) = "Canada" Then
Me!LcPostalCode.InputMask = ">L0L\ 0L0"
Else
Me!LcPostalCode.InputMask = ""
End If

End Sub
---------------------------------------------------------------------------------------------
Private Sub cmbLcProvState_DblClick(Cancel As Integer)
Me!cmbLcProvState.Undo
DoCmd.OpenForm "frmPrvStMgmt", , , , acFormAdd, , "cmbLcProvState"
End Sub
---------------------------------------------------------------------------------------------
Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
DoCmd.RunCommand acCmdSaveRecord
If CustInCompl() Then
If OpenArgs = "New" Then
DoCmd.OpenForm "frmCustInc"
Exit Sub
End If
MsgBox "Contact Information is incomplete." & vbCrLf & "All areas
marked
in red need to be completed."
Exit Sub
End If
If OpenArgs = "New" Then
giNumber = Me!ctContactID
DoCmd.Close acForm, "frmCustomers"
DoCmd.Close acForm, "frmCustList"
gbFindRec = True
DoCmd.OpenForm "frmCustList"
DoCmd.GoToControl "ContID"
DoCmd.FindRecord giNumber
DoCmd.GoToControl "btnEdit"
Else
DoCmd.Close acForm, "frmCustomers"
If Not IsLoaded("frmCustList") Then
gbFindRec = True
DoCmd.OpenForm "frmCustList"
End If
'DoCmd.GoToControl "ContID"

Forms!frmCustList!ContID.SetFocus
DoCmd.FindRecord giNumber, acStart, False, , False, acCurrent, True
DoCmd.GoToControl "btnEdit"
End If


Exit_btnDone_Click:
Exit Sub

Err_btnDone_Click:
MsgBox Err.Description
Resume Exit_btnDone_Click

End Sub
---------------------------------------------------------------------------------------------

Private Sub btnCont_Click()
On Error GoTo Err_btnCont_Click
Dim db As Database
Dim rs As Recordset
Dim stDocName As String
Set db = CurrentDb
DoCmd.RunCommand acCmdSaveRecord

 

[FatMan]

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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.

Reply to group, rather than allenbrowne at mvps dot org.

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