Still Having Problems with Forms

[painterz]

Hello All,

I've posted this problem in February and May and I still can't get around it.

Here is my basic set up:

Address (main form)-->Family (subform M/C = addressID)-->People (subform M/C = PeopleID)

The people have designations based on relationships within the Family--primary, spouse, or child. When my form misbehaves it is only showing me the primary at each address. If I change a "primary" to a "spouse" then that record disappears from form view.

If my underlying query is correct, then why would I only see the primary person for each address? It does seem to be filtering on "primary". I've checked my filters and my underlying query and I can't find anything that limits my records to those with a "primary" designation. I keep hoping the shoe maker's elves will solve this problem and one day my db magically works again, but I don't think they code :banghead:

Someone suggested a corrupt form--that could be possible but why does it work to begin with and then start limiting my records?

Someone else suggested I had a filter on--it does seem that's likely but I can't find where it is.

Also, one of my subforms is a datasheet. I thought maybe a datasheet subform and form view subform couldn't exist on the same main form. I changed my datasheet to form view and that didn't solve my problem.

The first record shown in the form is Kevin Magdison. He has a spouse but she doesn't show up.

I know someone out there has the answer to my dilemma.

Thanks in advance.

 

[CJ_London]

you need to tidy up your thinking - create the relationships and be clear about what you mean by people and family. The reason your form is not working as expected is that your linkchild and linkmaster properties are based on people, whereas you need family if you want to see all of them

 

[jdraw]

For what it is worth, I found an older MS Access template for Genealogy.
I found various references that led me to http://www.devhut.net/2015/10/27/ms-access-genealogy-database/

I downloaded the database and made a zip (attached). I do not know how good or bad it is, but it is in mdb format.

 

[painterz]

Hello CJ,

Unfortunately, you're not the only one to tell me I haven't thought this through. Evidently, I'm dense because to me it's clear. The family is a collection of people living at one address but doesn't contain their individual information.

You are correct, I am linked on PeopleID rather than FamilyID. Originally, I did link on FamilyID and it created errors so I changed to PeopleID. When I was linked on FamilyID, all people within the family had the primary's PeopleID so my data was linking to the wrong person. Do I need another master/child link somewhere?

I don't understand why the form works for a while and then stops. It seems it should either work or not. If I were to import the form again, it would work for a while and then stop again. I've made so many changes to this form since my last import, I would have to reinvent the wheel.

Thanks

 

[painterz]

Hello J,

Thanks for the database! I'll start digging in and see if it can help me.

 

[JHB]

...
When my form misbehaves it is only showing me the primary at each address. If I change a "primary" to a "spouse" then that record disappears from form view.

Because the query (qryFamily) for the form "Family" Record source, has the criteria that the PKRelationship should be equal to 1.

 

[painterz]

Hi JHB,

I removed the PKRelationship=1 from qryFamily and my frmPeople is still showing only the "primary" for each household. I have the master/child for frmPeople set to PeopleID. frmFamily should be the only form showing the "primary". Some households have more than one "primary"--two families living at one address. frmPeople should show all of the people within the family.

 

[JHB]

Sorry it is not clear for me what you want to be shown.
Could you please post some printscreens what you want.

 

[CHEECO]

I came across this database, if it is any help.

 

[painterz]

Thanks Cheeco, I'll check out the db.

JHB--I have no idea how to show screen shots of forms that aren't doing what I want them to do. The neighborhood input form is the way I want it to look, but on frmPeople (the big subform that contains the sub-subforms for phone numbers and notes), instead of showing 1 of 1 records for every single family, should show the total number of records per family, ie 1 of 2, 1 of 5, etc and allow me to go to those records. I was able to do this in April--no longer in May.

This has been a recurring issue with this form. I download a copy of the form, it works fine and then all of a sudden only shows the primary in each family. I have no idea what's causing it or how to get out of it. Yes, I can keep downloading the form and hopefully get all the changes made to the new form but this issue will happen again. I am trying to figure out why my form is so unstable.

Thanks everyone!

 

[jdraw]

Post a copy of your database -- compact and repair, then zip.
Give instructions to get to the issue; what you are doing;what you want to happen etc.

 

[CJ_London]

the db was uploaded on the original post and the issue is answered in post #2 and clarified in post #6 - the OP is mixing up the different FK's he has the linkchild/master set to people not families.

The reason it sometimes work is because sometimes the family FK is the same as the people FK (e.g. 1) so he gets the expected result, sometimes it is not so gets an unexpected result.

I did suggest that he completed the relationships to clarify what links to what but don't believe that has been done. Certainly wasn't acknowledged.

 

[jdraw]

Thanks CJ.

 

[painterz]

I did suggest that he completed the relationships to clarify what links to what but don't believe that has been done. Certainly wasn't acknowledged.

Hi CJ,

I'm missing something here. If I have my M/C FK set to Family, then all of the people have the same phone number and email address as the "primary". By having the M/C FK set to People, I'm able to record individual phone numbers and email addresses. How do I get around this?

You mentioned post #2 and #6. Is this within the same thread or do I look at the other times I've posted this problem? Is there a way for me to see all of my posts and the responses?

I didn't intend to be unresponsive to your suggestion. I definitely appreciate all of the help you have offered for this and other problems. I'm not sure how to "show" the relationships between the tables. I have the relationships set up in the queries, which is why I uploaded my db. How do I "complete relationships"?

Thanks,

painterz

 

[JHB]

...
JHB--I have no idea how to show screen shots of forms that aren't doing what I want them to do...

The way I do it, is to take a printscreen and insert it in Powerpoint, then I add some text boxes with a explanation what I expect to see/get or what ever I would like!

 

[CJ_London]

You mentioned post #2 and #6. Is this within the same thread or do I look at the other times I've posted this problem? Is there a way for me to see all of my posts and the responses?

same thread. To see all your posts, click on User CP to the left of the top bar, then list subscriptions. Alternatively click on Search>Advanced Search on the same bar, enter your user name a change the dropdown to 'Find Threads Started by User'

For relationships, in Access click on Database Tools>Relationships where you can drag and drop your tables and connect them together so you can see the full picture. These joins will then be repeated in new queries - and you can take a print screen (or use the snipping tool)

to take an image and upload to the thread.

Note that as far as queries are concerned, relationships are not set in stone, they form the 'skeleton' of your db - you can still add or change links in a query as required.

And as I think you understand the general rule for forms and reports is one form or report, one table - relationships are then reflected in the linkchild/linkmaster properties of the subform control and sometimes you need to do what you have done i.e. main form - properties, subform 1, families, subform 2, individuals with a linking control on the mainform to reflect your relationship between families and individuals

But since subform 1 is a simple list, you could just use a combo or listbox instead which would simplify things

 

[painterz]

Thanks JHB, that makes sense but I'm not a graphics user. I don't even have PP loaded on my computer.

 

[painterz]

Thank you CJ!!! I was thinking about your last post and I've been banging my head against trying to figure out my relationships. I think it's a design issue in my underlying queries because when my form was working, FamilyID wasn't being written to tblPeople, so I already had issues, but at least I could update my db. Now I'm dead in the water. I've been trying to form a concise design question to the forum and it's hard to condense my db down into a short paragraph.

 

[painterz]

the general rule for forms and reports is one form or report, one table - relationships are then reflected in the linkchild/linkmaster properties of the subform control

Hi CJ,

So if it's one table per form/subform, then my underlying query should just be a table? My current underlying query for frmPeople is qryNeighbors that contains four tables. Two of the tables provide the info for drop down boxes on the frmPeople. The other two tables show the the link between tblFamily and tblPeople. If my relationships are set, then I don't need to have it in my query?

I've attached a screenshot of the relationship between my main three tables and a pdf of all my tables. The surprising part is when I set the relationships, it looks like tblFamily should be the driving force behind my design, but tblAddress has to be at the top of the hierarchy because the addresses don't change -- the families come and go. This db is designed for a neighborhood.

Thanks

 

[CJ_London]

looking at your attachments, from what I understand your relationships are which is:

there are many addresses
each address can have many families
each family can have many people

then the FamilyID field in tblPeople should link back to the FamilyID in tblFamilies
and you don't need the peopleID field in tblFamily - unless it relates to the head of family. However if that is the case, that should be a yes/no field in tblPeople.


From your relationships view

I don't see what the purpose is for tblRelationships
I don't see the benefit of ListedPhone in tblPhone
tblTeenJobs is constructed poorly - what if you want to add another service such as car washing? you'll need to change the table design and forms and report to accommodate it.

What you should have is a table to list the jobs that teens can do (which can be added to as time goes on) called say tblSkills and what is called a join table between tblPeople and tblSkills to list the skills each person (teen) has.

Also the join between tblPeople and the new table should be a left join - not all people are teenagers and some adults will have those skills as well, but some people will have no skills at all (mores the pity) - and what about adult skills (tree felling, excavating etc).

Similarly the join between tblPeople and tblNotes should be a left join - not all people will have notes.

other fields I'm not clear about - why have childsphone in tblpeople when you have a phone table?
Wouldn't marital status be a part of relationships?

general comments
your naming convention - no spaces - good! mixing ID/PK bad. If a field is a primary key, use PK, if it is the family (or foreign) key, use FK.

without knowing more, tblPhoneType is probably not required - how may types are there? mobile/land? what else?

because the addresses don't change -- the families come and go

if this is the case you need 'datemovedin' and 'datemovedout' fields in tblFamilies, otherwise how do you know they are still there?

So if it's one table per form/subform, then my underlying query should just be a table?

correct - as I suggested before your main form could have tblAddress as the recordsource, then on that form you would have an unbound listbox called say lstFamilies with a rowsource of say

 SELECT FamilyID, FamilyName FROM tblFamilies WHERE AddressID=[AddressID]

then you people subform would have linkmaster set to lstFamilies and link child to FamilyID