Results 1 to 7 of 7
  1. #1
    Join Date
    May 2017
    Posts
    5

    Unanswered: Stopping queries to run when opening Form

    Hi All

    Posting first time on this forum. Its a very good forum on every level. I need some help on an access form i have created which has various tabs on it and each tab has queries (as datasheet) in it. These queries work with the value entered in combo box on the form. Now trouble is I have the backend database on a shared folder and frontend on local pc. When I try to open this form from a Main Navigation form, it tends to take a long time to open it. So i need a vb code which can stop these queries to run when the form opens which will make it open quicker. really appreciate if you can help with this.

    Thanks.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    603
    Provided Answers: 31
    DONT have lots of sub forms, instead, use 1 subForm outside the tab.
    Then when user clicks a tab,you swap out the rowSource for that tab.
    1. It doesn't run many queries
    2. Is saves memory which can fill up using your way.

    Code:
    Sub tab_change()
    Select case tab.name
       Case "Phones"
             Me.subForm.rowsource= "qsPhones"
       Case "Expenses"
         Me.subForm.rowsource= "qsXpens"
    End select
    End sub

  3. #3
    Join Date
    May 2017
    Posts
    5
    Many thanks ranman for your prompt reply.

    Sorry but I'm a bit confused on how to make 1 subform outside tab and click on the others. My thinking was to stop the queries running when the form opens up. For eg: Tab1 - runs employees query, Tab2 - runs employee salary query and Tab 3 - runs employee department info. Now a combo box on the form outside the Tabs controls the queries.

    What i've done is I inserted Employees Query into Tab1 (i.e. just drag and drop the query into that tab), similary Tab2 and Tab3 as well. Now when this form opens all these queries are running before the form totally opens up. If I press ESC then form opens up quickly. That's a work around which is not the right way of stopping the queries to run. I thought there will be a code similar to this : Me.Forms.queryname.RecordSource = "" . This will bring up no data. Please correct me if i'm wrong here. Thanks.

  4. #4
    Join Date
    May 2017
    Posts
    5
    Hi ranman

    I tried your method of querying on Tab Change but I'm getting error when I run it. Can you please help ? Appreciate it. Thanks.

    I used your code like this :
    Select Case TabCt11.Name
    Case "Tab1"
    Me.subform1.RowSource = "Queryname"

    Case "Tab2"
    Me.SubForm2.RowSource = "Query2"

  5. #5
    Join Date
    May 2017
    Posts
    5
    Hi Ranman, hope you are well. Really appreciate if you can please reply to my question. I have tried to use your Tab Change code but its not working. Please help. Thanks.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    401
    What error are you getting? Also have you got End Select after the Select Case statement as shown in the previous post?

  7. #7
    Join Date
    May 2017
    Posts
    5
    Yes I have added End Select at the end of and i tried two different commands. I'm getting Object required error.

    When I use Me.Subform1.RowSource = name of a query then i'm getting "Method or Statement not found" and when I use Me.Subform1.RecordSource then I'm getting Object required error.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •