If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Rule to prohibit someone from working on a project or in a time sl
Hi
I have a database table where you enter records of people with specific tasks in certain time slots. For example: Record 1: Jim Smith Project A Tues 10-12pm Record 2: Nancy Jones Project B Wed 2-4pm Record 3: Nancy Jones Project B Fri 10-12 Record 4: Jim Smith Project A Fri 10-12 Can I create a rule that prohibits entering records of certain people at specific time slots? For example, you cannot enter Jim Smith for Project A on Monday at 10-12? Thank you! Craig |
#2
|
|||
|
|||
Rule to prohibit someone from working on a project or in a time sl
"Craig" wrote in message
... Hi I have a database table where you enter records of people with specific tasks in certain time slots. For example: Record 1: Jim Smith Project A Tues 10-12pm Record 2: Nancy Jones Project B Wed 2-4pm Record 3: Nancy Jones Project B Fri 10-12 Record 4: Jim Smith Project A Fri 10-12 Can I create a rule that prohibits entering records of certain people at specific time slots? For example, you cannot enter Jim Smith for Project A on Monday at 10-12? Thank you! Craig Not at table level, no, but you should be using queries and forms to manipulate your data. If you use a form you can put some validation code in its Before Update event to determine whether or not the data is saved. Having said that, it might be worth considering using a combo box on a form, the combo could be set to show only available times for the user to choose from. Any mileage in that for you? Keith. www.keithwilby.co.uk |
#3
|
|||
|
|||
Rule to prohibit someone from working on a project or in a time sl
If you mean you do not want a duplicate of 'Jim Smith for Project A on Monday
at 10-12' then create an index in the table and set to unique. -- Build a little, test a little. "Craig" wrote: Hi I have a database table where you enter records of people with specific tasks in certain time slots. For example: Record 1: Jim Smith Project A Tues 10-12pm Record 2: Nancy Jones Project B Wed 2-4pm Record 3: Nancy Jones Project B Fri 10-12 Record 4: Jim Smith Project A Fri 10-12 Can I create a rule that prohibits entering records of certain people at specific time slots? For example, you cannot enter Jim Smith for Project A on Monday at 10-12? Thank you! Craig |
#4
|
|||
|
|||
Rule to prohibit someone from working on a project or in a time sl
From the data you provide, there is no way to determine that Jim Smith
Project A Mon 10-12 is disallowed. If that's what you really meant, you need a table of disallowed times per user/project combo. Or did you mean that times when users are already assigned are not available for new assignments? In either case, I would recommend that you split the start and stop times into separate fields, to handle this: if Jim Smith cannot be assinged on Mon 10-12, then presumably he also cannot be assigned Mon 9-11. If the two times are in the same field, then 10-12 9-11 and the assignment will be allowed. As Keith Wilby says, this is done at the form level, in the AfterUpdate event of the form that creates a new assignment. Say your input form is named fAsgn and contains the text fields txtEmpID, txtDayOfWeek, txtStartTime, txtStopTime, bound to fields in tblAsgn with the same names, but without the txt prefix. I'm assuming that you want to disallow any assignments whose times overlap existing assignments for a given person, regardless of project. Disclaimer - Untested Code! Create a query like this: SELECT EmpID FROM tblAsgn WHERE Forms!fAsgn!txtEmpID=EmpID AND Forms!fAsgn!txtDayOfWeek=DayOfWeek AND (Forms!fAsgn!txtStartTime = StartTime AND Forms!fAsgn!txtStartTimeStopTime OR Forms!fAsgn!txtStopTime StartTime AND Forms!fAsgn!txtStopTime=StopTime) Say you name it qFindOverlap In the fAsgn_AfterUpdate proc: If DCount("EmpID", "qFindOverlap") 0 then MsgBox "whatever" Cancel=TRUE End If An alternative is to build the criteria (the WHERE clause without the word WHERE) in a string (say sCrit) within your proc, and: If DCount("EmpID", "tblAsgn", sCrit)) then... However, this has two drawbacks - functions of this type run slower than queries, and building the string in code requires some complex punctuation to concatenate variables with literals. Good luck! -TedMi "Craig" wrote in message ... Hi I have a database table where you enter records of people with specific tasks in certain time slots. For example: Record 1: Jim Smith Project A Tues 10-12pm Record 2: Nancy Jones Project B Wed 2-4pm Record 3: Nancy Jones Project B Fri 10-12 Record 4: Jim Smith Project A Fri 10-12 Can I create a rule that prohibits entering records of certain people at specific time slots? For example, you cannot enter Jim Smith for Project A on Monday at 10-12? Thank you! Craig |
#5
|
|||
|
|||
Rule to prohibit someone from working on a project or in a time sl
Hi,
Well Keith is correct you will be doing this through a form and not the table - and it didn't look like is was only that you didn't want dupes for a person. But there are questions that need to be asked before anything can be considered. The big problems is how do you pick the times - if it is something that is standard the gets put in a table Like Tues 10-12, Mon 10-12, Mon 12-2 then Karls method of non dups will work but I don't think that is correct - for a meriod (sp) of reasons. i.e. what if you want 1-3, or 10-1. The time should be a start time and and end time. But beyond that lets talk about what you said "A rule that prohibits entering records of certain people" Basically for that you wan't another table that has those conditions. You will modify this to what your exact needs are (i.e. if you realy do have slots laid out instead of real time) PersonID ProjectID Days(use the day number) StartTime EndTime John Smith A Mon (2) 10:00 AM 4:00 PM John Smith Tues 8:00 AM 5:00 PM John Smith can't be assigned to project A between 10 and 4 on mondays and no projects on Tuesdays. This would be compaired to the entry as Keith said in the before update event when you put in the times. "Craig" wrote: Hi I have a database table where you enter records of people with specific tasks in certain time slots. For example: Record 1: Jim Smith Project A Tues 10-12pm Record 2: Nancy Jones Project B Wed 2-4pm Record 3: Nancy Jones Project B Fri 10-12 Record 4: Jim Smith Project A Fri 10-12 Can I create a rule that prohibits entering records of certain people at specific time slots? For example, you cannot enter Jim Smith for Project A on Monday at 10-12? Thank you! Craig |
Thread Tools | |
Display Modes | |
|
|