Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-12-2019, 11:10 PM   #1
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 402
Thanks: 204
Thanked 1 Time in 1 Post
access2010 is on a distinguished road
Copy field and close form

Using Ms Access 2003 we would like to copy the calculated data in the field
"AverageShareCost" to the the field "ShareCost_Average"
and then close the form, using the Green Arrow Bmp Button

Your suggestion, will be appreciated.

Thank you,
Nancy
Attached Files
File Type: mdb Investments_Purchases_SalesF_Copy_Average_Cost.mdb (592.0 KB, 20 views)

access2010 is offline   Reply With Quote
Old 01-12-2019, 11:55 PM   #2
bob fitz
Newly Registered User
 
Join Date: May 2011
Location: Essex England
Posts: 3,458
Thanks: 30
Thanked 670 Times in 654 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Copy field and close form

Quote:
Originally Posted by access2010 View Post

Your suggestion, will be appreciated.

Thank you,
Nancy
Not usually a good idea to save calculated data. Better to calculate it whenever and wherever it is required.
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Old 01-13-2019, 12:03 PM   #3
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 402
Thanks: 204
Thanked 1 Time in 1 Post
access2010 is on a distinguished road
Re: Copy field and close form

bob fitz
Thank you for your note, we tried creating the calculation as we need it without any success.
We need the "ShareCost_Average" cost, when we create our CVS file.

Do you have a suggestion as to how we can create this value weekly other then copying the value?

Thank you.
Paul

access2010 is offline   Reply With Quote
Old 01-13-2019, 12:40 PM   #4
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Copy field and close form

You can add it to your query as a function

Code:
Public Function GetShareAvg() As Currency
  Dim rs As DAO.Recordset
  Dim TranPrice As Currency
  Dim TranQuan As Long
  TranPrice = DSum("[TransactionQuantity]*[TransactionPrice]+[TransactionComm]", "QryInvestments_Purchases_Sales")
  TranQuan = DSum("[TransactionQuantity]", "QryInvestments_Purchases_Sales")
  If TranQuan <> 0 Then
    GetShareAvg = TranPrice / TranQuan
  End If
End Function
This works for the provided data, but it is using all the records. My guess is you want to do this by some group of records. I would have thought by portfolio, but there are 3 portfolios in your dataset and you use all of them. However, it can be done by group.

Now get Rid of the field in the table, and add the function to your query like
ShareCost_Avg:GetShareAvg()
MajP is online now   Reply With Quote
Old 01-14-2019, 11:40 PM   #5
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 402
Thanks: 204
Thanked 1 Time in 1 Post
access2010 is on a distinguished road
Re: Copy field and close form

MajP

Thank you for your suggestion which we have tried without success.

Can you please look at the similar code that we used in the past to copy a field and with your assistance, hopefully we can have the Copy Icon work and then save the record and close the form.

The Main form to open is "Investments_Purchases_SalesF"

Thank you,
Nicole
Attached Files
File Type: mdb Copy_Average_Equity_Cost.mdb (376.0 KB, 14 views)
access2010 is offline   Reply With Quote
Old 01-15-2019, 12:33 AM   #6
bob fitz
Newly Registered User
 
Join Date: May 2011
Location: Essex England
Posts: 3,458
Thanks: 30
Thanked 670 Times in 654 Posts
bob fitz has a spectacular aura about bob fitz has a spectacular aura about bob fitz has a spectacular aura about
Re: Copy field and close form

What is the name of the field to be copied and what is the name of the field that it is to be copied to and in what table will these fields be found?
__________________
Bob Fitzpatrick
Microsoft Community Contributor Award (2011)
If this helped, please click the scales at the bottom left of this posting.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Many thanks.


I have never failed. I have found a thousand ways that do not work!
bob fitz is offline   Reply With Quote
Old 01-15-2019, 04:33 AM   #7
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Copy field and close form

Quote:
Thank you for your suggestion which we have tried without success.

Can you please look at the similar code that we used in the past to copy a field and with your assistance, hopefully we can have the Copy Icon work and then save the record and close the form
The code I provided was tested and worked in the database.

See the last column of the query and the function in module Junk.
Attached Files
File Type: mdb Copy_Average_Equity_Cost_MajP.mdb (1.52 MB, 8 views)

MajP is online now   Reply With Quote
Old 01-15-2019, 09:22 AM   #8
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 402
Thanks: 204
Thanked 1 Time in 1 Post
access2010 is on a distinguished road
Re: Copy field and close form

bob fitz
Thank you for your note.

What is the name of the field to be copied = AverageShareCost
Table = this is a calculated field

what is the name of the field that it is to be copied to = AverageShareCost
Investments01_tbl

Thank you,
Paul
access2010 is offline   Reply With Quote
Old 01-15-2019, 09:36 AM   #9
access2010
Newly Registered User
 
Join Date: Dec 2009
Posts: 402
Thanks: 204
Thanked 1 Time in 1 Post
access2010 is on a distinguished road
Re: Copy field and close form

MajP

Thank you for your suggestion.
We must have entered your code incorrectly as we did not have success.
We previously used the Copy Icon that is on the form which we have uploaded and would like to adjust this copy and close command, if you can assist us.

Linda
access2010 is offline   Reply With Quote
Old 01-15-2019, 10:19 AM   #10
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Copy field and close form

Paul and Linda,
We can provide a solution to copy a calculated value into a table, but we are trying to steer you away from that. Calculated fields are not normally stored for many reason. Mainly the value is only as good as the instance you enter it. After that any changes in data will not be synchronized. For a financial database, I would find this very concerning. We are suggesting that you do not copy it into a field, but show the value when needed in a query you can then export the query if needed. I can provide the code, with the understanding this is not normally done this way.

Here are some simple queries that can show the data whenever you want. Any changes in the data will be reflected in the calculations

Code:
SELECT 
  Investments_Purchases_SalesT.Symbol_Stock, 
  Investments_Purchases_SalesT.TransactionQuantity, 
  Investments_Purchases_SalesT.TransactionPrice, 
  Investments_Purchases_SalesT.TransactionComm, 
  [TransactionQuantity] * [transactionPrice] + [transactioncomm] AS TransactionTotal, 
  [TransactionTotal] / [TransactionQuantity] *(
    [transactionquantity] / Abs([transactionquantity])
  ) AS AverageCost 
FROM 
  Investments_Purchases_SalesT 
ORDER BY 
  Investments_Purchases_SalesT.TransactionDate;
Produces

Code:
Symbol_Stock	TransactionQuantity	TransactionPrice	TransactionComm	TransactionTotal	AverageCost
VG	-600.00		$4.22		$160.00		($2,371.94)	-3.95323333333333
VG	400.00		$5.45		$9.99		$2,189.99	5.474975
VG	250.00		$5.70		$9.99		$1,434.97	5.73986
VG	800.00		$6.43		$125.15		$5,269.95	6.5874375
VG	-650.00		$6.31		$10.09		($4,088.16)	-6.28947692307692
VG	200.00		$41.39		$160.00		$8,438.00	42.19
VG	-150.00		$37.66		$160.00		($5,489.00)	-36.5933333333333
Then you can use that in an group by query

Code:
SELECT qrytransactioncost.symbol_stock, 
       SUM(qrytransactioncost.transactionquantity) AS SharesOwned, 
       SUM(qrytransactioncost.transactioncomm)     AS TotalCommission, 
       SUM(qrytransactioncost.transactiontotal)    AS TransactionBalance 
FROM   qrytransactioncost 
GROUP  BY qrytransactioncost.symbol_stock;
This would give you

Code:
Symbol_Stock	SharesOwned	TotalCommission	TransactionBalance
VG		250		$635.22		$5,383.81
Then you can use that for the shares avg

Code:
SELECT qrytransactiontotals.symbol_stock, 
       [transactionbalance] / [sharesowned] AS AvgShareCost 
FROM   qrytransactiontotals 
GROUP  BY qrytransactiontotals.symbol_stock, 
          [transactionbalance] / [sharesowned];
This gives you

Code:
Symbol_Stock	AvgShareCost
VG		21.53522
You can then link everything by Symbol_Stock like

Code:
SELECT qrytransactiontotals.symbol_stock, 
       qrytransactiontotals.sharesowned, 
       qrytransactiontotals.totalcommission, 
       qrytransactiontotals.transactionbalance, 
       qryavgcost.avgsharecost 
FROM   qrytransactiontotals 
       INNER JOIN qryavgcost 
               ON qrytransactiontotals.symbol_stock = qryavgcost.symbol_stock;
This would show

Code:

Code:
Symbol_Stock	SharesOwned	TotalCommission	TransactionBalance	AvgShareCost
VG		250		$635.22		$5,383.81		21.53522

MajP is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
save a database copy befor close aspen Modules & VBA 2 06-04-2013 02:29 AM
Advice to Copy Form Field to Sub-Form Field Automatically arw456 Forms 2 08-28-2008 12:26 AM
Form Event (Before and After Update) Copy Form/Field value to Another Table/Field CUIUS Forms 2 08-18-2006 04:55 AM
Copy DB / Close Form colette Modules & VBA 6 12-22-2005 08:19 AM
1st field in copy of form will not allow moving to next field russi General 1 06-27-2005 10:04 AM




All times are GMT -8. The time now is 08:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World