Pass Data from Calculated Field to Table Field

C

Chromedome

Registered User.

Local time

Today, 16:29

Joined

Jan 10, 2008

Messages

14

• Jan 30, 2008

• #1

Hi Everyone
Bit of a 'newbie' to Access 2007, but getting there.
Can anyone point me in the right direction:

I have a calculated field on the Main Form which I need to pass to a Table Field on the SAME form when the Calculated Field is updated.

I can get it to pass the data with the [On_Click] event but can't get it to pass it automatically with the [After_Update] or [On_Change] event

Can anyone point me in the right direction?

Thanks in advance

CD

 

KenHigg

Registered User

Local time

Today, 11:29

Joined

Jun 9, 2004

Messages

13,327

• Jan 30, 2008

• #2

OnExit or OnDirty

???

 

C

Chromedome

Registered User.

Local time

Today, 16:29

Joined

Jan 10, 2008

Messages

14

• Jan 30, 2008

• #3

Thanks KenHigg for your speedy reply
The On_Exit event works, but I have to select the field and then move off it (Exit) for the event to trigger (which is what it is designed to do).

The Calculated field updates instantly when I enter data in the subform and I wanted to update the Table Field in the same way. Had it working OK in ObjectPal but it's time to move on. Is there a way to automate it without user intervention?

Don't quite understand the 'Dirty' event.
The bit of simple code I'm using is as follows:

Private Sub TotalJobList_Exit(Cancel As Integer)
Dim TJL As Control
Dim TJ As Control

Set TJ = Me.TotalJob
Set TJL = Me.TotalJobList

TJ = TJL

MsgBox ("TJ Value " & TJ)
End Sub​

hope you can help

Regards

CD

 

C

Chromedome

Registered User.

Local time

Today, 16:29

Joined

Jan 10, 2008

Messages

14

• Jan 30, 2008

• #4

Hi again Ken

Looks like the Dirty property could do it if I understand it. While the Field is updated, but not saved, it is in its 'Dirty' state?

If this is correct then if Dirty is 'True' should I be able to copy the unsaved (but updated) Calculated Field data to the Table Field?

Could you point me in the direction of the basic code I would need to effect this?

Look forward to your reply

Sorry to be a 'nag'

Mark

 

KenHigg

Registered User

Local time

Today, 11:29

Joined

Jun 9, 2004

Messages

13,327

• Jan 30, 2008

• #5

You are correct how OnDirty works.

I'm not real clear on what you are trying to do but I like to add that if at all possible you shouldn't be storing the data in a second place but get it as you need it from the original.

 

C

Chromedome

Registered User.

Local time

Today, 16:29

Joined

Jan 10, 2008

Messages

14

• Jan 30, 2008

• #6

Hi Ken
Basically it is pretty simple (but if it can be simpler...):
I have a [JobsMain] Form with a [LineItem] SubForm.
On the JobsMain Form is a calculated field [TotalJobList] which collects the data from a calculated field in the Footer (notVisible) of the LineItem SubForm [=sum(

• *[Qty])]
  
  In the JobsMain Table I have a currency Field [TotalJob] in which I need to store the calculated data from [TotalJobList] for use in the [ProjectsMain] Form.
  
  Thus: in the [ProjectsMain] Form we can see the costs for all the individual Jobs within the Project plus see the total cost of the whole Project to date.
  
  Hope this makes sense and look forward to your reply
  
  Mark

 

KenHigg

Registered User

Local time

Today, 11:29

Joined

Jun 9, 2004

Messages

13,327

• Jan 30, 2008

• #7

You shouldn't be storing this. You should get it on the fly whenever you need it with either a domain function (dsum()?), a custome function a maybe a sql statement...

The main problem is that you'll have the value twice that could get out of sinc...