Can I load a query on opening a form 1

[NeilT123]

I have recently moved from Access 2003 to Access 2007 and one of my more complex forms is now significantly slower to open.

I have checked through all of my queries to only load the results necessary and have Auto Correct set to off and have been through the form and set auto correct to off on all of the text boxes but performance has not really improved.

The form is a single form but moves from record A to B to C using a filter. There are three subforms and another subform in the form footer.

Is it OK to have a subform in the footer of the master form?

The information in the subform in the footer is not updateable, it is there for my information but it is derived from a quite complex set of queries.

As I move from record A to B and so on will the query behind the subform be rerun on each record move? If so is there any way I can run the query once when the form is opened and save the results rather than keep running the query?

Sorry about the lack of terminology. As always as help is much appreciated.

Neil

 

[dhookom]

One of the most important items when trying to improve perfomance is the proper use of indexes. You would typically index all fields used for joins, filters, and sorting.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Make sure you are updated on all patches service packs for windows and office. 2007 had many performance issues early on.

http://allenbrowne.com/Access2007.html

 

[NeilT123]

All relevant fields are indexed and SP2 was already applied.

After doing some digging I have decided that my query listed in the queries forum is the cause and so have decided to base part of the form on a temporary table.

I have made an update table query and copied the code into the sql on the open form button but am getting a compile error expected end statement at the last ; in the code.

Any suggestions on how to correct this error?

Private Sub CmdFertApplns_Click()
DoCmd.SetWarnings False 'switch off warning messages re adding & deleting records
DoCmd.RunSQL (SELECT qryfrmIFFertOMP4.OMApplicationIndex, qryfrmIFFertOMP4.IncludeInReports, qryfrmIFFertOMP4.FieldCode, qryfrmIFFertOMP4.OMApplicationMonth, qryfrmIFFertOMP4.ManureConsistency, qryfrmIFFertOMP4.ApplicationRateUnits, qryfrmIFFertOMP4.ManureName, qryfrmIFFertOMP4.OMApplicationType, qryfrmIFFertOMP4.OMApplicationRate, qryfrmIFFertOMP4.TotalNapplied, qryfrmIFFertOMP4.TotalP2O5applied, qryfrmIFFertOMP4.TotalK2Oapplied, qryfrmIFFertOMP4.TotalMgOapplied, qryfrmIFFertOMP4.TotalSO3applied, qryfrmIFFertOMP4.[2002], qryfrmIFFertOMP4.[2003], qryfrmIFFertOMP4.[2004], qryfrmIFFertOMP4.[2005], qryfrmIFFertOMP4.[2006], qryfrmIFFertOMP4.[2007], qryfrmIFFertOMP4.[2008], qryfrmIFFertOMP4.[2009], qryfrmIFFertOMP4.[2010], qryfrmIFFertOMP4.[2011], qryfrmIFFertOMP4.[2012] INTO tblfrmIFFertOMTEMP
FROM qryfrmIFFertOMP4;)
DoCmd.SetWarnings True 'reset warnings
DoCmd.OpenForm "frmIfFertAppln"
End Sub

 

[dhookom]

RunSQL expects a string variable or string value inside quotes.

Private Sub CmdFertApplns_Click()
DoCmd.SetWarnings False 'switch off warning messages re adding & deleting records
Dim strSQL as String
strSQL = "SELECT OMApplicationIndex, IncludeInReports, FieldCode, " & _
   "OMApplicationMonth, ManureConsistency, ApplicationRateUnits, " & _
   "ManureName, OMApplicationType, OMApplicationRate, " & _
   "TotalNapplied, TotalP2O5applied, TotalK2Oapplied, " & _
   "TotalMgOapplied, TotalSO3applied, [2002], [2003], [2004], " & _
   "[2005], [2006], [2007], [2008], [2009], [2010], [2011], [2012] " & _
   "INTO tblfrmIFFertOMTEMP " & _
   "FROM qryfrmIFFertOMP4;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True 'reset warnings
DoCmd.OpenForm "frmIfFertAppln"
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Thank you Duane for your help on this. I have copied the code and used it on another form and the improvement in speed in navigating across the forms now they are partly based on temporary tables is immense

Thanks again