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
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
Also add a Close button so you can close the form, because there is some testing we have to do.
Bind the form to
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?
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:
Rather than using the
Now, we need to check the status of each of our employees. Let's create a procedure called
Can you see how this is more efficient (conceptually) already??
But you might say, "Hey, I don't have a Function named
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
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:
Now, just add this line to your
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
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:Code:
[B][U]Field[/U][/B] [B][U]Type[/U][/B] [B][U]Format[/U][/B] SignIn Date/Time General Date SignOut Date/Time General Date
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; cmdSignOutEggbe rt 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:Code:
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
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?
Code:
Dim fCheckIn As Boolean Dim fCheckOut As Boolean
Code:
Option Compare Database Option Explicit Private fCheckIn As Boolean Private fCheckOut As Boolean
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:Code:
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
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:Code:
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
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:Code:
CheckStatus
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.
Comment