I have a form with three subforms. The first one is based on a related table, and functions great. I can edit the information directly in the subform. My other two subforms are based on a query that uses the relationships between three related tables to get the fields I need to show here. It is returning the correct information from the correct table, and the relationships seem to be working fine, but I would really like to be able to edit the contents of this subform directly and have those changes carry to the appropriate tables as they are made.
My relationships seem unique, and although I think I know what I'm doing, and everything so far seems to be working quite well, I have to admit that there could be some problem with it. In that case, this paragraph hopefully will explain them well enough for problems to be found: The form in question is called ParcelInformationForm, and its source is the table, ParcelInformation. It shows all of the fields from ParcelInformation, and the primary key is ParcelNum, which is the link for each of the subforms. For the subforms I'm having trouble with, I used a query that uses the ParcelInformation table, but also a table called OwnershipAccountsByParcelNumber, which is linked to ParcelInformation by the ParcelNum field, which they have in common; the only other field in this table is AccountNum, and they are set as co-primary keys because I need this table to contain unique instances of the same account number in any given parcel number, but there can be the same account number in multiple parcel Numbers, so I think this relationship is working well. The trouble is that when I want the specific information for all of the accounts associated with that original parcel number, I have to then link to a third table, called AccountInformation, which is linked by account number to the OwnershipAccountsByParcelNumber table, but linked on the other side to the ParcelInformation table by ParcelNum. The only way I know to do this is a query that uses all three tables, showing the relationships described above, showing everything sorted by ParcelInformation.ParcelNum, but including the fields: OwnershipAccountsByParcelNumber.ParcelNum, OwnershipAccountsByParcelNumber.AccountNum, and all of the fields from the last table, AccountInformation. The two subforms go a little further by having search criteria in a couple of the fields from the AccountInformation table, basically to sort out active accounts from inactive ones, and to sort surface properties from mineral properties.
I just find it hard to believe that I can't have all of this information on the one form and have it all be editable in the one view of my form. The information from all of these forms is very appropriately related to be displayed this way, and it would be a shame to have to divide this down to multiple forms for what would have otherwise been very efficient data entry and retrieval.
Any help is greatly appreciated.
Let me know if I can elaborate on anything.