"Microsoft Access: Overflow" - displays on opening of form?

G

Guest

Hi all:
I have a form that displays the following error: "Microsoft Access:
Overflow" when the form is open on certain records. The records causing the
problem are records that have been appended and/or updated via two querries.

I thought it might indicate that the data is too large/long for the fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
 
D

Douglas J. Steele

Data that's too large for a field is the usual cause of Overflow errors,
usually a numeric value that's too large for the field type.
 
G

Guest

Douglas:
Thanks for the info but there are no numeric fields on the form or in the
database. Most of the fields are text, some yes/no, date/time and one memo
field and of course an autonumber. The query to append/update the data to
the table only updates the text fieldds. I have checked the length of every
record in the update for every field and compared them to see if they are
larger than the size of the field and all are smaller than the max size. Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan
 
G

Guest

Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some employees. They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1 AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] & "
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan
 
G

Guest

Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some employees. They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1 AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] & "
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan

P.S. Forgot to set the "notify me of replies" options in the other post.
Please reply to this one.
 
D

Douglas J. Steele

You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some employees. They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1 AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] & "
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







Douglas J. Steele said:
What's the SQL of the update query?
Click to expand...
Click to expand...
 
G

Guest

Doug:
I have checked the length of every field from tblImpPeopleSoft and compared
it to the coresponding field in tblContacts. All the fields in
tblImpPeopleSoft (including the concatenated) field is smaller than the
coresponding field in tblContacts. For example the size of address field in
tblContacts is 100 while the largest length of the cantenated field in
tblImpPeopleSoft is 88.

I honestly do not understand why the "overflow" error is showing up. Please
believe me I do appreaciate the time and effort you are point into this and
do appreciate and help you can give me.

Thanks,
FatMan

Douglas J. Steele said:
You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some employees. They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1 AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] & "
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







Douglas J. Steele said:
What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form or in the
database. Most of the fields are text, some yes/no, date/time and one
memo
field and of course an autonumber. The query to append/update the data to
the table only updates the text fieldds. I have checked the length of
every
record in the update for every field and compared them to see if they are
larger than the size of the field and all are smaller than the max size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of Overflow errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft Access:
Overflow" when the form is open on certain records. The records
causing
the
problem are records that have been appended and/or updated via two
querries.

I thought it might indicate that the data is too large/long for the
fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
Not sure if I mentioned this or not but the error only shows up when I open
a form and not when I acutally run the append querry or update querry. The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should mention
it.

Thanks,
FatMan

Douglas J. Steele said:
You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some employees. They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1 AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] & "
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







Douglas J. Steele said:
What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form or in the
database. Most of the fields are text, some yes/no, date/time and one
memo
field and of course an autonumber. The query to append/update the data to
the table only updates the text fieldds. I have checked the length of
every
record in the update for every field and compared them to see if they are
larger than the size of the field and all are smaller than the max size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of Overflow errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft Access:
Overflow" when the form is open on certain records. The records
causing
the
problem are records that have been appended and/or updated via two
querries.

I thought it might indicate that the data is too large/long for the
fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Any logic in the form's Open (or Load or Activate) events? Any fields on the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
Not sure if I mentioned this or not but the error only shows up when I open
a form and not when I acutally run the append querry or update querry. The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should mention
it.

Thanks,
FatMan

Douglas J. Steele said:
You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working with
data from an HR database that has duplicate records for some
Click to expand...
employees.
They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
Click to expand...
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
Click to expand...
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry
Click to expand...
is
ran.
Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form or
Click to expand...
in
the
database. Most of the fields are text, some yes/no, date/time and one
memo
field and of course an autonumber. The query to append/update the data to
the table only updates the text fieldds. I have checked the length of
every
record in the update for every field and compared them to see if
Click to expand...
they
are
larger than the size of the field and all are smaller than the max size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of Overflow errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft Access:
Overflow" when the form is open on certain records. The records
causing
the
problem are records that have been appended and/or updated via two
querries.

I thought it might indicate that the data is too large/long for the
fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



Douglas J. Steele said:
Any logic in the form's Open (or Load or Activate) events? Any fields on the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
Not sure if I mentioned this or not but the error only shows up when I open
a form and not when I acutally run the append querry or update querry. The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should mention
it.

Thanks,
FatMan

Douglas J. Steele said:
You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am working
with
data from an HR database that has duplicate records for some employees.
They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1, ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update querry is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form or in
the
database. Most of the fields are text, some yes/no, date/time and one
memo
field and of course an autonumber. The query to append/update the
data to
the table only updates the text fieldds. I have checked the length of
every
record in the update for every field and compared them to see if they
are
larger than the size of the field and all are smaller than the max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The records
causing
the
problem are records that have been appended and/or updated via two
querries.

I thought it might indicate that the data is too large/long for the
fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



Douglas J. Steele said:
Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
Not sure if I mentioned this or not but the error only shows up when I open
a form and not when I acutally run the append querry or update querry. The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some employees.
They
like to keep track of the "home/main" work area as well as any assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1, ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2, tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25, tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes = [f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or in
the
database. Most of the fields are text, some yes/no, date/time
and one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the length of
every
record in the update for every field and compared them to see
if they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The records
causing
the
problem are records that have been appended and/or updated
via two
querries.

I thought it might indicate that the data is too large/long
for the
fields.
Is this correct or could some on poin me in the right direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



Douglas J. Steele said:
Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to see
if
they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or updated
via
two
querries.

I thought it might indicate that the data is too large/long
for
the
fields.
Is this correct or could some on poin me in the right
direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Overflow shoud be error number 6. Assuming you've got error handling in your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to see
if
they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or updated
via
two
querries.

I thought it might indicate that the data is too large/long
for
the
fields.
Is this correct or could some on poin me in the right
direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
Thanks for the suggestion. I am working on a database designed and
programmed by someon else. I not sure if or where he would have put his
error handeling. I have added you code to the form's load event and had no
luck. Atcually I don't think they error occurs when the event loads. I put
a bunch of MsgBox commands in the code so I could try to figure out
when/where the error occurs. My test allowed me to get to the last msgbox
just be for the "end sub" line. Then the error message popped up.

Also, I tried appending one field at a time to see if it was an individual
field that was cuasing the problem. It didn't matter what the field was I
kept getting the "overflow" error.

Can you hlep me please and let me know where I should place the error
handling code. I understand that when a form is opened the following events
occur in this order: Open -> Load -> Activate -> Current.

There is only code on the current and load events.

I wish you knew how much I appreciate your help.

Thanks,
FatMan

Douglas J. Steele said:
Overflow shoud be error number 6. Assuming you've got error handling in your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to see
if
they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or updated
via
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
Thanks for the suggestion. I am working on a database designed and
programmed by someon else. I not sure if or where he would have put his
error handeling. I have added you code to the form's load event and had no
luck. Atcually I don't think they error occurs when the event loads. I put
a bunch of MsgBox commands in the code so I could try to figure out
when/where the error occurs. My test allowed me to get to the last msgbox
just be for the "end sub" line. Then the error message popped up.

Also, I tried appending one field at a time to see if it was an individual
field that was cuasing the problem. It didn't matter what the field was I
kept getting the "overflow" error.

Can you hlep me please and let me know where I should place the error
handling code. I understand that when a form is opened the following events
occur in this order: Open -> Load -> Activate -> Current.

There is only code on the current and load events.

I wish you knew how much I appreciate your help.

Thanks,
FatMan

Douglas J. Steele said:
Overflow shoud be error number 6. Assuming you've got error handling in your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to see
if
they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or updated
via
Click to expand...
Click to expand...
Click to expand...


Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



Douglas J. Steele said:
Any logic in the form's Open (or Load or Activate) events? Any fields on
the
form that have a calculation in the ControlSource (rather than simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to see
if
they
are
larger than the size of the field and all are smaller than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or updated
via
two
querries.

I thought it might indicate that the data is too large/long
for
the
fields.
Is this correct or could some on poin me in the right
direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

See what happens if you put it in the Form's Error event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Thanks for the suggestion. I am working on a database designed and
programmed by someon else. I not sure if or where he would have put his
error handeling. I have added you code to the form's load event and had
no
luck. Atcually I don't think they error occurs when the event loads. I
put
a bunch of MsgBox commands in the code so I could try to figure out
when/where the error occurs. My test allowed me to get to the last msgbox
just be for the "end sub" line. Then the error message popped up.

Also, I tried appending one field at a time to see if it was an individual
field that was cuasing the problem. It didn't matter what the field was I
kept getting the "overflow" error.

Can you hlep me please and let me know where I should place the error
handling code. I understand that when a form is opened the following
events
occur in this order: Open -> Load -> Activate -> Current.

There is only code on the current and load events.

I wish you knew how much I appreciate your help.

Thanks,
FatMan

Douglas J. Steele said:
Overflow shoud be error number 6. Assuming you've got error handling in
your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


FatMan said:
Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

:

Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
Yes there is some logic/coding on the "on current" and "on load" events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the
database and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any
fields on
the
form that have a calculation in the ControlSource (rather than
simply the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when I
open
a form and not when I acutally run the append querry or update querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I
should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert
into
another
table. What are the allowed sizes for each field in
tblContacts and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to
the table
while
the second updates a "memo" field for duplicate records. I
am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as
any
assignments
the employee has been given. There is nothing I can do
about the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName, ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity,
ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the
update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas:
Thanks for the info but there are no numeric fields on
the form
or
in
the
database. Most of the fields are text, some yes/no, date/time
and
one
memo
field and of course an autonumber. The query to append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them
to see
if
they
are
larger than the size of the field and all are smaller
than the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi all:
I have a form that displays the following error: "Microsoft
Access:
Overflow" when the form is open on certain records.
The
records
causing
the
problem are records that have been appended and/or updated
via
Click to expand...
Click to expand...


Douglas J. Steele said:
Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Yes there is some logic/coding on the "on current" and "on load"
events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the database
and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any fields
on
the
form that have a calculation in the ControlSource (rather than simply
the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up when
I
open
a form and not when I acutally run the append querry or update
querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert into
another
table. What are the allowed sizes for each field in tblContacts
and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to the
table
while
the second updates a "memo" field for duplicate records. I am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as any
assignments
the employee has been given. There is nothing I can do about
the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName,
ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity, ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET tblContacts.ctCustNotes
=
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas:
Thanks for the info but there are no numeric fields on the
form
or
in
the
database. Most of the fields are text, some yes/no,
date/time
and
one
memo
field and of course an autonumber. The query to
append/update
the
data to
the table only updates the text fieldds. I have checked the
length of
every
record in the update for every field and compared them to
see
if
they
are
larger than the size of the field and all are smaller than
the
max
size.
Any
ideas?

Once again your help is greatly appreciated.

Thanks,
FatMan

:

Data that's too large for a field is the usual cause of
Overflow
errors,
usually a numeric value that's too large for the field
type.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Hi all:
I have a form that displays the following error:
"Microsoft
Access:
Overflow" when the form is open on certain records. The
records
causing
the
problem are records that have been appended and/or
updated
via
two
querries.

I thought it might indicate that the data is too
large/long
for
the
fields.
Is this correct or could some on poin me in the right
direction?

Any help would be greatly appreciated.

Thanks,
FatMan
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Doug:
Added the code below to the error event and nothing happens.

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

Like I said NOTHING happens not even the message boxes appear.

I know you must be getting fraustrated with me and this problem and I can
understand that. I hope you can understand how much I appreciate all that
you have done so far. If we could meet trust me I would buy you a cold beer
(or the drink of your choice) more than once.

Thanks,
FatMan

Douglas J. Steele said:
See what happens if you put it in the Form's Error event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Thanks for the suggestion. I am working on a database designed and
programmed by someon else. I not sure if or where he would have put his
error handeling. I have added you code to the form's load event and had
no
luck. Atcually I don't think they error occurs when the event loads. I
put
a bunch of MsgBox commands in the code so I could try to figure out
when/where the error occurs. My test allowed me to get to the last msgbox
just be for the "end sub" line. Then the error message popped up.

Also, I tried appending one field at a time to see if it was an individual
field that was cuasing the problem. It didn't matter what the field was I
kept getting the "overflow" error.

Can you hlep me please and let me know where I should place the error
handling code. I understand that when a form is opened the following
events
occur in this order: Open -> Load -> Activate -> Current.

There is only code on the current and load events.

I wish you knew how much I appreciate your help.

Thanks,
FatMan

Douglas J. Steele said:
Overflow shoud be error number 6. Assuming you've got error handling in
your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
What can I do...where can I turn to? Am I only left with setting the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

:

Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
Yes there is some logic/coding on the "on current" and "on load"
events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the
database
and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any
fields
on
the
form that have a calculation in the ControlSource (rather than
simply
the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows up
when I
open
a form and not when I acutally run the append querry or update
querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I
should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to insert
into
another
table. What are the allowed sizes for each field in
tblContacts
and
in
tblImpPeopleSoft? Note that you're concatenating fields from
tblImpPeopleSoft together. Does the concatenated length exceed
the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
There are two queries - the first one appends the data to
the
table
while
the second updates a "memo" field for duplicate records. I
am
working
with
data from an HR database that has duplicate records for some
employees.
They
like to keep track of the "home/main" work area as well as
any
assignments
the employee has been given. There is nothing I can do
about
the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName,
ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity,
ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET
tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the
update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas:
Thanks for the info but there are no numeric fields on
the
form
or
in
the
database. Most of the fields are text, some yes/no,
date/time
and
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Sorry, I'm completely out of ideas.

Try creating a new database and importing everything from the old database
into the new one, just in case it's a sign of corruption.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Added the code below to the error event and nothing happens.

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

Like I said NOTHING happens not even the message boxes appear.

I know you must be getting fraustrated with me and this problem and I can
understand that. I hope you can understand how much I appreciate all that
you have done so far. If we could meet trust me I would buy you a cold
beer
(or the drink of your choice) more than once.

Thanks,
FatMan

Douglas J. Steele said:
See what happens if you put it in the Form's Error event.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


FatMan said:
Doug:
Thanks for the suggestion. I am working on a database designed and
programmed by someon else. I not sure if or where he would have put
his
error handeling. I have added you code to the form's load event and
had
no
luck. Atcually I don't think they error occurs when the event loads.
I
put
a bunch of MsgBox commands in the code so I could try to figure out
when/where the error occurs. My test allowed me to get to the last
msgbox
just be for the "end sub" line. Then the error message popped up.

Also, I tried appending one field at a time to see if it was an
individual
field that was cuasing the problem. It didn't matter what the field
was I
kept getting the "overflow" error.

Can you hlep me please and let me know where I should place the error
handling code. I understand that when a form is opened the following
events
occur in this order: Open -> Load -> Activate -> Current.

There is only code on the current and load events.

I wish you knew how much I appreciate your help.

Thanks,
FatMan

:

Overflow shoud be error number 6. Assuming you've got error handling
in
your
code, you could always tell it to ignore error 6:

ErrorHandler:
Select Case Err.Number
Case 6
Resume Next
Case Else
' generate your normal error message here
End Select

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
What can I do...where can I turn to? Am I only left with setting
the
warnings off while this form is open?

I do appreciate the time and effort you have put into this.

Thanks,
FatMan

:

Sorry, nothing obvious jumps out.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Doug:
Yes there is some logic/coding on the "on current" and "on load"
events.
They are as follows:

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


To be honest I did not do the coding and am only modifing the
database
and
its forms.

I can not beging to tell you how much I appreciate you help.

Thanks,
Fatman



:

Any logic in the form's Open (or Load or Activate) events? Any
fields
on
the
form that have a calculation in the ControlSource (rather than
simply
the
name of a field in the recordsource)?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doug:
Not sure if I mentioned this or not but the error only shows
up
when I
open
a form and not when I acutally run the append querry or
update
querry.
The
record source uses the following select statement.

SELECT tblContacts.* FROM tblContacts;

I don't think it has any affect on the problem but thought I
should
mention
it.

Thanks,
FatMan

:

You're extracting values from one table and trying to
insert
into
another
table. What are the allowed sizes for each field in
tblContacts
and
in
tblImpPeopleSoft? Note that you're concatenating fields
from
tblImpPeopleSoft together. Does the concatenated length
exceed
the
allowable
size in tblContacts?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Doug:
There are two queries - the first one appends the data to
the
table
while
the second updates a "memo" field for duplicate records.
I
am
working
with
data from an HR database that has duplicate records for
some
employees.
They
like to keep track of the "home/main" work area as well
as
any
assignments
the employee has been given. There is nothing I can do
about
the
duplicates...that is just the way it is.

Here is the SQL for the two querries:

Append querry:
INSERT INTO tblContacts ( ctPeopleSoftID, ctLastName1,
ctFirstName1,
ctEnglishLang, ctEnglishServ, ctFrenchLang, ctEmailName,
ctPhone1,
ctFax,
ctCellPhone, ctCompanyName, ctLcAddress, ctLcCity,
ctLcCountry,
ctLcPostalCode, ctLcProvState )
SELECT tblImpPeopleSoft.F1, tblImpPeopleSoft.F2,
tblImpPeopleSoft.F3, -1
AS
English, -1 AS EnglishServ, IIf([F4]="Y",-1,0) AS French,
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, tblImpPeopleSoft.F25,
tblImpPeopleSoft.F26,
tblImpPeopleSoft.F27
FROM tblImpPeopleSoft;


The update query:
UPDATE tblContacts, tblImpPeopleSoft SET
tblContacts.ctCustNotes =
[f15] &
"
- " & [f13] & Chr(13) & Chr(10) & [ctCustNotes]
WHERE (((tblContacts.ctPeopleSoftID)=[f1]) AND
((tblImpPeopleSoft.F15)<>"Substantive"));


If it helps the "overflow" error occurs even before the
update
querry
is
ran.

Any help you can provide is greatly appreciated.

Thanks,
FatMan







:

What's the SQL of the update query?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Douglas:
Thanks for the info but there are no numeric fields
on
the
form
or
in
the
database. Most of the fields are text, some yes/no,
date/time
and
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

"Microsoft Access: Overflow" - on form opening..what does it mean? 1
Overflow error on calculate field in query - Help Please 1
Access Overflow error 3
keep subform synchronized with main form in Microsoft Access 2003 1
Number of records in recordset on form (VBA) 3
Opening a Word doc using a command button on a form. 14
Skip loading the form 'on error'? 1
"Overflow Error" - need help please. 7

Top