Problem writing VB code.

[Phelonia via AccessMonster.com]

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"

 

[Arvin Meyer [MVP]]

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.

 

[Phelonia via AccessMonster.com]

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.

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"

 

[Arvin Meyer [MVP]]

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

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.

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"