If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem writing VB code.
Ok, completely new user trying to build a database to help track employees'
vacation and personal time usage. Previously, I used a query turned into a table to calculate the allowed vacation and personal time for the year, but I found that to be somewhat unhelpful, due to the fact that if someone's status (FT, PT, ARR or SAL) were to change, the allowed PTO would not change automatically - which is what I need it to do. So I tried doing it right from one of the text boxes on my form, but I can't get it to work there. Mainly because (I think) the PTOyear was in a field on the query table and no longer is. So I thought VB would work better, but I have no idea what I'm doing with it - go figure. I'm posting the code I have right now below, and what (I think) I need is a way to declare that PTOyear=2010 or whatever year I choose. As far as I can tell, if I can get this to work properly, I can change just that one option, variable, whatever it is, to say 2011 or 2012 and get everything to recalculate to be used again. Which would be really really nice. Any constructive advice would be appreciated. Thanks much! If Status.Employees="FT" Then If PTOyear-Year(HireDate.Employees)=20 Then AlwdVac.Form1="160" Else If PTOyear-Year(HireDate.Employees)=16 Then AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*8 Else IF PTOyear-Year(HireDate.Employees)=5 Then AlwdVac.Form1="120" Else IF PTOyear-Year(HireDate.Employees)=2 Then AlwdVac.Form1="80" Else If PTOyear-Year(HireDate.Employees)=1 Then If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1="64" Else AlwdVac.Form1="40" Else If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*2.67,0) Else AlwdVac.Form1="0" Else If Status.Employees="PT" Then If PTOyear-Year(HireDate.Employees)=20 Then AlwdVac.Form1="100" Else If PTOyear-Year(HireDate.Employees)=16 Then AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*5 Else IF PTOyear-Year(HireDate.Employees)=5 Then AlwdVac.Form1="75" Else IF PTOyear-Year(HireDate.Employees)=2 Then AlwdVac.Form1="50" Else If PTOyear-Year(HireDate.Employees)=1 Then If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1="40" Else AlwdVac.Form1="25" Else If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*1.67,0) Else AlwdVac.Form1="0" Else If Status.Employees="ARR" Then AlwdVac.Form1="0" Else If Status.Employees="SAL" THen AlwdVac.Form1="" 'This needs to be able to be entered manually, as the allowed vacation and personal for Salaried employees can vary Else AlwdVac.Form1="Status Needed" -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
Problem writing VB code.
If building a query worked, why did you find it necessary to turn it into a
table? Queries work just like tables as recordsources for forms and reports, and they are always up to date. Table data is static, and should not be used for calculations unless it is a temporary table. In which case, you delete the data using a delete query, and repopulate it from the original append query. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Phelonia via AccessMonster.com" u57034@uwe wrote in message news:a16df19bdd549@uwe... Ok, completely new user trying to build a database to help track employees' vacation and personal time usage. Previously, I used a query turned into a table to calculate the allowed vacation and personal time for the year, but I found that to be somewhat unhelpful, due to the fact that if someone's status (FT, PT, ARR or SAL) were to change, the allowed PTO would not change automatically - which is what I need it to do. So I tried doing it right from one of the text boxes on my form, but I can't get it to work there. Mainly because (I think) the PTOyear was in a field on the query table and no longer is. So I thought VB would work better, but I have no idea what I'm doing with it - go figure. I'm posting the code I have right now below, and what (I think) I need is a way to declare that PTOyear=2010 or whatever year I choose. As far as I can tell, if I can get this to work properly, I can change just that one option, variable, whatever it is, to say 2011 or 2012 and get everything to recalculate to be used again. Which would be really really nice. Any constructive advice would be appreciated. Thanks much! If Status.Employees="FT" Then If PTOyear-Year(HireDate.Employees)=20 Then AlwdVac.Form1="160" Else If PTOyear-Year(HireDate.Employees)=16 Then AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*8 Else IF PTOyear-Year(HireDate.Employees)=5 Then AlwdVac.Form1="120" Else IF PTOyear-Year(HireDate.Employees)=2 Then AlwdVac.Form1="80" Else If PTOyear-Year(HireDate.Employees)=1 Then If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1="64" Else AlwdVac.Form1="40" Else If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*2.67,0) Else AlwdVac.Form1="0" Else If Status.Employees="PT" Then If PTOyear-Year(HireDate.Employees)=20 Then AlwdVac.Form1="100" Else If PTOyear-Year(HireDate.Employees)=16 Then AlwdVac.Form1=(PTOyear-Year(HireDate.Employees))*5 Else IF PTOyear-Year(HireDate.Employees)=5 Then AlwdVac.Form1="75" Else IF PTOyear-Year(HireDate.Employees)=2 Then AlwdVac.Form1="50" Else If PTOyear-Year(HireDate.Employees)=1 Then If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1="40" Else AlwdVac.Form1="25" Else If 12-Month(HireDate.Employees)2 Then AlwdVac.Form1=ROUND((12-Month(HireDate.Employees))*1.67,0) Else AlwdVac.Form1="0" Else If Status.Employees="ARR" Then AlwdVac.Form1="0" Else If Status.Employees="SAL" THen AlwdVac.Form1="" 'This needs to be able to be entered manually, as the allowed vacation and personal for Salaried employees can vary Else AlwdVac.Form1="Status Needed" -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
Problem writing VB code.
I sent it to a table because when I tried putting the fields into my form, I
couldn't get it to work. It did work when I put it into the table. Arvin Meyer [MVP] wrote: If building a query worked, why did you find it necessary to turn it into a table? Queries work just like tables as recordsources for forms and reports, and they are always up to date. Table data is static, and should not be used for calculations unless it is a temporary table. In which case, you delete the data using a delete query, and repopulate it from the original append query. Ok, completely new user trying to build a database to help track employees' [quoted text clipped - 81 lines] Else AlwdVac.Form1="Status Needed" -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201001/1 |
#4
|
|||
|
|||
Problem writing VB code.
Forms and reports are built to display data. Tables store data. And queries
gather it for forms and reports. The only time a query might present a problem is for a form that required being updating, and the query would not allow it. That only happens with certain sets of joined tables or calculations. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Phelonia via AccessMonster.com" u57034@uwe wrote in message news:a171d958f8276@uwe... I sent it to a table because when I tried putting the fields into my form, I couldn't get it to work. It did work when I put it into the table. Arvin Meyer [MVP] wrote: If building a query worked, why did you find it necessary to turn it into a table? Queries work just like tables as recordsources for forms and reports, and they are always up to date. Table data is static, and should not be used for calculations unless it is a temporary table. In which case, you delete the data using a delete query, and repopulate it from the original append query. Ok, completely new user trying to build a database to help track employees' [quoted text clipped - 81 lines] Else AlwdVac.Form1="Status Needed" -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/201001/1 |
Thread Tools | |
Display Modes | |
|
|