1-to-1 Relationship + SubForm

[Fuse3k]

Hello,

So, for the first time, I have a 1-to-1 relationship between two tables. But, I'm having an issue with the form...

The Main Table is tblProjects and the related table is tblFees. Not all projects have fees assocated to it. With that said, I expect to have 0 records in the tblFees table if the related project record has no fees.

For the form, I have tblProject as the main form and tblFees as a subform. I have many different subforms and use a drop down box that acts as a "menu". It simply change the subform's control source to the selected subform.

When I choose the tblFees subform it seems to automatically try and create a record, even if no parent record (Projects) was created yet. This obviously triggers and error. If the Project record was created already it still seems to add a record with all null values.

Essentially, I dont want a record created in tblFees unless the user supplies data in one of the tblFees fields.

Is this just a poor form design or is this a common issue for 1-to-1 relationships.

Thanks in advance.

-Fuse

 

[CEH]

First question is why the 1 to1 ? Is a project only going to have one fee? Id so then make a field in the table... More than one fee for the same project? Then it should be a one to many.

 

[Fuse3k]

tblFees has multiple fields:

QuotedFee
ActualFee
WaiverApproval
ReoccuringAmt
ReoccuringInterval
BillingStatus

etc..

my old design had these fields in the tblProjects table. This is my attempt to normalize. I was taught if you have large numbers of null fields in your table, the design is flawed. About 50% of our projects have fees associated to it.

 

[Tanya]

Just because you have a 1 to many relationship doesn't mean you have to have a corresponding record in another table.

Have a look at my example, I think it will achieve what you are looking for.

Cheers
Tanya