Two Part - complicated question (To me at least...)

[Karen]

Hi

I have a Form that is designed using a tab control. Page 1 of the tab
control is of benefactors, page 2 of the tab control is of the deceased.
Basically, when benefactors call they do so to request prayer for the
deceased. Therefore, their name is added and the deceased person is added
(fields included on each page respectively) which is then added to the table
that is the record source. (both deceased and benefactor are in one table)

Question #1 - Based on the above, if more than one deceased is added for
each benefactor, the table is not repeating the benefactor name in the new
line its creating for the deceased, and therefore, it is not linked to the
correct benefactor. How do I fix this?

Once part one above has been added, the person that is assigned this "job"
to actually facilite the prayer for the deceased, is then assigned using a
"Worker Form". Their name is picked from a search List Box included on the
form and there is a subform with all the different prayer options in which
the user picks the prayer and selects SAVE to save the record. The record
source for this table is called "worker"

Question #2 - After I enter the deceased, I need to add a control to
immidiately jump to the Worker form, do as I described above for the worker,
and then when I select save, magically have the benefactor connected to the
worker which I then need to report on and keep track of. Make any sense??
If so, how do I do this?
(I should also mention that the form and subforms on this worker form are
linked in the relationship window and this part of the form is working
wonderfully,,,, just need to link the yungeleit to its benefactor.

In addition, question 2A - I need the ability to sometimes assign more than
one worker to a benefactor... so I may need to select a benefactor, select
the prayer, select save and before exiting the form, repeat the process using
different workers.

Can this be done?

By the way, using Access 2003

Thank you!!!

 

[John W. Vinson]

Hi

I have a Form that is designed using a tab control. Page 1 of the tab
control is of benefactors, page 2 of the tab control is of the deceased.
Basically, when benefactors call they do so to request prayer for the
deceased. Therefore, their name is added and the deceased person is added
(fields included on each page respectively) which is then added to the table
that is the record source. (both deceased and benefactor are in one table)

Question #1 - Based on the above, if more than one deceased is added for
each benefactor, the table is not repeating the benefactor name in the new
line its creating for the deceased, and therefore, it is not linked to the
correct benefactor. How do I fix this?

Once part one above has been added, the person that is assigned this "job"
to actually facilite the prayer for the deceased, is then assigned using a
"Worker Form". Their name is picked from a search List Box included on the
form and there is a subform with all the different prayer options in which
the user picks the prayer and selects SAVE to save the record. The record
source for this table is called "worker"

Question #2 - After I enter the deceased, I need to add a control to
immidiately jump to the Worker form, do as I described above for the worker,
and then when I select save, magically have the benefactor connected to the
worker which I then need to report on and keep track of. Make any sense??
If so, how do I do this?
(I should also mention that the form and subforms on this worker form are
linked in the relationship window and this part of the form is working
wonderfully,,,, just need to link the yungeleit to its benefactor.

In addition, question 2A - I need the ability to sometimes assign more than
one worker to a benefactor... so I may need to select a benefactor, select
the prayer, select save and before exiting the form, repeat the process using
different workers.

First off... you're building things backwards. Forms are *secondary* - they're
just windows, tools to manage data in tables; the tables are primary, and you
need to start there.

It sounds like what you actually have is a many to many relationship between
Decedents and Benefactors. Each deceased person may be prayed for by one or
more of your benefactors; each benefactor may pray for one or more deceased
persons... right?

If so, you need *three* tables: a table of Decedents; a table of Workers; and
a table of Assignments. The Decedents table would have a unique ID for the
deceased (perhaps an Autonumber), the person's name, and any other information
that you wish to record about that person, as a person. The Workers table
would have your prayer workers as a list, with a primary key WorkerID and that
person's name and contact information. The assignment table would have fields
for the decedent's ID, the workerID, and any information about this particular
assignment. If you are using a set list of prayers you would need a table for
that as well (but do leave an opening for the inspiration of the Holy Spirit).
You may also want other fields such as the date assigned and any comments from
the person praying.

You could use a Form based on the Workers table, with a Subform based on the
Assignments table to provide the linkage.

Note that your *FORMS* are not, and cannot be, linked in the relationships
window. The tables should be - Decedents linked one to many to Assignments,
Workers also linked one to many to Assignments, perhaps Prayers also linked
one to many to Assignments.

 

[Karen]

Thanks John -

I think you mis understood or I mis-wrote but either way, the tables of
course are the drivers and the forms are created as a window tool to manage
the data.
The tables are linked in the relationship window.

I initially did not have the benefactor and deceased seperated into two
tables but based on your recommendation - it is now done.

I need point #2 answered or maybe you did and I am not understanding - if I
just added a benefactor, (using my form) and want to use a control to jump to
the deceased form while ensuring that as I add the deceased person/s they
will be somehow linked to the benefactor....

 

[John W. Vinson]

I need point #2 answered or maybe you did and I am not understanding - if I
just added a benefactor, (using my form) and want to use a control to jump to
the deceased form while ensuring that as I add the deceased person/s they
will be somehow linked to the benefactor....

A Subform is the most straightforward way to do this. If you have a Form based
on the Benefactors table, with a Subform based on the Assignments table (using
the benefactor ID as the master/child link field), you can put a combo box on
the subform based on the Decedents table to allow entry of an assignment,
maintaining the link. It's a lot more work and a lot more code to open a new
form.

Or, if you have a more or less fixed list of benefactors, you can put a
similar subform on the decedent form to assign a benefactor to a (newly
entered perhaps) decedent, rather than assigning a decedent to a benefactor.
You could even do both!