How to link two subforms results in non-stop flickering of the form?

[CJ Jarvis]

Hello,
I have been trying to resolve this problem for a while and can't seem to find a solution anywhere.

I have a database with the following relationship setup: A) Contract table with field AssetID (AutoNumber) linked to table LineItems (One to many relationship). B) LineItems table with field LineItemID (AutoNumber) linked to two tables 1) LineItemsExpenseDetails (One to many relationship) and 2) LineItemsPaymentDetails (One to many relationship). The logic is that one contract can have multiple line items and each line item can have multiple expense and multiple payments. Not sure if the relationship optimally setup.

Created three datasheet subforms: 1) sfrmLineItems bound to table LineItems 2) sfrmLineItems bound to table LineItemsExpenseDetails and 3) sfrmPayments bound to table LineItemsPaymentDetails

Created a form to pull it all together as follows: An unbound text box to select the contract to view. Inserted sfrmLineItems to enter the multiple line items related to the selected contract. Next I inserted a Tab Control and named the first page "Expenses" and inserted the subform "LineItemsExpenseDetails". The second page I named "Payments" and inserted the subform "LineItemsPaymentDetails".

For a while could not get the expense and payment subform to link to the first LineItem subform's line item number. I read about a solution where you would insert an unbound text box in the Master subform and reference the field that is to be used as the link. In the subform that needs to be updated, reference the Name assigned to this unbound text box. This seemed to work ok for the Expense subform but when applied to the Payment subform and I previewed the form, it flickered uncontrollably. Can't seem to find a solution to this flickering or an alternate database setup solution. Your help is deeply appreciated.
Thanks in advance,
CJ

[ADezii]

This is one of those questions, at least for me, that is impossible to answer without having the physical Database in front of me.

[CJ Jarvis]

ADezii -
Thanks for your quick response. I have attached the database for your review.
CJ

[ADezii]

Give me a little time, and when I get a chance, I'll have a good look at it.

[ADezii]

CJ, before I can look at this DB in ernest, I must make sure that we are talking about the same DB.

A) Contract table with field AssetID (AutoNumber) linked to table LineItems (One to many relationship),

There is no [AssetID] Field in the Contracts Table (not Contract). There is a [ContractNumber] Field linked to the LineItems Table but also to an Assets Table.

B) LineItems table with field LineItemID (AutoNumber) linked to two tables 1) LineItemsExpenseDetails (One to many relationship) and 2) LineItemsPaymentDetails (One to many relationship).

There is no [LineItemID] Field in the LineItems Table, but a [LineItemNumber] Field. The LineItemsExpenseDetails and LineItemsPaymentDetails Tables do not even exist.

P.S. - You must be more detailed as to the information that you are providing, and to which is reflected in your Attachment.

[CJ Jarvis]

Good catch Dezii. You are absolutely correct. For your referenced (A) it was a typo error (guessing from lack of sleep trying to resolve and complete this database and hopefully not me losing my sanity). It should have been ContractNumber instead of AssetID; and for (B) I had redesigned and added to the database and submitted the newer version. Attached is the older one that should hopefully correspond with my original submission. My apologies for this. Please reference below for full details:
...............................

I have been trying to resolve this problem for a while and can't seem to find a solution anywhere.

I have a database with the following relationship setup: A) Contract table with field ContractNumber(AutoNumber) linked to table LineItems (One to many relationship). B) LineItems table with field LineItemID (AutoNumber) linked to two tables 1) LineItemsExpenseDetails (One to many relationship) and 2) LineItemsPaymentDetails (One to many relationship). The logic is that one contract can have multiple line items and each line item can have multiple expense and multiple payments. Not sure if the relationship optimally setup.

Created three datasheet subforms: 1) sfrmLineItems bound to table LineItems 2) sfrmLineItemsExpenseDetails bound to table LineItemsExpenseDetails and 3) sfrmLineItemsPaymentDetails bound to table LineItemsPaymentDetails

Created a form, frmContractsAccounting (data source is the Contract table), to pull it all together as follows: An unbound text box to select the contract to view. Inserted sfrmLineItems to enter the multiple line items related to the selected contract. Next I inserted a Tab Control and named the first page "Expenses" and inserted the subform "LineItemsExpenseDetails". The second page I named "Payments" and inserted the subform "LineItemsPaymentDetails".

For a while could not get the expense and payment subforms to link to the first LineItem subform's line item number. I read about a solution where you would insert an unbound text box in the Master subform and reference the field that is to be used as the link. In the subform that needs to be updated, reference the Name assigned to this unbound text box. This seemed to work ok for the Expense subform but when applied to the Payment subform and I previewed the form, it flickered uncontrollably. Can't seem to find a solution to this flickering or an alternate database setup solution. Your help is deeply appreciated.
Thanks in advance,
CJ

[ADezii]

@CJ - I tried to arrive at a solution which keeps your existing Table Structures, Relationships and Forms in tact. I feel as though I have partially accomplished this by:

1. Using the Main Form (Contact) in the context that it was originally.
2. Creating a true Sub-Form (LineItems) Linked directly to the Main Form (Contact).
3. Creating two, independent Forms (Payment and Expense Details) dynamically Linked to LineItems.
4. Keeping all Forms visible on the Main Form itself.
5. Auto Display of Contract Names along with their Start and End Dates.
6. Independent Find Contract capability.
7. Download the Attachment, paying particular attention to the Link Child/Master Field Properties as well as the Current() Event of the LineItems Form.

[CJ Jarvis]

Wow Dezii..you are awesome. I don't think that I can thank you enough. Your coding and database design is commendably efficient and much cleaner than my submitted version. Thanks for taking the time. I very much appreciate it and have definitely learnt something new today.

I guess my next hurdle is to try to filter the form then push reports out from this. I was looking at ways to accomplish this and my initial thought is to attempt creating a front page form to enter the Start Period, Start Year, End Period, and End Year and thereby forcing the contract form to show only data for that period/year range; then use that data to create reports summarizing the filtered data (summarized periods in the columns and summarized years expense and payments for the rows) . Is this a structure that you think is plausible? I am currently searching the threads to find anything close that could give me an idea how to accomplish this great feat (at least to a novice like me).
Thanks,
CJ

[ADezii]

I would imagine that you primarily need a Form dedicated to entering Contract Information only, given the varied number of Fields contained within.

[CJ Jarvis]

Yes, the form in the submitted database is dedicated to entering Contract information only. I created several forms so far: 1) a form to enter the contract main details and hyperlink copies of the contracts on my hard drive for viewing 2) a form to select the contract entered and update the expense and payments. I managed to put together a code (testing its accuracy now) that calculates the periods and the amounts to expense and write this to the expense table instead of entering it manually. Payments will be entered manually.

After all is said and done I have to output an amortization schedule like the below:

P1 P2 P3 ... Full Year
2011 Expenses $20 $10 $5 ... $35
2011 Payments $10
Total Prepaid/(Accrual)

2012 Expenses $20 $10 $5 ... $35
2012 Payments $10
Total Prepaid/(Accrual)

On the top I was thinking that I enter various fields from the actual contract (Contract Name, Counterparty Name, Contract Start Date, Contract End Date, Category...) and filter the data according to what is selected or show all if nothing is selected.

Yes, I know that its a big task and being an aging accountant trying to jump into database design leaves me at a disadvantage.
Thanks,
CJ

[ADezii]

Sounds like you need a Criteria Form listing several Fields that will actually Filter the Data for your Main Form.