Continuous Form Auto Fill In

[Balfour211]

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

 

[Tom van Stiphout]

On Thu, 15 Apr 2010 17:35:01 -0700, Balfour211

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