Check for existing date

[didacticone]

Hi everyone,

I have created a database for employees to sign it and out. The functionality has been working great but I am running into an issue of people forgetting if the signed in or not; therefore they will sign in or out twice.

The way it is formatted is every employees name is show on a form with a sign in and sign out button under their name. When they click sign in, it it fills in a field called date1 with the current date, it also fills in a field called signindate with the current date, it also fills in the current time in a field signintime. When they click sign out, it fills in a field called signouttime.

Could someone help me with the code to disable the button for signing in until the following day or popup a msgbox if the current date is already in the database for their name; for example they already signed in. I've tried a few things but am still struggling, thanks for the help!

[twinnyfo]

Post #48, Line 49 should be:

Me.Filter = "Employee = 0"

I forgot to make that change.

Also, lines 25 and 30 should reference the Field [Employee2] (see Post #39).

Put a break point at the MsgBox in the EH. When it gets there, type ?Err.Description in the Immediate Window. Cut and paste the error.

[twinnyfo]

And.... my thought is that we are trying to set a boolean variable as a date.... If we were setting it as a number, we would be OK. But that is a minor problem.

If your tbl_master also has a primary key (an ID field), we can change the DLookup() to this:

    fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)

Also noticed in post #48: Line 58 should be line 51 - you should not have that statement in the EH, but in the main sub.

[didacticone]

Put a break point at the MsgBox in the EH. When it gets there, type ?Err.Description in the Immediate Window. Cut and paste the error.

Not sure what you want me to do here.

[twinnyfo]

You achieved the same by putting the Err.Description in the MsgBox.

These other changes need to be made to fix any additional problems.

[didacticone]

Sorry misunderstood.

"Data type mismatch in criteria expression"

[didacticone]

Here is the entire code, with the new changes:

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
 
   fCheckIn = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[ID]", "tbl_Master", _
        "[Employee2] = " & 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
 
    Exit Function
EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
 
    Exit Function
End Function
 
 
Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
 
    Me.Filter = "Employee = 0"
    Me.FilterOn = True
CheckStatus
    Exit Sub
EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
 
End Sub

[twinnyfo]

Make the changes I've identified and these errors should go away.

[twinnyfo]

And does this form work yet? Any additional errors?

[didacticone]

"Data type mismatch in criteria expression" on form open

[twinnyfo]

Which line is causing the error? Everything looks fine to me.

[didacticone]

It doesnt get to CheckStatus, it goes through both filters then right to the error message

[twinnyfo]

Change line 39 above to:

MsgBox "There was an error checking the employee!  " & vbCrLf & vbCrLf & _

And change line 55 to:

MsgBox "There was an error opening the Form!  " & vbCrLf & vbCrLf & _

This will more easily tell you where your code is breaking

[didacticone]

Same error in the same place

[twinnyfo]

OK - So, I know some of this is my fault and has to do with me not having your DB right in front of my face so I can't actually walk through every step of this code and I also know that some of this is because we were using your old paradigm of different data types in your table than what we changed things to, but there is also a certain degree of troubleshooting that just takes a look at things and realizes that when we change ONE reference from Employee to Employee2, that should apply to every reference to Employee throughout the Code, because we are referring to different fields, get it?

Yet another reference to the Field Employee that needs to be Employee2.

[didacticone]

Gotcha... i should have seen that it is now:

 Me.Filter = "Employee2 = 0"

... when i change it i now get the error:

"the multi valued field "Employee2" cannot be used in a where or having clause."

[twinnyfo]

the multi valued field "Employee2"

Trying to be as nice as possible here. This is a long thread...

Please re-read Post #39 very carefully:

Make sure the "Allow Multiple Values" check box is unchecked. Click "Finish".

[didacticone]

thats on me misreading that... sorry... i think im there now... i will try it out this weekend and post back on Monday... thank you for all of your help and info! Have a great weekend!

[twinnyfo]

I hope this is getting to the point where it works. More importantly, I hope that when it works, you can look at the code we have done and the changes we made to your table and understand what we have done and why it works together.

Enjoy your weekend, too!

[didacticone]

Thats the plan... you have given me a ton of reference material which is great. thank you once again.

[didacticone]

Hey... hope you had a good weekend... just wanted to show you what i've come up with:

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
 
   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
 
 
    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 Sub cmdSignIn1_Click()
Me.Employee = 1
Me.signin = Now
Me.Refresh
End Sub
 
Private Sub cmdSignIn2_Click()
Me.Employee = 2
Me.signin = Now
End Sub
 
Private Sub cmdSignIn3_Click()
Me.Employee = 3
Me.signin = Now
Me.Refresh
End Sub
 
Private Sub cmdSignIn4_Click()
Me.Employee = 4
Me.signin = Now
Me.Refresh
End Sub
 
 
 
Private Sub Form_AfterUpdate()
Me.Command16.SetFocus
Me.Requery
CheckStatus
End Sub
 
Private Sub Form_Open(Cancel As Integer)
Me.Command16.SetFocus
On Error GoTo EH
 
    Me.Filter = "Employee = 0"
    Me.FilterOn = True
    Me.Command16.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
 

I used the afterupdate event of the form, and also set the focus to a transparent control button because it wouldnt let me disable a button on click because it had the focus. It seems to be working but as we know and i have learned that doesnt always mean it is correct... do you have any critiques? Thanks as always!

[twinnyfo]

Well done, Young Jedi!

First, you have looked forward, which is good, but remember that at this point, we were only checking to see if the enable/disable code was going to work.

So, some guidance (rather than critique): Rather than creating a useless invisible control, why not simply set the focus on the Close button (which you should have one instead of using the "Close button" at the top right of the Form). I always use a close button and disable the form's built in close buttons, because that way I can control what happens when someone wants to close the form. This is just a good habit to get into.

On another positive note, your decision to have a procedure in the AfterUpdate makes sense. However, I will show you that this is not necessary.

Also, you may notice that the code you have for each of the Sign-In buttons is nearly identical. This is a hint. When it is that similar, there are ways to streamline this into one procedure. I can show you shortly.

My main concern at this point is very simple: using sample data, do your sign in/out buttons properly enable/disable as desired? This is our starting place. Remember, one step at a time.....

[didacticone]

The close button is a great idea. I will create that and set the focus there.

The procedures are still something I struggle with, i just go longhand so to speak on everything. I look forward to seeing this in action though.

Also, with the code I have, yes the buttons enable and disable correctly for when the form opens and when i click sign in and the data logs correctly.

I think one of my next steps and thoughts (and not trying to get ahead of myself, just thinking) is getting the sign out button to look for an existing record and add the sign off data there rather than create a new record. Perhaps a procedure as well?

[twinnyfo]

You are thinking rightly.... We will get there. Let me know when all things are set to move forward.

[didacticone]

So i have added the close button. and set the coding correctly.

[didacticone]

Here is the code:

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
 
 
    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 Sub cmdSignIn1_Click()
Me.Employee = 1
Me.signin = Now
Me.Refresh
End Sub
 
Private Sub cmdSignIn2_Click()
Me.Employee = 2
Me.signin = Now
Me.Refresh
End Sub
 
Private Sub cmdSignIn3_Click()
Me.Employee = 3
Me.signin = Now
Me.Refresh
 
End Sub
 
Private Sub cmdSignIn4_Click()
Me.Employee = 4
Me.signin = Now
Me.Refresh
End Sub
 
Private Sub Form_AfterUpdate()
Me.cmdCloseForm.SetFocus
Me.Requery
CheckStatus
End Sub
 
Private Sub Form_Open(Cancel As Integer)
Me.weekday = Format(Me.date1, "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
 

[twinnyfo]

So, now that the enable/disable works, let's think about what your sign in procedure should look like.

If you think about it, if the sign in button is enabled, it means the DB did not find a record for that employee for today, correct? So, when we click that sign in button, we know if we filter by the employee and today's date, no record will be returned, but if Allow Additions is set to true (which it should be by default), then, your form is actually showing a NEW record.

Looking at the basics of your SignIn procedures, you have the basics correct. However, the procedures are redundant, in that they repeat certain steps and the only thing that changes is the employee ID. Understand?

So, rather than create four similar procedures, create one prcedure that uses the information you need better. Your procedure will look like this:

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 = 4
    Me.SignIn = Now
    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

Now, rather than having a procedure for each check in button, set the OnClick value for each of your command buttons to: =CheckIn(1) (the number will be the actual employee ID.

Hopefully now, you can see why your AfterUpdate procedure is now obsolete.

Let me know how this works. I kept Line 6 with the Field "Employee" because I assume that is the code you are using and you said it works.

Thoughts for the future. If this is the case for signing in, what would it look like for signing out? There is an easy way to check for a Full day, as well....

[didacticone]

OK, it works but only for employee whose index is 4

[didacticone]

Also I have tried various things for signing out... i tried to you docmdgotorecord, i tried dlookup but cant seem to figure out how to get it to go to the record based on the date. it just keeps adding a new record. its so frustrating!

[twinnyfo]

I also just realized something important. In your Code Block from Post #75, Line 35 should be:

Me("cmdSignOut" & Employee).Enabled = fCheckIn And Not fCheckOut

Do you understand why? We only want people to be able to sign out after they have already signed in. So, this is what it will look like. If the employee has not signe din, the Sign In button will be enabled and the sign out button disabled. After they sign in, they sign in button is disabled and the sign out button is enabled. After they sign out, both buttons will be disabled.

Hope this makes sense. We just want to prevent users from being able to enter wrong information at all costs.

[twinnyfo]

Concerning Post #77, I made a mistake in my code. I want you to trouble shoot my mistake, because it should be obvoius from the results of your post. The solution is obvious (I hope), but this will help you look at your code to see where things are going wrong.

[didacticone]

im assuming its line 11,

Me.Employee = 4

That should not be 4, it should be a reference to something? The id? I was thinking that but couldnt quite figure it out what it should be.

[twinnyfo]

Great! You are on the right track! Remember that this function uses a variable fed to it. That variable is: ???????

(Hint: it's also used in Line 6).

[didacticone]

wow i shouldve seen that it should be:

Me.Employee = Employee

[twinnyfo]

You have just successfully performed troubleshooting!

Now, what's the Functoin for Checking going to look like?

It might be a while before I can respond, as I have some things going on here....

[didacticone]

I have no idea!!!! I have tried so many different things and can't seem to get it... if you can maybe point me in a direction I can try and see what I can figure out. and of course bro... sorry to keep bothering you but your a great teacher!

[didacticone]

actually i think you gave me everything i needed... hows this:

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 = Now
    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

[twinnyfo]

Bravo! I will add one more detail tomorrow!

Very well done -- and you thought you were a novice......

[didacticone]

I am most certainly a novice... I have one more thing that i have been working on, and can start a new thread in the morning if you would like, since this one is quite long lol... in my original code i had a new form that would open if the guys only worked a half day... there are three different types of half days and i was playing around with some ideas but thing i need your advice. let me know what you think and if you want me to start a new thread... your the man for helping me with all of this. i have learned so much.

[sultanhameed]

I think "Data type is not matching in criteria expression" on form open. You may take a look.

[NeoPa]

DidacticOne:
I have one more thing that i have been working on, and can start a new thread in the morning

That sounds like a good idea. I'm sure Twinny won't fail to see it and/or respond.

[twinnyfo]

didacticone,

Does this code now work (I know we have not addressed the "full day" issue yet)?

[didacticone]

Yes it does! It's great. I have posted the next conquest here:

https://bytes.com/topic/access/answe...on#post3815462

Thank you!!!

[twinnyfo]

Also, I noticed from your follow-on thread that you have hard-coded the function calls to CheckIn/CheckOut for each of the buttons in VBA.

This does work. My intent in Post #76 was that the call would occur in the control itself. When you look at the event procedures in your properties window for your controls you will see [Event Procedure] in the field for your OnClick events. My intent was that you type in the call to the function directly: =CheckIn(1). This will keep your VBA page a bit neater.

Hoep this makes sense.

[NeoPa]

This may not be a 'Best Answer' for the thread, as such, but is still very good advice. Even experienced Access developers often overlook this capability to simplify the code.

Nice work Twinny :-)