Subform processing... is this possible?

[Louie Warren]

I have a form... let's say it is made up of department
information. Each record resides in a table; one row for
each department. Within this form is a subform that
contains employees from an employee table. I want to be
able to view this in datasheet or continuous form mode,
have a check box or something to identify this employee is
associated with the department, and when SAVE is clicked
on the main form, a record will be inserted into the
department table, and the relationship will be set up for
each employee checked in the employee table or a lookup
table. Can this be done? If so, how? Thanx!

 

[Albert D. Kallal]

Ok, so, we have a table of our Departments, and a nice form to edit that
department.

We now need a table called WhoIsInDeparment.

This table is simply going to be a "list" of the people for a given
department.

You will of course make a sub-form based on this "list" of people who are
going to be in the above department. That table will thus have:

ID (this is autonumber id..and not really needed here)
Department_ID (id of the department)
EmployeeId (id of the employee

So, all you do is then type in the id of the employee into the sub form. It
is un-likely that you actually know the EmployeeId, so you can use a combo
box in the subform, so you can search by employee name...but STORE the id in
the field EmployeeID.

Often, a lot of people call the above a junction table, but I absolute HATE
that term. Look, what you need is a "list" of employees that belong to a
department. So, just make a table called WhoIsInDeparment, or even called it
DepartmentEmployeeList. Simply put, you just need a table that will list
the employees in a given department.

As mentioned, your sub-form will only need ONE field called the EmployeeId.
However, the relationship is going to be maintained on the Department_ID
field. You don't need, or have to display the department_ID field, but you
MUST set this field as the link master/child field so ms-access knows to set
the correct department of the employee you are entering.

 

[Guest]

As usual, I didn't make myself clear... my bad...

Table 1... contains Department information... which
includes an autonumber DepartmentID.

Table 2... contains Employee information... which includes
an autonumber EmployeeID.

Table 3... contains the link between the two; the
DepartmentID in Table 1 and the EmployeeID in Table 2.

The forms and database already exist. Currently, the user
has to deal with one row at a time; Department data, with
a subform showing one Employee at a time. When he adds a
new employee to a department, he must duplicate the
department data, and then add the new employee in the
subform. I have been tasked with changing the form, so he
can go into the subform, see a list of names, and check
each he wants, a laundry list if you will, to add
to/remove from the department. Upon save, an INSERT will
be done into the appropriate tables for the new department
members (with a check), an UPDATE will be done for changes
made to existing members (with a check), or DELETE to
remove a member that was "de-selected" (removed check).

The VB or whatever is beyond my expertise... Just looking
for some examples or the like. Thanx!

L

 

[Albert D. Kallal]

The forms and database already exist. Currently, the user
has to deal with one row at a time; Department data, with
a subform showing one Employee at a time

Why not just show all of the employess in a subform?

It seems to make so much sense. You find a depamrnt, and then you see a nice
list of emploeess for that depamrnet. What could be simpler?

When he adds a
new employee to a department, he must duplicate the
department data

Why must data be duplicated? You have a relatonal database here. With a good
deisng, you NEVER have duplicate a whole bunch of data. If you add another
emploess to the sub form, ALL OF THE information about the depmarnt can be
had any time with a relation join. Ther eis NO reason to copy that depmarnt
data. So, in place of copying that depmarnt data, why not just store a
deparment id. In fact, the sub-form arramgnet will do this for you WITHOUT
any code.

To be fiar, you are not clear what, or where the data is being dupliated.
HOwever, there is NO reason to "duplicate" deparmnt data.

, and then add the new employee in the
subform. I have been tasked with changing the form, so he
can go into the subform, see a list of names, and check
each he wants, a laundry list if you will, to add
to/remove from the department.

Well, you could write that with a lot of effort. But really, why not just a
sub form that lists all the curretn emploess that are in the depamrent. If
you need to add a few more emploeess to that depmarnt, then with a nice
combo box to let the person "select" the emploees is is so easy, and once
gain no code is needed. Also, there is little, or no reason why you can't
have a buttion in the sub-form that you hit and it pops up the emploeye to
you can edit/view the empooee reocred also.

You can get some ideas for displaying data in a grid with the following
screen shots:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

 

[Louie Warren]

Damn, I'm an idiot...

-----Original Message-----


It seems to make so much sense. You find a depamrnt, and then you see a nice
list of emploeess for that depamrnet. What could be

simpler?

Basically, I have two "versions" of this form; the one
that displays the current data, and the one that allows
him to add a new department to the company and pick and
choose employees for that new department from the company
list... the later is where I was going with my previous
post. An employee can be a member of multiple
departments. Excuse me for being dense, but how can I do
that with a simple subform?

Why must data be duplicated? You have a relatonal database here. With a good
deisng, you NEVER have duplicate a whole bunch of data. If you add another
emploess to the sub form, ALL OF THE information about the depmarnt can be
had any time with a relation join. Ther eis NO reason to copy that depmarnt
data. So, in place of copying that depmarnt data, why not just store a
deparment id. In fact, the sub-form arramgnet will do this for you WITHOUT
any code.

To be fiar, you are not clear what, or where the data is being dupliated.
HOwever, there is NO reason to "duplicate" deparmnt data.

Once again, I have not explained myself properly. There
is no duplication; the main form has static data, and he
then associates, via the subform, an employee with the
static department data on the main form. As mentioned
above, the main problem was in adding a new department to
the company and picking employees from the entire company
list.

Well, you could write that with a lot of effort. But really, why not just a
sub form that lists all the curretn emploess that are in the depamrent. If
you need to add a few more emploeess to that depmarnt, then with a nice
combo box to let the person "select" the emploees is is so easy, and once
gain no code is needed. Also, there is little, or no reason why you can't
have a buttion in the sub-form that you hit and it pops up the emploeye to
you can edit/view the empooee reocred also.

I have obviously made this harder than it should be. I'll
continue to plug away at it and check back here often.
You have definitely helped me figure things out! Thanx!

You can get some ideas for displaying data in a grid with the following
screen shots:

http://www.attcanada.net/~kallal.msn/Articles/Grid.htm

Thanx!

 

[Albert D. Kallal]

Gee...sorry about no spell checking...yuk!

Basically, I have two "versions" of this form; the one
that displays the current data, and the one that allows
him to add a new department to the company and pick and
choose employees for that new department from the company
list... the later is where I was going with my previous
post. An employee can be a member of multiple
departments. Excuse me for being dense, but how can I do
that with a simple subform?

The above is easy. (lets hold off on the issues of each company having
multiple departments). In fact, as I answer the question, I just assumed
that we were assign employees to a department. If we simply build a
department form with a "sub-form" of the "employers", then nothing stops you
from entering the same employees again for other departments at all. So, you
have complete freedom here. So, yes, it is just a simple sub-form for each
department that would be our main form.

However, you have now also clarified that each company needs a list of
departments. So, now once again we have the exact same concept for each
company that has many departments!. Each company can have many departments.
So, we need a main form with company and a sub-from that allows you to
select and add as many departments as you please to company in our simple
sub-form. (and, again as before, we need a table called
CompanyDepartmentsList). It will work very similar as the
departments->employees setup

Now, once you get the two above main forms working (with their corresponding
sub-forms), then all you need to is to place a button on the departments
sub-form to open up the department form with the sub-form of employees. So,
what you do is have a nice button in the departments sub-form. I showed many
examples of those buttons in continues mode in those last screen shots. So,
really both company to departments, and departments to employees is really
the exact same problem...but you just solve it two times!

If you read close in the above, it does mean that employees are never
actually assigned to a company, but are only being assed to the department
that *belongs* to a company.