Truncated error

[Todd K.]

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," & [curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] & [strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error '8152':
string or binary data would be truncated" I have no idea what this means,
but since it is a string data type and I'm nowhere near the 65,000 character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Douglas J. Steele]

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"& _
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

 

[Todd K.]

I coded as below and when I ran it I got a "Run-time error '91': object
variable or with block variable not set". When I debugged, it highlighted
the line "Set rsCurr = CurrentDb.OpenRecordset(strSQL)". Note that I didn't
have "DAO.Recordset", just "Recordset". DAO was not an option.

'Declare variables
Dim rsCurr As Recordset
Dim strSQL As String

'Set variables
strSQL = "SELECT
Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,Balance,Spent,ProjectDescription FROM TblRptCompletion"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.Grant_Type
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Applicant
!ProjectAllocation = Me.Fund_Amt
!ProjectDescription = Me.Project_Desc
!Balance = Me.RemBal
!Spent = curProj_Alloc - curBal
.Update
.Close
End With
Set rsCurr = Nothing

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"& _
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," &
[curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] &
[strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error
'8152':
string or binary data would be truncated" I have no idea what this means,
but since it is a string data type and I'm nowhere near the 65,000
character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Regan via AccessMonster.com]

Hi

IN you Visual Basic window goto

Tools\References and check 'Microsoft DAO 3.6 Object library'

Then you can use ' Dim rsCurr as DAO.Recordset

Hope it helps

I coded as below and when I ran it I got a "Run-time error '91': object
variable or with block variable not set". When I debugged, it highlighted
the line "Set rsCurr = CurrentDb.OpenRecordset(strSQL)". Note that I didn't
have "DAO.Recordset", just "Recordset". DAO was not an option.

'Declare variables
Dim rsCurr As Recordset
Dim strSQL As String

'Set variables
strSQL = "SELECT
Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,Balance,Spent,ProjectDescription FROM TblRptCompletion"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.Grant_Type
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Applicant
!ProjectAllocation = Me.Fund_Amt
!ProjectDescription = Me.Project_Desc
!Balance = Me.RemBal
!Spent = curProj_Alloc - curBal
.Update
.Close
End With
Set rsCurr = Nothing

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

[quoted text clipped - 69 lines]

 

[Todd K.]

Regan -

Thanks, I'm always hesitant to go into DAO because 1) I'm not familiar with
it and 2) since this application is going on a lot of computers I don't want
to have to reference additional libraries on all of those computers. I did
find the solution. It turns out the field [ProjectName] in table
TblRptCompletion was set as nvarchar (50) and some of the Project Names I was
trying to insert were more than 50 characters long. Once I increased the
field size to 255, I had no more problems (I'm an idiot).

I appreciate your help.

Hi

IN you Visual Basic window goto

Tools\References and check 'Microsoft DAO 3.6 Object library'

Then you can use ' Dim rsCurr as DAO.Recordset

Hope it helps

I coded as below and when I ran it I got a "Run-time error '91': object
variable or with block variable not set". When I debugged, it highlighted
the line "Set rsCurr = CurrentDb.OpenRecordset(strSQL)". Note that I didn't
have "DAO.Recordset", just "Recordset". DAO was not an option.

'Declare variables
Dim rsCurr As Recordset
Dim strSQL As String

'Set variables
strSQL = "SELECT
Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,Balance,Spent,ProjectDescription FROM TblRptCompletion"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.Grant_Type
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Applicant
!ProjectAllocation = Me.Fund_Amt
!ProjectDescription = Me.Project_Desc
!Balance = Me.RemBal
!Spent = curProj_Alloc - curBal
.Update
.Close
End With
Set rsCurr = Nothing

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

[quoted text clipped - 69 lines]

limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Todd K.]

Douglas -

Thanks, I'm always hesitant to go into DAO because 1) I'm not familiar with
it and 2) since this application is going on a lot of computers I don't want
to have to reference additional libraries on all of those computers. I did
find the solution. It turns out the field [ProjectName] in table
TblRptCompletion was set as nvarchar (50) and some of the Project Names I was
trying to insert were more than 50 characters long. Once I increased the
field size to 255, I had no more problems (I'm an idiot).

I appreciate your help.

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"& _
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," &
[curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] &
[strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error
'8152':
string or binary data would be truncated" I have no idea what this means,
but since it is a string data type and I'm nowhere near the 65,000
character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Douglas J. Steele]

DAO was developed specifically for Jet databases.

You don't have to do anything to reference it on the other computers:
references live with the database, not Access, so once you've set it in your
database, everyone will have it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas -

Thanks, I'm always hesitant to go into DAO because 1) I'm not familiar
with
it and 2) since this application is going on a lot of computers I don't
want
to have to reference additional libraries on all of those computers. I
did
find the solution. It turns out the field [ProjectName] in table
TblRptCompletion was set as nvarchar (50) and some of the Project Names I
was
trying to insert were more than 50 characters long. Once I increased the
field size to 255, I had no more problems (I'm an idiot).

I appreciate your help.

I don't have an immediate answer as to why it's not working, but how
about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"&
_
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," &
[curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] &
[strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error
'8152':
string or binary data would be truncated" I have no idea what this
means,
but since it is a string data type and I'm nowhere near the 65,000
character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Robert Morley]

Almost the same code would've worked in ADO as well, if that's what you're
using. Since you seem to have fixed the problem, it doesn't really matter,
but if you need specifics on how to do that, just ask.


Rob

Douglas -

Thanks, I'm always hesitant to go into DAO because 1) I'm not familiar
with
it and 2) since this application is going on a lot of computers I don't
want
to have to reference additional libraries on all of those computers. I
did
find the solution. It turns out the field [ProjectName] in table
TblRptCompletion was set as nvarchar (50) and some of the Project Names I
was
trying to insert were more than 50 characters long. Once I increased the
field size to 255, I had no more problems (I'm an idiot).

I appreciate your help.

I don't have an immediate answer as to why it's not working, but how
about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"&
_
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," &
[curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] &
[strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error
'8152':
string or binary data would be truncated" I have no idea what this
means,
but since it is a string data type and I'm nowhere near the 65,000
character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.

 

[Guest]

DAO is part of a standard Windows installation, and
is required by Access, so you can be pretty sure it will
be there on all machines.

And if you don't want to reference the type library (which
I've never had problems with), you can declare everything
as 'object'. DAO still works without the type library, because
the methods and objects (such as Application.CurrentDb) are
Access Application methods and objects anyway:

You will sometimes have problems with DAO/Jet, but since
the Application.DoCmd methods are just wrappers on
Application.dbengine methods, you don't generally save
anything by choosing one method over the other:

'Declare variables
Dim rsCurr As object
Dim strSQL As String

'Set variables
strSQL = "SELECT
Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,Balanc
e,Spent,ProjectDescription FROM TblRptCompletion"
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
...
.Update
.Close
End With
Set rsCurr = Nothing

(david)

Douglas -

Thanks, I'm always hesitant to go into DAO because 1) I'm not familiar with
it and 2) since this application is going on a lot of computers I don't want
to have to reference additional libraries on all of those computers. I did
find the solution. It turns out the field [ProjectName] in table
TblRptCompletion was set as nvarchar (50) and some of the Project Names I was
trying to insert were more than 50 characters long. Once I increased the
field size to 255, I had no more problems (I'm an idiot).

I appreciate your help.

I don't have an immediate answer as to why it's not working, but how about
opening a recordset and adding the data that way?

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT Fund,Contact,County,ProjectName,ProjectID,Applicant,"& _
"ProjectAllocation, Balance, Spent,ProjectDescription "& _
"FROM TblRptCompletion "
Set rsCurr = CurrentDb.OpenRecordset(strSQL)
With rsCurr
.AddNew
!Fund = Me.GrantTYpe
!Contact = Me.Personnel
!County = Me.County
!ProjectName = Me.Project_Name
!ProjectID = Me.Project_No
!Applicant = Me.Application
!ProjectAllocation = Me.Fund_Amt
!Balance = Me.RemBal
!Spent = Me.Fund_Amt - Me.RemBal
!ProjectDescription = Me.ProjectDesc
.Update
.Close
End With
Set rsCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I have the following code in a Form:

'Declare variables
Dim strFundType As String
Dim strContact As String
Dim strCounty As String
Dim strProjTitle As String
Dim strProjID As String
Dim strApplicant As String
Dim curProj_Alloc As Currency
Dim strProj_Desc As String
Dim curBal As Currency
Dim curSpent As Currency
Dim strvalues As String

'Set variables
strFundType = Me.Grant_Type
strContact = Me.Personnel
strCounty = Me.County
strProjTitle = Replace(Me.Project_Name, "'", "")
strProjID = Me.Project_No
strApplicant = Me.Applicant
curProj_Alloc = Me.Fund_Amt
strProj_Desc = ",'" & Me.Project_Desc & "'"
curBal = Me.RemBal
curSpent = curProj_Alloc - curBal
strvalues = "'" & [strFundType] & " ','" & [strContact] & "','" &
[strCounty] & "','" & [strProjTitle] & "','" & [strProjID] & "','" &
[strApplicant] & "'," & [curProj_Alloc] & "," & [curBal] & "," &
[curSpent]

' Run the dataset query
DoCmd.RunSQL "DELETE FROM TblRptCompletion;"
DoCmd.RunSQL "INSERT INTO TblRptCompletion
(Fund,Contact,County,ProjectName,ProjectID,Applicant,ProjectAllocation,
Balance, Spent,ProjectDescription) VALUES (" & [strvalues] &
[strProj_Desc] &
");"

Sometimes it works great, sometimes I get the error: "Run-time error
'8152':
string or binary data would be truncated" I have no idea what this means,
but since it is a string data type and I'm nowhere near the 65,000
character
limit, I know it's not that the string is too long. When I debug, it
highlights the "INSERT INTO..." line.