How do you Populate a field o a form based on a field in another form ?

[JJordan]

I did add the Description to the Donation table for the reason you stated Cosmo. I did not need a one to many relationship. I still, however, need to learn how to do this. For example, I have a table for Contacts where ALL contacts are entered. If the Contact is a Family we serve, I also enter a BUNCH of info about he family. I have a linked Family table (linked to contacts) and then I have linked to Family table, tables for Children, DeceasedPerson, ProgramsAttended.

To enter New Family, you must First enter them as a New Contact. What I was trying to use this for (after realizing it was stupid to do it for the GIK description) was to have a Form that was for entering NewFamilies that starts with the Contacts form and and have the Family Form open with the ContactID (Linked field) already populated. I want people to be able to enter all the contact information and then click a button to open the form to enter faimly informati0on for that Contact. A sigle form with subforms does not work -- Too Many Fields. This may sound conusfing but it requires me to be able to open a form with the ContactID field populated by the one set for that Contact when you enter the new contact. .

I will work on it and review the stuff you have posted. Thanks for the help !

 

[HCoker]

I think I may have found it

Have you tried a macro that contains the SetValue command? I am missing some Access files on my home computer, and am going to try to see if it works at work today...but the way my book talks about it you do the following in your macro once your current record has been populated:

Open Form

SetValue where Item=field in newly opened table that you want to be automatically populated and the Expression variable = the field that you have populated in your first table.

I know this isn't VBA code, but the idea sounds logical (and simple!)

I'll let you know if it works for me later this morning...

 

[JJordan]

The form I am working with is to Add a NEW CONTACT (which is assigned a ContactID) and then Open a form to enter Famly Data for that contactID. It is not really populating a new form with data because the "Family" does not exist yet in the Family Table (both forms are being opened in Add mode for a NEW contact And New Family).

What I did that seems to work is create a new "FamlyContactFrm" to enter the basic contact information in teh Contacts Table. Then with a command button, I open the "FamilyDataFrm" and have the ContactID (Linked field between Contacts and Families tables) for the FamilyDataFrm set to a default vaule = forms!FamilyContactFrm!ContactID.

The FIRST command on the Button is to SAVE the existing record on the FamilyContactFrm." Then, borrowing from from code above, it checks to see if the FamilyDataFrm is OPEN and if it IS, it closes the form -- Next command is OpenForm FamilyDataFrm. These three steps are needed (save, close, open) so that the NEW contact ID will actually exist for the FamilyDataFrm to open with it set as the default value.

You have to Save form1, close and reopen the 2nd form with each new CONTACT you enter or the FamilyDataFrm will not have the required/needed ContactID Available.

I am sure there probably is a way to dynamically cause the ContactID field on form 2 to match whatever is on Form1 wihtout the save, close form2, open form2, when both forms are in ADD mode, but this seems to work for now.

 

[Cosmos75]

HCoker,

You can create the macro and then convert it to VBA (Access 2000)

JJordan,

I have a linked Family table (linked to contacts) and then I have linked to Family table, tables for Children, DeceasedPerson, ProgramsAttended.

It is my understanding that ideally if you have tables with similiar info, you'll want them in the same table even if that requires additional fields. So, I don't quite understand why you have different tables for for children, deceased person, contacts? Are you storing all the information for the person (Contact, Child, Deceased in one table and linking them to the appropriate table [Child, Deceased, ..])? What happens if a child grows up to be a contact? What happens if a contact needs to be added to the deceased table? Or am I just not understanding your table structure well enough?

You could take a snapshot of your relationships (using print screen button) and post a pic of that.

 

[HCoker]

JJordan,

If I understand you correctly, you are populating information in your New Contact form, one of the fields in which is "Contact ID". Then when you are finished you want to open the Family Data form and have the "Contact ID" field in that form automatically populated to match the Contact ID number from the New Contact Form.

You should be able to do that by having a macro that opens you Family Data form upon completion of the New Contract form, and then sets the value of the Contact ID field in the Family Data form to equal the value of the Contact ID field from the New Contact form. You do that using a "SetValue" command.

Does that make sense?

 

[JJordan]

Edit: Yes it does, I will try the Macro.

As to the structure and need for multiple tables, Caveat - I am NOT a programmer -- this is a volunteer project for Three Trees TableNames Below are made up for illustration of what I have (the actual names are worse - spaces and stuff I learned is inapproriate on some).

We have ONE table of Contacts. this includes all donors, volunteers, facilitators, families served, board members, and just contacts. Any one person can be multiple things -- a Contact can be a general Volunterr, A facilitqtor and a Donor for example. A family contact can be a family (obviously) and a Donor (we hope) and can become a facilitator.

TbleContacts - All contacts. In addition to Contact information, it has Yes/No fields named Donor, facilitator,, general volunteer, board member, newsletter. The Yes/No field is used to, for example, print a set of mailing labels for all contacts that want to get a newsletter, or send a mailing to all volunteers (or just to facilitoators or just to general vlunteers). The "Donor" yes/no field is automatically set to YES whenever a donation is entered for a Contact and is the one that I learned later is not needed to generate a list of Unique Donors, but it was already there and coded and in the forms, so I left it alone.

TbleDonatons (linked to Contacts by ContactID) - Miltiple donations per contact (we hope)

TbleFamilies (lnked to contacts with a family yes/no Box from TbleContacts - If Yes/No is YES, then the ContactID for that Contact is available for the TbleFamilies as the Linked field

Tblefamilies has a Unique Primary Key "FamilyID" and includes information on the contacts relationship to the children, other family contact names (maybe a Grandparent and a parent are listed, but only ONE is the "Contact" for the family.) Other basic info is kept about the family, race, programs attended (separate table becuase they can attend multiple programs).

TbleDeceased person is linked to TbleFamiles by the FamilyID (there may be more than one deceased person - both parents may have died or one parent and a sibling, so a table is needed for them). For each deceased person we keep track of cause of death, relationship to children, date of death, ...

TbleChildren is linked to TbleFamiies by the FamilyID because we track multiple children and a lot of information about each child. (Obviously there can be multiple Children in one family).

If a child from TbleChildren becomes a "Contact", they would be added as a new Contact for some "reason" -- not just becuase they are a child in a family we serve. Thus, they would need a separate "ContactID to be a Donor, or a Volunteer, or Facilitator.

TblProgramsAttended to track the programs a family attends. Linked by familyID to TbleFamilies. A family can attend multipel programs and we need to know programs completed, starte and not completed, etc.

This is probably not the best setup, but it is what I came up with when I needed to track multiple items for One contact.