By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,961 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

Check for existing date

100+
P: 250
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!
Mar 22 '18 #1
Share this Question
Share on Google+
93 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,212
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!
Mar 22 '18 #2

100+
P: 250
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 :

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signin", , , , acFormAdd
  2. Forms!frm_signin!Employee = "Joe Smith"
  3. Forms!frm_signin!date1 = Me.date1
  4. Forms!frm_signin!signintime = Time()
  5. Forms!frm_signin!signindate = Me.date1
  6. Forms!frm_signin!signin = True
  7. DoCmd.Close acForm, "frm_signin"
  8. MsgBox "Joe is now signed in."
Mar 22 '18 #3

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 22 '18 #4

100+
P: 250
OK so for sign out, this is what i have:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signinmatt", , , "date1 = #" & date1 & "#"
  2. Forms!frm_signinmatt!signouttime = Time()
  3. Forms!frm_signinmatt!signoutdate = Date
  4. Forms!frm_signinmatt!signout = True
  5.  
  6. lresponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
  7.  
  8. If lresponse = vbNo Then
  9.    On Error GoTo Command11_Click_Err
  10.  
  11.     DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
  12.  
  13.  
  14. Command11_Click_Exit:
  15.     Exit Sub
  16.  
  17. Command11_Click_Err:
  18.     MsgBox Error$
  19.     Resume Command11_Click_Exit
  20. Else
  21.   MsgBox "Matt is now signed out"
  22.    On Error GoTo closemattform_Click_Err
  23.  
  24.     DoCmd.Close acForm, "frm_signinmatt"
  25.  
  26.  
  27. closemattform_Click_Exit:
  28.     Exit Sub
  29.  
  30. closemattform_Click_Err:
  31.     MsgBox Error$
  32.     Resume closemattform_Click_Exit
  33.  
  34. End If
  35. MsgBox "Matt is now signed out"
  36. 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
Mar 22 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 22 '18 #6

100+
P: 250
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?
Mar 22 '18 #7

100+
P: 250
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?
Mar 22 '18 #8

100+
P: 250
OK maybe not the cleanest, but this worked

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signinmatt"
  2. If Forms!frm_signinmatt!date1 = Date Then
  3. MsgBox "you have already signed in today"
  4. DoCmd.Close acForm, "frm_signin"
  5. DoCmd.Close acForm, "frm_signinmatt"
  6. Else
  7.  
  8. DoCmd.OpenForm "frm_signin", , , , acFormAdd
  9. Forms!frm_signin!Employee = "Matt Smith"
  10. Forms!frm_signin!signintime = Time()
  11. Forms!frm_signin!signindate = Me.date1
  12. Forms!frm_signin!signin = True
  13. Forms!frm_signin!date1 = Me.date1
  14. DoCmd.Close acForm, "frm_signin"
  15. DoCmd.Close acForm, "frm_signinmatt"
  16. MsgBox "Matt is now signed in."
  17. End If
let me know what youre thinking, honestly lol... just want you to see im trying to figure it out
Mar 22 '18 #9

100+
P: 250
or this, for sign in button:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signin", , , , acFormAdd
  2. Forms!frm_signin!Employee = "Matt Smith"
  3. Forms!frm_signin!signintime = Time()
  4. Forms!frm_signin!signindate = Me.date1
  5. Forms!frm_signin!signin = True
  6. Forms!frm_signin!date1 = Me.date1
  7. DoCmd.Close acForm, "frm_signin"
  8. Me.Command29.SetFocus
  9. Me.Command6.Enabled = False
  10. Me.Command7.Enabled = True
  11. MsgBox "Matt is now signed in."
and for sign out button:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frm_signinmatt", , , "date1 = #" & date1 & "#"
  2. Forms!frm_signinmatt!signouttime = Time()
  3. Forms!frm_signinmatt!signoutdate = Date
  4. Forms!frm_signinmatt!signout = True
  5. 'MsgBox "Matt is now signed out. Good Bye!"
  6. lresponse = MsgBox("Did you work a full day?", vbYesNo, "Continue")
  7.  
  8. If lresponse = vbNo Then
  9.    On Error GoTo Command11_Click_Err
  10.  
  11.     DoCmd.OpenForm "frm_typeofday", acNormal, "", "", , acNormal
  12.  
  13.  
  14. Command11_Click_Exit:
  15.     Exit Sub
  16.  
  17. Command11_Click_Err:
  18.     MsgBox Error$
  19.     Resume Command11_Click_Exit
  20. Else
  21. Me.Command6.Enabled = True
  22. Me.Command29.SetFocus
  23. Me.Command7.Enabled = False
  24.   MsgBox "Matt is now signed out"
  25.    On Error GoTo closemattform_Click_Err
  26.  
  27.     DoCmd.Close acForm, "frm_signinmatt"
  28.  
  29.  
  30. closemattform_Click_Exit:
  31.     Exit Sub
  32.  
  33. closemattform_Click_Err:
  34.     MsgBox Error$
  35.     Resume closemattform_Click_Exit
  36.  
  37. End If
  38. Me.Command6.Enabled = True
  39. Me.Command29.SetFocus
  40. Me.Command7.Enabled = False
  41. MsgBox "Matt is now signed out"
That seems to work to enable/disable the buttons.
Mar 22 '18 #10

twinnyfo
Expert Mod 2.5K+
P: 3,212
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel As Integer)
  2. On Error GoTo EH
  3.  
  4.     Me.cboEmployee = ""
  5.     Me.Filter = "Employee = '" & Me.cboEmployee & "'"
  6.     Me.FilterOn = True
  7.  
  8.     Exit Sub
  9. EH:
  10.     MsgBox "There was an error initializing the Form!  " & _
  11.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  12.     Exit Sub
  13. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. On Error GoTo EH
  3.  
  4.     'What will you put here?
  5.     'Hint: Me.NewRecord is a
  6.     'Boolean value (true/false)
  7.     'that indicates whether the
  8.     'form is sitting on a new record
  9.  
  10.     Exit Sub
  11. EH:
  12.     MsgBox "There was an error setting the controls!  " & _
  13.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  14.     Exit Sub
  15. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cboEmployee_AfterUpdate()
  2. On Error GoTo EH
  3.  
  4.     'What will you put here?
  5.     'Hint: much of what you
  6.     'need is already here in 
  7.     'some form or another. 
  8.     'The tricky part is how to
  9.     'get today's record? 
  10.  
  11.     Exit Sub
  12. EH:
  13.     MsgBox "There was an error selecting the record!  " & _
  14.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  15.     Exit Sub
  16. End Sub

Checking in?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCheckIn_Click()
  2. On Error GoTo EH
  3.  
  4.     'What will you put here?
  5.     Me.Refresh
  6.  
  7.     Exit Sub
  8. EH:
  9.     MsgBox "There was an error signing in!  " & _
  10.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  11.     Exit Sub
  12. End Sub
The Me.Refresh will essentially re-filter your form based on the current data.


Checking out?
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdCheckOut_Click()
  2. On Error GoTo EH
  3.  
  4.     'What will you put here?
  5.     Me.Refresh
  6.  
  7.     Exit Sub
  8. EH:
  9.     MsgBox "There was an error signing Out!  " & _
  10.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  11.     Exit Sub
  12. End Sub

Code wisely, Young Jedi....

I'm always here to hepp work through the snags!!!
Mar 22 '18 #11

100+
P: 250
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.
Mar 22 '18 #12

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 22 '18 #13

100+
P: 250
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.
Mar 22 '18 #14

twinnyfo
Expert Mod 2.5K+
P: 3,212
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:

Expand|Select|Wrap|Line Numbers
  1. Private Function EmployeeTime(Employee As String)
  2. On Error GoTo EH
  3.  
  4.     'What will you put here?
  5.     'Hint: much of what you
  6.     'need is already here in
  7.     'some form or another.
  8.     'The tricky part is how to
  9.     'get today's record?
  10.  
  11.     Exit Function
  12. EH:
  13.     MsgBox "There was an error sellecting the Employee!  " & _
  14.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  15.     Exit Function
  16. 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.
Mar 22 '18 #15

100+
P: 250
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
Mar 22 '18 #16

twinnyfo
Expert Mod 2.5K+
P: 3,212
Expand|Select|Wrap|Line Numbers
  1. Dim fCheckIn  As Boolean
  2. Dim fCheckOut As Boolean
  3.  
  4. fCheckIn = Nz(DLookup("[signindate]", "tbl_Master", _
  5.     "[Employee] = '" & "Employee Name" & "' " & _
  6.     "AND [signindate] = #" & Date & "#"), False)
  7.  
  8. fCheckOut = Nz(DLookup("[signoutdate]", "tbl_Master", _
  9.     "[Employee] = '" & "Employee Name" & "' " & _
  10.     "AND [signoutdate] = #" & Date & "#"), False)
  11.  
  12. cmdButtonMatt.Enabled = Not (fCheckIn And fCheckOut)
Mar 22 '18 #17

100+
P: 250
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:

Expand|Select|Wrap|Line Numbers
  1. Private Function EmployeeTime(Employee As String)
  2. Dim fCheckIn  As Boolean
  3. Dim fCheckOut As Boolean
  4.  
  5. fCheckIn = Nz(DLookup("[signindate]", "tbl_Master", _
  6.     "[Employee] = '" & "Employee Name" & "' " & _
  7.     "AND [signindate] = #" & Date & "#"), False)
  8.  
  9. fCheckOut = Nz(DLookup("[signoutdate]", "tbl_Master", _
  10.     "[Employee] = '" & "Employee Name" & "' " & _
  11.     "AND [signoutdate] = #" & Date & "#"), False)
  12.  
  13. btn_signin.Enabled = Not (fCheckIn And fCheckOut)
  14.  
  15. On Error GoTo EH
  16.  DoCmd.OpenForm "frm_signin", , , , acFormAdd
  17. Forms!frm_signin!Employee = "Matt Smith"
  18. Forms!frm_signin!signintime = Time()
  19. Forms!frm_signin!signindate = Me.date1
  20. Forms!frm_signin!signin = True
  21. Forms!frm_signin!date1 = Me.date1
  22. DoCmd.Close acForm, "frm_signin"
  23.  
  24. MsgBox "Matt is now signed in."
  25.  
  26.     Exit Function
  27. EH:
  28.     MsgBox "There was an error sellecting the Employee!  " & _
  29.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
  30.     Exit Function
  31. End Function
Mar 26 '18 #18

twinnyfo
Expert Mod 2.5K+
P: 3,212
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!
Mar 26 '18 #19

100+
P: 250
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!
Mar 26 '18 #20

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 26 '18 #21

100+
P: 250
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!
Mar 26 '18 #22

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 26 '18 #23

100+
P: 250
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!
Mar 27 '18 #24

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 27 '18 #25

100+
P: 250
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.
Mar 27 '18 #26

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  
  3. Me.Recordset.AddNew
  4. Me.Recordset("employee") = "Matt Smith"
  5. Me.Recordset("signintime") = Time()
  6. Me.Recordset("signindate") = Me.date1
  7. Me.Recordset("signin") = "true"
  8. Me.Recordset("date1") = Me.date1
  9. Me.Recordset.Update
  10. MsgBox "Matt is now signed in."
  11. End Sub
Is that cleaner?
Mar 27 '18 #27

twinnyfo
Expert Mod 2.5K+
P: 3,212
I know you are very concerned about binding your form to the table; but if you did, this woul dbe your code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2.  
  3. Me.txtSignin = Now()
  4. MsgBox "Matt is now signed in."
  5.  
  6. 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.
Mar 27 '18 #28

100+
P: 250
OK sounds good... thanks again
Mar 27 '18 #29

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

Expand|Select|Wrap|Line Numbers
  1. If DLookup("signindate", "tbl_master", "signindate = #" & Date & "#") And Me.Employee = "Matt Smith" Then
  2. 'If Date = DLookup("date1", "tbl_master") And Me.Employee = "Matt Smith" Then
  3. MsgBox "you have already signed in today"
  4. Else
  5. DoCmd.GoToRecord , , acNewRec
  6. Me.Employee = "Matt Smith"
  7. Me.signintime = Time()
  8. Me.signindate = Me.date1
  9. Me.signin = True
  10. Me.date1 = Date
  11. MsgBox "Matt is signed in"
  12. Me.Command39.SetFocus
  13. Me.Command6.Enabled = False
That appears to be working...

For the sign out button I have:

Expand|Select|Wrap|Line Numbers
  1. If DLookup("signindate", "tbl_master", "signindate = #" & Date & "#") And Me.Employee = "Matt Smith" Then
  2.  
  3.     Me.signouttime = Time()
  4.     Me.signoutdate = Date
  5.     Me.signout = True
  6.     Me.Command39.SetFocus
  7.     Me.Command7.Enabled = False
  8.     MsgBox "Matt is now signed out."
  9. Else
  10.     MsgBox "Matt has not signed in today"
  11. 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
Mar 27 '18 #30

twinnyfo
Expert Mod 2.5K+
P: 3,212
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:
Expand|Select|Wrap|Line Numbers
  1. Field     Type         Format
  2. SignIn    Date/Time    General Date
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5. On Error GoTo EH
  6.  
  7.     Me.Filter = "Employee = ''"
  8.     Me.FilterOn = True
  9.  
  10.     Exit Sub
  11. EH:
  12.     MsgBox "There was an error initializing the Form!  " & _
  13.         "Please contact your Database Administrator.", _
  14.         vbCritical, "WARNING!"
  15.     Exit Sub
  16. 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?
Expand|Select|Wrap|Line Numbers
  1. Dim fCheckIn  As Boolean
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private fCheckIn  As Boolean
  5. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckStatus()
  2. On Error GoTo EH
  3.  
  4.     CheckEmployee "Joe Smith"
  5.     CheckEmployee "Eggbert Schmuckatelli"
  6.     CheckEmployee "Harold Umptyfratz"
  7.  
  8.     Exit Sub
  9. EH:
  10.     MsgBox "There was an error checking the status!  " & _
  11.         "Please contact your Database Administrator.", _ 
  12.         vbCritical, "WARNING!"
  13.     Exit Sub
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Function CheckEmployee(Employee As String)
  2. On Error GoTo EH
  3.  
  4.     fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
  5.         "[Employee] = '" & Employee & "' " & _
  6.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  7.         Format(Date, "yyyy-mm-dd") & "'"), False)
  8.  
  9.     fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
  10.         "[Employee] = '" & Employee & "' " & _
  11.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  12.         Format(Date, "yyyy-mm-dd") & "'"), False)
  13.  
  14.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  15.     Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  16.  
  17.     Exit Function
  18. EH:
  19.     MsgBox "There was an error checking the status!  " & _
  20.         "Please contact your Database Administrator.", _ 
  21.         vbCritical, "WARNING!"
  22.     Exit Function
  23. 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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Mar 27 '18 #31

100+
P: 250
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!
Mar 29 '18 #32

twinnyfo
Expert Mod 2.5K+
P: 3,212
In a properly structured DB (see this Article on DB Normalization), you would have a Table with just your Employess listed. Example:

tblEmployees
Expand|Select|Wrap|Line Numbers
  1. ID   EmplyeeName   EmployeeAddress  ... etc.
  2. 1    Joe Schmoe    123 Main St.
  3. 2    Mel Function  321 What Rd.
  4. 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!
Mar 29 '18 #33

100+
P: 250
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
Expand|Select|Wrap|Line Numbers
  1. 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?
Mar 29 '18 #34

twinnyfo
Expert Mod 2.5K+
P: 3,212
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....
Mar 29 '18 #35

100+
P: 250
The table just has the primary key (ID field) and the employee name
Mar 29 '18 #36

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 29 '18 #37

100+
P: 250
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!!!
Mar 29 '18 #38

twinnyfo
Expert Mod 2.5K+
P: 3,212
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub CheckStatus()
  2. On Error GoTo EH
  3.  
  4.     CheckEmployee 2
  5.     CheckEmployee 5
  6.     CheckEmployee 7
  7.  
  8.     Exit Sub
  9. EH:
  10.     MsgBox "There was an error checking the status!  " & _
  11.         "Please contact your Database Administrator.", _ 
  12.         vbCritical, "WARNING!"
  13.     Exit Sub
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Function CheckEmployee(Employee As Integer)
  2. On Error GoTo EH
  3.  
  4.     fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
  5.         "[Employee] = " & Employee & " " & _
  6.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  7.         Format(Date, "yyyy-mm-dd") & "'"), False)
  8.  
  9.     fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
  10.         "[Employee] = " & Employee & " " & _
  11.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  12.         Format(Date, "yyyy-mm-dd") & "'"), False)
  13.  
  14.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  15.     Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  16.  
  17.     Exit Function
  18. EH:
  19.     MsgBox "There was an error checking the status!  " & _
  20.         "Please contact your Database Administrator.", _ 
  21.         vbCritical, "WARNING!"
  22.     Exit Function
  23. 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!
Mar 29 '18 #39

100+
P: 250
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:

Expand|Select|Wrap|Line Numbers
  1. me.signin = now
?
Mar 29 '18 #40

twinnyfo
Expert Mod 2.5K+
P: 3,212
Also once this is answered, is the code for each button simply:

Expand|Select|Wrap|Line Numbers
  1. 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!
Mar 29 '18 #41

100+
P: 250
Also when i try to open the form now im getting "type mismatch in expression" error.
Mar 29 '18 #42

twinnyfo
Expert Mod 2.5K+
P: 3,212
Correction to the above:

Expand|Select|Wrap|Line Numbers
  1. Private Function CheckEmployee(Employee As Integer)
  2. On Error GoTo EH
  3.  
  4.     fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
  5.         "[Employee2] = " & Employee & " " & _
  6.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  7.         Format(Date, "yyyy-mm-dd") & "'"), False)
  8.  
  9.     fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
  10.         "[Employee2] = " & Employee & " " & _
  11.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  12.         Format(Date, "yyyy-mm-dd") & "'"), False)
  13.  
  14.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  15.     Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  16.  
  17.     Exit Function
  18. EH:
  19.     MsgBox "There was an error checking the status!  " & _
  20.         "Please contact your Database Administrator.", _ 
  21.         vbCritical, "WARNING!"
  22.     Exit Function
  23. End Function
Mar 29 '18 #43

Rabbit
Expert Mod 10K+
P: 12,357
Anyone can sign in as anyone else? Seems like a security issue. What do you do about accidental logins?
Mar 29 '18 #44

100+
P: 250
Now i got enter parameter value... employee.value
Mar 29 '18 #45

twinnyfo
Expert Mod 2.5K+
P: 3,212
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.
Mar 29 '18 #46

100+
P: 250
As soon as i open the form... sorry
Mar 29 '18 #47

100+
P: 250
I added some sample data to the table and that removed the messaged but the buttons are not disabling here is my code:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim fCheckIn  As Boolean
  4. Dim fCheckOut As Boolean
  5. Private Sub CheckStatus()
  6. On Error GoTo EH
  7.  
  8.     CheckEmployee 1
  9.     CheckEmployee 2
  10.     CheckEmployee 3
  11.     CheckEmployee 4
  12.  
  13.     Exit Sub
  14. EH:
  15.     MsgBox "There was an error checking the status!  " & _
  16.         "Please contact your Database Administrator.", _
  17.         vbCritical, "WARNING!"
  18.     Exit Sub
  19.  
  20. End Sub
  21. Private Function CheckEmployee(Employee As Integer)
  22. On Error GoTo EH
  23.  
  24.     fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
  25.         "[Employee] = " & Employee & " " & _
  26.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  27.         Format(Date, "yyyy-mm-dd") & "'"), False)
  28.  
  29.     fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
  30.         "[Employee] = " & Employee & " " & _
  31.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  32.         Format(Date, "yyyy-mm-dd") & "'"), False)
  33.  
  34.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  35.     Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  36.  
  37.     Exit Function
  38. EH:
  39.     MsgBox "There was an error checking the status!  " & _
  40.         "Please contact your Database Administrator.", _
  41.         vbCritical, "WARNING!"
  42.     Exit Function
  43. End Function
  44.  
  45.  
  46. Private Sub Form_Open(Cancel As Integer)
  47. On Error GoTo EH
  48.  
  49.     Me.Filter = "Employee = ''"
  50.     Me.FilterOn = True
  51.  
  52.     Exit Sub
  53. EH:
  54.     MsgBox "There was an error initializing the Form!  " & _
  55.         "Please contact your Database Administrator.", _
  56.         vbCritical, "WARNING!"
  57.     Exit Sub
  58.      CheckStatus
  59. End Sub
Mar 29 '18 #48

100+
P: 250
I stepped through the code and the error is coming up here:
Expand|Select|Wrap|Line Numbers
  1. Private Function CheckEmployee(Employee As Integer)
  2. On Error GoTo EH
  3.  
  4.    fCheckIn = Nz(DLookup("[SignIn]", "tbl_Master", _
  5.         "[Employee] = " & Employee & " " & _
  6.         "AND Format([SignIn], 'yyyy-mm-dd') = '" & _
  7.         Format(Date, "yyyy-mm-dd") & "'"), False)
  8.  
  9.     fCheckOut = Nz(DLookup("[SignOut]", "tbl_Master", _
  10.         "[Employee] = " & Employee & " " & _
  11.         "AND Format([SignOut], 'yyyy-mm-dd') = '" & _
  12.         Format(Date, "yyyy-mm-dd") & "'"), False)
  13.  
  14.     Me("cmdSignIn" & Employee).Enabled = Not fCheckIn
  15.     Me("cmdSignOut" & Employee).Enabled = Not fCheckOut
  16.  
  17.     Exit Function
  18. EH:
  19.     MsgBox "There was an error checking the status!  " & _
  20.         "Please contact your Database Administrator.", _
  21.         vbCritical, "WARNING!"
  22.     Exit Function
  23. End Function
The bold or first part of that code skips it to the error
Mar 29 '18 #49

100+
P: 250
i changed the error coding to:
Expand|Select|Wrap|Line Numbers
  1. EH:
  2.     MsgBox "There was an error with the search!  " & vbCrLf & vbCrLf & _
  3.         Err.Description & vbCrLf & vbCrLf & _
  4.         "Please contact your Database Administrator.", vbCritical, "WARNING!"
and received the following error upon opening the form
"data type mismatch in criteria expression."
Mar 29 '18 #50

93 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.