Modifications to an Access Database Template

Question

I am having difficulty making modifications to an Access Database Template.  I am using the "Charitable Contributions" template and need to add fields for the name of a second person connected with a charitable gift.  I had no problem adding the necessary fields to the Contributor table, but these additional fields are not being pulled through to the forms and reports in the database.  How do I get these forms and reports to see the new fields so that I can add these new fields to the forms and reports?

Many thanks,

Greg

Answer 1

What is the Recordsource?

I'm sure you can work out your problem with a properly normalized database, but ...

Answer 2

Scott:

I do understand the design issue that everyone has mentioned. However, our small community organization receives many donations from married couples who desire to be recognized for a joint gift but may or may not share a common family name.  For our purposes, we see them as a "giving unit" and need to acknowledge them together as well as send materials from our organization to them jointly.  Additionally, we receive gifts from many small charities and trusts. Again, there may be more than a single person at the entity that we want to acknowledge.  Sometimes they are family members, other times they are business partners.  Either way, we need to be able to acknowledge and address them jointly.  In all of the above cases. listing them separately would not be acceptable and sending them separate mailings would be viewed by them as wasteful.

In any case, the problem that I am having is that the Recordsource is NOT reflecting the added fields when I access it on the Gift form.  The new fields do not appear on the Available fields list.  I cannot understand why it is not updating.  If their something I have to update?  Any suggestions as to why this might be happening?

Many thanks,

Greg

Answer 3

 I had no problem adding the necessary fields to the Contributor table, but these additional fields are not being pulled through to the forms and reports in the database.  

Many thanks,

Greg

First, let me support what Karl and Ken have said. The foundation of a well run database is proper table design. If there is a potential for multiple donors per gift then you need three tables to model that. You would need a Gifts table, a Donors table and a GiftsDonors table to model the many to many relationship.

However, I'm responding to your question above. Adding fields to a table does not, automatically, add controls to the forms or reports. A form or report has a Recordsource. That property defines what fields are available to be bound to controls on the form or report. A Recordsource can be either a table or a query. If its a table, then the Recordsource will reflect the added fields. You can then add those fields to the form (or report) by either dragging the field from the Available fields list or adding a control from the Control list and selecting the field using the Controlsource property drop down list.

Answer 4

I'm not familiar with the template, but adding additional columns to a table in the way you have done would be bad relational database design.  The table should be decomposed to reflect the many-to-many relationship type between gifts and donors.  This would involve creating a further table which resolves the relationship type into two one-to-many relationship types.  The new table would have two foreign key columns referencing the primary keys of the table which model the gifts and donors entity types respectively. If you wish to record each donor's contribution separately the amount of the contribution would be a non-key column in the new table.  The table modelling the gifts entity type would thus no longer include a foreign key referencing the primary key of that modelling the donors entity type, as I'd imagine to be the case at present, or a column recording the amount of the gift if the individuals' contributions are recorded separately.

You might be opening a can of worms here as the database, as currently structured, will reflect that structure not only in its forms and reports, but also in the queries which are their basis, and in any macros or VBA code executed in the day to day operation of the database.  You could find that you need to make significant changes to the design of these objects.  This would probably not be unduly difficult, for an experienced developer, but could be time consuming.

Answer 5

I am not familiar with Template "Charitable Contributions" but to be able to have multiple contributors should not be a matter of adding fields but having a child table related one-to-many.

What is the table structure and name where you are trying to add fields?