Link to home
Create AccountLog in
Avatar of BFanguy
BFanguyFlag for United States of America

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
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of 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
Avatar of Arana (G.P.)
Arana (G.P.)

Maybe change that second query to the seocnd list GOT FOCUS, so it wont run unnecessarily?
Avatar of 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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of BFanguy

ASKER

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