HELP: Run-time error '-2147352567 (80020009):

[Guest]

hello,

I am getting the following error:
"Run-time error '-2147352567 (80020009): You can't assign a value to this
object."

I am attempting to query an Access table to retireve a MAX value. I then
need to re-format this value and assign it (the new, re-formatted value, a
string) to a txtBox on a form. No matter what I do, when I attempt to assign
the value to a variable (be it the txtbox or any other), I get the run-time
error. The code is shown below. Can anyone explain this error, why it is
occurring, and how I can resolve it?

Dim Pad4 As String 'Number of zeroes to pad the Numeric portion of new S3ID
Dim rst As New ADODB.Recordset 'For the recordset
Dim fld As ADODB.Field 'The field within the recordset.
Dim I As Integer 'To count/walk through the recordset being
returned.


'Message box to prompt user

If Response = vbOK Then

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open Source:="SELECT count(tblDummyS3ID.DummyS3ID)+1 AS
NewMaxNumber FROM tblDummyS3ID"
For I = 0 To rst.Fields.Count - 1
Set fld = rst.Fields(I)
MsgBox ("Fields(I) = " & rst.Fields(I).Value)
newMaxNumber = rst.Fields(I).Value
MsgBox ("NewMaxNumber is " & newMaxNumber)
Next I
txtS3ID = NewMaxS3ID 'RUN-TIME ERROR GENERATED HERE

rst.Close
Set fld = Nothing
Set rst = Nothing

'txtS3ID = newMaxNumber
NewMaxS3ID = newMaxNumber
NumLength = Len(NewMaxS3ID)
MsgBox ("NewMaxS3ID = " & NewMaxS3ID & ". currentMaxS3ID = " &
CurrentMaxS3ID)
Select Case NumLength
Case Is = 1
NewMaxS3ID = Pad4 & NewMaxS3ID
Case Is = 2
NewMaxS3ID = Pad3 & NewMaxS3ID
Case Is = 3
NewMaxS3ID = Pad2 & NewMaxS3ID
Case Is = 4
NewMaxS3ID = Pad1 & NewMaxS3ID
End Select
NewMaxS3ID = "AA" & NewMaxS3ID
txtS3ID = NewMaxS3ID 'RUN-TIME ERROR GENERATED HERE
ALSO

'Value for the txtbox is now formatted.
MsgBox ("The generated temporary S3ID is " & NewMaxS3ID)

'Code to insert the new value, NewMaxNumber back into the table.
SqlInsertStr = "Insert into tblDummyS3ID (DummyS3ID) Values
(NewMaxS3ID+1);"
recset.Open "tblDummyS3ID", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
With recset
.AddNew
.Update
End With
recset.Close
Set recset = Nothing
Else
MsgBox ("You have cancelled the On-Boarding of this employee. You
may continue On-Boarding.")
End If

 

[OldPro]

hello,

I am getting the following error:
"Run-time error '-2147352567 (80020009): You can't assign a value to this
object."

I am attempting to query an Access table to retireve a MAX value. I then
need to re-format this value and assign it (the new, re-formatted value, a
string) to a txtBox on a form. No matter what I do, when I attempt to assign
the value to a variable (be it the txtbox or any other), I get the run-time
error. The code is shown below. Can anyone explain this error, why it is
occurring, and how I can resolve it?

Dim Pad4 As String 'Number of zeroes to pad the Numeric portion of new S3ID
Dim rst As New ADODB.Recordset 'For the recordset
Dim fld As ADODB.Field 'The field within the recordset.
Dim I As Integer 'To count/walk through the recordset being
returned.

'Message box to prompt user

If Response = vbOK Then

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open Source:="SELECT count(tblDummyS3ID.DummyS3ID)+1 AS
NewMaxNumber FROM tblDummyS3ID"
For I = 0 To rst.Fields.Count - 1
Set fld = rst.Fields(I)
MsgBox ("Fields(I) = " & rst.Fields(I).Value)
newMaxNumber = rst.Fields(I).Value
MsgBox ("NewMaxNumber is " & newMaxNumber)
Next I
txtS3ID = NewMaxS3ID 'RUN-TIME ERROR GENERATED HERE

rst.Close
Set fld = Nothing
Set rst = Nothing

'txtS3ID = newMaxNumber
NewMaxS3ID = newMaxNumber
NumLength = Len(NewMaxS3ID)
MsgBox ("NewMaxS3ID = " & NewMaxS3ID & ". currentMaxS3ID = " &
CurrentMaxS3ID)
Select Case NumLength
Case Is = 1
NewMaxS3ID = Pad4 & NewMaxS3ID
Case Is = 2
NewMaxS3ID = Pad3 & NewMaxS3ID
Case Is = 3
NewMaxS3ID = Pad2 & NewMaxS3ID
Case Is = 4
NewMaxS3ID = Pad1 & NewMaxS3ID
End Select
NewMaxS3ID = "AA" & NewMaxS3ID
txtS3ID = NewMaxS3ID 'RUN-TIME ERROR GENERATED HERE
ALSO

'Value for the txtbox is now formatted.
MsgBox ("The generated temporary S3ID is " & NewMaxS3ID)

'Code to insert the new value, NewMaxNumber back into the table.
SqlInsertStr = "Insert into tblDummyS3ID (DummyS3ID) Values
(NewMaxS3ID+1);"
recset.Open "tblDummyS3ID", CurrentProject.Connection,
adOpenDynamic, adLockOptimistic
With recset
.AddNew
.Update
End With
recset.Close
Set recset = Nothing
Else
MsgBox ("You have cancelled the On-Boarding of this employee. You
may continue On-Boarding.")
End If

Where is NewMaxS3ID dimmed? Or is it a field? If it is a field, what
is its data type?

 

[Guest]

The problem is not obvious, but there are a couple of issues:
txtS3ID is not qualified. If it is a text box on your form, it should be
referred to as
Me.txtS3ID. Also, is it enabled?
Have you tried a direct assignment to the text box? Is the variable really
necessary or are you just using space and time?
Me.txtS3ID = rst.Fields(I).Value
The variable newMaxNumber is not dimmed in the procedure. It allows an
assignment, so it must be dimmed somewhere or you are not using Option
Explicit and if you are not you should be.

And just a helpful hint on padding your number:
Rather than using a select statment to make it 4 long, this will do just as
well:

NewMaxS3ID = Format(NewMaxS3ID, "0000")

 

[Guest]

Great Question, Old Pro.

Dim'ing the variable resolved the issue. Another lesson learned the hard
way.

Thanks,
Rich