asked on

Access 2010 Continuous form tied to a SQL Server view runs query twice before displaying records.

I have a continuous form based on a view from SQL Server 2008R2.
If i open the view it takes 10 seconds for the records to display.
If i open the form it takes 20 seconds for the records to display. You can see the Running Query in the bottom right, it runs for 10 seconds, then starts again for another 10 seconds.
It looks like it runs twice (and unfortunately takes twice as long).

Is this normal?

Any help would be greatly appreciated

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BFanguy

ASKER

Thanks guys,
I had two drop down list boxes, i have an afterupdate on box 1 that does a count of records for box 2, if the count is 1, i display the records on the form, else, i requery the box2 and set the drop down. so what i am seeing is the DCount/DLookup running, then the record source requery.

Private Sub Group_AfterUpdate()
If DCount("Resource", "Dashboard_Group_Resources") = 1 Then
Me.Resource = DLookup("Resource", "Dashboard_Group_Resources")
Me.FilterOn = False
Forms!Operations_Dashboard.RecordSource = "Operations_Dashboard"
DoCmd.Requery
Else
Me.Resource = ""
Me.FilterOn = False
Forms!Operations_Dashboard.RecordSource = "Operations_Dashboard"
DoCmd.Requery
Me.Resource.SetFocus
Me.Resource.Dropdown
End If
End Sub

Arana (G.P.)

Maybe change that second query to the seocnd list GOT FOCUS, so it wont run unnecessarily?

BFanguy

ASKER

arana,
good catch, no need to requery the form if they have not picked an item from the 2nd drop down box.
If DCount("Resource", "Dashboard_Group_Resources") = 1 Then
Me.Resource = DLookup("Resource", "Dashboard_Group_Resources")
Me.FilterOn = False
Forms!Operations_Dashboard.RecordSource = "Operations_Dashboard"
DoCmd.Requery
Else
Me.Resource = ""
Me.Resource.SetFocus
Me.Resource.Requery
Me.Resource.Dropdown
End If

SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BFanguy

ASKER

Joe, learn something new every day! this was the definitely the problem!!

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Cool !!