none
Crosstab always pops up before the main form opens in MS Access RRS feed

  • Question

  • I have a main form contains two sub forms created in MS Access. There are two text boxes on the main form. After the user enters the start and end dates in the text boxes, and click on the search button, the information in these two sub forms should be recalculated and refreshed the results on the sub forms and show within the main form.

    Once, I changed the data source of a sub form with a crosstab query and saved it. I don’t know whether it is the problem coming from. Now, after the user enters the dates, and search the result, this crosstab query result opens by itself first before the main form with two sub forms open.

    I have deleted the record source of the sub form from the property, and assigned the record source for the sub form in the search button by code. But it didn't solve the problem.

    How can I stop the crosstab query to pop up? Thanks.

    Friday, July 20, 2018 3:00 AM

All replies

  • Hi Jane,

    I'm not sure I am following. Are you able to post some screenshots maybe?

    Friday, July 20, 2018 3:06 PM
  • Does the search button's Click event procedure or macro call the OpenQuery method or action?  If the RecordSource properties of the subforms are referencing the controls in the parent form as parameters, as I'd expect, then you merely need to Requery the two subforms.

    Ken Sheridan, Stafford, England

    Friday, July 20, 2018 3:44 PM
  • Private sub Output_Results_Click()

    ‘Delete query to delete all records in the Output table

    DoCmd.OpenQuery “Output_Records_Del”

    ‘Append query uploading new records to the Output table based on the dates that user entered in the Start and End date text boxed.

    DoCmd.OpenQuery “Output_Records_App”

    ‘Delete query to delete all records in the Output Total table

    DoCmd.OpenQuery “Output_Total_Del”

    ‘Append crosstab query to the Output Total table. Then another query re-arrange the Output Total table’s order assigned to the Output Total sub form’s record source.

    DoCmd.OpenQuery “Output_Total_App”              ‘The pop up query is from here

    ‘Refresh subforms

    [Output_Records subform].Requery

    [Output_Total subform].Requery

    End Sub

    Once I changed the Crosstab query directly to the Output Total sub form’s Record Source since the Crosstab had the correct result, but it did not reflect on the Output Total sub form. After I found out the problem, and fixed it, I changed it back to the original way which was the re-arrange order’s query assigned to the Output Total sub form’s Record source. Now the Output Total sub form display the correct result, but the Crosstab query result always shows before the main form with these two sub forms. I would like after the search button click, just the main form with these two sub forms opens without the Crosstab result as a datasheet popping up before them. 
    Saturday, July 21, 2018 3:49 PM
  • Below is my code for the search button:

    Private sub Output_Results_Click()

    ‘Delete query to delete all records in the Output table

    DoCmd.OpenQuery “Output_Records_Del”

    ‘Append query uploading new records to the Output table based on the dates that user entered in the Start and End date text boxed.

    DoCmd.OpenQuery “Output_Records_App”

    ‘Delete query to delete all records in the Output Total table

    DoCmd.OpenQuery “Output_Total_Del”

    ‘Append crosstab query to the Output Total table. Then another query re-arrange the Output Total table’s order assigned to the Output Total sub form’s record source.

    DoCmd.OpenQuery “Output_Total_App”              ‘The pop up query is from here

    ‘Refresh subforms

    [Output_Records subform].Requery

    [Output_Total subform].Requery

    End Sub

    Once I changed the Crosstab query directly to the Output Total sub form’s Record Source since the Crosstab had the correct result, but it did not reflect on the Output Total sub form. After I found out the problem, and fixed it, I changed it back to the original way which was the re-arrange order’s query assigned to the Output Total sub form’s Record source. Now the Output Total sub form display the correct result, but the Crosstab query result always shows before the main form with these two sub forms. I would like after the search button click, just the main form with these two sub forms opens without the Crosstab result as a datasheet popping up before them. 

    Saturday, July 21, 2018 3:51 PM
  • If all of the queries are so-called 'action queries' then I don't see any obvious reason, on the basis of the information available, why a datasheet of the Output_Total_App should open.  I'd suggest that, rather than calling the OpenQuery method of the DoCmd object, you try calling the Execute method of the Application object:

        CurrentDb.Execute "QueryNameGoesHere", dbFailOnError

    Ken Sheridan, Stafford, England

    Saturday, July 21, 2018 6:54 PM
  • Thanks Ken for your advice! I will try your method tomorrow and let you know.
    Tuesday, July 24, 2018 2:38 AM