By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

How to automatically save a VALUE into a table field?

P: 9
Hi

I am developing an On Leave app. in which trying to calculate employees days on leave.

I have created a Form consists of this fields (from a table):

(1). OnLeaveID (used as for the Relationship - Auto)
(2). StartDate (the starting on leave date - short date)
(3). EndDate (the last date on leave - short date)
(4). NumPublicDay (the number of public holidays)
(5). CalcDaysOnLeave (calculate days on leave applied)
(6). DaysOnleave (this field is used to save result/value no. (5) as its field data)
(7). BFOnLeave (no. of days brought forward from last year)
(8). EligOnLeave (no. of days eligible for that particular year)
(9). AvaiOnLeave (balance on leave available)

The idea is like this (as what I want, all must be auto-calc. in same single form):

i.(2),(3),(4),(7),(8) <<< manual data entry
ii.(5) = (3) - (2) - (4) <<< returns a value but I want this result to be kept in field (6) for next reference; report etc. I put something like this in the CalcDaysOnLeave's Control Source :
= [EndDate]-[StartDate]-[NumPublicDay]
iii. Then I want to use the (6) data for my 2nd calculation :
(9) = (7) + (8) - (6)

I think I didn't face any problem with the rest, except on how to keep a value (6) into the field data in the table.

Could you please assist me on this? I am really grateful with your solution. Thanks.
Feb 13 '12 #1

✓ answered by NeoPa

I won't do it all for you but I'll post some example code that you'll need to convert for your system (as you haven't shared any real details of yours yet) :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Control2_AfterUpdate()
  2.     Call CheckNUpdate
  3. End Sub
  4.  
  5. Private Sub Control3_AfterUpdate()
  6.     Call CheckNUpdate
  7. End Sub
  8.  
  9. Private Sub Control4_AfterUpdate()
  10.     Call CheckNUpdate
  11. End Sub
  12.  
  13. Private Sub CheckNUpdate()
  14.     With Me
  15.         .Control6 = Null
  16.         If IsNull(.Control2) Then Exit Sub
  17.         If IsNull(.Control3) Then Exit Sub
  18.         If IsNull(.Control4) Then Exit Sub
  19.         .Control6 = DateDiff("d", CDate(.Control2), CDate(.Control3)) - .Control4
  20.     End With
  21. End Sub

Share this Question
Share on Google+
16 Replies


NeoPa
Expert Mod 15k+
P: 31,492
Calculable fields should never be stored in a Normalised database. See Database Normalisation and Table Structures.

It seems like you need a fundamental rethink I'm afraid.
Feb 13 '12 #2

P: 9
Thanks for your respond.

I'm aware of the Normalisation method, however I need to have the value (5) kept in a field (6) so that I can always refer it for next calculation, report views etc.

In that case, is there any better solution for me to follow seems what I want is that just right after the user entered data (7), (8),(2), (3) and (4), result (5) will automatically generated. Then comes result (9).

Thanks.
Feb 13 '12 #3

Rabbit
Expert Mod 10K+
P: 12,366
It's simple substitution.
(9) = (7) + (8) - ((3) - (2) - (4))
Feb 13 '12 #4

NeoPa
Expert Mod 15k+
P: 31,492
If it's a calculation then the way to handle it is to calculate the result any time you need it. I guess that means (9) becomes :
Expand|Select|Wrap|Line Numbers
  1. (9) = (7) + (8) + (4) + (2) - (3)
Feb 13 '12 #5

P: 9
Its not the calculation that matters me. The idea is that I want to have the result/value (5) saved in a table. I'm expecting for VBA code or something that may be I can place on the AfterUpdate or anything...

Sorry for the incomplete info provided...
Feb 13 '12 #6

Rabbit
Expert Mod 10K+
P: 12,366
What we're saying it that there is no need to store it. And that is because 1) it is a waste of space and 2) there is no way to guarantee that the data would be correct.
Feb 13 '12 #7

P: 9
I got what you meant. May be I need to re-clarify the reason why I really need to store it. Its main purpose is to let admin know the record of how many days someone take leave. Normally, at the end of every month, the admin will re-check days on leave for every person. He/she needs to know for his/her report: how many days on leave brought forward from last year, how many days eligible for that particular year, total days on leave available for that year, what date start on leave, until when, how many days on leave (this is where data 6 plays its role), how many days on leave left.

I hope this might help you in assisting me with the way on how to solve the problem. Thanks.
Feb 13 '12 #8

P: 9
Just for your info, I've completed the app structure, login form, entry forms, reports etc except this no. (6) data that makes me stuck for days. I have only 2 days left to present to my Group Managing Director before attending a 3 days managerial course abroad.

I'm really sorry for this such a problematic thing I come up with. I truly have no clue on solving this matter.
Feb 13 '12 #9

NeoPa
Expert Mod 15k+
P: 31,492
If you're set on storing the value (Which I - and I suspect we - still advise against) then I would look at adding code into the AfterUpdate event procedures of all the controls on the form which are involved in the calculation. Each event procedure should call a single procedure which validates the data in all these controls and, if acceptable, calculates the result and applies it to the control bound to field #6.

Remember, though this answers your question, it doesn't mean that any of us have recommended this approach, as that could damage our reputation (quite reasonably).
Feb 13 '12 #10

P: 9
I'm really sorry for going against the norm but the app & co. practice requires me doing so.

Well then, what AfterUpdate coding should I use to apply the value from unbound textbox (5) into bound textbox (6)? I'm figuring on INSERT INTO but have no idea on the coding structure (I'm not a programmer or system developer/analyst, just trying to computerise whichever manuals in my co.) - let's say the tablename : TOnLeave.

I really appreciate for your patience & kindness in helping me to sort out the misery. Thanks.
Feb 13 '12 #11

NeoPa
Expert Mod 15k+
P: 31,492
I won't do it all for you but I'll post some example code that you'll need to convert for your system (as you haven't shared any real details of yours yet) :

Expand|Select|Wrap|Line Numbers
  1. Private Sub Control2_AfterUpdate()
  2.     Call CheckNUpdate
  3. End Sub
  4.  
  5. Private Sub Control3_AfterUpdate()
  6.     Call CheckNUpdate
  7. End Sub
  8.  
  9. Private Sub Control4_AfterUpdate()
  10.     Call CheckNUpdate
  11. End Sub
  12.  
  13. Private Sub CheckNUpdate()
  14.     With Me
  15.         .Control6 = Null
  16.         If IsNull(.Control2) Then Exit Sub
  17.         If IsNull(.Control3) Then Exit Sub
  18.         If IsNull(.Control4) Then Exit Sub
  19.         .Control6 = DateDiff("d", CDate(.Control2), CDate(.Control3)) - .Control4
  20.     End With
  21. End Sub
Feb 14 '12 #12

P: 9
Here goes the details as I can source it out for you:

Form name : FOnLeave
Table name : TOnLeave
Field list (all textboxes):
(1). OnLeaveID (used as for the Relationship - Auto)
(2). StartDate (the start date on leave - short date, manual entry)
(3). EndDate (the last date on leave - short date, manual entry)
(4). NumPublicDay (the number of public holidays, manual entry)
(5). CalcDaysOnLeave (calculate days on leave applied, autogenerated, locked, unbound, I used Control Source formula as per my 1st post and it returns with a correct result << pls advice me if what I'm doing is wrong)
(6). DaysOnleave (this field is used to save result/value no. (5) as its field data, bound, this is the problem I faced)
(7). BFOnLeave (no. of days brought forward from last year, manual entry)
(8). EligOnLeave (no. of days eligible for that particular year, manual entry)
(9). AvaiOnLeave (balance on leave available, autogenerated, locked, unbound, used Control Source << I think this shouldn't be any problem if I managed to settle with field #6)

So far, I didn't use any event procedures/macro/sql yet.
I put a delete button, in case the admin wants to delete any record.
All those fields data will be used in a report (except the OnLeaveID.

Thanks.
Feb 14 '12 #13

NeoPa
Expert Mod 15k+
P: 31,492
That's interesting, but it's your turn now to convert what I've already posted to match your project. I'm happy to provide the expertise, but I don't see my role as doing the donkey-work for you.
Feb 14 '12 #14

P: 9
From the earlier on, I'm just trying to pin the problem on the one I've put in Bold ...

"(6). DaysOnleave (this field is used to save result/value no. (5) as its field data, bound, this is the problem I faced)"

i.e the best method on keeping the unbound calculated value (5) into bound field (6).

...seems I'm a newbie in this VBA coding and trying to benefit the advantages of it in our daily office operations.

Hope this minor coding (compared to your expertise as what I'm expecting) won't deject you from helping people like me :) Thanks

Note: I'm using Ms Access 2010
Feb 14 '12 #15

P: 9
@NeoPa
Mr Neopa,

Millions thanks to you for assisting me on solving my prob. With the codes given, I finally managed to complete my On Leave App. I've also done with my presentation to my GMD just now, he's 100% satisfied and ups for admin use.

You saved my day! Thanks to Bytes!!!
Feb 14 '12 #16

NeoPa
Expert Mod 15k+
P: 31,492
Pleased to hear it :-)

I guess you worked out [Control5] was entirely redundent in the end (hence it was no part of my suggested solution) ;-)
Feb 14 '12 #17

Post your reply

Sign in to post your reply or Sign up for a free account.