Need help with a Checkbox

[bigtman07]

Good afternoon everyone! I just wanted to say I have been browsing this forum for a while and you all seem to know your stuff haha So I need a little help here. I'm sorry admins if I have this in a bad spot. Rookie mistake. Please move it and let me know if there was a better spot.



My work has tasked me with making a database since I am the only one here that has any access experience. Now my access is minimal. I have created a couple inventory databases but outside of that a little VB programming. So with that said... I am in need of some assistance with a checkbox. I have most of my database completed. But the powers that be have requested I try to automate the form and the entries a little more. What I am wanting to do is use a text box and a text box to show when someone has clocked in and out. Seems pretty simple right?!? Well for the life of me I cannot get it to work. I am using the after update box for the checkbox. Now when I put in anything in there it tells me I must have it in a macro. Ok fine so I go to put it into a macro and that is where I get stuck. The stupid macro builder doesn't have the options that I am reading I need. So I made a VB module to house the code I know that works. But now I can't call the module from a macro!! Ugh!!! This is getting frustrating. Any direction would be greatly appreciated!

[RuralGuy]

If this db is not going to be on the web then there is no reason to be forced to use macros.

[bigtman07]

Ok well the Database is hosted on our sharepoint server so I used a template and that template was a web template. So I guess that makes some of the difference. lol

[RuralGuy]

Too late to start over? I don't use macros and know little about them.

[KathyL]

First you say you ‘want to use a text box’. Ok, I’d agree, make one text box for a clock in date/time, and one text box for a clock out date/time.


Then you say ‘after update box for the checkbox’. Where did your idea for a checkbox come from, you don’t say. A checkbox to do what?


Access does not force you to use macros. One option for creating an event procedure is to use a default macro, or you can create your own VB code. Perhaps you are trying to say you are creating an event procedure, an ‘after update’ event procedure for something… but which something? When they click something? Or when they exit some field?


So what is your calculation? Do you want it for the amount of time elapsed between clock in and clock out? Or, do you just want a text value, something like “employee is working”, v.s. “employee has gone home”, as the result of showing “when someone has clocked in and out”. You’re not specific enough.

[June7]

Macros can only call VBA Function procedures, not Sub procedures. So just change to Function and see what happens. I also do not use macros. If you must serve the db via Sharepoint then based on what little I understand about Sharepoint you are basically limited to macros in the web-based forms. Seems I have seen database with both web and non-web forms in it. No idea how that would work with Sharepoint.

[bigtman07]

Thanks for the Replies everyone. Sorry for the confusion. I posted at the end of the workday and my brain was a little on the cooked side haha. I misstyped my thoughts.

Ok so here is what the boss is wanting. He wants to have 2 text boxes that will display the most recent check in and check out times. These boxes should be populated by a checkbox. If that makes any sense???
We would love to have another table to track all the users when the check in and when they check out. It seems like a simple idea. Check a box it puts in the time. These records are stored in the table. I just can't link the checkbox to the textbox to input the timestamp.

[KathyL]

“Ok so here is what the boss is wanting. He wants to have 2 text boxes that will display the most recent check in and check out times. These boxes should be populated by a checkbox. If that makes any sense??? “


A checkbox field is either ‘on/off’, ‘yes/no’, ‘true/false’, all the same thing. A checkbox can NOT populate another field, however, you can attach an event procedure to a checkbox such that when it is checked, some procedure happens in the background, like updating the current date/time into one or both text boxes or doing calculations.
I’m concerned that you’re not familiar enough with Access to be jumping off to do Visual Basic event procedures. We're not going to be able to train you here. Check with your local community college for classes.


“We would love to have another table to track all the users when the check in and when they check out. It seems like a simple idea. Check a box it puts in the time. These records are stored in the table. I just can't link the checkbox to the textbox to input the timestamp.”


I don’t know your tables and relationships. If you’re thinking of adding another transaction-like table to store each new checkin/checkout date/time, ok, but you’re potentially needing some serious VB code/event procedure to create those records behind the scene. A simple way to start it off, would be for you to have a pop up form which would allow you to key in the new record going into this new table.


There’s always more than one way to skin the cat.

[June7]

For a 'check in/check out' type of activity, look at the Lending Library template database. Might get some ideas.

[bigtman07]

Absolutely KathyL I totally agree. I got thrown in this mess because I did take a couple access classes. Sadly it's been a while since I have done anything in depth. I do understand that the check box is true/false or off/on. I would like to attach some sort of action to populate the time in and time out fields using the check box. But if that is going to be too in depth than is there any way to get the date and time to be input using the date fields? Since there is more than one way to skin this cat... is there a fool proof way to have a user do an action that will populate the date and time??

[June7]

No such thing as 'fool proof' - there is always a better fool! How can you force them to check the checkbox any more than typing a date in textbox?

Could have fields in table defined as 'required' then if user puts focus on the bound control, they can't leave it until filled in. Also, the record would not commit until they complete the required fields. Access will keep harrassing user until they do so. If you want to control the harrassment then instead of defining field as required in table, use code. Could have code in form BeforeUpdate event that validates data. Looks to see if any required fields are null and if so, kicks the user back to the form and they can't leave until properly completed.

Code behind the checkbox Change event could populate textboxes.

Can set DefaultValue property so field will have value when record created.

etc.

[bigtman07]

You do have a point. The fool is always going to be better lol Ok well what we were trying to avoid is a missed keystroke or poor data entry on the user end. Thats why we would like to use system time rather than user input. Is there maybe a way to attach it to an action button? I'm just throwing out ideas here. lol I personally brought it to them as a data entry field but they want it automated. So that's what I'm trying to do. If it is going to be too complicated then so be it lol.

[June7]

The more 'user friendly' the more code.

We have thrown out some ideas. You just have to research further how they can relate to your business process and try.

Yes, code can be behind a command button (similar to behind a checkbox but use Click event instead) or toggle button or radio button. Again, this is a convenience for the user so they don't have to type full date and nothing forces user to push button. So still need data validation code.

[bigtman07]

Very well. I appreciate all the help and the options. I'm sure I can figure it out with a little help. I'm going to see if we can leave it as user input for now while I find a way to make it more user friendly. With this being a web based form is that going to complicate things further??

[June7]

Yes, I believe only macro coding will be possible. I never built a web database and don't use macros.