Prev: How can I check my back ground?
Next: An Open Letter Regarding visio john .........
From: Balfour211 on 15 Apr 2010 20:35
I am using Access 2003. I have a continuous form that has three fields. One
field is the date, and the other two fields are drop down Combo boxes that
have a table as their source. The first field is names and the second field
is tasks/jobs. There are fifteen people in the table that is the source of
the first combo box (call it Lname). The combo box input is limited to the
data in the table.

Every day, the same 15 people get a task, the task may change on any given
day. Right now, I have to go down the list and enter a different name into
"Lname" 15 times. What I would like to do is use code to refer to the index
table of names (tblIndex_Names) and with the push of a button, have the
fifteen names automatically entered into the fields. I would then just have
to go down the list and input the task for each person.

I do not want to hard code the names into the program, because every once in
a while the people change. In this case, I want to just change the names in
the Index Table and it would enter that person the next time I push the
button.

This is a simplified version of my problem. If I can find out a way to do
this, I will be able to integrate it into my overall project.

Thanks in advance,
Balfour211
From: Tom van Stiphout on 16 Apr 2010 00:20
On Thu, 15 Apr 2010 17:35:01 -0700, Balfour211
<Balfour211(a)discussions.microsoft.com> wrote:

I can see why you would want to do this, but I am going to advise
against it. The reason: it requires a sloppy database design, and data
integrity trumps convenience. The table design should be something
like this:
tblAssignments
AssignmentID PK Autonumber
TaskDate datetime required default Date()
EmployeeID long int FK required
TaskID long int KF required
Also you need a unique index on TaskDate+EmployeeID to ensure
employees only get a single task per day (if that is indeed a hard
rule).

That last "required" is clearly a business rule: it doesn't make sense
to have an assignment record without a TaskID. Someone who was not
given a task should not even appear in an Assignment table.
Yet when we try to insert 15 rows with blank TaskIDs, the insert will
fail and you will get an error message.

One suggestion I would have is if users often get the same assignment
as yesterday, you could write an append query to copy the 15 rows from
yesterday to today, fixing up the date but leaving EmployeeID and
TaskID the same. Then you can requery the form and fix up a few
records that may have different tasks than yesterday.

-Tom.
Microsoft Access MVP



>I am using Access 2003. I have a continuous form that has three fields. One
>field is the date, and the other two fields are drop down Combo boxes that
>have a table as their source. The first field is names and the second field
>is tasks/jobs. There are fifteen people in the table that is the source of
>the first combo box (call it Lname). The combo box input is limited to the
>data in the table.
>
>Every day, the same 15 people get a task, the task may change on any given
>day. Right now, I have to go down the list and enter a different name into
>"Lname" 15 times. What I would like to do is use code to refer to the index
>table of names (tblIndex_Names) and with the push of a button, have the
>fifteen names automatically entered into the fields. I would then just have
>to go down the list and input the task for each person.
>
>I do not want to hard code the names into the program, because every once in
>a while the people change. In this case, I want to just change the names in
>the Index Table and it would enter that person the next time I push the
>button.
>
>This is a simplified version of my problem. If I can find out a way to do
>this, I will be able to integrate it into my overall project.
>
>Thanks in advance,
>Balfour211
 | 
Pages: 1
Prev: How can I check my back ground?
Next: An Open Letter Regarding visio john .........