Possible function creation

[didacticone]

I have been working on a timesheet database with the help of the bytes community! I have the main form displaying four employees names with 2 buttons under each name (sign in, sign out). The coding is the following:

Option Compare Database
Option Explicit
Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean
Private Sub CheckStatus()
On Error GoTo EH
 
    CheckEmployee 1
    CheckEmployee 2
    CheckEmployee 3
    CheckEmployee 4
 
    Exit Sub
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
 
End Sub
Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 Me.cmdCloseForm.SetFocus
   fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
    'Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
    Me("cmdSignOut" & Employee).Enabled = fCheckIn And Not fCheckOut
 
    Exit Function
EH:
    MsgBox "There was an error checking the employee!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
 
    Exit Function
End Function
 
Private Function CheckIn(Employee As Integer)
On Error GoTo EH
    Dim strFilter As String
 
    'Filter by this employee
    strFilter = "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Employee = Employee
    Me.signin = Date
    Me.signintime = Time
    Me.Refresh
    CheckStatus
 
    Exit Function
EH:
    MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function
 
Private Function CheckOut(Employee As Integer)
On Error GoTo EH
 
     Dim strFilter As String
 
    'Filter by this employee
    strFilter = "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Employee = Employee
    Me.signout = Date
    Me.signouttime = Time
    Me.Refresh
    CheckStatus
    Exit Function
EH:
    MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function
 
Private Sub cmdSignIn1_Click()
'Chris
CheckIn (1)
End Sub
 
Private Sub cmdSignIn2_Click()
'Joe
CheckIn (2)
End Sub
 
Private Sub cmdSignIn3_Click()
'Justin
CheckIn (3)
End Sub
 
Private Sub cmdSignIn4_Click()
'Matt
CheckIn (4)
End Sub
 
 
 
Private Sub cmdSignOut1_Click()
CheckOut (1)
End Sub
 
Private Sub cmdSignOut2_Click()
CheckOut (2)
End Sub
 
Private Sub cmdSignOut3_Click()
CheckOut (3)
End Sub
 
Private Sub cmdSignOut4_Click()
CheckOut (4)
End Sub
 
Private Sub Form_AfterUpdate()
'Me.cmdCloseForm.SetFocus
'Me.Requery
'CheckStatus
End Sub
 
Private Sub Form_Open(Cancel As Integer)
Me.txt_dayofweek = Format(Me.txt_dayofweek, "dddd")
Me.cmdCloseForm.SetFocus
On Error GoTo EH
 
    Me.Filter = "Employee = 0"
    Me.FilterOn = True
    Me.cmdCloseForm.SetFocus
CheckStatus
    Exit Sub
EH:
    MsgBox "There was an error opening the Form!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
 
End Sub
 

For the sign out part I need to be able to code in if someone is taking a half day or if they worked a full day.

Currently I am using the following code for the sign out button:

DoCmd.OpenForm "frm_signinmatt", , , "date1 = #" & date1 & "#"
Forms!frm_signinmatt!signouttime = Time()
Forms!frm_signinmatt!signoutdate = Date
Forms!frm_signinmatt!signout = True
'MsgBox "Matt is now signed out. Good Bye!"
lresponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
 
If lresponse = vbNo Then
   On Error GoTo Command11_Click_Err
 
    DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
 
 
Command11_Click_Exit:
    Exit Sub
 
Command11_Click_Err:
    MsgBox Error$
    Resume Command11_Click_Exit
Else
 
Me.Command29.SetFocus
Me.Command7.Enabled = False
  MsgBox "Matt is now signed out"
   On Error GoTo closemattform_Click_Err
 
    DoCmd.Close acForm, "frm_signinmatt"
 
 
closemattform_Click_Exit:
    Exit Sub
 
closemattform_Click_Err:
    MsgBox Error$
    Resume closemattform_Click_Exit
 
End If
 
Me.Command29.SetFocus
Me.Command7.Enabled = False
MsgBox "Matt is now signed out"

The code opens a form based on a query with the criteria of the specific employee. So i have 4 "typeofday" forms, each one has the criteria of the employee who is signing out. The typeofday form has 3 buttons, (1/2 Vacation, 1/2 Sick, 1/2 Personal). The code under each button is similar to above where it will open a form with the employee as the criteria and fill in a field called "timeofftype". I was wondering if there was a better way to do this? Any advice on how to clean up my code? Thanks guys!

[twinnyfo]

So, you really have four types of work days:

1. Full Day of Work
2. 1/2 Day Vacation
3. 1/2 Day Sick
4. 1/2 Day Personal

I would Have a small table (tblWorkTypes) with two fields: ID, WorkType. It would have four records:

ID  WorkType
1   Full Day of Work
2   1/2 Day Vacation
3   1/2 Day Sick
4   1/2 Day Personal

Create a field in your tbl_master based on this table.

Create a Combo Box for the main form, using the field in tbl_master as a control Source. When someone tries to sign out, filter by the employee (we've already been able to accomplish this). Then the WorkType Combo box will be for the current employee. Have them select their work type and then an additional button to set the sign out date/time and re-check the status of your employees.

Make sense?

[didacticone]

I think so... and I can work through it to see how it goes... would i need to index the WorkType field in tbl_master to the field in the new table?

Also, perhaps this is something of importance to mention. The computer i am using is touchscreen that is why i have been hesitant to stay away from the combo boxes as they are harder to use on a small touchscreen laptop... would it be possible to keep the bones of what I have with the yes/no msgbox and pop up my other form? My type of day form had three large buttons to press that were easy for the guys... what i have been trying, and failing so far was to have the form open to the current record based on who clicked sign out (since we have the coding in there) and when they click on the type of day it will then add the data to the table. Does that make sense?

[twinnyfo]

If you have a touchscreen, you can increase the font size of the combo boxes. No sense in keeping things tiny just because that is the default in Access....

Another option (again, since you have a limited number of employees), is to have an option group with four large buttons indicating the work day type. After someone tries to sign out, that option group becomes visible, they depress the option group button that identifies the work type and then they actually sign out. It would be a three-click series: push button for employee sign out, push button for work type, push OK button. It would all be pretty straight-forward, and I don't think too complex. Much of what you would need for that is essentially there.

Standing by to hepp....

[didacticone]

I am going to try both options... actually have been... but what I am discovering is, its making the main form cluttered. I can sense your hesitancy with my original idea, but the guys really like the simplicity of the new form popping up and all they have to do is click one button... is this doable without me having to use my sloppy method of referencing additional forms based on queries, etc.? I'm trying to find a balance between what you suggest and feedback from the end user if that makes sense.

[twinnyfo]

A pop up form will work. However, I would still suggest that rather than "fillling in the form" via VBA, that You filter the form (as you do the main form. Then selecting the work type would simply update teh table directly.

There are always multiple ways to skin a cat....

[didacticone]

Absolutley... but that is where i am struggling... what i have come up with so far is this:

Private Function CheckOut(Employee As Integer)
Dim LResponse As Integer
LResponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
 
If LResponse = vbYes Then
On Error GoTo EH
 
     Dim strFilter As String
 
    'Filter by this employee
    strFilter = "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Employee = Employee
    Me.signout = Date
    Me.signouttime= Time
    Me.Refresh
    CheckStatus
    Exit Function
EH:
    MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
    Else
 
    DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
   strFilter = "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    End If
End Function

Now i have bound the typeofday table to tbl_master but i am unsure if it is filtering properly on open since there is nothing to reference, also assuming it is, i was trying to code the buttons, but wasnt sure if a function would be the way to go or something else? I guess to simplify it there are two steps:

1) opening the type of day form in a filtered state, (which i may have done with the code provided?)

2) coding the three buttons accordingly

thanks!

[twinnyfo]

1. Try to think of it this way. When someone signs out, we need to first determine if they worked a full day or not. Whatever the result of that then gets saved to the table, along with the actual sign out date/time.

2. For the buttons, you can use an option group, which would be your easiest approach, because then the value of the option group would be saved directly to the worktype field.

So, using this second pop up form construction, approach it this way: once someone clicks the sign out button, since you are popping up a new form, there is no need to filter the main form. However, you already know how to filter the main form, yes? So, rather than filte rthe main form, we filter the popup form. This can be done from the main form like this:

DoCmd.Open acForm, "YourPopUpFormName"
With Forms![YourPopUpFormName].Form
    .Filter = (you have the right idea to create a filter string--put it here)
    .FilterOn = True
End With

Take a look and see if that makes sense.

Then, the employee selects the type of work day, click OK and they are done. There might be a few mop up operations required, but, again, one step at a time.

[didacticone]

To your points:

1) I believe this is being accomplished with my msgbox, right? If they click yes, then it enters the data in table accordingly. If not then it needs to put it the actual sign in date and time along with opening up the typeofday form using the sample code you provided. Sound good so far?

2) I have never used an option group before today, i was playing around with it a little earlier using toggle buttons. I noticed the default even is "Got Focus"- is this where i would input the type of day to fo into the worktype field? or do i need i another button that says if button x is true then worktype = y? Sorry this is a new concept for me.

[didacticone]

OK, think i am closer- here is my CheckOut code:

Private Function CheckOut(Employee As Integer)
Dim LResponse As Integer
LResponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
 
If LResponse = vbYes Then
On Error GoTo EH
 
     Dim strFilter As String
 
    'Filter by this employee
    strFilter = "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    Me.Employee = Employee
    Me.signout = Date
    Me.signouttime = Time
    Me.Refresh
    CheckStatus
    Exit Function
EH:
    MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
    Else
  Me.Employee = Employee
    Me.signout = Date
    Me.signouttime = Time
    Me.Refresh
    CheckStatus
   DoCmd.OpenForm "frm_typeofday", , , "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"
    Me.Filter = strFilter
    Me.FilterOn = True
    End If
End Function

This seems to be opening the typeofday form to the correct record. Now i'm just working with the option groups- i realized my last post was wrong... i bound the group to the field... the only issue is its returning a number instead of the words, im working on it though... does this seem like im on the right track though?

[twinnyfo]

Think of the option group as a Yes/No field on steroids. You can have a group of buttons which all indicate a different value. So, when you create your option group, the values you assign to your buttons will be 1-4, and that value is then saved in the appropriate field.

I'll have to describe more tomorrow.

[didacticone]

OK, i actually just used regular command buttons and it is working fine, wouldnt mind learning more about the option group though... i am going to make another post about another issue... basically trying to eliminate opening forms like i was doing... i will post the link here. here is the link to that... https://bytes.com/topic/access/answe...rm#post3815481

Thanks!

[didacticone]

So i worked on the option group and came up with a solution that i think works... let me know what you think...

For the afterupdate event of the frame of the group i have put in:

Private Sub Frame27_AfterUpdate()
 Select Case Frame27.Value
      Case 1
         txtOptGroup.Value = "1/2 Vacation"
      Case 2
         txtOptGroup.Value = "1/2 Sick"
      Case 3
         txtOptGroup.Value = "1/2 Personal"
   End Select
End Sub
 

Then on the current event of the form i have:

Private Sub Form_Current()
 Select Case txtOptGroup.Value
      Case "1/2 Vacation"
         Frame27.Value = 1
      Case "1/2 Sick"
         Frame27.Value = 2
      Case "1/2 Personal"
         Frame27.Value = 3
      Case Else
         Frame27.Value = Null
   End Select
End Sub

This returns the text value to the table and not the numeric. Is this an ok way to do it in your opinion?

[twinnyfo]

No. Build your option group with those values. Have the Option Group bound to the Work Type field. Then, when someone click a button in the Option Group, the value of the Work Type field is automatically updated. No need for any code....

[didacticone]

When i try to change the values from numeric to the actual value- i get an error- "the value you entered isnt valid for this field"

[twinnyfo]

I am not sure I follow you. Is the field on your table numeric (it should be)? Then, build your option group, assign the values, give names to the buttons, assign it to the field in the table.

[didacticone]

So i give them a label name- i dont pick a default- then the next screen asks for a value for each label name... that is making me keep it numeric

[twinnyfo]

Yes. It should be numeric, corresponding to the value from the work types table (the index for the types of work). Make sense?

[didacticone]

OMG- of course- got it-