MS ACCESS CODE PROBLEM - Can't assign a value to this object

I am getting the above error.
All I am trying to do is to assign field [CalcTotalChargeBacks] on a form  to a control bound to a table [TotalChargebacks]. But I get this error "Can't assign a value to this object" Both controls are on the same main form. I have been at this for hours. What the hell is up with this? Is should be simple as pie. I am new too MS Access but have been progamming for years, and I cannot figure this out. HELP !
Private Sub Form_Close()
Forms![Chargeback Maint]![TotalChargebacks] = Forms![Chargeback Maint]![CalcTotalChargeBacks]
End Sub
nussedogAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
The problem is that the form close event is too late to do this (assuming that this code is on the form [Chargeback Maint]).

0
mbizupCommented:
Also in the interest of keeping your database normalized, it is a good idea to calculate and display totals as needed --- but you should avoid storing calculated values in your tables.

0
MikeTooleCommented:
I presume that the Form_Close routine is in a different form to [Chargeback Maint]?

What is the Control Source of Forms![Chargeback Maint]![TotalChargebacks] ?

The syntax you've used to refer to the form makes use of the fact that Controls is the default collection. Try being more specific and see if it changes the error:

Forms![Chargeback Maint].Controls("TotalChargebacks") = ... etc  
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

nussedogAuthor Commented:
thanks for the input. Whether I agree or disagree regarding storing calculated values in the table, let me rephrase the question as I have been working on this for 3 hours now.

If for what ever reason I choose to store a calculated value in a table. What is the best way to do it. Please provide sample code, and on what event do I attach the code?

thanks
0
mbizupCommented:
Need some more details.   Am  I correct in my assumption that the controls are on the same form as the code?  Or are you referring to controls on a seperate form?
0
nussedogAuthor Commented:
same form
0
Mike EghtebasDatabase and Application DeveloperCommented:
Private Sub Form_Close()
Forms![Chargeback Maint].SetFocus
Forms![Chargeback Maint]![TotalChargebacks].SetFocus
Forms![Chargeback Maint]![TotalChargebacks] = Forms![Chargeback Maint]![CalcTotalChargeBacks]
End Sub
0
Mike EghtebasDatabase and Application DeveloperCommented:
If same form:

Private Sub Form_Close()

meTotalChargebacks].SetFocus
me![TotalChargebacks] = Forms![Chargeback Maint]![CalcTotalChargeBacks]

End Sub
0
Mike EghtebasDatabase and Application DeveloperCommented:
or

Private Sub Form_Close()
    meTotalChargebacks].SetFocus
    me![TotalChargebacks] = me![CalcTotalChargeBacks]
End Sub

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
sorry for the typo


Private Sub Form_Close()
    me![TotalChargebacks].SetFocus
    me![TotalChargebacks] = me![CalcTotalChargeBacks]
End Sub
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd do this in the Form_BeforeUpdate event, which will fire anytime the data is saved.

<Whether I agree or disagree regarding storing calculated values in the table>
Whether you agree with it or not, it is a bad idea to store calculated values in a table. Root values can change, and if the calced value isn't changed along with them your data will be skewed. The ONLY time you'd want to store calced values is if the db is being flatfiled for reporting/archival purposes (which would mean that there would never be data added to it), or if you need to take a "snapshot" (which would be, more or less, archival).
0
nussedogAuthor Commented:
Here is a snapshot of the form and the code. I believe the problem may be that excluding the KEY field, there are no active fields on the main form, all of them are calculated, summarized values from the subforms. So when you update an existing record, you never really seem to hit the main form which is why the BeforeUpdate never seems to trigger.
Chargeback-Form.doc
0
nussedogAuthor Commented:
LSMConsulting
To continue the discussion, I am would agree if the tabled data being used in a calculation had the potential (within the users app) of being updated from multiple sources. But if the designer is careful and does not provide the end user with more than one update process then storing calculated fields makes reporting and dumps to excel etc. much much easier.  I have done this for years and never run into a problem except of my own making, i.e. going under the user layer and altering data.

But all that being said, your approach is best, particularly for the novice DB/APP designer.
0
Mike EghtebasDatabase and Application DeveloperCommented:
nussedog,

did you try the last version?  TotalChargebacks has to have focus before you assign a vlue to it.

Private Sub Form_Close()
    me![TotalChargebacks].SetFocus
    me![TotalChargebacks] = me![CalcTotalChargeBacks]
End Sub

Mike
0
nussedogAuthor Commented:
eghtebas,

yes, and I got this message:
"Object doesn't support this property or method"
0
Mike EghtebasDatabase and Application DeveloperCommented:
you mean at

me![TotalChargebacks].SetFocus

Strange.

If you are using a button to close this form, then use it under that button this because by the time you are at Private Sub Form_Close() event, Access doen't want to set focus. I understand this rxcludes X on the top-right of the form to close it bt try is until we could get to the buttom of it.

Mike
0
nussedogAuthor Commented:
i really need to do this as a beforeupdate because i am allowing user to move from record to record without closing form.
0
Mike EghtebasDatabase and Application DeveloperCommented:
Ok then put it in that event.

Also, I wan't able to see your doc file (have to reinstall my MSWord), Just in case there are some subforms involved, the need to get the focus first. like

Forms![MainForm].SetFocus
Forms![MainForm]![SubForm].SetFocus
Forms![MainForm]![SubForm]![txtBox].SetFocus
Forms![MainForm]![SubForm].Form.[txtBox]= Forms![Chargeback Maint]![CalcTotalChargeBacks]


Mike
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
We'll have to agree to disagree ... I just can't see the need for storing calculated values in a live app. To me, it's a sign of "lazy" programming ... not that there's anything wrong with that, but it has it's place and, when dealing with data, that's not one of them. But it's your data.

What is the .ControlSource of your txtBox? If it's a calculated field, or if you're doing calcs in that field, then you won't be able to assign a value to it. For example, if your .ControlSource is =Me.Field1 + Me.Field2 then you can't assign a value directly to txtBox.
0
nussedogAuthor Commented:
LSMConsulting
Well, I guess I see it as more concise and time saving approach rather than lazy. Had you thought about the fact that if totals etc are stored within records that
a) it provides quicker less time consuming access to the data, again particularly if there are any number of data exports.
b) if precludes other report writers or programmers from getting the calculation wrong. Once the data is stored it only need be retrieved.
c) it can greatly reduce code and code snipets which may need to be inserted to achieve the calculation. Patricularly in the SQL world that deman stored procedures. Often these types of contstructs are not 'reusable' and are coded over and over as a function of poor design
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> a) it provides quicker less time consuming access to the data

Generally, it is a good idea to view the data tables via some query where you can have an alias fields Total: A + B. Using this practice it doesn't make any significant difference in ease of accessing data.

In addition, you don't have to worry if Total (as a stored field in a table) is correct because someone could alter either of the fields (A and/or B).

 re:> Once the data is stored it only need be retrieved.

Also fields A and/or B could be revised making the sored value useless.

re:> c) it can greatly reduce code and code snipets which may need to be inserted to

If there is some comlicated calculation involved, the bese way is using a function call like

Result:fnResult([A],[B])

Amongst the EE experts, you will not find a single person to say storing a calculated value in a table is a sound practice.

========
Changing the subject to the original question, Many times I was frsutrated why I couldn't assign a value to a text box (similar to your question). After some serach, I learned the solution was to transfer to focus to the text box first.

According to your last response, you mentioned:

CalcTotalChargeBacks.setfocus

causing an error. The must be some other problem in the application if we cannot give to a focus to a control unless it is disabled. Is your control disabled?

Regards,

Mike
0
nussedogAuthor Commented:
firstly, the settings i have on the field are enabled=No ; locked=yes. And thanks for pursuing this for me. The field has to be locked to prohibit user from changing i.e. display only unless I manually redirect focus when user moves to that field. That all seemed like so much work. This whole thing should have been really easy.  MS Access......................  hmmmm...............

secondly continuing the debate. I am confounded about this concern with users altering data used in a stored calculated field. Let's take an example. Let's say that we have a simple order field and we want to store the extended total. Qty x Unit Price. The quantity is a no brainer. It will always be manually entered via the form. The Unit Price on the other hand could be based on a lookup, or manually entered/overridden or both. Either way changing the qty or unit price via the form causes no problems with data integrity. If the user changes the unit price in some part master table this would have no impact on the order itself as it has already been entered. You would not have the order automatically change based on the change to the unit price in a supporting table. You alrealdy have the agreement with the customer.
This is just a simple example, but my point is that I find it silly that you would design an application where the resulting calculation would be dynamic which is implied by your arguement. Once the calculation is made it is static unless changed by the user via that same form. To design otherwise doesn't make sense to me.
Yes ???
0
nussedogAuthor Commented:
typo. I wrote "This is just a simple example, but my point is that I find it silly that you would design an application where the resulting calculation would be dynamic ......."

Meant to write" This is just a simple example, but my point is that if find it silly that you would design an application where the resulting calculation would dynamically be affected by data in static tables...."
0
Mike EghtebasDatabase and Application DeveloperCommented:
re:> irstly, the settings i have on the field are enabled=No ; locked=yes.

Then try:

    me![TotalChargebacks].Enabled = True
    me![TotalChargebacks].SetFocus
    me![TotalChargebacks] = me![CalcTotalChargeBacks]
    me![TotalChargebacks].Enabled = False

It will make enable it for a split second and disable it right away. This should take care of the proplrm.
===================
re:> thanks for pursuing this for me.

It wasn't very much fun I was having the same problem. I would like someelse dosen't

brb

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
re:> If the user changes the unit price in some part master table this would have no impact on the order itself

Method 1: Maintaining calculated value in the data table

Qty      Unit Price               Total
------    -------------             --------
 4            $5                         $20

Now sombody changes 4 to 3, we will have:

Qty      Unit Price               Total
------    -------------             --------
 3            $5                         $20   <---- this should read $15 but is isn't
                                                              because it is stored as a calulated field

Method 2: Not maintaining calculated value in the data table

Qty      Unit Price              
------    -------------          
 4            $5                        

In a form or query it will read:

Qty      Unit Price               Total
------    -------------             --------
 4            $5                         $20   Where  Total: Qty + [Unit Price]

Now sombody changes 4 to 3, we will have:

Qty      Unit Price               Total
------    -------------             --------
 3            $5                         $15   Where  Total: Qty + [Unit Price]
                                                             
It will still calculate the correct value without any additional effort.

Of course, there are times when we need to freeze some data using a maketable query for a besiness reason. These kind of exception has to be handled as it is needed.

Good Luck,

Mike
0
nussedogAuthor Commented:
thanks again. I enjoyed our little discussion. unfortunately this is not the place to continue the thread as we are still apart on this. I think with a little time i could bring you over to the dark side.

I will accept your answer as solution as it best fits the circumstances.

regards,

rich
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.