Form/Subform relationship help

[bkirsch]

Database attached...




The "client" form is the primary form for the database. This form only shows client name (primary key) and a combo box. "Main" is subformed (one to many) and represents a contract or in some cases multiple contracts. "main" has 3 tabbed subforms (one to many as well) that act as contract details linked via "contract number".

I can't seem to figure out why when I add a 2nd contract for a clientto main form it doesnt blank the three tabbed subforms to add details. Instead it holds the 3 subforms or details from the previous contract.


Ideally, I would like to be able to select a client at the very top form and have it pull the multiple contracts and respective details in the "main" form and 3 tabbed subfroms....

[June7]

Cannot replicate the issue, when I click Add New Contract, all forms are blank.

Real problem I encounter is that can't add records to subforms. Even though added a record in 1 Premium CLIENTS, get a message can't add Details because record required in 1 Premium CLIENTS. Established Relationships don't agree with the Master/Child links. Relationships are set on CustomerID and ContractNumber. The Master/Child links are set on Company and Client. Which is correct? Think you need to fix the Master/Child links.

Why not a combobox for selecting the Company on main form?

The child link and company fields on subforms should be hidden or locked.

Suggest you downsize the forms.

[bkirsch]

Ya the issue you are talking about only happens after I zip the file? any suggestions?

I could do a combo box on the main with the drop down including date to differentiate between one clients contracts but when I tried it the combo box and form/combo box doesn't list records in alphabetical order. So I was using the "clients" form to group all contracts by the same company. Also, say when I had a second contract to "cheez it" and type "cheez it" in as the client on the "main" form the subform auto fills "client" name and "contract number" but it holds the previous cheez it contract "contract number" instead of using the new autonumber from the "main" form

[June7]

Zipping project could not have caused the issue with the Master/Child links.

Attached is a revision of your project according to my understanding of relationships and how I would set up Main Form.

EDIT: Purpose served, file removed.

[bkirsch]

Awesome I got it figured out...

LAST QUESTION:

I still cannot figure out how to append only new records from "contract details" to "amortization" using the y/n field on the "contract details" subform


Thank you again for all your help!

[June7]

The only Yes/No field in table 1SuiteDealDetails is Appended. This field is not on the subform.

Why do you need to use code to append record? Just go to the tab and start entering. What data needs to go to amortization? This will require VBA code in some event such as button Click or data control AfterUpdate.

Did you notice that I removed the Company/Client fields from the dependent tables? This was duplicating data. Why is Cash/Trade in the 3 dependent tables? Other fields are also replicated in 2 or all 3 tables (Partner Type, Sales Rep, Inventory Element, Escalator). Why?

[bkirsch]

The reason for the append/duplication is completely not my idea but the person I am creating this for requires it....Basically a sales rep will be entering the information in "main" and "1 suite deal details" then the append needs to duplicate the fields that are in both to "amortization" so that a member of the finance team can create the amortization schedule without having to look back at "1 sutie deal details" to enter the same data...

[June7]

That's just it. That is not a justification for duplicating the data in tables. Use unbound controls on Amortization with ControlSource that is an expression. The expression would refer to controls on Details to grab the data and display it. However, this means the values would not be editable on Amortization. You have comboboxes for the replicated fields. Should these be editable? Do you want to pass the data to the other table and still allow edit? If the data should not be altered then should not be duplicated in tables. Please clarify.

If the expression suggestion is appropriate, the follwing works best when I do this.

Name the subform container controls different from the object they hold, like ctrDetails.
Name the data controls different from the fields they are bound to, like tbxEscalator, or cbxInventoryElement.

Expression on Amortization would be like: =[Forms]![Main Form]![ctrDetails].[Form].[tbxEscalator]

[bkirsch]

ok this sounds like a much better way of doing it but I am confused on the controlsource expressions...

"Expression on Amortization would be like: =[Forms]![Main Form]![ctrDetails].[Form].[tbxEscalator]"

is "[ctrdetails]" the feild from main I would like to be the source in amort?
"[tbxescalator]' the feild name in amort?

sorry im not very good with this...

[June7]

Reread my previous post. It explains what the names are for. Both names are references to the Details subform. The expression goes in textbox on Amortization.

[bkirsch]

Im sorry but you lost me with the "subform container controls differing from the data they hold" and "data controls"...

[June7]

Name the subform container controls different from the object they hold, like ctrDetails.

Subforms are created by placing a subform container control on a main form. The container control 'holds' an object (table, query, form), this is set in the SourceObject property of the container control. With the form/subform in design view, select the edge of the 'subform', this selects the container control. Click inside the container control and this selects the object held by the container.

Name the data controls different from the fields they are bound to, like tbxEscalator, or cbxInventoryElement.

Data controls: textbox, combobox, listbox, checkbox, option group

[bkirsch]

Im sorry I just really cannot figure this out...is there an easier way without changing the names of anything?

[June7]

Don't see how I can be more explicit with instructions. I tested without changing names and it would not work.

You might review Access Help or some other tutorials about building form/subform.

You can give objects and controls any name you wish. This is a property setting.