Any advice?

[durdle]

Good day Everyone,

I am looking for some help with my vacation database attached. I have spent alot of time tring to reach my

resoultion using queries but that seems like it is not working. I just need to be given some direction in how

I should go about this. I am creating a vaction database for 600 employees that only permits so many people

off per day in any given tourband(which is various intervals throughout the day.IE 12:00AM to 7:00AM is

TB10).I think I have to use VBA and I am not sure where or how to begin. Bacically what I am tring to find

is a treshold(maxium number of people off) for a given Tourband(recgonized in a table as a field name

"curtour"). In order to find this treshold I have to Count all the people in the given Tourband and multiply

it by 4%. This result will give us the maxium amount off people permitted off per day. To add a litte more

confusion, each person has a tourband and a divison(which can be noted as TS, SUT or FLR, and this value is

found in the table tblemployee under "curpos"). I am only concerned with the with the TS division when it

comes to Tourband, with the other Divisions(I.E SUT, FLR) their threshold will be set at an overall basis. It

will have to look like this

TB Avail = Count([Empid], "tblemployee", [curtour] = [forms]![frmemployee]![curtour])
ForcastAbsent = TB Avail * .10
TB Avail - ForecastAbsent - ExistingVacation = TrueAvail

TB Avail --> The Sum of people within the current tourband
ForecastAbesent --> Is the predicted people that will not be at work today this is obviously approx.
ExistingVaction --> People who already have that certain day off, scheduled in the past

I am think this code with have to go under the Confirm button on my frmemployee form. So I am thinking it

will go like this,

If Me.[curtour] = TB10 or TB08 and Me.[curpos] = TS Then
Let ExistingVacation = DCount([Empid], "tblvacation", [curtour] = [forms]![frmemployee]![curtour])
Let ExistingFloater = DCount([Empid], "tblfloater", [curtour] = [forms]![frmemployee]![curtour])
Let TB Avail =DCount([Empid], "tblemployee", [curtour] = [forms]![frmemployee]![curtour])
Let ForcastAbsent = TB Avail * .10
Let TrueAvail = TB Avail - ForecastAbsent
Let Threshold = TrueAvail * .04
If Threshold > (ExistingVacation + ExistingFloater) Then
Msgbox("Sorry no vacation time for this day")
exit sub
else
DoCmd.RunSQL "INSERT INTO tblvacation (empid, vhours, vdate, curtour, curpos) Values

(forms!frmemployee!empid, forms!frmemployee!txtvhours, forms!frmemployee!txtvdate, forms!frmemployee!curtour,

forms!frmemployee!curpos);"

Exit

Endif

Any advice would be greatly appreciated.

durdle
________
starcraft ii replays

 

[WayneRyan]

durdle,

It has been a long week and I have no idea of the logic behind this.

I fixed a few of the syntax errors. Obviously curtour is a string and
in code the values it will be compared with must be enclosed in single
quotes.

You should also use the Nz function to ensure that if they have no
data, then the value is 0.

When you have your code in design view, click on the left margin and
you will see a little red dot. This is a break point. At run-time
Access will stop:

Hover over a variable to see its value.
F8 will single-step you through the code.
F5 will let you "run to completion".
Ctrl-F9 will move the current execution line to the position of the
cursor.

I'm sure that if you inspect the values while it runs, it will make
more sense to you.

If Me.[curtour] = ("TB10" or Me.curtour = "TB08") and Me.[curpos] = "TS" Then
   ExistingVacation = Nz(DCount([Empid], "tblvacation", "[curtour] = '" & [forms]![frmemployee]![curtour] & "'"), 0)
   ExistingFloater = Nz(DCount([Empid], "tblfloater", "[curtour] = '" & [forms]![frmemployee]![curtour] & "'"), 0)
   Me.[TB Avail[ = DCount([Empid], "tblemployee", "[curtour] = '" & [forms]![frmemployee]![curtour] & "'"), 0)
   Me.ForcastAbsent = TB Avail * .10
   Me.TrueAvail = Me.[TB Avail] - Me.ForecastAbsent
   Me.Threshold = Me.TrueAvail * .04
   If Me.Threshold > (Me.ExistingVacation + Me.ExistingFloater) Then
      Msgbox("Sorry no vacation time for this day")
      Exit Sub
   else
      DoCmd.RunSQL "INSERT INTO tblvacation (empid, vhours, vdate, curtour, curpos) " & _
                   "Values (" & forms!frmemployee!empid & ", & & _
                                forms!frmemployee!txtvhours & & ", " & _
                                forms!frmemployee!txtvdate & ", " & _
                                forms!frmemployee!curtour & ", " & _
                                forms!frmemployee!curpos) & ";"

   End If
End If

hth,
Wayne

 

[durdle]

Too few parameters error

Good day,

I am getting an "Too few parameters error:Expected 1" on the indicated line below:

strsql1 = "Select Count([empid]) as CountofEmpID from tblvacation where tblvacation.curtour=" & Me.curtour
strsql2 = "Select count(empid) as CountofTourEmps from tblEmployee where tblEmployee.curtour=" & Me.curtour
strsql3 = "Select Count([empid]) as CountofEmpIDF from tblfloater where tblfloater.curtour=" & Me.curtour

Set db = CurrentDb()
Set rs1 = db.OpenRecordset(strsql1) <-----This is where I am getting the error
Set rs2 = db.OpenRecordset(strsql2)
Set rs3 = db.OpenRecordset(strsql3)

Just wondering if anyone my know what is causing this?
I know the syntax is correct and the remaining parameters are optional.

Thanks
________
bmw k1200r history

 

[WayneRyan]

d,

Look at the last post, curtour is a string, it should be seperated
by single-quotes. Also, look at the spacing, it doesn't hurt to
put an extra space or two before/after the "=".

Wayne

 

[durdle]

Thanks for your help Wayne, working now.
________
digital vaporizer