"Overflow Error" - need help please. FatMan 14.08.2006 23:22:01 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

Re: "Overflow Error" - need help please. "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 15.08.2006 04:22:33 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? -- 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[ at ]discussions.microsoft.com> wrote in message news:E1107C6B-E50A-436F-BFA3-844F2DC3C2EE[ at ]microsoft.com... [Quoted Text] > 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

Re: "Overflow Error" - need help please. "SurendranSNV via AccessMonster.com" <u23007[ at ]uwe> 15.08.2006 10:09:33 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 wrote: [Quoted Text] >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 -- Message posted via http://www.accessmonster.com

Re: "Overflow Error" - need help please. FatMan 16.08.2006 23:50:02 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" wrote: [Quoted Text] > 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? > > -- > 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[ at ]discussions.microsoft.com> wrote in message > news:E1107C6B-E50A-436F-BFA3-844F2DC3C2EE[ at ]microsoft.com... > > 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 > > >

Re: "Overflow Error" - need help please. "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 17.08.2006 02:36:47 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[ at ]discussions.microsoft.com> wrote in message news:1312E426-622E-4A74-AEAE-8DE6E1981E2F[ at ]microsoft.com... [Quoted Text] > 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. > > "Allen Browne" wrote: > >> 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" <FatMan[ at ]discussions.microsoft.com> wrote in message >> news:E1107C6B-E50A-436F-BFA3-844F2DC3C2EE[ at ]microsoft.com... >> > 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.

Re: "Overflow Error" - need help please. FatMan 17.08.2006 23:17:02 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: [Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message > news:1312E426-622E-4A74-AEAE-8DE6E1981E2F[ at ]microsoft.com... > > 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

Re: "Overflow Error" - need help please. "Allen Browne" <AllenBrowne[ at ]SeeSig.Invalid> 18.08.2006 01:53:11 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[ at ]discussions.microsoft.com> wrote in message news:24EE3562-065A-46D0-BC1A-64DA6808C66C[ at ]microsoft.com... [Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message >> news:1312E426-622E-4A74-AEAE-8DE6E1981E2F[ at ]microsoft.com... >> > 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