Run-Time Error

G

Guest

I'm trying to update a element in my table with a calculation:-

Me.Planned_DailySupplementAllowance=[Destination Subform].Form!DSASubTotal
Me.Planned_TerminalCost = ([Destination Subform].Form!IntA - 1) *
DLookup("[TerminalRate]", "System", "[RecordID]=1")

However, I receive the following error msg:-

Run-Time '-2147352567 (80020009)

You can't assign a value to this object.

If you have another method, please enlighten me.

Any guidance would be appreciated.
 
G

Guest

Are you trying to assign the calculated value to a field that is bound to a
record source field?

Clint
 
G

Guest

allow me to 'continue' this thread....

i AM trying to assign the calculated value to the a field that is bound to a
record source field and my code is

Me.FollowupDate = "SELECT TOP 1 [Screening Log].MR_Number,
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS Expr1, [Screening Log].[IRB Number]" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number WHERE ((([Screening
Log].MR_Number)=[Me].[MR_Number]) AND (([Screening Log].[IRB
Number])=[Me].[IRB Number]));"

cherman said:
Are you trying to assign the calculated value to a field that is bound to a
record source field?

Clint

Ken said:
I'm trying to update a element in my table with a calculation:-

Me.Planned_DailySupplementAllowance=[Destination Subform].Form!DSASubTotal
Me.Planned_TerminalCost = ([Destination Subform].Form!IntA - 1) *
DLookup("[TerminalRate]", "System", "[RecordID]=1")

However, I receive the following error msg:-

Run-Time '-2147352567 (80020009)

You can't assign a value to this object.

If you have another method, please enlighten me.

Any guidance would be appreciated.
Click to expand...
Click to expand...
 
G

Guest

Hi Ted.

I assume you are asking if this will work? You cannot assign a dynamic
source to a field that is bound (in code like you are showing) and, as far as
I know, you cannot assign a SQL statement directly to a field.

However, you can unbind the field and then assign the SQL statement to a
variable in code and then assign the variable to the field.

HTH,
Clint


Ted said:
allow me to 'continue' this thread....

i AM trying to assign the calculated value to the a field that is bound to a
record source field and my code is

Me.FollowupDate = "SELECT TOP 1 [Screening Log].MR_Number,
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS Expr1, [Screening Log].[IRB Number]" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number WHERE ((([Screening
Log].MR_Number)=[Me].[MR_Number]) AND (([Screening Log].[IRB
Number])=[Me].[IRB Number]));"

cherman said:
Are you trying to assign the calculated value to a field that is bound to a
record source field?

Clint

Ken said:
I'm trying to update a element in my table with a calculation:-

Me.Planned_DailySupplementAllowance=[Destination Subform].Form!DSASubTotal
Me.Planned_TerminalCost = ([Destination Subform].Form!IntA - 1) *
DLookup("[TerminalRate]", "System", "[RecordID]=1")

However, I receive the following error msg:-

Run-Time '-2147352567 (80020009)

You can't assign a value to this object.

If you have another method, please enlighten me.

Any guidance would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi clint,

the proper code i should have submitted (which is still ending up with that
annoying message) is as follows:

Option Explicit

Private Sub Form_Current()
Me.FollowupDate.Value = "SELECT TOP 1
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS FollowupDate" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number" _
& "WHERE ((([Screening Log].[IRB Number])=[Me].[IRB Number]) AND
(([Screening Log].MR_Number)=[Me].[MR_Number]));"
End Sub

it is trying to solve for a follow-up date when a patient is supposed to be
seen at a clinic using the values of the above parameters on a form called
'Screening Log'.

i don't grasp what it would take to implement your approach. can you expand
on it.

regards,

-ted

cherman said:
Hi Ted.

I assume you are asking if this will work? You cannot assign a dynamic
source to a field that is bound (in code like you are showing) and, as far as
I know, you cannot assign a SQL statement directly to a field.

However, you can unbind the field and then assign the SQL statement to a
variable in code and then assign the variable to the field.

HTH,
Clint


Ted said:
allow me to 'continue' this thread....

i AM trying to assign the calculated value to the a field that is bound to a
record source field and my code is

Me.FollowupDate = "SELECT TOP 1 [Screening Log].MR_Number,
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS Expr1, [Screening Log].[IRB Number]" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number WHERE ((([Screening
Log].MR_Number)=[Me].[MR_Number]) AND (([Screening Log].[IRB
Number])=[Me].[IRB Number]));"

cherman said:
Are you trying to assign the calculated value to a field that is bound to a
record source field?

Clint

:

I'm trying to update a element in my table with a calculation:-

Me.Planned_DailySupplementAllowance=[Destination Subform].Form!DSASubTotal
Me.Planned_TerminalCost = ([Destination Subform].Form!IntA - 1) *
DLookup("[TerminalRate]", "System", "[RecordID]=1")

However, I receive the following error msg:-

Run-Time '-2147352567 (80020009)

You can't assign a value to this object.

If you have another method, please enlighten me.

Any guidance would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi clint,

i've cleaned this up a bit and encapsulated what i want to happen in this
compact bit of vba:

Private Sub Form_Current()
Dim Future_Visit As Date
If Not IsNull(Me.IRB_) Then Me.Future_Visit.Value = DLookup("FollowUp",
"Query4")
End Sub

i suspect this next item may not properly fall on the same thread as this
but i'd like to ask how would one cause any changes in a control on the form
called 'OnStudy Date' (which is used by Query4 to compute 'Future_Visit') to
refresh/modify/update the value of 'Future_Visit' while the user is still on
the same record. i can safely say that after changing 'OnStudy' and using the
scroll wheel to leave and then return to the changed record that the value of
Future_Visit got changed, but i thought it would be kind of kool if the value
of Future_Visit could change without moving to and fro. is this do-able?

i read a few responses to other postings which seemed to suggest that the
BeforeUpdate event is the place to place this code but (so far) it's not
given me the results i'm looking for; the code i'm focusing on is just below:

Private Sub On_Study_Date_BeforeUpdate(Cancel As Integer)
Dim Future_Visit As Date
If Me.Dirty Then
If Not IsNull(Me.IRB_) Then
Me.Future_Visit.Value = DLookup("FollowUp", "Query4")
End If
End If
End Sub

thoughts?



cherman said:
Hi Ted.

I assume you are asking if this will work? You cannot assign a dynamic
source to a field that is bound (in code like you are showing) and, as far as
I know, you cannot assign a SQL statement directly to a field.

However, you can unbind the field and then assign the SQL statement to a
variable in code and then assign the variable to the field.

HTH,
Clint


Ted said:
allow me to 'continue' this thread....

i AM trying to assign the calculated value to the a field that is bound to a
record source field and my code is

Me.FollowupDate = "SELECT TOP 1 [Screening Log].MR_Number,
IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=7,DateAdd('m',[Months],[RegisteredDate])-1,IIf(DatePart('w',DateAdd('m',[Months],[RegisteredDate]))=1,DateAdd('m',[Months],[RegisteredDate])+1,DateAdd('m',[Months],[RegisteredDate]))) AS Expr1, [Screening Log].[IRB Number]" _
& "FROM [Screening Log] INNER JOIN MasterTable ON [Screening Log].[IRB
Number] = MasterTable.IRB_Number WHERE ((([Screening
Log].MR_Number)=[Me].[MR_Number]) AND (([Screening Log].[IRB
Number])=[Me].[IRB Number]));"

cherman said:
Are you trying to assign the calculated value to a field that is bound to a
record source field?

Clint

:

I'm trying to update a element in my table with a calculation:-

Me.Planned_DailySupplementAllowance=[Destination Subform].Form!DSASubTotal
Me.Planned_TerminalCost = ([Destination Subform].Form!IntA - 1) *
DLookup("[TerminalRate]", "System", "[RecordID]=1")

However, I receive the following error msg:-

Run-Time '-2147352567 (80020009)

You can't assign a value to this object.

If you have another method, please enlighten me.

Any guidance would be appreciated.
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

Run time error 1
Error with no value 2
Runtime Error 4
Delete Contents of Date Field 2
Run-time error 2147352567 (80020009) 0
Error ==> You can't assign value to this object 1
Changing Form check boxes on open 4
HELP: Run-time error '-2147352567 (80020009): 3

Top