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]

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]

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 :

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]

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]

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

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]

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]

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]

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]

OK maybe not the cleanest, but this worked

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]

or this, for sign in button:

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:

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]

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:

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:

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:

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 today's 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?

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?

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]

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]

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]

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]

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:

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.

[didacticone]

OK i see what you are saying theoretically... ill mess around with it and see how far i get. when you say simple lookup to disable the button im sure that will take me a while haha

[twinnyfo]

Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean
 
fCheckIn = Nz(DLookup("[signindate]", "tbl_Master", _
    "[Employee] = '" & "Employee Name" & "' " & _
    "AND [signindate] = #" & Date & "#"), False)
 
fCheckOut = Nz(DLookup("[signoutdate]", "tbl_Master", _
    "[Employee] = '" & "Employee Name" & "' " & _
    "AND [signoutdate] = #" & Date & "#"), False)
 
cmdButtonMatt.Enabled = Not (fCheckIn And fCheckOut)

[didacticone]

hey... sorry i was away for a few days... so im trying out your suggestions, a very naive monday question, with what you provided, would it be one button for sign in/out or two? I currently have two and just want to get an idea for where youre leading me. This is what i have under the sign in button:

Private Function EmployeeTime(Employee As String)
Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean
 
fCheckIn = Nz(DLookup("[signindate]", "tbl_Master", _
    "[Employee] = '" & "Employee Name" & "' " & _
    "AND [signindate] = #" & Date & "#"), False)
 
fCheckOut = Nz(DLookup("[signoutdate]", "tbl_Master", _
    "[Employee] = '" & "Employee Name" & "' " & _
    "AND [signoutdate] = #" & Date & "#"), False)
 
btn_signin.Enabled = Not (fCheckIn And fCheckOut)
 
On Error GoTo EH
 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"
 
MsgBox "Matt is now signed in."
 
    Exit Function
EH:
    MsgBox "There was an error sellecting the Employee!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Function
End Function

[twinnyfo]

When your form opens, just have a combo box that the employee chooses their name from. I know you say the employees may not be very computer savvy, but we should be able to safely assume that they can pick their name out of a drop down list.

When a name is selected, determin if they have done any signing in/out today, using the example in Post #17.

If they have neither signed in nor out for the day, then enable the sign in button, which simply signs them in. Again, best way to do this is to either bind the form to tblt_master--or just have code run in the background that updates the table and refreshes the sign in/out buttons. No need to open additional forms, automate filling them and then close.

If they have signed in but not out, show the sign in time and enable the sign out/full day controls. This is most easily done by binding the table to tbl_master.

If they have signed in and out, tell them so.

Hope this hepps!

[didacticone]

So, I'm totally lost at the moment. Having a hard time. I think, since I'm obviously not picking this up quickly, and I would like to stick with my forms layout as I have gotten positive feedback, I will just leave it the way it is. I know its not pretty or efficient but it works. I would love to have it automate the enabling and disabling of the buttons based on if they have signed in/out but I can't seem to get that to work, so my toggle work around will have to do for now. I wish I had the coding knowledge you did but I feel I have learned some things and will continue to as I dissect the database once I have time. If you have any other tips to add with my current format it is greatly appreciated, otherwise i totally appreciate all your time spent helping me!

[twinnyfo]

My best advice to you at this point is to just keep it simple.

The more complx and compicated things are, the more apt they are to break or to cause confusion in the future.

Consider this simple example: let's say the company goes from four employees to five. You now have t reprogram your DB, add buttons, customize code, test it and make sure it works exactly like it works for the others. That doesn't sound so bad. But what about ten employees?

On the other hand, if you develop your one form properly, and your company adds or removes as many employees as it wants, all you have to do is update a list of employees.

However, we can sometimes get caught up in a particular paradigm, just because it "works." This is not to nit-pick. I've done the same things many times in the past. Sometimes, you need to break your working paradigms in order to develop a better paradigm.

Your work can get done on one form with very few lines of code. I'm willing to work through that with you.

[didacticone]

I totally get your point... attempt to think of all the future variables. I learned that early on while making databases. The fortunate thing for me where I work and my group is expansion is not a possibility. We are unique in that regard and with the positive feedback I have received so far on the form design i am hesitant to change. I hope you understand my reasoning, change isnt easy for everyone but this has been received well and i am very happy about that. If you want me to start working on it the way you recommend and see how far i get and maybe we work through it together if youre willing, im willing to do it just for the learning experience but i think the practical application at my particular job, the current format works better. let me know you what you think. thanks again!

[twinnyfo]

Just as another thought--you can make changes without changing the look of your form. I am not talking about aesthetics. I am talking about good practices within DB design; it doesn't matter what it looks like. Opening another form, moving to a new record, filling that record and closing that form (which is probably not even visible for a brief moment) is simply not a good design. Even if you don't bind the form, why not just use a recordset? And, your design still doesn't address your primary question very well at all.

You can keep things the way they are, as that is your choice.

[didacticone]

No dont get me wrong, I want to make the code more efficient. My instincts just go to what gets it done and not the best code since I am not formally trained. I am going to work on just using a recordset like you said... i agree that is a cleaner method. My original issue of enabling and disabling the buttons I am still clearly struggling with. I want it to work, even if it is a drop down, I would just like to learn how to do it. I have been stepping through all of the code like you showed me, thats a cool trick that I didnt know. Your advice is greatly appreciated!

[twinnyfo]

How many buttons do you have on your main form? What are their names? Exactly what do you want to happen (to all buttons, all text boxes, etc.) in all situations (sign in when they have yet to sign in; sign in after they've signed in; sign out without sign in; sign out after sign in; sign out after sign out)? What code have you specifically tried to accomplish this?

However, a better question is, How are you approaching this project/problem? Don't look at it as "all these things you have to do to make it work". Think of it as many, very small problems that need to be solved, one at a time.

Develop a flow of what you want to happen (not in code--just a list). Try to include as many aspects of what you want to do (and what you want the code to do). After you generate your list of things that must happen, put them in a logical order: A must occur before B, but C can't happen until D has happened. F might be a tiny step, but relies on E in order to get done. Then, work through the issues, piece by piece, until all function together correctly.

This is how you approach problem solving in your projects.

Again, I am happy to assist with each of your little pieces--even if you must begin a new thread for each piece. This is what this forum is for.

[didacticone]

I think a lot of the questions you have asked have been answered, as the database functions, albeit with perhaps not the most efficient design, but again, i'm working on something that is not my forte so just getting it to work is an accomplishment. If there is a way to refine it, i am willing to learn it. Putting the sloppy existing code behind us for a minute here is where im at:

the form has eight butttons- a sign in and sign out buttton for each of the 4 employees

when the sign in button is clicked it populates the data i want and EVENTUALLY (LOL) gets it to the table. it then disables the sign in button.

when the sign out button is clicked it checks the current date against the date1 field and if there is a match it pulls that record and fills in the date from my code. then the sign out button is disabled.

when the form is opened it enables the sign in button and disables the sign out button.

What it would like to happen is when the database opens it checks if there is a value in the date1 field for each employee that matches the current date.

If there is a match (which there never should be) a msgbox should pop up.

If not then only the sign button is enabled and the sign out button is disabled.

Once the sign in button is clicked i want it to populate the data i have in my code now.

Then the sign in button should become disabled and the sign out button should be enabled.

When the sign out button is clicked i want it to check the current date against the date1 field and if a match is found pull that record and populate the data according to my existing code and then disable the sign out button.

I think i am close to this... but no matter what i've tried i cant seem to get it to work.

[didacticone]

Also i have cleaned up the code a little i think- my sign in button now looks like this:

Private Sub Command6_Click()
 
Me.Recordset.AddNew
Me.Recordset("employee") = "Matt Smith"
Me.Recordset("signintime") = Time()
Me.Recordset("signindate") = Me.date1
Me.Recordset("signin") = "true"
Me.Recordset("date1") = Me.date1
Me.Recordset.Update
MsgBox "Matt is now signed in."
End Sub

Is that cleaner?

[twinnyfo]

I know you are very concerned about binding your form to the table; but if you did, this woul dbe your code:

Private Sub Command6_Click()
 
Me.txtSignin = Now()
MsgBox "Matt is now signed in."
 
End Sub

And the Message Box is not really required, because if you display the signin date/Time, and disable the sign-in button it is already a visual cue the employee has signed in.

However, we will work with what you have and how you want it. I have some things I must attend to today, so I wil get back to you later today.

We will work through this.

[didacticone]

OK sounds good... thanks again

[didacticone]

So when you have a chance- have bound the form to the table and under the sign in button i have the following:

If DLookup("signindate", "tbl_master", "signindate = #" & Date & "#") And Me.Employee = "Matt Smith" Then
'If Date = DLookup("date1", "tbl_master") And Me.Employee = "Matt Smith" Then
MsgBox "you have already signed in today"
Else
DoCmd.GoToRecord , , acNewRec
Me.Employee = "Matt Smith"
Me.signintime = Time()
Me.signindate = Me.date1
Me.signin = True
Me.date1 = Date
MsgBox "Matt is signed in"
Me.Command39.SetFocus
Me.Command6.Enabled = False

That appears to be working...

For the sign out button I have:

If DLookup("signindate", "tbl_master", "signindate = #" & Date & "#") And Me.Employee = "Matt Smith" Then
 
    Me.signouttime = Time()
    Me.signoutdate = Date
    Me.signout = True
    Me.Command39.SetFocus
    Me.Command7.Enabled = False
    MsgBox "Matt is now signed out."
Else
    MsgBox "Matt has not signed in today"
End If

That doesnt seem to be working- no matter what it just pops up the msgbox saying matt has not signed in today.

Am i close, or closer? lol

[twinnyfo]

First, I think you are getting closer to understanding a few things. So, anything new that you learn and can put into practice is a good thing, right?

Let's begin by making a new form for signing in. You can still keep the old one that works, but I want to work through some concepts with you that should help in the long run.

Next, humor me by adding two fields to tbl_master:

Field     Type         Format
SignIn    Date/Time    General Date
SignOut   Date/Time    General Date

I ask this because I want to show you how to streamline your data, and thus, your code.

Now, you've described what your sign in form "looks" like, so we will go with what you have. So, on this new form create those same eight buttons (one for sign in and one for sign out for each employee). However--and this is very important--you will want to give them very specific names. I can only assume that you have the names of your Employees in Your Table? You must use the name of the employee as found in your table in the name of your command buttons. So, your sign in buttons will all be named cmdSignIn[Employee Name]; your sign out buttons will all be named cmdSignOut[Employee Name]. Example: cmdSignInJohn Smith; cmdSignOutEggbert Schmuckatelli, etc. Make sense, so far? Just make sure none of your employees have any special characters like apostrophes or commas in them--if so, change their names in the DB.

Also add a Close button so you can close the form, because there is some testing we have to do.

Bind the form to tbl_master, as before. Remember, when the form first opens, we don't want to have any records available for editing (and we will get to more later on), but we will filter the form based upon the Employee Name, but on one that doesn't produce any records. So, the VBA behind your form, to begin with will look like this:

Option Compare Database
Option Explicit
 
Private Sub Form_Open(Cancel As Integer)
On Error GoTo EH
 
    Me.Filter = "Employee = ''"
    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

Do you see why we do this? We Filter by something that returns no records, but because we have to text boxes associated with any fields in the table, this is immaterial.

So, remember that I said we break this down into smaller problems? Let's start with the problem of enabling/disabling the sign in/out buttons. You will now see why we have given these command buttons such strange names.

At this point, you could write code that checks the status of each employee and then enables/disables buttons for each employee. However, doing such would be redundant (you would be duplicating code that you can consolidate easily). So, how do we do that?

We will create a function that can be called multiple times that will perform this function for you. Granted, you will have to call this function for each employee you have, and in a perfect world, the DB would go out and find your employees, but, since you have a small operation, for now, this will suffice.

However, if you think about this in greater depth, you may start to ask yourself a question.... What I really want is a "real-time" assessment of the employee's sign in/out status. So, wouldn't I want to check this status anytime anyone clicks a button?

Right you are! And, if you break this into even smaller pieces, what we are doing is not just checking the status of an employee, we are checking the status of each employee. So, what you want is two functions: One function will check the status of an employee; the other function asks that function to check the status of each employee. Let me show you how that is done.....

Remember our Boolean variables from Post #17?

Dim fCheckIn  As Boolean
Dim fCheckOut As Boolean

Well, rather than declaring them within a procedure, lets declare them in the Form. We do this by placing their declaration above the other procedures, but below the first two lines of code:

Option Compare Database
Option Explicit
 
Private fCheckIn  As Boolean
Private fCheckOut As Boolean

Rather than using the Dim statement, we use the Private statement, which means that these variables are available for use anywhere in the VBA module--not just one particular procedure. Since we will be using these variables throughout this form, this is why we do it.

Now, we need to check the status of each of our employees. Let's create a procedure called CheckStatus, that will send the list of employee names to your function (which is yet un-built) that will check their status and determine what to do with each button. Conceptually, all you need this tiny procedure to do is check the status of each employee by name. Thus, when you add employees (or remove employees), this is the only VBA you need to change (besides adding/removing buttons). This is all you need:

Private Sub CheckStatus()
On Error GoTo EH
 
    CheckEmployee "Joe Smith"
    CheckEmployee "Eggbert Schmuckatelli"
    CheckEmployee "Harold Umptyfratz"
 
    Exit Sub
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _ 
        vbCritical, "WARNING!"
    Exit Sub
End Sub

Can you see how this is more efficient (conceptually) already??

But you might say, "Hey, I don't have a Function named CheckEmpoyee yet!" That's OK--we'll make that right now! Look closely at the VBA below:

Private Function CheckEmployee(Employee As String)
On Error GoTo EH
 
    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = '" & Employee & "' " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "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 status!  " & _
        "Please contact your Database Administrator.", _ 
        vbCritical, "WARNING!"
    Exit Function
End Function

Remember in the previous block we were sending the Employee name to the function in question. That function takes that employee name, checks their status in tbl_master. Also remember that tbl_master has two new fields, SignIn and SignOut, which are date/Time fields, and hold the date and time, not just the date. But, by using the Format() function, we are converting those date/times into text strings which represent the date only, and compare it to today's date, in the same format.

Then, a neat little trick I learned from Microsoft many years back, notice your command buttons can be referenced with a combination of text and variable: Me("cmdSignIn" & Employee). This is why you needed to name your command buttons so diligently! If someone has checked in today, the sign in button is disabled. If someone has checked out today, the sign out button is disabled.

Now, just add this line to your OnOpen event we created earlier:

CheckStatus

You can put that right after the filter statements. This will check the status of your employees!

Remember: small chunks at a time. Build this slowly, and work through understanding what is going on before you go to any future steps. Let me know how this works. Practice adding some sample data in tbl_master, and let me know what the results are. any errors or surprising results, we can work through tomorrow.

[didacticone]

Sorry i came down with a bug yesterday and was away... this was a nice gift to return to. Thank you for this. I am going to work on it today... I will not be back until Monday, so that gives me plenty of time lol... if i run into any initial issues i will let you know. I did have one question... you said " I can only assume that you have the names of your Employees in Your Table?" What do you mean by this? Their names were getting entered only when they signed in... they werent field names... so just a little confused there... thanks again!

[twinnyfo]

In a properly structured DB (see this Article on DB Normalization), you would have a Table with just your Employess listed. Example:

tblEmployees

ID   EmplyeeName   EmployeeAddress  ... etc.
1    Joe Schmoe    123 Main St.
2    Mel Function  321 What Rd.
3    Risky Walrus  987 Over Dr.

Then, rather than saving the Employee's Name to any related tables, you only save the index. This saves on DB resources and helps with look ups, combo boxes and everything else.

Based upon your description, this is not how you are listing your employees, but you save the actual name of the employee in your table. As long as the employee's name is exactly the same, every single time, you are safe, but any time a name might get entered incorrectly, you will have issues.

This is why I would always recommend to have the employee select their name from a combo box, which would then do initial determinations on whether they have signed in/out. Then, rather than having to hard code names, you just have a reference to the employee index from the combo box. Adding an employee (and having a fully functional DB) is as simple as adding a name to the employees table. I know you are not looking to expand, but what if someone quits and you have to hire, not an additional employee, but a different employee? I am trying to save you from headaches with such issues.

You may say, "Well, it's easy to just reprogram these three lines of code here and rename some objects here--it won't take but 15 minutes to do these updates." True, but we musn't be satisfied with a project that works just because it works. We should think about proper project development and adhering to good, sound, tried and true development principles.

Anecdotally, I am completely self-taught. However, everything I learned, I learned the hard way. I kept doing things the wrong way, but kept doing them that way "because they worked". After years of struggling and scratching my head, I started looking at my DB structure and code and said, "Hey, what if I do this...? I can streamline this section by doing it another way!" So, over time, I gradually ironed out very many of my "wrong" ways of doing things for the "right" way of doing things. Having come upon certain road blocks and hurdles, I came across Bytes. Lo and behold, many of my "tried-and-true-practices-that-I-learned-the-hard-way" were actually long-established DB design and development paradigms universally accepted as best practices.

Hmmphh!! Why didn't anyone tell me how to do this in the first place? I could have saved thousands of hours of re-work and many bottle of Tylenol!

Folks on this forum must think I just keep harping on the same old things over and over again. I do--because I want to prevent others from going down the road of mediocrity with a project that "works". There are certain principles which, if known and understood, will allow you to go beyond your current level of proficiency and let you explore other areas of your craft.

You may not see a "need" for that right now--but I guarantee you that sooner or later, those who have you creating the employee DB will eventually ask, "Hey! I was wondering if you could get the db to do...." And it doesn't matter what it is. I want you to have the confidence to be able to help your boss (or yourself, if you are the boss).

Enjoy your weekend!

[didacticone]

OK, totally understood, and feel like that is my motto as well... "if it works, it works"... but i dont want that to be it... understanding the proper way is awesome and also like you said saves on the tylenol and typing redundant lines of code... OK so i actually do have a seperate table calles "tbl_employees" structured just like you showed except with just the names, no addresses or anything... how does this factor in? Also, bear with me because i haven't even read through your entire post yet, but what code would go behind the sign in and out buttons... would i just need

me.signin = now ()

or do i need to add the name of the employee to the code? or because of the other table it is going to be referenced?

[twinnyfo]

OK so i actually do have a seperate table calles "tbl_employees" structured just like you showed except with just the names, no addresses or anything... how does this factor in?

Does your table have an index for each employee (see post #33 above) or just the name?

do i need to add the name of the employee to the code? or because of the other table it is going to be referenced?

Let's figure out your table structure first. This will drive how we add the employee to tbl_master....

[didacticone]

The table just has the primary key (ID field) and the employee name

[twinnyfo]

And tbl_master uses the index? Or the text value for your Employee? Th9is will affect how we determine check in/out status from Post #31.

Plus:
How do you want your sing in form to work? Combo box with two buttons or 8 buttons?

I don't want us going off the rails on a crazy train until we figure out for sure how you want it to look/feel.

[didacticone]

i'll be honest im not familiar with using the index, so im going to assume it uses the text value for employee...

i would prefer the four names listed on my form (as they are now, just labels) with a sign in and out for each...

nice ozzy reference LOL... i think i have driven you off the rails already my friend... sorry and thanks!!!

[twinnyfo]

i'll be honest im not familiar with using the index, so im going to assume it uses the text value for employee...

Then let's get you set up properly.

(Forgive me if you already know how to do this or have already done this, but I want to make sure it is done right.)
Open tbl_master in design view.

Add a field called "Employee2" - this is the field we will use for this experiment going forward. You can always change the original Employee field in the future, but we will work through this step-by-step.

Select Data Type of Lookup Wizard...

Select the radio button for "I want the lookup field to get the values from another table or query."

Click "Next >".

Select your Employees Table (whatever its name is). Click "Next >".

Click on the ID and the Employee Name (whatever your field names are), so that they are both in the right side pane of the window. Click "Next >".

Select the Sort order of your Employee Name. Click "Next >".

Make sure the "Hide key column (recommended)" check box is checked. Click "Next >".

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

Click "Yes" to save the table.

Open the table is datasheet view and examine your new field. It will be shown as a drop down list, from which you can select an employee. However, what is actually happening is that you are selecting the index for that employee. The size of the field is only as large as an integer.

So, now, this changes a few things from Post #31. Your Command Buttons, you must use the index of the employee as found in your employees table in the name of your command buttons. So, your sign in buttons will all be named cmdSignIn[Employee Index]; your sign out buttons will all be named cmdSignOut[Employee Index]. Example: cmdSignIn2; cmdSignOut6, etc. Does this make sense?

Then, some minor changes to our other code from that same post:

Private Sub CheckStatus()
On Error GoTo EH
 
    CheckEmployee 2
    CheckEmployee 5
    CheckEmployee 7
 
    Exit Sub
EH:
    MsgBox "There was an error checking the status!  " & _
        "Please contact your Database Administrator.", _ 
        vbCritical, "WARNING!"
    Exit Sub
End Sub

(of course you will add the employee index from your table) Do you understand why we made these changes?

And the other function:

Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "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 status!  " & _
        "Please contact your Database Administrator.", _ 
        vbCritical, "WARNING!"
    Exit Function
End Function

The only changes are that Employee (the value sent to the function) is a integer (you may have it in your table as a Long if it is an autonumber) and the removal of the single quotes in the DLookup() functions, because we are looking for numbers and not text strings. All else remains the same.

This is your assignment for the weekend!

Hope this hepps!

[didacticone]

So following along with what you said I am stuck with some of the language... "However, what is actually happening is that you are selecting the index for that employee. The size of the field is only as large as an integer.

So, now, this changes a few things from Post #31. Your Command Buttons, you must use the index of the employee as found in your employees table in the name of your command buttons.

What is considered the index of the employee?

Also once this is answered, is the code for each button simply:

me.signin = now

?

[twinnyfo]

Also once this is answered, is the code for each button simply:

me.signin = now

Let's not get ahead of ourselves. We will get there. Small chunks, one step at a time.... Remember?

What is considered the index of the employee?

tblEmployees.ID is the index. It is the value that "represents" everything else in that particular record.

This might help you understand better. There are many things that could be associated with an employee: Name, Address, Birth Date, Favorite Color, left- or right-handed. All of this could go into one table concerning employees. The index, will represent all those attributes of each employee elsewhere in your DB.

Let say you are building a form that schedules employees. You have dates set aside, work periods on each day, etc. And you can add employees to each work period. Rather than typing each each employee's name (and taking up additional space with long strings), you use a drop down box which shows the employee's name, but stores the employee's index. Then, let say you want to see whether the employee in a particular work period had red hair, writes left-handed and lives in Hoboken, NJ. Well, you use the index to find the record that pertains to that employee. This is a foundational understanding of a relational database.

Hope this hepps!

[didacticone]

Also when i try to open the form now im getting "type mismatch in expression" error.

[twinnyfo]

Correction to the above:

Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee2] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "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 checking the status!  " & _
        "Please contact your Database Administrator.", _ 
        vbCritical, "WARNING!"
    Exit Function
End Function

[Rabbit]

Anyone can sign in as anyone else? Seems like a security issue. What do you do about accidental logins?

[didacticone]

Now i got enter parameter value... employee.value

[twinnyfo]

When are you getting this error? It is more helpful to us to tell us exactly when and where so we can focus troubel shooting.

[didacticone]

As soon as i open the form... sorry

[didacticone]

I added some sample data to the table and that removed the messaged but the buttons are not disabling here is my 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
 
    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "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 status!  " & _
        "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 = ''"
    Me.FilterOn = True
 
    Exit Sub
EH:
    MsgBox "There was an error initializing the Form!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Sub
     CheckStatus
End Sub

[didacticone]

I stepped through the code and the error is coming up here:

Private Function CheckEmployee(Employee As Integer)
On Error GoTo EH
 
   fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
        "[Employee] = " & Employee & " " & _
        "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
        Format(Date, "yyyy-mm-dd") & "'"), False)
 
    fCheckOut = Nz(DLookup("[SignOut]", "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 status!  " & _
        "Please contact your Database Administrator.", _
        vbCritical, "WARNING!"
    Exit Function
End Function

The bold or first part of that code skips it to the error

[didacticone]

i changed the error coding to:

EH:
    MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
        Err.Description & vbCrLf & vbCrLf & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"

and received the following error upon opening the form
"data type mismatch in criteria expression."