Form/Subform help

[BlueJacket]

I keep getting the "Control can't be edited, it is bound to an Autonumber field" error whenever I try to link a PropertyID to a new case.

I have a main form where I'm creating a new case for my Quiet Title table (tblQuietTitle). The user inputs the new case number and assigns an attorney and judge. I also have a datasheet subform that I want to link each property that is involved in that case. A property will only ever be assigned to one case, but one case can encompass multiple properties. I have a CaseNumber field in my Property Details table (tblProperties) to link the two tables.

Parent form query:

SELECT tblQuietTitle.CaseNumber, tblPropertyDetails.PropertyID, tblPropertyDetails.ClientID, tblQuietTitle.JudgeID, tblQuietTitle.AttorneyID
FROM tblQuietTitle LEFT JOIN tblPropertyDetails ON tblQuietTitle.CaseNumber = tblPropertyDetails.CaseNumber;

Subform query:

SELECT tblPropertyDetails.PropertyID, tblPropertyDetails.CaseNumber
FROM tblQuietTitle LEFT JOIN tblPropertyDetails ON tblQuietTitle.CaseNumber = tblPropertyDetails.CaseNumber;

Attached is the layout of my (basic, uncompleted) form. I feel like I'm running into this error because I'm going about this wrong and I need to be pointed in the right direction. I could create a new table that links the CaseNumber to PropertyID, but that doesn't seem quite right either...

Thanks in advance.

 

[Minty]

Remove the join / parent table from the sub form query. You should only need the child table fields in that form. Ditto with the parent form, you don't need the child fields in the query for the parent form.

 

[BlueJacket]

Good to know, and done. I'm still getting that error though.

 

[Minty]

You have to have a parent record before you can have a child record, which may mean your parent / child forms are actually the wrong way around?

 

[BlueJacket]

So in creating a new case, I have the new case flow as follows:

frmClientInfo ---> frmNewProperty ---> frmNewCase

This is how the work flows naturally as well, since we do stuff with properties that never become cases. So, basically I'm creating a new property, then I'm creating a new case, then the subform is to link the two, which I feel naturally fits in the New Case form.

 

[Minty]

If you have saved relationships with enforced referential integrity you'll need to remove those to enable you to create the records that way.

You know your business model - so if that is how you work then your database should reflect that.

 

[BlueJacket]

Isn't Enforced Referential Integrity good to have though?

I may just go ahead and make a new table for it. That will have the added benefit that jjuuussttt in case one property is apart of two cases at the same time, the database will already be able to handle it.

However, I still want to understand why this is happening.

 

[Minty]

Enforced Integrity can be a good thing, but as you have found sometimes it can make your life a little awkward.

In your case, the referential rules don't fit how your business actually (currently) works.
This can highlight a process gap that perhaps you didn't see before, or maybe a process improvement to close a potential error. "Databasing" your business processes can be helpful from this perspective!

 

[BlueJacket]

Interesting point to think about.

Thanks for your help.