Copy field 1 to field 2 problem

[access2010]

Could we please have a suggestion for the best way to copy a record from one field in a MsAccess 2003 table to another field in the same table?

We have a calculated field aclled = AverageShareCost
with the following calculation
=[Bought_Drip_Dollars]/[Bought_Drip_Quantity]

The value of this field can change, but its current value when the form is opened is to be copied to
Net_Share_Cost

We have not had any success after trying many expressions and I would appreciate your suggestions.

The two fields above are needed for different purposes.

Thank you Linda

 

[The_Doc_Man]

Your choice of nomenclature is a bit confusing. Did you mean "a field from one record" rather than "a record from one field"?

Your statement of when to show this value is also confusing. You would not normally do this when the form is opened, but rather when it loads data from whatever is the current record.

You say you have had no success with many different expressions. The expression you need (if I am still guessing correctly) is

[Net_Share_Cost] = [Bought_Drip_Dollars]/[Bought_Drip_Quantity]

If this isn't working, it isn't because of the formula but rather is probably due to where you PUT that formula. I know that your account is a group account with several people using it. perhaps even some new folks rotating in now and then. I have to ask if you understand the concept of an Event Handler or Event Routine. If you do not, you need (desperately) to learn, because otherwise you will have a hard time getting this right.

You also need to clarify WHEN you want this to occur, because you can cause things to occur when a form Loads (for the first time) vs. when new data loads (such as when you move to a different record on the same form). They are two different events.

 

[access2010]

Your choice of nomenclature is a bit confusing. Did you mean "a field from one record" rather than "a record from one field"?

Your statement of when to show this value is also confusing. You would not normally do this when the form is opened, but rather when it loads data from whatever is the current record.

You say you have had no success with many different expressions. The expression you need (if I am still guessing correctly) is

[Net_Share_Cost] = [Bought_Drip_Dollars]/[Bought_Drip_Quantity]

If this isn't working, it isn't because of the formula but rather is probably due to where you PUT that formula. I know that your account is a group account with several people using it. perhaps even some new folks rotating in now and then. I have to ask if you understand the concept of an Event Handler or Event Routine. If you do not, you need (desperately) to learn, because otherwise you will have a hard time getting this right.

You also need to clarify WHEN you want this to occur, because you can cause things to occur when a form Loads (for the first time) vs. when new data loads (such as when you move to a different record on the same form). They are two different events.

Thank you The_Doc_Man for your suggestion. I finally have finally found out what the problem is, one value is in the SUB FORM and the other value is in the FORM. Could you please suggest a way of copying the SUB FORM's value to the FORM?

Nicole

 

[CJ_London]

You have asked this question many times

You have not provided form names so I refer you again to this link

Forms: Refer to Form and Subform properties and controls

 

[The_Doc_Man]

I will answer this two different ways because code could be running either in the parent form or the child form. There will be two forms and two controls. One control is on the parent form. The other control is on the child form (subform).

IF your code is running in the parent form:

Me.[name-of-control-on-parent-form] = Me.name-of-subform-control-on-parent-form.Form.name-of-control-on-subform

If your code is running in the child form (subform) at the time:

Me.Parent.name-of-control-on-parent = me.[name-of-control-on-subform]

As to where you put this, there is a sort of a problem. Remember that subforms activate before parent forms. IF you run the code in the subform and the Form_Current routine is doing this action, the Form_Current routine has not run yet on the parent form. If it does anything to that target control, it could possibly overwrite whatever the sub-form code did.

 

[Pat Hartman]

Could you please suggest a way of copying the SUB FORM's value to the FORM?

There is one main form record but many subform records. It makes no sense to copy a value from the subform to the mainform. Exactly which subform record has the value you wish to copy? On the other hand, referencing a main form field from a subform makes sense and as Doc suggested, you would use Me.Parent.ControlName to reference the control you want to use.