How to disable form from allowing all subforms to have blank records?

[Clifford86]

I have two tables "TBL_Addresses" & "TBL_Families"



I have a two forms "FRM_Contacts" & "FRM_Families_Subform". The "FRM_Contacts is used to add/edit records. The "FRM_Families_Subform" is used on "FRM_Contacts" as a subform in datasheet view.

My problem is if a new record in "FRM_Contacts" form is created with data yet no data is entered in the subform and the "FRM_Contacts" is Saved/Closed. Then I have blanks records in "TBL_Families" which links to "TBL_Addresses".

I am trying to find out how to prevent this from happening. What I would like to happen is ::: If someone starts to enter data on the form "FRM_Contacts" yet leaves the subform blank. That an error message will show alerting them. Then take them back to the form to complete the blank record in the subform or give them the option to delete the blank record in the subform if there is already records in the subform. If no records at all in the subform then give the option to delete the main form that is linked to the subform.

The main goal is to make sure that there is no blank or missing records in the tables. Any help is much appreciated and the best approach to actually prevent blank records.

Thanks for the help in advance.
Clifford86

[davegri]

Do you have any default values set for the subform? That will cause the record to be created there automatically when the main record is created.

[Clifford86]

The "TBL_Addresses" have two default values, for State & Active Status. The "TBL_Families" have one default values for Active Status.
The two tables have a relationship with each other using a AutoNumber for ID_Addresses.

Them are the only default values that I am aware of.

Sincerely,
Clifford86

[davegri]

You'll need to remove the default value for TBL_Families. Then code the On_Enter event for the subform control to set that value. The subform record won't be created until you click into the subform.

[June7]

DefaultValue for subform fields should NOT automatically create record just because record on main form is entered. Something else is causing this.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ArviLaanemets]

Remove all default values from tables, and set default values for form controls linked to those fields instead. Or instead use form's BeforeUpdate event to update those controls with default values when they have no value entered.

When you have some default value for field in table, then whenever you add a new record, this field is populated with default value - i.e. the record will be updated when you close file or remove to another record.
When you have some default value for control in form, then whenever you add a new record, this control is populated with default value, but the linked field will be upated only when the record is saved. User can always broke new record creating pressing Esc (twice?). You also can use BeforeUpdate event to cancel updating when some key field controls (NB! Not having default value defined) are empty.

[Clifford86]

Thanks, however I don't even want the info in TBL_Addresses unless it has data in TBL_Families. I want data together or not at all.

This database is new and work in progress. I am going to attach a copy.
DTPHAC.zip

Sincerely,
Clifford86

[ArviLaanemets]

Looking at structure of table TBL_Families:
1. With current structure a family must have a single address;
2. The only way you need a separate table for addresses, is when several families and/or churches can have same address. Otherwise you can simply have all address fields directly in TBL_Families and TBL_Churches.

[Clifford86]

Right, the TBL_Families table actually list everyone in the church Adults to Children. So instead of entering address information for each person it is put in the TBL_Addresses table and is what I am wanting. As the TBL_Families table can have mutable people at the same address.

Sincerely,
Clifford86

[orange]

Clifford86,

Can I ask you to step back and describe to us what this database is intended to support in simple terms That is - the purpose of the database. I don't think we can make focused comments/assistance until we (me anyway) understand how the pieces fit together in simple terms.
Do you intend to have multiple churches at some point?
What exactly is a Contact?
Can different Family members live at different addresses?

Perhaps you could use some sample data to show/tell us what you want to store and what you want to get as the result of a query or report. You can make up names to illustrate your requirement as necessary. (Ethyl Ether, Polly Dactall, Porky Pig...)

Tables are where data is stored. Forms are the means by which users interact with the data. I would recommend you do not use spaces or special (non alphanumeric) characters in field and/or object names.

Good luck with your project.

[Clifford86]

The main goal for now is to be able to create a church directory and keep track of member attendance along with notes of services and special events.

Reports will be used for things like the directory, services details, service averages of attendance, member attendance averages, family "Household" attendance averages etc. Also day, week, month, quarterly and yearly reports to show averages of attendance for persons, household and church wide.

I am not directly looking at having multiple churches in one database. However be able to use the database at different churches is a possibility.

A contact is each persons information. Name, Birthday, Anniv., Cell Phone, Mailing Address, Physical Address, City, State, Zip. However since a person can live at the same address as another person is the reason I have a table for address and families. ::: So Luke, John and Cliff will have there own record in families with there name Birthday, Anniv., Cell Phone and notes because they all live at the same address (household) so they share the same address in the address table. Maddie and Gene live in another household with there own address, so they will share the same address in the address table and they have there own personal info in the families table. John2 lives by himself, so he will have his address in the address table with his personal info in the families table. Etc etc etc.

I hope this helps.

Sincerely,
Clifford86

[orange]

Good stuff.
More questions. Do you need to record any historic info such as Gina's/Robert's previous address(es)?
Services, Attendance,..households etc will require knowledge of the relationships among your tables.
As a suggestion, you may wish to step away from physical Access database and work (pencil/paper or similar) and build up a data model based on your description of "the business". What info do you need to record for Services for example? What fields are needed?

I recommend that you work through 1 or 2 of the tutorials from RogersAccessLibrary that are identified in this link. Once you have worked through a tutorial or 2, you can use the same process to design your own data model.

There are several other articles in the link that may help with concepts and techniques. One that you may find helpful is called "stump the model" which will assist you to review, and revise the data model. A tested, vetted data model will serve as a blueprint for your Access database structure. And from experience I can assure you that getting the database structure correct(tables and relationships) is key to a well functioning, maintainable database application.

Here is a link to a Youth Church data model at Barry Williams' site. It may give you some ideas.

Good luck with your project.

[Clifford86]

No I do not need to store any historic address info, if someone moves then the address will just be updated to the new address. If a person moves from the address but not everyone. Then that person will be moved to a new address for themselves. . Only thing along that line would be attendance/service records. Which would include the service type, time, day who was present or not present with a note for the service in general and a note for each person. The notes would tell info like if John was absent because he was sick or that a service was canceled because of weather.

The main goal for now is to get a church directory and from their over time other things will be added like the attendance info. I already have a good idea of what the goal is for the database.

I will look into the resources that you sent. Thanks for the sites.

Sincerely,
Clifford86

[June7]

So would a more appropriate field name than tblFamilies be tblPeople?

Why have ID_Church_Info field in tblAddresses? I sorta get why LNSort field is in tblAddresses but think it is probably not necessary. And HomePhone is there in case there is a land line for that address, as rare as that may be now.

Instead of associating POBox with physical address, might have fields for Mailing and Physical addresses in tblFamilies then POBox and Physical address would be separate records in tblAddresses.

If you want to prevent an address record without associated family records, I expect need code behind main form to check for existence of related family records when main form closes and if none found, remove address record.

[Clifford86]

So would a more appropriate field name than tblFamilies be tblPeople?

I sure guess it would be better and help prevent confusion of others.

Why have ID_Church_Info field in tblAddresses?

I am sure that's not valid. I was trying to figure something out. I just didn't remove it yet.

I sorta get why LNSort field is in tblAddresses but think it is probably not necessary.

I included this here for the directory report. To sort the directory by head of households last name.

HomePhone is there in case there is a land line for that address, as rare as that may be now.

It is rare. Though still possible. So I included it.

Instead of associating POBox with physical address

It would actually be in the Mailing address. If no Physical address then it would be left blank. I was just using filler data.

If you want to prevent an address record without associated family records, I expect need code behind main form to check for existence of related family records when main form closes and if none found, remove address record.

I am not sure what code to use to create this. Though it is something that will work.