UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update Field In Form From Subform Using Event, Access 2007    
 
   
LeicsChris
post Jun 12 2018, 03:14 PM
Post#1



Posts: 119
Joined: 23-May 17



Hi All

Here in the UK we have "Attachment of Earnings" Court orders (AOE) where we must deduct money from a staffs wages and pay to certain creditors. One creditor could have many staff orders and one employee could have many orders.

For information I have an employee table and AOE Creditor table linked to a main "AOE" table with one Court order per record. In this record I have an "Opening Balance" field and "Current Balance" field.

I also have an "AOE Transactions" table with one wage deduction per record. Relevant fields in this table are "Date Deducted" and "Amount Deducted"

One of the forms I use is a main form "AOE Details" form, the recordset comes from a query pulling the information from the AOE table, Employee table and creditor table. In this form I have a subform with the recordset coming from a query using the "AOE transactions" table. The subform is a continuous form with one deduction per line.

In the footer of the subform I have a text box control that sums the "Amount Deducted" and shows the total deducted to date.

In the main form I have another text box control linked to the subform footer control to bring the total deducted from the subform to the main form.

In the main form the field "Opening Balance" does not change from when set up but the "Current Balance" should change with any change/addition to the subform.

I have a simple macro to update this field with the setvalue command ( [Opening balance] minus [total deducted] ) which I would like to trigger when the "Amount Deducted" field in the subform is either changed or a new record added.

I have tried the After Update, Before Update, On dirty and on change event but none update the field on the main form with the expected result. They appear to use a previous value and not the new value.

What am I doing wrong. What event should I use, and on the field event, subform event or elsewhere?

Thanks, I am scratching my head as to why I cant get it to work,

Chris
Go to the top of the page
 
mike60smart
post Jun 12 2018, 03:31 PM
Post#2


UtterAccess VIP
Posts: 12,610
Joined: 6-June 05
From: Dunbar,Scotland


Hi Chris

Any chance you can upload a zipped copy of the Db with some sample data?


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
LeicsChris
post Jun 12 2018, 03:35 PM
Post#3



Posts: 119
Joined: 23-May 17



Hi Mike

I'm not sure I can upload the DB as its part of a large operational system that also controls our rosters, accounts and operational telesales processes (we are a telesales company), and has maybe 35 tables and over 50k records.

If you can tell me an easy way to make a copy without the data and add a couple of relevant test data then I will happily upload a copy.

Regards

Chris
Go to the top of the page
 
mike60smart
post Jun 12 2018, 03:54 PM
Post#4


UtterAccess VIP
Posts: 12,610
Joined: 6-June 05
From: Dunbar,Scotland


Hi Chris

You can create a new Blank Db

Then carry out an Import from your operational Db.

Part of the Import process will allow you to specify Structure only

By the way has this Db been split into FE & BE ??

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
tina t
post Jun 12 2018, 03:55 PM
Post#5



Posts: 5,419
Joined: 11-November 10
From: SoCal, USA


well, your first mistake is storing a calculated value - a normalization no-no. you should store raw data only, and calculate the current balance at runtime to display in a form or report.

if you insist on storing a calculated value, i'd think using the subform's Form_AfterUpdate event would be the event to use.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 
LeicsChris
post Jun 12 2018, 04:39 PM
Post#6



Posts: 119
Joined: 23-May 17



Hi All

Attached File  Files.zip ( 1.99MB )Number of downloads: 2


Here is the DB

after linking the back end to the front end, select the user "Admin", and the password to logon is admin,

On main menu press accounts menu, and then AOE's

Once on the AOE's select Admin from the employee drop down and I have entered one court order.

Press View Deductions and the form in question comes up.

Thanks All

Chris
Go to the top of the page
 
LeicsChris
post Jun 12 2018, 04:45 PM
Post#7



Posts: 119
Joined: 23-May 17



Hi Tina

I dont normally store calculated values in a table, in fact this is my only one.

I did this because i am under the impression that if I use a totals query in another table that it makes the whole query non updatable.

I will try your suggestion and if that doesn't work will rethink the form.

Thanks

Chris
Go to the top of the page
 
LeicsChris
post Jun 12 2018, 05:45 PM
Post#8



Posts: 119
Joined: 23-May 17



Hi All

I have taken Tina's wisdom and calculated the current balance on the go and now dont store the calculated field.

I had a summary sheet prior to it and using a totals query, and adding the total to the form query allows me to calculate the balance also on the summary sheet, even though the transactions are in a separate table and numerous records.

Thanks again

Chris
Go to the top of the page
 
projecttoday
post Jun 12 2018, 09:38 PM
Post#9


UtterAccess VIP
Posts: 9,696
Joined: 10-February 04
From: South Charleston, WV


That's nice.

I took a look at your database. I managed to display the navigation pane once but now I can't do it. Holding down the shift key when opening it doesn't seem to work. How can I get to the navigation pane?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
LeicsChris
post Jun 13 2018, 01:36 AM
Post#10



Posts: 119
Joined: 23-May 17



Hi
that's how I get into the navigation pane, I hold down the shift key whilst opening it and keep it held down until it has fully opened

regards
chris
Go to the top of the page
 
projecttoday
post Jun 13 2018, 03:30 AM
Post#11


UtterAccess VIP
Posts: 9,696
Joined: 10-February 04
From: South Charleston, WV


So! I just didn't wait long enough. Thanks.

So your problem is solved now?

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
LeicsChris
post Jun 13 2018, 06:45 AM
Post#12



Posts: 119
Joined: 23-May 17



Hi
Yes my problem is now solved.

onwards and upwards to the next challenge.

Thanks All

Chris
Go to the top of the page
 
projecttoday
post Jun 13 2018, 07:54 AM
Post#13


UtterAccess VIP
Posts: 9,696
Joined: 10-February 04
From: South Charleston, WV


thumbup.gif

--------------------
Robert Crouser

My company's website
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 09:08 AM