Check for existing date

[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