Copy data from calculated field to table (1 Viewer)

[access2010]

Using Access 2003, our Director would like to see data copied to our table, which she would like to then use

===
Click on the above Green Button to copy both fields and then close this form.
(Sometimes the equity would have been sold and “SharesOwned” would be Zero and “AverageShareCost” would be Zero.)

A = Copy = the number of shares (net) owned to the field => Net_Share_Cost
B = Copy = the Average Share Cost => Net_Share_Quantity

Close this form

===
We know that what we require is not suggested, but this information is requested by our Director.

Thank you,
Maria

 

[theDBguy]

Hi Maria. Since we are not familiar with your database, the above instruction is not clear to me. Are you able to post a sample db with test data? Where is the calculation coming from?

 

[June7]

Which record should this data be copied into? All of them?

What your director wants doesn't make sense.

TransactionNumber is a text field. Why do some of these values have leading zeros and some don't? And why do some have a comma?

 

[The_Doc_Man]

The general rule is that if something is invariant, you make a query that does any needed computation. However, if there is a variability in one or more factors contributing to the result, you store the factors.

Therefore, given your description of variability, the general idea would be that you would store your two computed fields plus a date or date/time in an appropriately structured table. Let me say this: If your director is a real whiz s/he will understand that you need to keep your data "pure" by having a separate table. If your director is a real doink then you might do better to say that you will store the stuff in a special place set aside just to make this happen correctly. Whatever you do, don't allow a "doink" to assert technical ideas that will not be consistent with good database design.

As to how you might do it, I'm going to make a leap of faith here. I'm going to assume that the record sources that feed this form are aggregate records OR the form computes something using domain aggregate functions, one of the two. But there probably is no place in the form's record source for the info you want. So...

1. Build a table with date/time and fields for the things your boss wants stored.

2. Build a command button on the form. Let the button wizard build code underneath the button-click event to do a Refresh.

3. Go into the event routine and remove the .Refresh operation. Put in some VBA to dynamically build an INSERT INTO table (fields) VALUES (values) type of statement. Execute it. Problem solved.

The event code MIGHT resemble something like this:

Private Sub StoreStuff_Click()

Dim strSQL As String

strSQL = "INSERT INTO yournewtablename (thedatefield, thenetquantityfield,  thenetcostfield) "
strSQL  = strSQL & "VALUES (#" & FormatDateTime( Now(), vbGeneralDate ) & "#,'"
strSQL = strSQL & CStr( Me.Net_Share_Quantity ) & "','"
strSQL = strSQL & CStr( Me.Net_ShareCost) & "') ;"
DoCmd.RunSQL strSQL

End Sub

It might resemble that. You would have to diddle with it a bit. Put in the correct table and field names and control names and such.

 

[access2010]

theDBguy
Thank you for your note.
Everything in the database at our charity works.
The only additional items that we would like are to click the Green Picture which would then;
A = copy the contents of “SharesOwned” to the field => Net_Share_Cost
B = copy the contents of “AverageShareCost “ to the field => Net_Share_Quantity
C= And then Close this form

Your assistance will be appreciated.
Nicole

 

[access2010]

June7
Thank you for your comments.
The only items that our Director needs are noted below.
A = copy the contents of “SharesOwned” to the field => Net_Share_Cost
B = copy the contents of “AverageShareCost “ to the field => Net_Share_Quantity
C= And then Close this form

I am sorry for the complications, the Table attached to this database has all the field that we use in different tables and forms, but the only items we now require are noted above

Nicole

 

[June7]

Did not answer questions in post 3.

 

[access2010]

The_Doc_Man

Thank you for your suggestions.
We had previously manually copied the Data into our table which would show the results in our continuous form.

I think that fewer errors would be made if a click of the Green Icon programmatically did our required copying.

A better explanation is available by looking at the screen shot, attached.

Nicole

 

[access2010]

June7
Thank you for your comments.
A screen shot of what we are now doing and what we would like has been uploaded.

Nicole

 

[June7]

That does not clarify where you want these two values stored. You show only 1 table in the db. Which record should these values be inserted into - all of them?

Code can run an UPDATE action SQL to populate any or all records.

Also appear to have the numbers reversed. 250 shares is in Cost and $22 is in Quantity.

 

[mike60smart]

Hi

What you are asking is not possible to achieve

Your Form is a Continuous Subform which has a Number of Records

In your Form Footer you have calculated Controls as follows :-

1. Unbound Control with this :- =Sum([TransactionQuantity])
2. Unbound Control with This:- =Sum(Nz([TransactionQuantity])*Nz([TransactionPrice])+Nz([TransactionComm]))/Sum([TransactionQuantity])

You want the following to happen:

1. Total of All of the Records on the Subform to be placed in a Bound Control named Net_Share_Quantity
2. Total of all of the records on the Subform to be placed in a Bound Control named Net_Share_Cost

What you actually need to do is have a Calculation for EACH Record on the Subform which gives you the AverageShareCost

You can then Sum this Control and then Divide by the Total Transaction Quantity

 

[access2010]

June7
Thank you for your comments, which have been adjusted in the new Data Base just uploaded, (Updated_Copy_Cost_&_Quantity.Mdeb)

Your assistance in helping us copy the two fields and then close the form will be appreciated.

Thank you.
Paul

 

[access2010]

mike60smart
Thank you for your comments.
The data on our form is working the way we require and our director would like to add,
A = copy the contents of “SharesOwned” to the field => Net_Share_Cost
B = copy the contents of “AverageShareCost “ to the field => Net_Share_Quantity
C= And then Close this form

Thank you.
Paul

 

[June7]

There is no db uploaded in post 12.

None of you have given a direct answer to questions in post 3. The image does not clarify. We already know you want to save these calculated results to fields. Now if you want the same data in every record just tell us "Yes, we want those values saved to every record." Assuming that Yes is your answer, run an UPDATE action SQL as described in post 4. However, since your fields are number not text, there should be no need for CStr() function nor apostrophe delimiters.

The code can be in a button click event but then that relies on user to remember to click. What if they forget? Might be better to automate with some event that does not depend on user, such as form Close.

 

[access2010]

June7
Thank you for your note, I thought that the database had been uploaded and I will upload it now.
YES = We want to save these calculated results to fields.
YES = We would like to run an UPDATE action
YES = We would like the code to be in a button click event
YES = We would like to automate with some event that does not depend on user, such as form Close (most of the database users are volunteers).

Thank you, June7, as you understand what our Director would like to occur
Maria

 

[mike60smart]

Hi Maria
In the attached I applied an After update to the Transaction Comm Control.

These calculations can be applied as you enter each Individual Record.

You will not be able to apply the Totals from your Form Footer to All Records in your Subform

View attachment Updated_Copy_Cost_&_Quantity.zip

 

[access2010]

Thank you all for your assistances.

We have created this Edited Database “K_Updated_Copy_Cost_&_Quantity.Mdb” so that our Director could have her needed data for Monday Afternoon.

A = we have added the button “K” which manually copies the data from “SharesOwned” into memory and then is manually placed into the field “Net_Share_Quantity”.

B = we have added the button “K” which manually copies the data from “AverageShareCost” into memory and then is manually placed into the field “Net_Share_Cost”.

What we would like assistance with is to Click the GREEN button and perform the functions “A” and “B” and then close the form.

I/We do appreciate all of your assistance.
Maria

 

[access2010]

mike60smart
Thank you for your Database, but for our system to work, we need to have the copied data on the Main Form, so that the information can be used on our other forms.

Maria

 

[access2010]

June7
Thank you for your assistance and suggestions and I can smell success around the corner.

Please see my note #13.
Maria

 

[mike60smart]

Hi

You said "we need to have the copied data on the Main Form"

Seeing as you only supplied 1 Form this would be very difficult to achieve.

If you supply the database with the related Main and Subform then this is much easier to achieve.