By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,139 Members | 1,228 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,139 IT Pros & Developers. It's quick & easy.

Possible function creation

100+
P: 250
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim fCheckIn  As Boolean
  4. Dim fCheckOut As Boolean
  5. Private Sub CheckStatus()
  6. On Error GoTo EH
  7.  
  8.     CheckEmployee 1
  9.     CheckEmployee 2
  10.     CheckEmployee 3
  11.     CheckEmployee 4
  12.  
  13.     Exit Sub
  14. EH:
  15.     MsgBox "There was an error checking the status!  " & _
  16.         "Please contact your Database Administrator.", _
  17.         vbCritical, "WARNING!"
  18.     Exit Sub
  19.  
  20. End Sub
  21. Private Function CheckEmployee(Employee As Integer)
  22. On Error GoTo EH
  23.  Me.cmdCloseForm.SetFocus
  24.    fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
  25.         "[Employee] = " & Employee & " " & _
  26.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  27.         Format(Date, "yyyy-mm-dd") & "'"), False)
  28.  
  29.     fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
  30.         "[Employee] = " & Employee & " " & _
  31.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  32.         Format(Date, "yyyy-mm-dd") & "'"), False)
  33.  
  34.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  35.     'Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  36.     Me("cmdSignOut" & Employee).Enabled = fCheckIn And Not fCheckOut
  37.  
  38.     Exit Function
  39. EH:
  40.     MsgBox "There was an error checking the employee!  " & vbCrLf & vbCrLf & _
  41.         Err.Description & vbCrLf & vbCrLf & _
  42.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  43.  
  44.     Exit Function
  45. End Function
  46.  
  47. Private Function CheckIn(Employee As Integer)
  48. On Error GoTo EH
  49.     Dim strFilter As String
  50.  
  51.     'Filter by this employee
  52.     strFilter = "[Employee] = " & Employee & " " & _
  53.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  54.         Format(Date, "yyyy-mm-dd") & "'"
  55.     Me.Filter = strFilter
  56.     Me.FilterOn = True
  57.     Me.Employee = Employee
  58.     Me.signin = Date
  59.     Me.signintime = Time
  60.     Me.Refresh
  61.     CheckStatus
  62.  
  63.     Exit Function
  64. EH:
  65.     MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
  66.         Err.Description & vbCrLf & vbCrLf & _
  67.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  68.     Exit Function
  69. End Function
  70.  
  71. Private Function CheckOut(Employee As Integer)
  72. On Error GoTo EH
  73.  
  74.      Dim strFilter As String
  75.  
  76.     'Filter by this employee
  77.     strFilter = "[Employee] = " & Employee & " " & _
  78.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  79.         Format(Date, "yyyy-mm-dd") & "'"
  80.     Me.Filter = strFilter
  81.     Me.FilterOn = True
  82.     Me.Employee = Employee
  83.     Me.signout = Date
  84.     Me.signouttime = Time
  85.     Me.Refresh
  86.     CheckStatus
  87.     Exit Function
  88. EH:
  89.     MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
  90.         Err.Description & vbCrLf & vbCrLf & _
  91.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  92.     Exit Function
  93. End Function
  94.  
  95. Private Sub cmdSignIn1_Click()
  96. 'Chris
  97. CheckIn (1)
  98. End Sub
  99.  
  100. Private Sub cmdSignIn2_Click()
  101. 'Joe
  102. CheckIn (2)
  103. End Sub
  104.  
  105. Private Sub cmdSignIn3_Click()
  106. 'Justin
  107. CheckIn (3)
  108. End Sub
  109.  
  110. Private Sub cmdSignIn4_Click()
  111. 'Matt
  112. CheckIn (4)
  113. End Sub
  114.  
  115.  
  116.  
  117. Private Sub cmdSignOut1_Click()
  118. CheckOut (1)
  119. End Sub
  120.  
  121. Private Sub cmdSignOut2_Click()
  122. CheckOut (2)
  123. End Sub
  124.  
  125. Private Sub cmdSignOut3_Click()
  126. CheckOut (3)
  127. End Sub
  128.  
  129. Private Sub cmdSignOut4_Click()
  130. CheckOut (4)
  131. End Sub
  132.  
  133. Private Sub Form_AfterUpdate()
  134. 'Me.cmdCloseForm.SetFocus
  135. 'Me.Requery
  136. 'CheckStatus
  137. End Sub
  138.  
  139. Private Sub Form_Open(Cancel As Integer)
  140. Me.txt_dayofweek = Format(Me.txt_dayofweek, "dddd")
  141. Me.cmdCloseForm.SetFocus
  142. On Error GoTo EH
  143.  
  144.     Me.Filter = "Employee = 0"
  145.     Me.FilterOn = True
  146.     Me.cmdCloseForm.SetFocus
  147. CheckStatus
  148.     Exit Sub
  149. EH:
  150.     MsgBox "There was an error opening the Form!  " & vbCrLf & vbCrLf & _
  151.         Err.Description & vbCrLf & vbCrLf & _
  152.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  153.     Exit Sub
  154.  
  155. End Sub
  156.  
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signinmatt", , , "date1 = #" & date1 & "#"
  2. Forms!frm_signinmatt!signouttime = Time()
  3. Forms!frm_signinmatt!signoutdate = Date
  4. Forms!frm_signinmatt!signout = True
  5. 'MsgBox "Matt is now signed out. Good Bye!"
  6. lresponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
  7.  
  8. If lresponse = vbNo Then
  9.    On Error GoTo Command11_Click_Err
  10.  
  11.     DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
  12.  
  13.  
  14. Command11_Click_Exit:
  15.     Exit Sub
  16.  
  17. Command11_Click_Err:
  18.     MsgBox Error$
  19.     Resume Command11_Click_Exit
  20. Else
  21.  
  22. Me.Command29.SetFocus
  23. Me.Command7.Enabled = False
  24.   MsgBox "Matt is now signed out"
  25.    On Error GoTo closemattform_Click_Err
  26.  
  27.     DoCmd.Close acForm, "frm_signinmatt"
  28.  
  29.  
  30. closemattform_Click_Exit:
  31.     Exit Sub
  32.  
  33. closemattform_Click_Err:
  34.     MsgBox Error$
  35.     Resume closemattform_Click_Exit
  36.  
  37. End If
  38.  
  39. Me.Command29.SetFocus
  40. Me.Command7.Enabled = False
  41. 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!
Apr 3 '18 #1
Share this Question
Share on Google+
18 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,210
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:

Expand|Select|Wrap|Line Numbers
  1. ID  WorkType
  2. 1   Full Day of Work
  3. 2   1/2 Day Vacation
  4. 3   1/2 Day Sick
  5. 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?
Apr 3 '18 #2

100+
P: 250
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?
Apr 3 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,210
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....
Apr 3 '18 #4

100+
P: 250
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.
Apr 3 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,210
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....
Apr 3 '18 #6

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

Expand|Select|Wrap|Line Numbers
  1. Private Function CheckOut(Employee As Integer)
  2. Dim LResponse As Integer
  3. LResponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
  4.  
  5. If LResponse = vbYes Then
  6. On Error GoTo EH
  7.  
  8.      Dim strFilter As String
  9.  
  10.     'Filter by this employee
  11.     strFilter = "[Employee] = " & Employee & " " & _
  12.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  13.         Format(Date, "yyyy-mm-dd") & "'"
  14.     Me.Filter = strFilter
  15.     Me.FilterOn = True
  16.     Me.Employee = Employee
  17.     Me.signout = Date
  18.     Me.signouttime= Time
  19.     Me.Refresh
  20.     CheckStatus
  21.     Exit Function
  22. EH:
  23.     MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
  24.         Err.Description & vbCrLf & vbCrLf & _
  25.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  26.     Exit Function
  27.     Else
  28.  
  29.     DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
  30.    strFilter = "[Employee] = " & Employee & " " & _
  31.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  32.         Format(Date, "yyyy-mm-dd") & "'"
  33.     Me.Filter = strFilter
  34.     Me.FilterOn = True
  35.     End If
  36. 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!
Apr 3 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 3,210
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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Open acForm, "YourPopUpFormName"
  2. With Forms![YourPopUpFormName].Form
  3.     .Filter = (you have the right idea to create a filter string--put it here)
  4.     .FilterOn = True
  5. 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.
Apr 3 '18 #8

100+
P: 250
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.
Apr 3 '18 #9

100+
P: 250
OK, think i am closer- here is my CheckOut code:

Expand|Select|Wrap|Line Numbers
  1. Private Function CheckOut(Employee As Integer)
  2. Dim LResponse As Integer
  3. LResponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
  4.  
  5. If LResponse = vbYes Then
  6. On Error GoTo EH
  7.  
  8.      Dim strFilter As String
  9.  
  10.     'Filter by this employee
  11.     strFilter = "[Employee] = " & Employee & " " & _
  12.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  13.         Format(Date, "yyyy-mm-dd") & "'"
  14.     Me.Filter = strFilter
  15.     Me.FilterOn = True
  16.     Me.Employee = Employee
  17.     Me.signout = Date
  18.     Me.signouttime = Time
  19.     Me.Refresh
  20.     CheckStatus
  21.     Exit Function
  22. EH:
  23.     MsgBox "There was an error checking the Employee in!" & vbCrLf & vbCrLf & _
  24.         Err.Description & vbCrLf & vbCrLf & _
  25.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  26.     Exit Function
  27.     Else
  28.   Me.Employee = Employee
  29.     Me.signout = Date
  30.     Me.signouttime = Time
  31.     Me.Refresh
  32.     CheckStatus
  33.    DoCmd.OpenForm "frm_typeofday", , , "[Employee] = " & Employee & " " & _
  34.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  35.         Format(Date, "yyyy-mm-dd") & "'"
  36.     Me.Filter = strFilter
  37.     Me.FilterOn = True
  38.     End If
  39. 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?
Apr 3 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 3,210
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.
Apr 3 '18 #11

100+
P: 250
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!
Apr 4 '18 #12

100+
P: 250
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Frame27_AfterUpdate()
  2.  Select Case Frame27.Value
  3.       Case 1
  4.          txtOptGroup.Value = "1/2 Vacation"
  5.       Case 2
  6.          txtOptGroup.Value = "1/2 Sick"
  7.       Case 3
  8.          txtOptGroup.Value = "1/2 Personal"
  9.    End Select
  10. End Sub
  11.  

Then on the current event of the form i have:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  Select Case txtOptGroup.Value
  3.       Case "1/2 Vacation"
  4.          Frame27.Value = 1
  5.       Case "1/2 Sick"
  6.          Frame27.Value = 2
  7.       Case "1/2 Personal"
  8.          Frame27.Value = 3
  9.       Case Else
  10.          Frame27.Value = Null
  11.    End Select
  12. 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?
Apr 4 '18 #13

twinnyfo
Expert Mod 2.5K+
P: 3,210
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....
Apr 5 '18 #14

100+
P: 250
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"
Apr 5 '18 #15

twinnyfo
Expert Mod 2.5K+
P: 3,210
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.
Apr 5 '18 #16

100+
P: 250
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
Apr 5 '18 #17

twinnyfo
Expert Mod 2.5K+
P: 3,210
Yes. It should be numeric, corresponding to the value from the work types table (the index for the types of work). Make sense?
Apr 5 '18 #18

100+
P: 250
OMG- of course- got it-
Apr 5 '18 #19

Post your reply

Sign in to post your reply or Sign up for a free account.