Building Forms Right

[mjhopler]

Testdatabase.zipI have a database with loan data. In the primary table, the data is stored on the loan level (one record per loan). However, a certain borrower may have multiple loans (our one-to-many). I have built a form two ways and think the form with the subform is the right way; however, seems weird I need a subform when all data is coming from the same table.

A few issues:

1. In my form with a subform, when I change the officer name and customer name, it changes data in the TableWithData. No clue why this is happing, but the customer name of the first record selected changes to be the account number. If this is the right form to use, please help me figure out why it is changing CustomerName and stop it.
2. If it is better to use the one form, then I run into a couple problems:
   
   1. I am using an unbound field to select the customer name and want the records for each loan to appear below. I cannot get the unbound field to work unless I bring in the primary key for the table which is account number. By doing this, I only can show one account at a time.
   2. Second, even if I do get it to show all records continuous, down the road I do have other one-to-may relationships where I will need to bring in a subform. I believe this would not allow me to show the accounts in the continuous manner.

Thanks for the help. Note, I am not an expert in access. Please dumb down the answers as much as possible.

I have attached a sample database and I use Access 2010.

Thank you!!!

[June7]

In form/subform arrangement, both forms should not have the same data source.

Take the example of a purchase order and purchase order details.

Main form would be bound to Orders table and subform bound to OrderDetails table. The Master/Child links of the subform container control would synchronize the related records.

In your case, assuming a loan can have only one borrower, main form would be bound to borrower table (ID, name, address, phone) and subform bound to loans table (borrowerID, loanNum, loanDate, amount, etc). An alternative is a single form bound to loans table with a combobox to select borrower from a list.