How to Enter a Two-Week Shift Using Option Buttons and Weekday Function
Hello Folks:
I have a feature in my db that allows me to select certain days of the week (option buttons) to submit as a one week shift for employees. Currently, I can only select a paramater to enter a shift one week at a time. I need a solution to enter two weeks at a time . I will enclose the form and the code on the submit button for reference.
batch.bmp
command-button.bmp
I have a feature in my db that allows me to select certain days of the week (option buttons) to submit as a one week shift for employees. Currently, I can only select a paramater to enter a shift one week at a time. I need a solution to enter two weeks at a time . I will enclose the form and the code on the submit button for reference.
batch.bmp
command-button.bmp
Rey Obrero (Capricorn1)
did you try adjusting the End Date to span two weeks from the Start Date?
ASKER
Yes. The problem is that I need to factor two consecutive rotating days off each week. So, I select the shift and leave two days of the week null. Then I select the code for the day off and only select those two days, leaving the other days null. If the days off remained the same, it would be no problem, but they change. For example:
Week1: Monday and Tuesday Off
Week2: Wednesday and Thursday Off
I should have been clearer on this.
Thanks.
Week1: Monday and Tuesday Off
Week2: Wednesday and Thursday Off
I should have been clearer on this.
Thanks.
For a given employee doing a given job on a given date, don't you need both start and end times?
<Week1: Monday and Tuesday Off
Week2: Wednesday and Thursday Off>
do you have a table for this schedules?
Week2: Wednesday and Thursday Off>
do you have a table for this schedules?
ASKER
ASKER
Folks?
It's been several days since the last post and I still don't have a solution. Can anyone assist me? Thanks.
It's been several days since the last post and I still don't have a solution. Can anyone assist me? Thanks.
I still don't have the foggiest idea what the form does. Maybe it inserts records by employee and shift code into a table which are subsequently populated by another form with actual start and end times?
Perhaps you could explain what the form does in a 1 week situation - what the user does when the form is opened?
Perhaps you could explain what the form does in a 1 week situation - what the user does when the form is opened?
ASKER
Would it be easier if I upload the db so you can experiment with it?
That would be good. Strip the mdb down to the minimum required to support the question - do a Compact & Repair - and then upload.
ASKER
GRayL:
The file has been preparared and uploaded. I added just one empoyee. So, just click on "Batch Entry" and fill in the fields. Enter the parameter dates and select which days you wish to add. I always leave two radio buttons in null status for the days off. Once I upload the shift, I change the code to RDO (Regular Day Off) and only select the two buttons that were initially left null. As discussed, I want to span out the dates to two weeks, but I want the option of selecting different days off for the second week. In it's current form, I can enter one week at a time with certain days off. Thanks.
AlphaRoster-2-.mdb
The file has been preparared and uploaded. I added just one empoyee. So, just click on "Batch Entry" and fill in the fields. Enter the parameter dates and select which days you wish to add. I always leave two radio buttons in null status for the days off. Once I upload the shift, I change the code to RDO (Regular Day Off) and only select the two buttons that were initially left null. As discussed, I want to span out the dates to two weeks, but I want the option of selecting different days off for the second week. In it's current form, I can enter one week at a time with certain days off. Thanks.
AlphaRoster-2-.mdb
I've looked and it does not make any sense. Text21 in the Header should probably be a combo box in which you list all the EmployeeID, FirstName and LastName. The EmployeeID should be invisible but appear in the ID text box in the Detail section. Why does EmployeeID in the table have a caption 'Last 4'. Why are there 26 Pay Periods, etc., etc. Most of the form controls have the same name as the field they contain - bad.
It might be better to start and describe in plain words what you are trying to do. What is the intent of the form? I see the form is bound to a query in which 3 tables are joined. Are you trying to populate a table with employees, workdates, and shifts, prior to the start of the work perios, after which you will populate the start and end times?
Please take a cut at describing the process step-by-step for employee inputs and time-keeping inputs as if everything was already there and working.
It might be better to start and describe in plain words what you are trying to do. What is the intent of the form? I see the form is bound to a query in which 3 tables are joined. Are you trying to populate a table with employees, workdates, and shifts, prior to the start of the work perios, after which you will populate the start and end times?
Please take a cut at describing the process step-by-step for employee inputs and time-keeping inputs as if everything was already there and working.
ASKER
GRay:
I designed the program for a specific purpose, and that was to assign employees their schedules for the purpose of an Alpha Roster. So, the Employee Shift Table will hold their shift for any given day. The shift can be entered manually one day at a time via the Employee Form, or by the Batch Form where you can schedule them a week at a time. The pay periods are nothing more than identifiers for any given day. You see this typically with the government. So, the pay periods are not part of any function, but they are required for tracking purposes.
Once the shift is entered for the week, let's say parameter date from 4/3/2011 to 4/9/2011, I would select only 5 days from the days listed (radio buttons) and leave two blank. This would represent a 5-day work week with 2 days off. I would then clear the radio buttons and select the two buttons that were initially null (days off) and select "RDO" from the Shift Code field and hit submit again. The schedule for that employee will show on the Employee Form, which can be further processed individaully if any of those days happen to change. The combobox at the top of the Employee Form will help me locate the employee much faster, since it's in Ascending mode.
What I want to do is to populate a shift from the batch Form, but I want to find a way to enter two weeks at a time with rotating days off. So, a parameter from 4/3/2011 to 3/16/2011 would consist of a 40-hour work week. I would populate the fields on the batch Form and from the Shift Code field, I would select, let's say A1400. I would leave two radio buttons null for each week and hit submit. The shift has been uploaded, and now I would clear the radio buttons, not changing the paramater dates, and select the two buttons (initially null) for each week to represent the days off. The code for the days off would be "RDO". So the end product would be a 40-hour work week with two days off each week.
It works the way it should, but I want to enter two weeks at a time from the batch form with the option of selecting different days off for each week. As discussed, I can span the date parameter for two weeks, but the days off will change in week two. I don't know if the code for the Submit button can be modified to perhaps include another set of radio buttons with day labels to represent week two. Both weeks would feed off of the parameter dates, which would allow me to select different days off each week and submit in one shot.
I hope this makes better sense.
I designed the program for a specific purpose, and that was to assign employees their schedules for the purpose of an Alpha Roster. So, the Employee Shift Table will hold their shift for any given day. The shift can be entered manually one day at a time via the Employee Form, or by the Batch Form where you can schedule them a week at a time. The pay periods are nothing more than identifiers for any given day. You see this typically with the government. So, the pay periods are not part of any function, but they are required for tracking purposes.
Once the shift is entered for the week, let's say parameter date from 4/3/2011 to 4/9/2011, I would select only 5 days from the days listed (radio buttons) and leave two blank. This would represent a 5-day work week with 2 days off. I would then clear the radio buttons and select the two buttons that were initially null (days off) and select "RDO" from the Shift Code field and hit submit again. The schedule for that employee will show on the Employee Form, which can be further processed individaully if any of those days happen to change. The combobox at the top of the Employee Form will help me locate the employee much faster, since it's in Ascending mode.
What I want to do is to populate a shift from the batch Form, but I want to find a way to enter two weeks at a time with rotating days off. So, a parameter from 4/3/2011 to 3/16/2011 would consist of a 40-hour work week. I would populate the fields on the batch Form and from the Shift Code field, I would select, let's say A1400. I would leave two radio buttons null for each week and hit submit. The shift has been uploaded, and now I would clear the radio buttons, not changing the paramater dates, and select the two buttons (initially null) for each week to represent the days off. The code for the days off would be "RDO". So the end product would be a 40-hour work week with two days off each week.
It works the way it should, but I want to enter two weeks at a time from the batch form with the option of selecting different days off for each week. As discussed, I can span the date parameter for two weeks, but the days off will change in week two. I don't know if the code for the Submit button can be modified to perhaps include another set of radio buttons with day labels to represent week two. Both weeks would feed off of the parameter dates, which would allow me to select different days off each week and submit in one shot.
I hope this makes better sense.
Do the two days off per week always move up, Mon/Tue, Tue/Wed, Wed/Thu, etc.
Sorry, looking at you post at http:#a35301179
Week1: Monday and Tuesday Off
Week2: Wednesday and Thursday Off
would that be: Mon/Tue,Wed/Thu, Fri/Sat, Sun/Mon, etc.
Week1: Monday and Tuesday Off
Week2: Wednesday and Thursday Off
would that be: Mon/Tue,Wed/Thu, Fri/Sat, Sun/Mon, etc.
ASKER
GRayL:
Yes. The two consecutive days off would always change for week two. So, if I were to span the paramamter dates for two weeks and select a shift from the Shift Code field, I would click the "Everyday" checkbox to populate every radio button. Then I would deselect the days off and hit submit. Then I would clear all the boxes, select RDO from the Shift Code field, and select the days off before I hit submit again, completing a two-week schedule with 4 days off.
As someone with your experience, you can probably find issues with my model, but I tried to follow the db basics with my overall objective in mind. I"m very aesthic when it comes to design, especially when I work in SharePoint. Access has always been a challenge for me, but I always manage to create a program that works and is user friendly. Bear with me, and I hope that my goal with this project is clear enough for you guys to assist me. Thanks.
Yes. The two consecutive days off would always change for week two. So, if I were to span the paramamter dates for two weeks and select a shift from the Shift Code field, I would click the "Everyday" checkbox to populate every radio button. Then I would deselect the days off and hit submit. Then I would clear all the boxes, select RDO from the Shift Code field, and select the days off before I hit submit again, completing a two-week schedule with 4 days off.
As someone with your experience, you can probably find issues with my model, but I tried to follow the db basics with my overall objective in mind. I"m very aesthic when it comes to design, especially when I work in SharePoint. Access has always been a challenge for me, but I always manage to create a program that works and is user friendly. Bear with me, and I hope that my goal with this project is clear enough for you guys to assist me. Thanks.
Then if you knew the days off for the first full week of the year then you could calculate the two days off for all remaining weeks right?
ASKER
Yes. Although the days off may change during the year due to operational needs, the two rotating days off would be the standard. If an employee cannot take those two consecutive days off during any given week, then those days that were already entered can be individually updated from the Employee form.
I'm sorry I've been busy the last several days. I really don't have time to build the additions required for the form but you may want to consider adding a second column beside the first. If you are positive you will never revert to a single week requirement again, you only need two dates, Start for first week, and End for the second week. The days off for the second week would be those of the first week plus 2 days. The code for handling the shift for the second week should be identical to that existing for the first week. I suggest you make that change as a new form and unload the new form by itself in a new mdb. That way I can download the new form and import it into the old mdb and be able to work with old and new at the same time.
ASKER
GRay:
I'm not sure if I followed your instructions correctly, but here are both the old and the new mdb.
New-db.mdb
I'm not sure if I followed your instructions correctly, but here are both the old and the new mdb.
New-db.mdb
Where to start. When I open frm_Batch_Entry2, the text box at the top of the form Text21 is supposed to contain the last name of the employee selected in the form tbl_Employees. Why are you naming a form tbl_Employees? It should be frmEmployees. Why use underscores? - more difficult to type - more error prone - tblEmployees is just as readable as tbl_Employees and easier to type. I noticed you have several controls which have the same name as the field they contain. That is poor db design practice. Going back to the last name at the top of the field, change the control source to: =Forms!tbl_Employees!Text3
I urge you to have a look at: http://www.acc-technology.com/namconv.htm and modify your form(s) accordingly.
When you select a Start date, your code should automatically fill in the End date, two weeks later. As soon as you fill in the Start date, the Pay Period should be calculated. It looks like you can have any number of Pay Period(s) regardless of the Start date. I took it that there are 26 x two-week pay periods in a year.
Why do you format the LastName to lowercase in Text21?
Would it not be better to have a combobox for start which is populated by all the odd week start for the year of the date() you are working in?
As you can see, you have not given us an overview wherein you define what you mean by pay period. There is no naming convention, and these little mysteries like driving the LastName to lowercase makes troubleshooting this current mdb far more difficult than it should be. I feel like Don Quixote.
I urge you to have a look at: http://www.acc-technology.com/namconv.htm and modify your form(s) accordingly.
When you select a Start date, your code should automatically fill in the End date, two weeks later. As soon as you fill in the Start date, the Pay Period should be calculated. It looks like you can have any number of Pay Period(s) regardless of the Start date. I took it that there are 26 x two-week pay periods in a year.
Why do you format the LastName to lowercase in Text21?
Would it not be better to have a combobox for start which is populated by all the odd week start for the year of the date() you are working in?
As you can see, you have not given us an overview wherein you define what you mean by pay period. There is no naming convention, and these little mysteries like driving the LastName to lowercase makes troubleshooting this current mdb far more difficult than it should be. I feel like Don Quixote.
ASKER
GRay:
You're as funny as you are knowledgable (Don Quixote). The design , albeit below standards, accomplishes my objective. Yes, I need to work on the naming conventions, but I am not worried too much about the pay periods. They are there to fill in manually, nothing else. I will follow the naming protocol, but can you solve my issue with the two-week shift entry? I will submit the updated mdb with the changes, and if you have the patience, hopefully you can see this through. I doubt this is the worst you've seen, but if so, then it's a challenge. :)
You're as funny as you are knowledgable (Don Quixote). The design , albeit below standards, accomplishes my objective. Yes, I need to work on the naming conventions, but I am not worried too much about the pay periods. They are there to fill in manually, nothing else. I will follow the naming protocol, but can you solve my issue with the two-week shift entry? I will submit the updated mdb with the changes, and if you have the patience, hopefully you can see this through. I doubt this is the worst you've seen, but if so, then it's a challenge. :)
I'll do what I can. When you used the batch form did you ever have a Start date that was not Sunday and an End date that was not the following Saturday. If not, then you do not need an End date. X will always go from 0 to 6. In the two week scheme, X will go from 0 to 13. I do not see a need to run the process twice to pick up the two days off. Is the code RDO for days off for everyone? If a checkbox is True, then use the data from the form. If it is False, use RDO. From where I sit you should be able to Loop through the two weeks in much the same manner as you did for the one week entry. The Command4 Click event would process an Else in the If statement (this would be the day off) and here you would insert 'RDO' in the code field. Getting the picture?
ASKER
GRay:
Our pay periods always start on a Sunday and end on a Saturday. I will need an end date as per your suggestion. Yes, RDO will always be the code for days off for all employees. I use underscores when I'm designing in SharePoint, so its a force of habit. I cleaned it up as best I could for troubleshooting. It seems that I created this program without anticipating that I would be seeking assistance from Experts. So, it is important to follow proper naming conventions to mitigate the time it takes to solve a problem. Thanks for hanging in there and I hope the code for the two-week loop is forthcoming. AlphaRoster-2-.mdb
Our pay periods always start on a Sunday and end on a Saturday. I will need an end date as per your suggestion. Yes, RDO will always be the code for days off for all employees. I use underscores when I'm designing in SharePoint, so its a force of habit. I cleaned it up as best I could for troubleshooting. It seems that I created this program without anticipating that I would be seeking assistance from Experts. So, it is important to follow proper naming conventions to mitigate the time it takes to solve a problem. Thanks for hanging in there and I hope the code for the two-week loop is forthcoming. AlphaRoster-2-.mdb
I will need and end date as per your suggestion. ??
I thought I said if it always starts on a Sunday and always end on a Saturday, why the End date?
ASKER
Just to be clear, the shift start day would always be a Sunday and the End would always be a Saturday.
Example for two-week shift:
4/17/2011 - 4/30/2011
Example for two-week shift:
4/17/2011 - 4/30/2011
OK, I will modify the form and get back to you in a day or two.
BTW, what is an Alpha-Roster? I also think you should throw the Gender table away and make M or F the only allowable content of the Gender field.
I modified the form to accept a two week input.
AlphaRoster-2-.mdb
AlphaRoster-2-.mdb
ASKER
GRay:
The RDO is not working properly for the second week. For example, if I use the span 5/1/2011 - 5/14/2011 with the 2nd and 3rd as RDOs for week 1 and the 11th and 12th as RDOs for week 2, the RDOs for week 2 reflect 9th and 10th.
So, it seems that the RDOs for week 1 are the same days for week two with different dates.
The RDO is not working properly for the second week. For example, if I use the span 5/1/2011 - 5/14/2011 with the 2nd and 3rd as RDOs for week 1 and the 11th and 12th as RDOs for week 2, the RDOs for week 2 reflect 9th and 10th.
So, it seems that the RDOs for week 1 are the same days for week two with different dates.
i am mystified. Later
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
GRay:
I will try on Wednesday when I'm back at the office. Thanks for the update.
I will try on Wednesday when I'm back at the office. Thanks for the update.
ASKER
GRay:
I'm in business. Thanks for hanging in there with me. Can I call on you again? With what I learned from you, I can better prepare a file for troubleshooting.
I'm in business. Thanks for hanging in there with me. Can I call on you again? With what I learned from you, I can better prepare a file for troubleshooting.
Thanks, glad to help. Call any time.