Check for existing date

**twinnyfo** · Mar 22 '18, 12:54 PM

1. How does your database know "who" is signing in? Does the employee select their name froma dropdown box (or some other means)?

2. Do any employees ever work through midnight? That is, do you have some shifts that might sign in during the evening and sign out during the morning? This would determine how to calculate whether to allow them to re-sign in.

Thanks!

**didacticone** · Mar 22 '18, 12:58 PM

hey twinny! so the form has all the employees names with a sign in and sign out button under each name. the code behind sign in is :

Code:

DoCmd.OpenForm "frm_signin", , , , acFormAdd
Forms!frm_signin!Employee = "Joe Smith"
Forms!frm_signin!date1 = Me.date1
Forms!frm_signin!signintime = Time()
Forms!frm_signin!signindate = Me.date1
Forms!frm_signin!signin = True
DoCmd.Close acForm, "frm_signin"
MsgBox "Joe is now signed in."

**twinnyfo** · Mar 22 '18, 01:06 PM

Welcome back, didacticone!

This generates a couple more questions....

1. Is the sign out the same procedure?

2. Are sign-ins and sign-outs saved to the same table?

Comments:

There is a lot of redundant data on your sign-in form. If you have the employee's name and use Now() for their sign-in, you have the date and the time (MS Access stores Dates/Times as a Date and a Time). No need for a sign-in Yes/No field, as a value in the sign-in date/time indicates a sing-in.

Not the most efficient way to accomplish this, but we will leave it as is, for now.

**didacticone** · Mar 22 '18, 01:13 PM

OK so for sign out, this is what i have:

Code:

DoCmd.OpenForm "frm_signinmatt", , , "date1 = #" & date1 & "#"
Forms!frm_signinmatt!signouttime = Time()
Forms!frm_signinmatt!signoutdate = Date
Forms!frm_signinmatt!signout = True

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
  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
MsgBox "Matt is now signed out"
End Sub

The sign in and sign out is all stored in the same table (tbl_master).

And i actually do understand some of the redundancies... as you know i have been working on this for a while and am still learning/cleaning a lot up... there were some ideas i had that i ditched but never cleaned up the coding yet... this has been a great DB to create and learn all this stuff

**twinnyfo** · Mar 22 '18, 01:35 PM

More questions:

1. Still don't know if people work over night?

2. It appears that each employee has their own signout form (DoCmd.Close acForm, "frm_signinmatt ")?

3. What determines a "full day"? Number of hours? The employee saying so?

More generic thoughts:
Assuming that no employees work over night, all this could be accomplished on one form, with tbl_master as its record source. An employee could select their name from a drop down menu. The form filters records by the Employee's name and today's date. This would take the employee to a new record if there was no sign in for today, and the sign in button would be enabled. If they have already signed in, the sign in button is disabled and the sign out button is enabled.

The sign in/out buttons simply enter a Date/time (plus the employee name for sign in). Signing out would determine a "full day"--if it is based on number of hours worked.

I don't know if that is the "intent" here, but this would highly simplify something that appears to be overly complexified.

Let me know your thoughts.

**didacticone** · Mar 22 '18, 01:54 PM

OK, so no, employees never work overnight, sorry forgot to clarify that.

Yes, the main sign in form does not have a record source. I have laid it out with the 4 employees names and a sign in and out button under there name that they just tap. I have (for some reason) created a separate form for each employee based on a query with the only criteria being that employees name. So each button fills in those forms rather than just add the data right to the table. In hindsight cant understand my thinking here.

A full day is when the employee says so.

Understanding that i probably over complicated things here, your concept of creating a new record if there isn't one for a certain employee on the current date is in my coding and working. I guess my question would then be your next statement, how to get coding in there to enable the sign in button if its a new record for that date and then disable it and then enable the sign out button. And then when would sign in be enable again? After they click sign out?

**didacticone** · Mar 22 '18, 01:58 PM

OK, having started to tinker around, i realized i dont want the main form to have a record source. i dont want multiple records to navigate. it is a simple sign in/out form.

Im guessing that is why i created the forms, but in theory i only need one since i am coding in the names anyway... that make sense to you?

**didacticone** · Mar 22 '18, 02:06 PM

OK maybe not the cleanest, but this worked

Code:

DoCmd.OpenForm "frm_signinmatt"
If Forms!frm_signinmatt!date1 = Date Then
MsgBox "you have already signed in today"
DoCmd.Close acForm, "frm_signin"
DoCmd.Close acForm, "frm_signinmatt"
Else

DoCmd.OpenForm "frm_signin", , , , acFormAdd
Forms!frm_signin!Employee = "Matt Smith"
Forms!frm_signin!signintime = Time()
Forms!frm_signin!signindate = Me.date1
Forms!frm_signin!signin = True
Forms!frm_signin!date1 = Me.date1
DoCmd.Close acForm, "frm_signin"
DoCmd.Close acForm, "frm_signinmatt"
MsgBox "Matt is now signed in."
End If

let me know what youre thinking, honestly lol... just want you to see im trying to figure it out

**didacticone** · Mar 22 '18, 02:21 PM

or this, for sign in button:

Code:

DoCmd.OpenForm "frm_signin", , , , acFormAdd
Forms!frm_signin!Employee = "Matt Smith"
Forms!frm_signin!signintime = Time()
Forms!frm_signin!signindate = Me.date1
Forms!frm_signin!signin = True
Forms!frm_signin!date1 = Me.date1
DoCmd.Close acForm, "frm_signin"
Me.Command29.SetFocus
Me.Command6.Enabled = False
Me.Command7.Enabled = True
MsgBox "Matt is now signed in."

and for sign out button:

Code:

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.Command6.Enabled = True
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.Command6.Enabled = True
Me.Command29.SetFocus
Me.Command7.Enabled = False
MsgBox "Matt is now signed out"

That seems to work to enable/disable the buttons.

**twinnyfo** · Mar 22 '18, 02:49 PM

Concenptually here:

Form: frm_SignIn
Record Source: tbl_Master
Allow Additions: True
Combo Box: cboEmployee (Row Source: your list of employees, but unbound to underlying table)
Text Box: txtSignIn (Control Source: SignInDateTime) *
Text Box: txtSignOut (Control Source: SignOutDateTime )*
Both of the Sign In/Out text boxes will be disabled and locked (view only)
Chcek Box: chkFullDay (Control Source: FullDay)*
Command Button: cmdCheckIn
Command Button: cmdCheckOut
*Assuming these Fields are in tbl_Master

When the Form opens, have this in the OnOpen Event:

Code:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH

    Me.cboEmployee = ""
    Me.Filter = "Employee = '" & Me.cboEmployee & "'"
    Me.FilterOn = True

    Exit Sub
EH:
    MsgBox "There was an error initializing the Form!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

When you open this Form, because it has a filter of Employee = '' it will filter no records. However, because AllowAdditions is True, it is displaying a new record.

To go along with this, you need something to enable/disable your controls. WARNING: Learning Environment:

Code:

Private Sub Form_Current()
On Error GoTo EH

    'What will you put here?
    'Hint: Me.NewRecord is a
    'Boolean value (true/false)
    'that indicates whether the
    'form is sitting on a new record

    Exit Sub
EH:
    MsgBox "There was an error setting the controls!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

When an Employee Selects a name from the Combo Box, we want to bring up 1) a new record if the employee has not signed in or 2) their record for today if they have signed in. WARNING: Learning Environment:

Code:

Private Sub cboEmployee_AfterUpdate()
On Error GoTo EH

    'What will you put here?
    'Hint: much of what you
    'need is already here in 
    'some form or another. 
    'The tricky part is how to
    'get [B][U]today's[/U][/B] record? 

    Exit Sub
EH:
    MsgBox "There was an error selecting the record!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Checking in?

Code:

Private Sub cmdCheckIn_Click()
On Error GoTo EH

    'What will you put here?
    Me.Refresh

    Exit Sub
EH:
    MsgBox "There was an error signing in!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

The Me.Refresh will essentially re-filter your form based on the current data.

Checking out?

Code:

Private Sub cmdCheckOut_Click()
On Error GoTo EH

    'What will you put here?
    Me.Refresh

    Exit Sub
EH:
    MsgBox "There was an error signing Out!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

Code wisely, Young Jedi....

I'm always here to hepp work through the snags!!!

**didacticone** · Mar 22 '18, 05:22 PM

Wow... thats a lot to digest lol... thank you so much for all of that. I will try to work it out... the one thing i think i want to stick with though is the form i have currently... im on a small touch screen laptop and I am trying to make it as simple for a couple of employees who struggle with computers, (like me with coding lol). The feedback so far is they like the one screen with their name already there and the big buttons underneath it. I will try to incorporate your suggestions into what i have and get back to you... once again thank you for all the help and wish me luck.

**twinnyfo** · Mar 22 '18, 05:59 PM

There may be some programmatic ways for the buttons you currently have to be disabled for checking in/out, without using any additional forms.

Clicking one of the employee buttons could filter the form properly and enable/disable the requisite controls. All would still be accomplished on one form, and still maintain the look and feel of what you have now.

**didacticone** · Mar 22 '18, 06:08 PM

What i did is left the main form without a record source. i have it load with the the sign out buttons disabled and the sign in buttons enabled. when sign in is clicked it then becomes disable and enables sign out. when sign out is click it disables as well. i have task scheduler running nightly to close and open the database and the signin form loads up when the db is opened with the access app minimized. i know this isnt the best way to do it but it works and allows me to keep using it while i try to dissect your code and see if i can link it record wise to enable/disable the buttons.

**twinnyfo** · Mar 22 '18, 06:28 PM

It will be identical to using a combo box. Instead of AfterUpdate, it is OnClick. Since you only have a few employees, you can do this relatively simply. The OnClick for each person's button would direct the code to a function on your Form: =EmployeeTime(" EmployeeName"). That will run the following code, which would be identical to Post #11, Code Block #3:

Code:

Private Function EmployeeTime(Employee As String)
On Error GoTo EH

    'What will you put here?
    'Hint: much of what you
    'need is already here in
    'some form or another.
    'The tricky part is how to
    'get today's record?

    Exit Function
EH:
    MsgBox "There was an error sellecting the Employee!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

When an employee clicks their name, either the check in button will be enabled and the Full Day check box and Check out are disabled or vice versa.

You can even set it so that if someone has already checked out for the day, that their name button is disabled.

Disabling of the employee buttons will use a simple lookup in the table.