How to automatically save a VALUE into a table field?

[ayinky]

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.

[NeoPa]

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.

[ayinky]

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.

[Rabbit]

It's simple substitution.
(9) = (7) + (8) - ((3) - (2) - (4))

[NeoPa]

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 :

(9) = (7) + (8) + (4) + (2) - (3)

[ayinky]

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...

[Rabbit]

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.

[ayinky]

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.

[ayinky]

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.

[NeoPa]

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).

[ayinky]

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.

[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) :

Private Sub Control2_AfterUpdate()
    Call CheckNUpdate
End Sub

Private Sub Control3_AfterUpdate()
    Call CheckNUpdate
End Sub

Private Sub Control4_AfterUpdate()
    Call CheckNUpdate
End Sub

Private Sub CheckNUpdate()
    With Me
    	.Control6 = Null
        If IsNull(.Control2) Then Exit Sub
        If IsNull(.Control3) Then Exit Sub
        If IsNull(.Control4) Then Exit Sub
        .Control6 = DateDiff("d", CDate(.Control2), CDate(.Control3)) - .Control4
    End With
End Sub

[ayinky]

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.

[NeoPa]

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.

[ayinky]

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