calculated field

[melda11]

my form is bound to a query.This query selects from a table(which has all the fields I mentioned above except for ToBeTaken) and creates a calculated field ToBeTaken.

 

[melda11]

ok here is my database.I used both vba code and queries.In my query the calculated fields works but I have to enter the Forward,Balance and No_of_Days_Due everytime I enter a new employee and the vba code fot the OnCurrent event doesnt work.The controls are still blank.Any help will be graetly appreciated.Thx.

 

[pbaldy]

2 general comments. You'll find more people able to help if you post an mdb version; not that many people have 2007 yet. Second, I'm not really comfortable with the design if it's going to flow the way I think it will. It's hard to tell without some data in it. I would lean towards a transactional design where you recorded transactions for the accrual or use of vacation days. All of your fields are basically calculated, and I don't see how a person would get days to begin with. With a transactional system, you don't have to store balance or forward or anything. It can all be calculated from the transactions, and I think you have a better audit trail if someone questions their balance.

 

[melda11]

ok this is the mdb version.Unfortunately I cannot change the fields because all these fields and data in the table are straight from excel unless I create another Select query.But I think I still need to store all these fields coz we need to check first if an employee has enough vacation days before filling the form.I will have a sub form in another form that will show all these fields and the balance for checking purposes.Then I will have a command button on this form which will open Form 1 and the details entered.

 

[ajetrumpet]

Melda,

So what exactly is wrong with the query from the database you gave us in post #22??

It looks as though you used the SWITCH function appropriately...

 

[melda11]

I also entered some test data..

 

[melda11]

I have created calculated fields from these fields using the switch function.
Forward_updated From Forward
Balance_Updated From Balance and
No_of_days_updated From No_of days_Due..
These caculated fields will only work if I re-enter the caculated value in the origional fields everytime I enter a new record.

 

[ajetrumpet]

I must say that I am getting a bit confused with all the redundant conversation here.

Please tell me if all of the following are true...

** You want a separate record for each leave application that is filled out.
** You want a place in the database where you can view all of the current (updated) numbers related to each employees' leave schedule.
** With each leave application record, you want it to reflect the numbers that will be in effect after that application is approved.

 

[melda11]

yes that is true..I want the updated values to be displayed in the table and the form.Is this possible? I need to view an employees leave details in the form which is why I need to update the table tok I hope you guys understand what Im trying to do here.

 

[ajetrumpet]

Melda,

I am assuming that the expressions you wrote in post #10 are all working OK from the query you posted in the sample database. If they are, here's what I would suggest you do (this may be a bit confusing, but it should give you something similar to what you want)...

* Create a "requests" table (which will serve as a "temporary table" that passes information on to another table that will house the permanent "leave" record) which has all of the fields that your current query has.

* Create a "full leave information" table with all of the same fields as the "requests" table. Then, create a DATA ENTRY specified Form that is based on the "full leave" table.

* Aside from all the other fields that will be on the "full leave" table's Form, create a combo box to lookup the list of all Employee ID's. Make sure that all controls on the form (including the combo box) are bound to the

 

[melda11]

ok this will take a while to digest but I will definitely try it and let you know of the outcome..
Thanks

 

[melda11]

I saw it a while ago but it seems to have disappeared..

 

[ajetrumpet]

Great!!!!!!!!!!!!

I bet you didn't copy it down.............
I have it on WordPad, hold on a minute....

 

[melda11]

Its ok I already have a copy of it sent to my email before it disappeared ..Thx..

 

[ajetrumpet]

Melda,

I am assuming that the expressions you wrote in post #10 are all working OK from the query you posted in the sample database. If they are, here's what I would suggest you do (this may be a bit confusing, but it should give you something similar to what you want)...

* Create a "requests" table (which will be your official "application leave" table) which has all of the fields that your current query has.

* Create a "full leave information" table with all of the same fields as the "requests" table. Then, create a DATA ENTRY specified Form that is based on the "full leave" table.

* Aside from all the other fields that will be on the "full leave" table's Form, create a combo box to lookup the list of all Employee ID's. Make sure that all controls on the form (including the combo box) are bound to the table's fields.

* Create a query with this code...

SELECT TOP 1 [all fields here]
FROM [requests]
WHERE [EmployeeID] = Forms!YourForm!YourControl
ORDER BY ["TO" field from the "requests" table] DESC;

*In the "AfterUpdate" of the combo box, write this...

Me."TO"field = DLookup ("["TO"field]", "CreatedQuery")
Me."FROM"field = DLookup ("["FROM"field]", "CreatedQuery")

To populate the controls with the correct calculated values for the permanent "leave request" record (from my post (#17)...
* Put a command button on the form that says something like "Calculate", and with the "OnClick" event of that button, write the code from post #17. The only thing that will be tricky here is that you will have to populate the form controls by performing your mathematical expressions on "DLookup" Values (Looked up in the "requests" table).

So, the "OnClick" Event code will be modified so you can assign temporary string values to your controls....

Dim Forward as Long (or Integer)
Dim To_Be_Taken as Long (Or Integer), etc...

ForwardControlName = DLookup ("[ForwardField]", "requests")
To_Be_TakenControlName = DLookup ("[To_Be_TakenField]", "requests")

ForwardControlName = Forward
To_Be_TakenControlName = To_Be_Taken

[color=red]Write the code from #17 now, but make sure to use the
temporary strings (i.e. "Foward", "To_Be_Taken", etc...) in place of
the actual form control names)[/color]

Now you have all of the values that you wanted in the controls and ready to be entered permanently for storage in the "full leave" table.

ENTER THE RECORD!!

This should be pretty close to what you're looking for, but perhaps it might be easier to just restructure your database a bit....??

 

[ajetrumpet]

well nevermind then.

 

[melda11]

Thanks again I really really appreciate this

 

[ajetrumpet]

All I have to say is that it was fun to figure out, but a LITTLE BIT complex.

Feel fortunate that the fiscal year does not end on October 31st in my world, because it does in many others!!

I suppose I'll be back in a couple years to see if you have posted anything on this...

 

[melda11]

hey ajetrumpet!
You are amazing .After a few modifications, your idea actually worked for me...Thanks man.This is an amazing forum.Thank you all for your excellent input