Synchronizing two forms?

[eskelies]

Hello all,

I have two forms, which are already synchronized, however, everytime I page to the next set of data both queries run again. This slows down my database to a point of unreponsiveness. Is there a way to speed up these forms from running over and over?

[FishVal]

Hello all,

I have two forms, which are already synchronized, however, everytime I page to the next set of data both queries run again. This slows down my database to a point of unreponsiveness. Is there a way to speed up these forms from running over and over?

Hi, eskelies.

You definitely should provide some more information.

• What does "synchronized" means?
• What queries run?
• Any additional information you consider to help others to understand your situation.

Regards,
Fish

[eskelies]

Fish,

Sorry...Let me try and explain better. I have two queries below, which I know are large:

I am doing analysis on the data that is represented in the two queries. I do not use all of the data when I build forms around them, instead I use certain fields which populate within the appropriate form. For instance, I use Account Number, prior contribution yield, est contribution yield, etc. in query 1. The same thing goes for query 2, however, my goal is to synchronize form 1 (ie. query 1) with Form 2 (query 2), so that when I select a certain account number the information in form 1 and form 2 shows up under account number in the "new" form. I started doing this by taking an unbound form (ie. Form 3) and bringing in Form 1. Then from there I linked form 1's account number with form 2's account number. In this instance it works, however, everytime I try to bring up a new account number it seems as if Access is requesting query 1 and query 2 to run again. I was hoping there is a way to get this to run smoother and faster.

I hope there is enough information here to help me. Thank you.

QUERY 1:

SELECT [Current Accrual Yield Summary].[Account Number ], [Current Accrual Yield Summary].[Account Description ], [Prior Accrual Yield Summary].[SumOfContribution (Accr) Yield (Prior)], [Accrual Yield Impact of Lost Positions (Summary)].[SumOfYield Impact of Change in EI & EA on Lost Positions], [Accrual Yield Impact of New Positions (Summary)].[SumOfYield Impact of Change in EI & EA on New Positions], [AYCC No Position Changes With RC (Summary)].[AYCC NO Pos With RC], [AYCC No Position Change and No RC (Summary)].[AYCC No Pos & No RC], [AYCC With Position Changes & W/O RC (Summary)].[AYCC With Position Changes & W/O RC], [AYCC With Position Changes and With RC (Summary)].[AYCC With Pos Changes and With RC], [Accrual Yield Impact of Existing Swap Positions (Summary)].[SumOfYield Impact of Change in EI & EA] AS SWAPs, [Accrual Yield Impact of Pref Divs on Existing Pos (Summary)].[SumOfYield Impact of Change in EI & EA] AS [Preferred Dividends], [Accrual Yield Capstock Impact].[Yield Impact of Capstock], [Current Accrual Yield Summary].[SumOfCurrent Contribution (Accr) Yield]-[Est Current Day Accrual Yield] AS [Unexplained Difference], [Current Accrual Yield Summary].[SumOfCurrent Contribution (Accr) Yield], NZ([Prior accrual Yield Summary].[SumOfContribution (Accr) Yield (Prior)])+NZ([SumOfYield Impact of Change in EI & EA on Lost Positions])+NZ([SumOfYield Impact of Change in EI & EA on New Positions])+NZ([AYCC NO Pos With RC])+NZ([AYCC No Pos & No RC])+NZ([AYCC With Position Changes & W/O RC])+NZ([AYCC With Pos Changes and With RC])+NZ([SWAPs])+NZ([Preferred Dividends])+NZ([Yield Impact of Capstock]) AS [Est Current Day Accrual Yield]
FROM ((((((((([Current Accrual Yield Summary] LEFT JOIN [Prior Accrual Yield Summary] ON [Current Accrual Yield Summary].[Account Number ] = [Prior Accrual Yield Summary].[Account Number ]) LEFT JOIN [Accrual Yield Impact of Lost Positions (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [Accrual Yield Impact of Lost Positions (Summary)].[Account Number ]) LEFT JOIN [Accrual Yield Impact of New Positions (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [Accrual Yield Impact of New Positions (Summary)].[Account Number ]) LEFT JOIN [AYCC No Position Changes With RC (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [AYCC No Position Changes With RC (Summary)].[Account Number ]) LEFT JOIN [AYCC With Position Changes & W/O RC (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [AYCC With Position Changes & W/O RC (Summary)].[Account Number ]) LEFT JOIN [AYCC No Position Change and No RC (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [AYCC No Position Change and No RC (Summary)].[Account Number ]) LEFT JOIN [AYCC With Position Changes and With RC (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [AYCC With Position Changes and With RC (Summary)].[Account Number ]) LEFT JOIN [Accrual Yield Capstock Impact] ON [Current Accrual Yield Summary].[Account Number ] = [Accrual Yield Capstock Impact].[Account Number ]) LEFT JOIN [Accrual Yield Impact of Existing Swap Positions (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [Accrual Yield Impact of Existing Swap Positions (Summary)].[Account Number ]) LEFT JOIN [Accrual Yield Impact of Pref Divs on Existing Pos (Summary)] ON [Current Accrual Yield Summary].[Account Number ] = [Accrual Yield Impact of Pref Divs on Existing Pos (Summary)].[Account Number ]
WHERE ((([Current Accrual Yield Summary].[Account Number ])<>9817 And ([Current Accrual Yield Summary].[Account Number ])<>9818 And ([Current Accrual Yield Summary].[Account Number ])<>9819));

QUERY 2:

SELECT [Prior Day Fund Data].[Account Number ], [Prior Day Fund Data].[Account Description ], [Prior Day Fund Data].[Account Class ], [Prior Day Fund Data].[Prior 5-Place NAV], [Earned Income and Amort NAV Impact Summary].[SumOfEarned Income NAV Impact ($)] AS [Earned Income], [Earned Income and Amort NAV Impact Summary].[SumOfEarned Amort NAV Impact ($)] AS [Earned Amort], [Expense NAV Impacts].[Expense NAV Impact ($)] AS Expense, [Income Distribution NAV Impact Summary].[SumOfIncome Distribution NAV Impact ($)] AS [Income Distribution], [Non-Futures Transaction NAV Impact Summary].[SumOfNet Gain/Loss NAV Impact ($)] AS [Net Gain/Loss (NF)], [Futures Transaction NAV Impact Summary].[SumOfNet Gain/Loss NAV Impact ($)] AS [Net Gain/Loss (F)], [Preferred Transactions NAV Impact Summary].[SumOfNet Gain/Loss NAV Impact ($)] AS [Preferred Transactions], [Distributed Gain Loss NAV Impact Summary].[SumOfDistributed Gain Loss NAV Impact ($)] AS [Distributed Gain Loss], [Capstock NAV Impact Summary].[SumOfCapstock NAV Impact ($)] AS Capstock, FValNAVImpactSum.[SumOfFair Value NAV Impact ($)] AS [Fair Value], [Prior 5-Place NAV]+NZ([SumOfEarned Income NAV Impact ($)])+NZ([SumOfEarned Amort NAV Impact ($)])+NZ([Expense NAV Impact ($)])+NZ([SumOfIncome Distribution NAV Impact ($)])+NZ([Non-Futures Transaction NAV Impact Summary].[SumOfnet Gain/Loss NAV Impact ($)])+NZ([Futures Transaction NAV Impact Summary].[SumOfnet Gain/Loss NAV Impact ($)])+NZ([SumOfDistributed Gain Loss NAV Impact ($)])+NZ([SumOfCapstock NAV Impact ($)])+NZ([Preferred Transactions NAV Impact Summary].[SumOfNet Gain/Loss NAV Impact ($)])+nz([sumoffair value nav impact ($)]) AS [Pre Pricing NAV Impact], [Non-Futures Security Price NAV Impact Summary].[SumOfNon-Futures Security Price NAV Impact($)] AS [Non-Futures Security Price (NF)], [Futures Security Price NAV Impact Summary].[SumOfFutures Security Price NAV Impact($)] AS [Futures Security Price (F)], [Amort/Accret adj to NAV].[Amort/Accret NAV Impact Adjustment ($)] AS [Amort/Accret Adjustment], [Preferred Security Price NAV Impact Summary].[SumOfPreferred Security Price NAV Impact($)] AS [Preferred Security Price], [Swap Mark to Market Transaction NAV Impact Summary].[SumOfSwap NAV Impact by Class ($)] AS [Swap by Class], [Pre Pricing NAV Impact]+NZ([SumOfNon-Futures Security Price NAV Impact($)])+NZ([SumOfFutures Security Price NAV Impact($)])+NZ([Amort/Accret NAV Impact Adjustment ($)])+NZ([SumOfPreferred Security Price NAV Impact($)])+NZ([SumOfSwap NAV Impact by Class ($)]) AS [Final Pricing NAV Impact], [Amort/Accret adj to NAV].[Current 5 Place NAV], [Final Pricing NAV Impact]-[Amort/Accret adj to NAV].[Current 5 Place NAV] AS [Unexplained Variance], [Prior Day Fund Data].Benchmark, [Prior Day Fund Data].[Investment Desk], [Prior Day Fund Data].Objective
FROM ((((FundClassList INNER JOIN (((((((((([Prior Day Fund Data] LEFT JOIN [Expense NAV Impacts] ON ([Prior Day Fund Data].[Account Class ] = [Expense NAV Impacts].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Expense NAV Impacts].[Account Number ])) LEFT JOIN [Income Distribution NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Income Distribution NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Income Distribution NAV Impact Summary].[Account Number ])) LEFT JOIN [Swap Mark to Market Transaction NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Swap Mark to Market Transaction NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Swap Mark to Market Transaction NAV Impact Summary].[Account Number ])) LEFT JOIN [Earned Income and Amort NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Earned Income and Amort NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Earned Income and Amort NAV Impact Summary].[Account Number ])) LEFT JOIN [Distributed Gain Loss NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Distributed Gain Loss NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Distributed Gain Loss NAV Impact Summary].[Account Number ])) LEFT JOIN [Futures Transaction NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Futures Transaction NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Futures Transaction NAV Impact Summary].[Account Number ])) LEFT JOIN [Futures Security Price NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Futures Security Price NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Futures Security Price NAV Impact Summary].[Account Number ])) LEFT JOIN [Non-Futures Transaction NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Non-Futures Transaction NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Non-Futures Transaction NAV Impact Summary].[Account Number ])) LEFT JOIN [Non-Futures Security Price NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Non-Futures Security Price NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Non-Futures Security Price NAV Impact Summary].[Account Number ])) LEFT JOIN [Preferred Security Price NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Preferred Security Price NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Preferred Security Price NAV Impact Summary].[Account Number ])) ON (FundClassList.NewClass = [Prior Day Fund Data].[Account Class ]) AND (FundClassList.[Account Number ] = [Prior Day Fund Data].[Account Number ])) LEFT JOIN [Preferred Transactions NAV Impact Summary] ON ([Prior Day Fund Data].[Account Number ] = [Preferred Transactions NAV Impact Summary].[Account Number ]) AND ([Prior Day Fund Data].[Account Class ] = [Preferred Transactions NAV Impact Summary].[Account Class ])) LEFT JOIN [Capstock NAV Impact Summary] ON ([Prior Day Fund Data].[Account Class ] = [Capstock NAV Impact Summary].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Capstock NAV Impact Summary].[Account Number ])) LEFT JOIN [Amort/Accret adj to NAV] ON ([Prior Day Fund Data].[Account Class ] = [Amort/Accret adj to NAV].[Account Class ]) AND ([Prior Day Fund Data].[Account Number ] = [Amort/Accret adj to NAV].[Account Number ])) LEFT JOIN FValNAVImpactSum ON (FundClassList.[Account Number ] = FValNAVImpactSum.[Fund Number]) AND (FundClassList.NewClass = FValNAVImpactSum.[Account Class ])
WHERE ((([Prior Day Fund Data].[Account Number ])<>9817 And ([Prior Day Fund Data].[Account Number ])<>9818 And ([Prior Day Fund Data].[Account Number ])<>9819 And ([Prior Day Fund Data].[Account Number ])<>9898));

[FishVal]

Ok.

Quite impressive.
Could you post more details of how do you populate Form3.
Is it bound to any of the recordsets (or to both of them)?
What does it mean "bringing in Form 1"?
Is Form3 expected to be read-only?
If any code is used, then please post it.

Regards,
Fish.

P.S. You may also find interesting the article about How to Optimize Queries and Recordsets

[eskelies]

Form 3 can be either bound or unbounded. In this case, I have it being unbound.

I have some code in form 2, which is:

Private Sub Form_Current()
If Not Me.NewRecord Then
    RequeryAccount Number
End If
End Sub

The subform control (form 2) has link master fields which is below. This protects my database from manual updated records.

My source Object is: query 2

Link child Fields: Account Number

Link master fields: txtaccount number

Form 2 and Form 1 are both bound to query 1 and query 2.

I would expect form 3 to be read-only with form 1 and 2's data changing together.

The issue I am having reminds me of dynamically synchronizing combo boxes. The difference being they are specific forms working together and populating different data based on account numbers.

Hope this helps!!

[FishVal]

Hi, eskelies.

I would like to clarify that the very nature of your question requires a complete understanding of the forms interactions. So far I couldn't give you a helpful advice just because I still have a little idea about what is going in your db.

Form 3 can be either bound or unbounded. In this case, I have it being unbound.

I have some code in form 2, which is:

Private Sub Form_Current()
If Not Me.NewRecord Then
RequeryAccount Number
End If
End Sub

Sorry, but the code makes so little sense .... )) Doesn't it?

The subform control (form 2) has link master fields which is below. This protects my database from manual updated records.

My source Object is: query 2

Link child Fields: Account Number

Link master fields: txtaccount number

Form 2 and Form 1 are both bound to query 1 and query 2.

Could you give a complete description of the forms interactions. If they are bound via form/subform relationships, then please specify form hierarchy. If they interacts via code, then please post it.

I would expect form 3 to be read-only with form 1 and 2's data changing together.

The issue I am having reminds me of dynamically synchronizing combo boxes. The difference being they are specific forms working together and populating different data based on account numbers.

Hope this helps!!

You may attach sanitized copy of your db.

Regards,

Fish

[eskelies]

Fish,

Sorry if my information is unclear. I thank you for taking the time to help me even though I am a pain in the butt. :)

Does a moderator have to give me the ability to attach files?

Hi, eskelies.

I would like to clarify that the very nature of your question requires a complete understanding of the forms interactions. So far I couldn't give you a helpful advice just because I still have a little idea about what is going in your db.



Sorry, but the code makes so little sense .... )) Doesn't it?


Could you give a complete description of the forms interactions. If they are bound via form/subform relationships, then please specify form hierarchy. If they interacts via code, then please post it.



You may attach sanitized copy of your db.

Regards,

Fish

[FishVal]

Fish,

Sorry if my information is unclear. I thank you for taking the time to help me even though I am a pain in the butt. :)

Hi, eskelies.

Don't worry, that is not a problem.

Does a moderator have to give me the ability to attach files?

No special permission is required to attach file.

• Sanitize your db: remove all sensitive information and leave dummy records in relevant tables, remove unnecessary objects
• Compact/repair
• Pack to zip or rar
• Having made a post, click on [Edit/Delete] button, then click on [Manage Attachments], upload your file and save the changes to the post

Regards,
Fish

[eskelies]

trying to upload database file....

[eskelies]

Fish,

what I want to happen is take query Accrual yield Impact Final and query NAV impact summary (r086) and synchronize them. When I say synchronize them I mean the following:
I would like the user to beable to go into frmselectfund. Once they select their fund they will then beable to run a button macro (which i have not built) that will pull up another form. Let's call it test. This form needs to have both queries built into the form. When you click forward both queries will change.

trying to upload database file....

[NeoPa]

Please use the [ CODE ] tags in all your future posts (See How to Ask a Question). As a full member now, you need to be a bit more aware of the rules.

ADMIN.

PS. Also subscribing to the thread.

PPS. Don't feel you need to include the previous post in your reply. Unless there's anything particular you need to refer to explicitly, this text can be junked.

[FishVal]

Hi, eskelies.

Sorry for delay.
I looked at your db hoping I could help you optimize queries.
But ... :) ... queries based on several tenths another queries is somewhat I could not sort quickly and easily. So I will left them as they are so far.
Lets return to the form.
As far as I understand you just need a form with 2 subforms that will be affected by a single navigation bar.

That is quite simple. I would attach a sample db where [frmMain] has [frm1] and [frm2] subforms and navigation bar of [frmMain] points both subforms to a record with a same PK value.
Additions in forms are disabled because it will require additional coding. Not complicated, but I guess just for starters this enough.

The code (in [frmMain] module):

Private Sub Form_Current()
    Me.frm2.Form.Recordset.FindFirst "keyID=" & Me.frm1.Form.keyID
End Sub
 
Private Sub Form_Open(Cancel As Integer)
    Set Me.Recordset = Me.frm1.Form.Recordset
End Sub
 

Does this look like what you are expecting for?

Regards,
Fish

[eskelies]

That is exactly what I am looking for. I actually did it another way. I don't know what is easier, so I will compare them both. I just made a make table of all the queries I used and linked master/child fields.

I apprecaite all your help.