MS Access employee shift registration

Posted on 2012-09-19
Last Modified: 2012-11-12
Hello experts!

Using Access 2007, I'm trying to make a shiftregistration software.

As you can see underneath, I have a many to many relation between employees and dates. On each shift/date, I register any amount of employees as well as some time variables. Also on each shift I register some shift-specific notes.
Table viewI already made a form including a subform for this purpose - working well as of now. But I have a few concerns...

Here you can see the form containing subform wich displays the current employees registered on the choosen date. In the subform I am able to add and remove employees as well as register different time variables.
Form with subformEach time I have a new shift, I will first have to go to the shifts list and create a new shift/date in order to be able to register employees or notes on that shift. I would much rather like to embed a calendar control, where I can click any date, even if it has been created in the shifts table or not - and when I add my first employee or start writing a note on that date, the shift is automatically added to the shifts table.

How is that possible?

Please let me know, if you need any additional information.

Best regards

Question by:Raahaugen
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this,

    add a textbox to your form
    set the Format property to Short Date or whatever date format you have
    on the same Format tab
    set the Show date Picker property to  For Dates

    when the textbox got the focus, you will see a calendar control in the right side of the textbox, click on it and it will display the calendar

    Author Comment

    Hi capricorn1,

    Thanks for your input. Unfortunately it is not what I would like... I would like to use a calendarcontrol that stays on screen.

    Also this is not my biggest concern - that is how I can pick any date and start adding employees - without having to go to the shifts table and manually add the date first.

    Hopefully you are able to understand what I would like?

    Best regards

    LVL 119

    Expert Comment

    by:Rey Obrero
    ok.. see if this helps

    Author Comment

    I'm afraid not. The calendar control you are referring to was an addition to access 97 and 2003 - and not really relevant in the 2007 and above versions due to the pop-up calendar you mentioned at first.

    I think I will just try making the form from the beginning. I think I messed it up somehow in the process...

    If anyone have any additional comments - maybe some good guidance on how to make this subform thing work really good. I can't imagine, no-one has made anything similar before?

    Best regards,


    Accepted Solution


    I made up a solution myself. I used VBA to lookup the shifts table to check if the shift already exists - and if not create the shift - before attaching employees to the shift.

    Also I used the build in calendar control in MS Access 2007, which did the job.

    But thanks anyway!


    Author Closing Comment

    I figured out the solution myself.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now