navigate records on the form is slow

[masoud_sedighy]

i am using a form like form customer orders in northwind 2003 database. in my form there are 3 sub forms.

data of subform1 will be filtered by one of the field in the main form.
subform2 will be filtered by one of the field in the subform1 and ...

also i have added some code for highlighting current row of each form/sub form , now my problem is when navigate thorough records of main form by using navigation button , showing each record after pressing navigation button is a bit slow (it takes 5 seconds), i do not know what is the problem, and i do not know how to solve the problem, problem is from subforms or codes for highlighting current row? it is usual?

 

[JHB]

Try comment out all the code for highlighting current row, to get an idea off if it is the code or if it is the subforms, which slow down the navigation speed.

 

[masoud_sedighy]

i comment out all the code for highlighting current row, now the speed is better also when comment out requery of sub forms (Me.Parent![fsubFIN].Requery) is very better. I do not know how to solve problem because i need highlight and also requery of sub forms when select each record on the top sub form.​

[FONT=&quot] [/FONT]​

i attached my database. The main form is (frmItem_Details_new3).​

​

​

The code of on current event for one of subforms is like below:

  [LEFT][B]Private Sub Form_Current()[/B][/LEFT]
  [LEFT]On Error GoTo ErrTrap[/LEFT]
  [LEFT]    ' Assign row number to Rank (bound control)[/LEFT]
  [LEFT]    ' - if not a new record (This check is meant to[/LEFT]
  [LEFT]    ' prevent inadvertant adding of fresh records[/LEFT]
  [LEFT]    ' merely by navigating to a new record)[/LEFT]
  [LEFT]    If Me.NewRecord = False Then[/LEFT]
  [LEFT]        Me.Rank = Me.CurrentRecord[/LEFT]
  [LEFT]    End If[/LEFT]
  [LEFT]    [FONT=&quot][/FONT][/LEFT]
  [LEFT]    ' Assign current record nmber to TxtRef[/LEFT]
  [LEFT]    Me.TxtRef = Me.CurrentRecord[/LEFT]
  [LEFT]    [FONT=&quot][/FONT][/LEFT]
  [LEFT]    ' This statement is necessary for clean[/LEFT]
  [LEFT]    ' display of color highlights (clearing up[/LEFT]
  [LEFT]    ' hangover of previous highlights - if any)[/LEFT]
  [LEFT]    Me.Recalc[/LEFT]
  [LEFT]    [FONT=&quot][/FONT][/LEFT]
  [LEFT]    ' Note - TxtRef is an unbond text box in[/LEFT]
  [LEFT]    '            form header or footer, while Rank[/LEFT]
  [LEFT]    '            is a bound text box holding row number.[/LEFT]
  [LEFT]    [FONT=&quot][/FONT][/LEFT]
  [LEFT]    '            The following expression is used for[/LEFT]
  [LEFT]    '            highlighting the current row through[/LEFT]
  [LEFT]    '            conditional formatting:[/LEFT]
  [LEFT]    '            ([Rank]=[TxtRef]) Or _[/LEFT]
  [LEFT]    '            (Fn_NewRec()=True And [Rank] Is Null)[/LEFT]
  [LEFT]    Me.Parent![fsubFIN].Requery[/LEFT]
  [LEFT][FONT=&quot] [/FONT][/LEFT]
  [LEFT]ExitPoint:[/LEFT]
  [LEFT]    On Error GoTo 0[/LEFT]
  [LEFT]    Exit Sub[/LEFT]
  [LEFT]        [FONT=&quot][/FONT][/LEFT]
  [LEFT]ErrTrap:[/LEFT]
  [LEFT]    MsgBox Err.Number & " - " & Err.Description[/LEFT]
  [LEFT]    Resume ExitPoint[/LEFT]
    [LEFT][B]End Sub[/B][/LEFT]
  
  [LEFT][B]Conditional formatting: ([Rank]=[TxtRef]) Or (Fn_NewRec()=True And [Rank] Is Null)[/B][/LEFT]
  [LEFT] [/LEFT]

 

[JHB]

Conditional formatting takes time and you have a lot of them.
Maybe you can build in some function when the main form moves to an other record, that updating of other subforms in a subform Oncurrent will not take place.