Full Version: Making a combo box data follow the query
UtterAccess Forums > Microsoft® Access > Access Forms
AllenRhein
I have 2 tables. 1 with the basic church membership info in it and another one to store the birthdays in.
I have a key field in the basic table of Id and a field of Id# in the birthday table.
I have a query that runs that combines the 2 tables so I get the fields in the query of: Members name, Id#, Name(so i can store all of the family's birthdays by family member name), Month, and day.
What I want to do is build a form that will let me use a combo box to find the member family names and then add each individual family member's birthday and store that in the birthday table linked by ID number. So the table will have the family ID number, Family member first name and the month and day in it.
I have tried using the forms wizard and I can get the form to display all of the main family names in the combo box but not relate them to the family ID when I put the family member name in.
GroverParkGeorge
It is generally not a good idea to try to do data entry through forms which use multi-table queries as their recordsource. It is almost always preferable to base each form on a single table or query based on a single table for the recordsource. In this case, I believe that is also going to be the best approach for you here.

For that reason, you should adopt a form/subform design, which will allow you to display records form the family table in the main form and related records from your birthday table in the subform.

However, it is also very important to start with a sound table design in the first place.

THere are some suggestions for that. You need at least two tables,but I would be surprised is that is all you should have. The tables are for the families and for the family members. The family table contains attributes, or facts that pertain to the family. The family member table contains attributes, or facts that pertain to individuals who are members of a family.

The family table should have a primary Key (preferably using the AutoNumber) and it should be named something "FamilyID". I know that Microsoft publishes a lot of templates which uses simply "ID" as the name for primary key fields, but that is a shamefully inadequate practice.

The fields in this table will look like this (or similar fields). There may be additional fields in this table. All such fields provide ONE piece of data about families.

tblFamily
=========
FamilyID (Primary Key, autonumber)
FamilyName (Text field )
AddressLineOne(Text field )
AddressLineTwo(Text field )
City (Text field )
State (Text field )
PostalCode (Text field )

tblFamilyMember
=========
FamilyMemberID (Primary Key, autonumber)
FamilyID (Foreign Key, Long Integer)
FirstName
LastName
DateofBirth

The relationship between these two tables is one-to-many. One family has one or members. The relationship is defined on the FamilyID, which is the primary key in the family table and the foreign key in the family member table.

When you create a form, based on the family table, you can insert into it a subform based on the familiy member table and let Access coordinate between them by linking onthe Master and Child fields.

With that approach, your task will be much simpler.

Best of luck.

George
AllenRhein
I already have that structure in place.
The main table has the following fields:
Id auto number
names txt
children txt
address
email
The birthday table has the following fields:
Id# long integer
name txt
month long integer
day long integer
Oset the tables up this way because the person before me had used excel for the database....This really scares me to death when somebody uses a spreadsheet to keep data. Rather than figure out how to seperate all of the data out the fields like city state etc. I just used one field. I used 2 fields for the month and day of birth so I can use month header in the report when I print out the report for issue to the congregation.
I will try and use the form subform suggestion and see if it will track between the 2 tables.
GroverParkGeorge
The structure I described is different from the one you describe. You need to change your tables. I can pretty much guarantee that you won't get forms to work without that change.

Families are entities. A family can consist of one or more people. A single person constitutes a family. A husband, wife and 7 children constitute a family, and so on.

A person belongs to at least one family (for our purposes here I'm not going to go too far along the path of what constitutes a "family" in the era of widespread divorce and remarriage, and assume the traditional "one family per person" definition.)

People have various attributes, or characteristics, which define them: First Name, Last Name, Birthdate and Sex being the essentials. They also belong to a family.

To properly describe the families in your organization, therefore, you need ONE table for "FAMILY" and one for "FAMILYMEMBERS".

The link between them will be defined, not by a name, but by the properly defined Primary and Foreign keys.

Name is a particularly risky choice for defining relationships between tables, because, well to use an example close to hand, there are lots of people named "George", and lots of people named "Hepworth" and even, to my personal knowledge, at least three "George Hepworths" all alive today. Some of us are related. No way would it be safe to create a relationship between tables on the basis of any combination of those names. Even if you establish that, as of today, no two people in your organization share names, you can't gurantee that someone else will not sign up tomorrow who does have a name that already exists.

There is no apparent reason to create as separate birthdate table. "Birthdate" is an attribute of a person and it belongs in the "Person" table. The only reason you would segregate birthdays would be if each person had more than one birthdate, which, of course, is impossible. A person can have multiple anniversary dates (first day of school, first day on a new job, etc.) but not more than one birthdate. Attributes which have singular values like birthdates are part of the definition of the person and belong in the person table, as the full date on which the person was born.

Also, birthdates are composed of month, day and year, not just month and day. That is the essence of the "birthdate"--> it is the day of our births. If you are concerned about parsing out month and day for reports, there are very simple date functions to do that, i.e. Month() and Day(). Therefore, the advantages of storing the real birthdate outweigh any small gains having separate fields would offer.


THere are some links to a number of discussions that will be useful in understanding why this preparation work is so important.



Best of luck.

George



Edited by: GroverParkGeorge on Wed Aug 12 1:56:37 EDT 2009.
AllenRhein
I got the form/subform to do what I wanted...thanks.
HAs to not storing the birth year my church made a decision not to store ages just the month and day of birth.
Also the key fields are Id and Id#. So the relationship worked just fine.
GroverParkGeorge
Congratulations on resolving your immediate problem.
est of luck with the rest of your project. Post back when you run into difficulties again.
George
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.