Still Having Problems with Forms

[painterz]

Hi CJ,

Thank you for the in depth analysis! I've been trying to wrap my head around the list box idea. I like it and it does get rid of a subform. Thanks for the SQL statement. Here are the answers to your questions:

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

Yes, this is correct

I don't see what the purpose is for tblRelationships

I use the relationships when I make the directory. It helps me to join spouses and not roommates. I also use it for the Teen Jobs article in the neighborhood newsletter, the query is limited to PKRelationship=3 (child). I didn't include the Directory report or queries in my db so it would fit within the requirements for upload.

I don't see the benefit of ListedPhone in tblPhone

I use this field for the Directory report to allow residents to choose what they want listed (if anything) in the neighborhood directory. Also, if they've chosen to have their phone unlisted, I don't give out their number except to a Board member.

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

I see your point but I'm having enough problems getting my Neighborhood Input Form and underlying queries to work properly. The jobs are determined by the neighborhood and is for the kids only. It's not a big deal for me to add another field to the table and form. I've been with this neighborhood for 11 years and they've never added a job. The adults have other avenues to advertise their business on a bigger scale than our neighborhood newsletter.

why have childsphone in tblpeople when you have a phone table?

I agree, I should have included the childsphone in tblPhone. The childsphone is used for the Teen Jobs report. Once I get this other mess cleaned up, I can make that change.

Wouldn't marital status be a part of relationships?

I'm not sure where I was going with this. This database was originally used for my business and I used 'marital status' as a key to use 'Mr. and Mrs.' for a salutation. When I adopted my db for the neighborhood, I guess I thought it would be used in the same way but I have both spouses names now so it's redundant. I do use it periodically, for my own use, when there is a divorce (I use it to remind me of a possible name change) or when someone is widowed (so I know to check the directory report for any mistakes).

mixing ID/PK bad

You make a good point but it's too imbedded in everything now to make the change.

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

Phone types are home, cell, work, mobile (not used), fax (rarely used), pager, and unknown. I get information from different sources so I don't always know the designation of the phone number and I give it an 'unknown' until it's known then change the designation.

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

When the families move out, they've sold their house. There's no point in keeping their info so I delete it from the database. I do note the move in date in the notes subform.

Quote:
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

This one is throwing me off. I understand the Neighborhood Input Form's record source is tblAddress. However, frmPeople is based on qryNeighbors consisting of two tables, tblFamily and tblPeople, that I don't know how to pare down to one. The 'family' owns or rents their house, owes the HOA, and chooses to be listed in the directory. Those attributes are passed down to the people when I run my reports. If they owe the HOA, it shows up in a report. If they choose not to be listed in the directory, they're not. How do I get this down to one table?

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

AND

then the FamilyID field in tblPeople should link back to the FamilyID in tblFamilies

I think the M/C link should be FamilyID as well. However, as I mentioned earlier, when I set the M/C to FamilyID--everyone in the family gets the primary's PeopleID, resulting in the primary's phone number and email address.

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.

PeopleID does relate to the head of the family (PKRelationship=1 for 'primary'). If I add a Y/N field, would that simplify things? It would drop the link between tblFamily and tblPeople and may solve my problem in getting my form to work. tblPeople already has the associated FK so wouldn't it be redundant?

So, I'll try the list box and create a left join from tblNotes to tblPeople. I would assume in the list box I can select a name and it would show me all the people associated with that name. If that's the case, there's one problem solved.

Thanks for your input! I'll start working on the changes now.

 

[CJ_London]

some further observations

When the families move out, they've sold their house. There's no point in keeping their info so I delete it from the database. I do note the move in date in the notes subform.

a) deleting data is generally a bad idea, better to have a 'deleted' flag so you can exclude them in your queries. b) what happens if the move down the road? do you delete and the renter it all again? c) what is the point of adding a note if you have deleted the 'parent' record?

I use this field for the Directory report to allow residents to choose what they want listed (if anything) in the neighborhood directory. Also, if they've chosen to have their phone unlisted, I don't give out their number except to a Board member.

so shouldn't this just be a 'include in listings' flag?

You make a good point but it's too imbedded in everything now to make the change.

a few hours now will save 10's of hours later

 

[painterz]

Hi CJ,

I had a 'Move' button for when someone moved within the neighborhood. I just dropped it recently bc I've never used it. I would re-instate the 'Move' button. The 'move in' date goes with the new family's notes not the one that was deleted--their notes are deleted.

The 'list' is a listing flag. There's also one to have their name and email address listed, so I have three of them.

I've been working with the list box. It's been a long time since I've worked with controls so need to look up their properties. I'm positive I have code using a list box but not sure which db it's in. I've been waiting patiently for my memories to be retrieved from deep storage--they haven't surfaced yet :0

 

[painterz]

Hi CJ,

Thanks for all your help. I finally got my forms to work again!!! I kept my original design but changed the m/c links between frmFamily and frmPeople to FamilyID and dropped the PeopleID from frmFamily. I did update my table relationships as you suggested. I spend yesterday coding and working more today on some minor bugs. Other than that, my db is in great shape!

 

[CJ_London]

glad to hear it - good luck with the rest of the project