Temp Employee hourly report (1 Viewer)

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
I have a form where employees register their daily work hours. I am trying to put a restriction on Temprary employees that since they work hourly wages they can't register hours as non-performance such as ( public holiday, vacations, sick leave or military duty, minus hours... etc). The employees are selected from a dropdown list and the non-performance option is set as a radio button ( when it's checked it gives me the non-performance dropdown list )

i want the temporary employees to not be able to check the radio button in the first place.
The code is supposed to check if the selected employee number is equal to that in the employee hours table and whether the temporary_field is set to True or not (of course in access it's -1) so it basically checks if the employee is actually temporary or not , and also checks the date (if it falls between the temporary employee's hire date and termination date )


i have tried 2 variatons :

1st attempt was regular recordset
and i tried both enabled and visible none of it worked, i put msgbox in between to see if it actually enters the if statement, turns out it doesn't!


Code:
Private Sub PersNr_Change()

    Dim db As DAO.Database
    Dim rstpp As DAO.Recordset

    Dim dateBegin As Date
    Dim dateEnd As Date

    Set db = CurrentDb
    Set rstpp = db.OpenRecordset("tblPersonalStundenplanung", dbOpenDynaset, dbSeeChanges)     //[this is the employee hours table]


    dateBegin = rstpp!PersPlan_Datum_Beginn      [hire date]
    dateEnd = rstpp!PersPlan_Datum_Ende           [termination date]

    If Me!mDatum >= dateBegin And Me!mDatum <= dateEnd Then   // [mDatum is the current date ]
         MsgBox (Me!mDatum)
       If Me!PersNr = rstpp!PersNr Then                                                 // [the employee number ]
                MsgBox (Me!PersNr)
                MsgBox (rstpp!PersNr)
           If DLookup("PersPlan_Temporaere", "tblPersonalStundenplanung", "PersPlan_Temporaere = -1") Then        //[temporary field]
                MsgBox (DLookup("PersPlan_Temporaere", "tblPersonalStundenplanung", "PersPlan_Temporaere = -1"))
               Me!Option191.Enabled = False
               Me!LinkNr.Visible = False
           End If
       End If
    End If

End Sub
____________________________________________________________________
2nd attempt was to use sql as string for the recordset

Code:
Private Sub PersNr_Change()

    Dim db As DAO.Database
    Dim rstpp As DAO.Recordset
    Dim dateBegin As Date
    Dim dateEnd As Date

    Dim emp01 As String
    emp01 = " SELECT * FROM tblPersonalStundenplanung WHERE PersPlan_Temporaere = -1 "

    Set db = CurrentDb
    Set rstpp = db.OpenRecordset("emp01", dbOpenDynaset, dbSeeChanges)

    dateBegin = rstpp!PersPlan_Datum_Beginn
    dateEnd = rstpp!PersPlan_Datum_Ende

                    If rstpp!PersNr = Me!PersNr.Value Then
                            MsgBox (Me!PersNr)
                            MsgBox (rstpp!PersNr)
                      If Forms!frmStundenrapport!mDatum >= dateBegin And Me!mDatum <= dateEnd Then
                            MsgBox (dateBegin)
                            MsgBox (Me!mDatum)
                            MsgBox (dateEnd)
                                MsgBox (rstpp!PersPlan_Temporaere)
                                Forms!frmStundenrapport!Option191!Enabled = False
                                Forms!frmStundenrapport!LinkNr.Enabled = False
                      End If
                    End If

End Sub

so this didn't work at all, i kept getting 3078 runtime error though i went and checked that i wrote and spelled everything correctly, even checked the direct vba window as well. bbut no luck there

______________________________
Ps.
i also tried this code on

Form_Load()
Form_Currrent()
After_Update()
Combobox_Change()
Combobox_Click()

I know some of these make no sense but i just tried for the sake of it...
but as you can see none worked o_O😭🤯

thank you so much in advance for any tips and clues as to how it might work or why it didn't
really appreciate it
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
I would have thought you would need to use the employee number as criteria to find the temp field.?
FWIW I have tended to hold the ID, Name and anything else I needed like this in TempVars to save having to lookup every time.?
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
I would have thought you would need to use the employee number as criteria to find the temp field.?
FWIW I have tended to hold the ID, Name and anything else I needed like this in TempVars to save having to lookup every time.?

It's because the names and ID numbers are in the employee table but the Is_A_Temp information and hire and termination dates are in the employee hours table

the two tables are connected with the id number (PersNr)

i could use a dlookup and use it as a variable , right??
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
It's because the names and ID numbers are in the employee table but the Is_A_Temp information and hire and termination dates are in the employee hours table

the two tables are connected with the id number (PersNr)

i could use a dlookup and use it as a variable , right??
You would still have to look for the field for a particular employee though, would you not?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
So look that status of that employee when a form is loaded and lock relevant controls if a temp?
As I mentioned, I would do it once on login and use that throughout.
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
So look that status of that employee when a form is loaded and lock relevant controls if a temp?
As I mentioned, I would do it once on login and use that throughout.

the development form i have u can choose the employees, but in the productive form ie. if i open mine (without logging) i find my name already selected and i can tap in my hours right away, that's why i thought to write the program in Form Load()
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
You are doing this?
Code:
DLookup("PersPlan_Temporaere", "tblPersonalStundenplanung", "PersPlan_Temporaere = -1")
which is looking up a field where a field is -1 ?, not looking up that field for a PerNr = <whatever the employee number is> ?

You are not even taking into account which employee it is for.? In fact it will find the first record that has Temp as True.?
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
You are doing this?
Code:
DLookup("PersPlan_Temporaere", "tblPersonalStundenplanung", "PersPlan_Temporaere = -1")
which is looking up a field where a field is -1 ?, not looking up that field for a PerNr = <whatever the employee number is> ?

You are not even taking into account which employee it is for.? In fact it will find the first record that has Temp as True.?

1594043304658.png

OMG i just saw what what you meant, man i'm such an idiot 🤦‍♀️ok then how about if i did this ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
No !
More like
Code:
ID_Temp = Dlookup("PersPlan_Temporaere", "tblPersoanlStudenplanung","PersNr=" & Me.PersNr )

Assuming PersNr is numeric, if not
Code:
ID_Temp = Dlookup("PersPlan_Temporaere", "tblPersoanlStudenplanung","PersNr='" & Me.PersNr & "'")
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
No !
More like
Code:
ID_Temp = Dlookup("PersPlan_Temporaere", "tblPersoanlStudenplanung","PersNr=" & Me.PersNr )

Assuming PersNr is numeric, if not
Code:
ID_Temp = Dlookup("PersPlan_Temporaere", "tblPersoanlStudenplanung","PersNr='" & Me.PersNr & "'")


aaah yesss of course, obviously i've lost my senses.. Thankyou so much
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
btw what's your understanding of PersPlan_Temporaere ??
because the field is boolean, yes temp no not temp , and if i select based on this criteria, wouldn't it just select everyone ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
because the field is boolean, yes temp no not temp , and if i select based on this criteria, wouldn't it just select everyone ?
No, you would be checking for each employee. You could try and bring it in with the employee details, but for each employee, you would need to check their status in some way?
That is the reason for using the employee number as criteria?
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
No, you would be checking for each employee. You could try and bring it in with the employee details, but for each employee, you would need to check their status in some way?
That is the reason for using the employee number as criteria?

yes, to be able to see the whether the selected employee a temp is or not
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
yes, to be able to see the whether the selected employee a temp is or not

so after checking all the employees, what am i supposed to do to determine that yes indeed this employee is a temp and he should be restricted, and to have the program actually enter the if statement not just ignore it ?
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
Who is actually processing this data.?
If each employee then test for the user.
If you then for each employee processing you enable/disable the relevant controls or set values to zero.

Remember, I and everyone else here have no idea how your system is working?, or whether it should even be working that way.?
Regardless you need to check for each employee as you process each employee.
How you do that is up to you.?

If you could bring in the status with the other employee data, that would be more efficient and easier I would have thought.?
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
Who is actually processing this data.?
If each employee then test for the user.
If you then for each employee processing you enable/disable the relevant controls or set values to zero.

Remember, I and everyone else here have no idea how your system is working?, or whether it should even be working that way.?
Regardless you need to check for each employee as you process each employee.
How you do that is up to you.?

If you could bring in the status with the other employee data, that would be more efficient and easier I would have thought.?

yes all employees use this program, i have thought about testing the user yesterday but i actually don't know where to find the users 😅. my coworker assigned this task to me for my training but i don't know where to go from here, i'm not sure how the entire system works either!
 

Gasman

Enthusiastic Amateur
Local time
Today, 17:14
Joined
Sep 21, 2011
Messages
14,044
Well most systems would have a login form, to limit it to the people who are meant to use it.
That would be the time I would capture their PersNr and even their status. Then use those throughout the system.

Ask your coworker. You cannot be expected to do everything yourself. It is training you said, not a test.?
 

m-taha

New member
Local time
Today, 17:14
Joined
Dec 6, 2019
Messages
29
Well most systems would have a login form, to limit it to the people who are meant to use it.
That would be the time I would capture their PersNr and even their status. Then use those throughout the system.

Ask your coworker. You cannot be expected to do everything yourself. It is training you said, not a test.?

everyone uses it, they all register their hours and they all have it installed on their pc as a remote desktop connection. there is a username, i am still searching where i can find this information in the database.

it is a training 😁 my coworker always tells me to just try any idea that comes to mind try it, if i get stuck anywhere he'll check it with me but he's on vacation 😅 so it's kind of a bad timing
 

Users who are viewing this thread

Top Bottom