Work hours Report for Temporary Employees

[m-taha]

Hello everyone, I'd really like some help with this.
We have a Hour Report Form For Employees to report their work hours which is clear of course everyone has it. What I want to do is if an Employee is a Temporary Employee then they can't report hours as "Holiday" if they take a day off.

This is my attempt (failed attempt) at trying to make this portion of the code work (the code was written by my coworker who is guiding me through my training)

this is his:

Private Sub Befehl69_Click()

Dim Datum, Mitarbeiter, Auftrag, Planschritt, Leistungsgruppe, Kommentar As Variant
Dim insertQuery As String

Dim qdf As QueryDef

'Testweise auf False um Fehlerquelle auszuschliessen fg
Application.Echo False

Datum = Me!mDatum
Mitarbeiter = Me!PersNr
Auftrag = Me!obnr
Leistungsgruppe = Me!LeistungsgruppeNr
Planschritt = Me!LinkNr.Column(1)
Kommentar = Me!Kommentar


If validateInput(Auftrag, Planschritt, Me!Anz, Mitarbeiter, Datum, Leistungsgruppe) = False Then
    Application.Echo True
    Exit Sub
End If

'If Me!LinkNr.text = "Regie" Then
'        Me!Rahmen_Regie_Akkord.Value = True
'    Else
'        Me!Rahmen_Regie_Akkord.Value = False
'End If
Dim NrAuftrag As Long
Dim IDMandant As Integer
Dim rst As DAO.Recordset
Dim Server As String
IDMandant = GetConfigValue("IDMandant")

If GetInstanz = 1 Then
    Server = ""
Else
    Server = "\DEV"
End If

If Me.txtNrMandant <> IDMandant Then
    'Rapport auf Auftrag eines anderen Mandanten
    Dim cmd As ADODB.Command
    Set cmd = New ADODB.Command
    cmd.ActiveConnection = "Driver={ODBC Driver 13 for SQL Server};Server=merz-ts01" & Server & ";Database=eDach_Stammdaten; Trusted_Connection=yes;"
    cmd.CommandType = adCmdStoredProc
    cmd.CommandText = "sp_GetMithilfeAuftrag"
    cmd.Parameters.Append cmd.CreateParameter("@NrHauptAuftrag", adInteger, adParamInput, 255, Auftrag)
    cmd.Parameters.Append cmd.CreateParameter("@NrMandant_Hauptauftrag", adInteger, adParamInput, 255, Me.txtNrMandant)
    cmd.Parameters.Append cmd.CreateParameter("@NrMandant_Mithilfeauftrag", adInteger, adParamInput, 255, IDMandant)
    cmd.Parameters.Append cmd.CreateParameter("@NrMithilfeauftrag", adInteger, adParamOutput, 255)
    cmd.Execute
    
    NrAuftrag = cmd.Parameters("@NrMithilfeauftrag").Value
    If DLookup("[Planschritt]", "qryUnionPlanschritte", "[IDPlanschritt]=" & Planschritt & " AND [IDMandant]=" & Me.txtNrMandant) = "Regie" Then
        Planschritt = CInt(GetConfigValue("planschritt_mithilfe_regie"))
    Else
        Planschritt = CInt(GetConfigValue("planschritt_mithilfe"))
    End If
    
    
    'Auftragsverantwortung zuweisen
    ' Andreas in sql query nehmen für webapp
    'CurrentDb.Execute "UPDATE tblAufträge SET Auftrag_Baustellenleitung = " & GetConfigValue("PersNr_Verantwrtung_Mithilfeauftraege")
    
    'Kunden vom Objekt übertragen
    'CurrentDb.Execute "INSERT INTO tblZuo_Auftrag_Kunde ( Auftrag_nr, Kunden_Nr, NrFunktion, Hauptansprechpartner, Rechnungsadresse, VorAdresse, COAdresse )" _
    '    & "SELECT tblAufträge.IDAuftrag, tblZuo_Objekt_Kunde.Kunden_Nr, tblZuo_Objekt_Kunde.NrFunktion, tblZuo_Objekt_Kunde.Hauptansprechpartner, tblZuo_Objekt_Kunde.Rechnungsadresse, tblZuo_Objekt_Kunde.VorAdresse, tblZuo_Objekt_Kunde.COAdresse" _
    '    & "FROM (tblObjekt LEFT JOIN tblZuo_Objekt_Kunde ON tblObjekt.IDObjekt = tblZuo_Objekt_Kunde.Objekt_Nr) RIGHT JOIN tblAufträge ON tblObjekt.IDObjekt = tblAufträge.ObjektNr" _
    '    & "WHERE tblAufträge.IDAuftrag = " & IDAuftrag
    
    
Else
    NrAuftrag = Auftrag
End If

'Datensatz speichern
Set rst = CurrentDb.OpenRecordset("tblRapport", dbOpenDynaset, dbSeeChanges)

rst.AddNew
rst!NrMandant_Rapport = IDMandant
rst!Datum = Datum
rst!PersNr = Mitarbeiter
rst!AuftragNr = NrAuftrag
rst!Anz = Me.Anz
rst!Rapport_MwSt = 0.077
rst!Rapport_Regiestunden = Me.Rahmen_Regie_Akkord
rst!LinkNr = Planschritt
rst!Kommentar = Kommentar


rst.Update

'Name und Datum stehen lassen wenn häkchen gesetzt sind
If Me!c1 = -1 Then
    Me!mDatum = Datum
Else
    Me!mDatum = Date
End If
    
If Me!c3 = -1 Then
    Me!PersNr = Mitarbeiter
Else
    Me!PersNr = Null
End If



Select Case optRapportart2.Value
    Case 1: 'Produktiv
        If Me!c2 = -1 Then
            Me!obnr = Auftrag
        Else
            Me!obnr = Null
        End If
        
        If Me!c4 = -1 Then
            'Me!obnr = Auftrag
            Me!LeistungsgruppeNr = Leistungsgruppe
        Else
            Me!LeistungsgruppeNr = Null
        End If
        
        'Me!LinkNr.Requery
        
        If Me!c5 = -1 Then
            Me!LeistungsgruppeNr = Leistungsgruppe
            Me!LinkNr = Planschritt
        Else
            Me!LinkNr = Null
        End If
    
    Case 2: 'Unproduktiv
        Me!obnr = Auftrag
        
        If Me!c4 = -1 Then
            Me!LeistungsgruppeNr = Leistungsgruppe
        Else
            Me!LeistungsgruppeNr = Null
        End If
        
        Me!LinkNr.Requery
        
        If Me!c5 = -1 Then
            Me!LeistungsgruppeNr = Leistungsgruppe
            Me!LinkNr = Planschritt
        Else
            Me!LinkNr = Null
        End If
        
    
    Case 3: 'NichtLeistung
        Me!obnr = Auftrag
        Me!LeistungsgruppeNr = Leistungsgruppe
        
        If Me!c5 = -1 Then
            Me!LinkNr = Planschritt
        Else
            Me!LinkNr = Null
        End If
        
    
    Case 4: 'Ämtli
        If Me!c2 = -1 Then
            Me!obnr = Auftrag
        Else
            Me!obnr = Null
        End If
        
        If Me!c4 = -1 Then
            Me!LeistungsgruppeNr = Leistungsgruppe
        Else
            Me!LeistungsgruppeNr = Null
        End If
        
        Me!LinkNr.Requery
        
        If Me!c5 = -1 Then
            Me!LeistungsgruppeNr = Leistungsgruppe
            Me!LinkNr = Planschritt
        Else
            Me!LinkNr = Null
        End If

End Select

Me!Anz = Null
Me!NrProfitCenter = Null
Me!Kommentar = Null

DoCmd.GoToControl ("bef_datum_-1")
Me!Anzeige_Rapporte.Requery
Me!Anzeige_Material.Requery
Me!Anzeige_Arbeiten.Requery

'Me!Anzeige_SollStd.Visible = False
'Me!Anzeige_Rapportiert.Visible = False
Me!Anzeige_Stundentotal.Requery

'Me!Rahmen_Regie_Akkord = 0
'If Me!c2 = -1 And Me!Auftr_Wartungsvertrag = -1 Then
'    Me!Rahmen_Regie_Akkord.Visible = True
'Else
'    Me!Rahmen_Regie_Akkord.Visible = False
'End If

Application.Echo True

Me.frmStundenrapportsubKommentare.Requery

End Sub

and i tried to add this :

Case 3: 'NichtLeistung
Me!obnr = Auftrag
Me!LeistungsgruppeNr = Leistungsgruppe

If Me!c5 = -1 Then
Me!LinkNr = Planschritt
Else
If DCount("PersonalNr", "tblPersoanlStundenplanung", "PersPlan_Temporaere = -1") Then
If Me!LinkNr = DCount("IDPlanschritt", "qryUnionPlanschritte", "Planschritt = 'ferien'") Then
MsgBox ("Choice not available for you")
Exit Sub
End If
Else
Me!LinkNr = Null
End If
End If


i got this error when executing it.
I really appreciate the help.

 

[vba_php]

what i don't understand is why you are writing all of this code when doing what you want to do is *very* simple. wouldn't it simply be easier to put a "flag" field in the "users" table or "employees" table, then relate that table to the "hours worked" table. then with that query as the source of the form, simply have a combo box dropdown to pick the "type" of work hours being entered. THUS, if [type of employee = temp], deny them a saving of the record. it's that simple. 3 lines of code.

 

[m-taha]

what i don't understand is why you are writing all of this code when doing what you want to do is *very* simple. wouldn't it simply be easier to put a "flag" field in the "users" table or "employees" table, then relate that table to the "hours worked" table. then with that query as the source of the form, simply have a combo box dropdown to pick the "type" of work hours being entered. THUS, if [type of employee = temp], deny them a saving of the record. it's that simple. 3 lines of code.

I know it sounds pathetic, yet honestly I still don't know how do to things, i just started from the middle , but i'm trying to also understand how it works.
I understand what you said and i've come acress something like that before but i don't exactly know what it means at the same time or how it should be done correctly.
but i'll try to make it work

 

[vba_php]

do you have a file to upload? upload it here, and I or someone else will re-upload it and show you it can be done very easily. =)

 

[The_Doc_Man]

To be honest, most of us will not understand the code written with German names on all of the variables. It would be hard as all heck for us to quickly get through that. I know my own college German is too rusty. Mein Deutsch is nicht sehr gut; ich habe nicht genug worte.

However, I can offer something I think will help. What you are talking about is something called "role-based" operation, where a person's role within the company allows or denies access to certain options. You can search this forum for the topic of "User Roles" and would get lots of advice and viewpoints on this kind of problem. Don't neglect that search to get more ideas, but I will try to give you a brief idea of how to attack the simple task you have described. Adam suggested some kind of flag. I will take that to the next level since he didn't go very far with his idea.

First and most important: Remember that you are letting the users enter their own hours. To do this, they must log in or in some other way identify themselves. If you have a reasonably reliable method of logging in, you know who they are. Once you know who they are, you can try to tailor some things for them. If you know their name, you almost certainly have a table somewhere that will tell you their role using no worse than a simple DLookup operation. Let me propose a role-based table.

You normally have labor types in a table. So you have "Normal" and "Sick" and "Leave" and "Holiday" and some other categories. This is a one-size-fits-all design. But perhaps there is another way to do this. The amount of space likely taken up by this labor-type table is small, or at least I would think it was not huge. How many roles could you have? (That is rhetorical.) Probably not more than two or three. Let me assume for now that you have full-time, part-time, temporary, and nothing else. So what I'm going to propose is a little tedious to set up but will be lightning fast when running.

Build several sets of records in the table that list labor types that drives the combo box where the users select their labor types for each entry. More specifically, make one set of records listing the legal labor types for each category of employee. So for a full-time employee, you have all types. For other types, you might have fewer entries. Let's say you have F for full-time, P for part-time, and T for temporary. Have records that look like this in the list of legal labor types and employee codes:

F, Normal
F, Sick
F, Leave
F, Holiday
F, Overtime
T, Normal
T, Sick
P, Normal
P, Sick
P, Leave
P, Holiday

Note that this table says a Temp can only have Normal or Sick times. A Part Timer has everything a full-timer has except Overtime.

Now instead of having something like
SELECT LaborType, LaborCode, ... FROM LaborTypeTable ;
as the .Rowsource of the combo box that you use to identify the labor type on each entry, let the combo box use
SELECT LaborType, LaborCode, ... FROM LaborTypeTable WHERE EmpType = '" & EmpRoleCode & "' ;"

You will of course have to diddle around with this because I can't see your forms to know how your combo box was built. All it takes is adding one more column to the labor types list to show the employee type to which that labor type is available.

So... your table that supplies the drop-down choices can be told to filter based on the employee's role because with that one extra column, you have the basis for filtering. Then if your combo box doesn't include the types you wanted to exclude, the employee can't select those restricted types. If they can't select it, you don't need any code to check after-the-fact whether they picked a bad code. This is an example of an old USA saying, "An ounce of prevention is worth a pound of cure."

Does this way of looking at it help?

 

[m-taha]

do you have a file to upload? upload it here, and I or someone else will re-upload it and show you it can be done very easily. =)

i wish i could, but it's in the company's database server as it doesn't belong to me

 

[vba_php]

i wish i could, but it's in the company's database server as it doesn't belong to me

well that's easy, isn't it? just copy the data from one file to a new one, replace the data with false information in the tables, and upload that. that should be easy for you to do. you only have to include a few fake records. shouldn't take you but a few minutes.

 

[m-taha]

To be honest, most of us will not understand the code written with German names on all of the variables. It would be hard as all heck for us to quickly get through that. I know my own college German is too rusty. Mein Deutsch is nicht sehr gut; ich habe nicht genug worte.

However, I can offer something I think will help. What you are talking about is something called "role-based" operation, where a person's role within the company allows or denies access to certain options. You can search this forum for the topic of "User Roles" and would get lots of advice and viewpoints on this kind of problem. Don't neglect that search to get more ideas, but I will try to give you a brief idea of how to attack the simple task you have described. Adam suggested some kind of flag. I will take that to the next level since he didn't go very far with his idea.

First and most important: Remember that you are letting the users enter their own hours. To do this, they must log in or in some other way identify themselves. If you have a reasonably reliable method of logging in, you know who they are. Once you know who they are, you can try to tailor some things for them. If you know their name, you almost certainly have a table somewhere that will tell you their role using no worse than a simple DLookup operation. Let me propose a role-based table.

You normally have labor types in a table. So you have "Normal" and "Sick" and "Leave" and "Holiday" and some other categories. This is a one-size-fits-all design. But perhaps there is another way to do this. The amount of space likely taken up by this labor-type table is small, or at least I would think it was not huge. How many roles could you have? (That is rhetorical.) Probably not more than two or three. Let me assume for now that you have full-time, part-time, temporary, and nothing else. So what I'm going to propose is a little tedious to set up but will be lightning fast when running.

Build several sets of records in the table that list labor types that drives the combo box where the users select their labor types for each entry. More specifically, make one set of records listing the legal labor types for each category of employee. So for a full-time employee, you have all types. For other types, you might have fewer entries. Let's say you have F for full-time, P for part-time, and T for temporary. Have records that look like this in the list of legal labor types and employee codes:

F, Normal
F, Sick
F, Leave
F, Holiday
F, Overtime
T, Normal
T, Sick
P, Normal
P, Sick
P, Leave
P, Holiday

Note that this table says a Temp can only have Normal or Sick times. A Part Timer has everything a full-timer has except Overtime.

Now instead of having something like
SELECT LaborType, LaborCode, ... FROM LaborTypeTable ;
as the .Rowsource of the combo box that you use to identify the labor type on each entry, let the combo box use
SELECT LaborType, LaborCode, ... FROM LaborTypeTable WHERE EmpType = '" & EmpRoleCode & "' ;"

You will of course have to diddle around with this because I can't see your forms to know how your combo box was built. All it takes is adding one more column to the labor types list to show the employee type to which that labor type is available.

So... your table that supplies the drop-down choices can be told to filter based on the employee's role because with that one extra column, you have the basis for filtering. Then if your combo box doesn't include the types you wanted to exclude, the employee can't select those restricted types. If they can't select it, you don't need any code to check after-the-fact whether they picked a bad code. This is an example of an old USA saying, "An ounce of prevention is worth a pound of cure."

Does this way of looking at it help?

thank you for replying , German is pretty difficult hhh ,
i should've explained what's going on in that database, even if you see the forms and the tables it looks pretty confusing i still can't my out of it.. but i could screenshot the forms and their queries.

(this is just my process)
i don't exactly know how the log in process works(remote desktop environment), but when i enter form to report my own hours, my name is already selected and i just select one of the radio buttons (productive, unproductive, and if i did nothing at all) if i was productive i choose the work month for the company from the first drop down list and then choose which department did my job and the next drop down the nature of my activities in that department and then enter my hours in the text box below it .

the code for all this is in the save button
that's pretty what i myself understand from it

i got an idea from your example, since it's too complicated to make another column in that database. i could make a query and use it in the 'select case' code bit, just have to figure out a way to test it since i don't know who is temp and who's not..

 

[The_Doc_Man]

since i don't know who is temp and who's not..

You just identified the central point of your problem. Solve that and you have a way to solve the original question. And you don't have to add a column to the table. If you can JOIN the table to an employee table where that information is located, that would work just as well. But the table to be joined is merely the list of available labor codes, not the whole hour-entry table itself. You can use a JOIN in a .Rowsource, too. That's just SQL like a .RecordSource or .ControlSource.