Refresh a subform based on a query

[speedball]

Hello

I have a form that contains a tab control. On each tab is a subform whose recordsource is a query. When combo boxes on the main form are updated the query is updated and the subform updates.

The problem that I have is that it takes a long time to refresh the subform and I'm sure there must be a better way to do it.

The following is an extract of the relevant code that I have at the moment:

The main form is called 'WorkOutstandingViewMainFormBeta'
The subform is called 'WorkOutstandingViewTab1Form'

When the form is first opened...

    Me.WorkOutstandingViewTab1Form.Form.RecordSource = "WorkOutstandingQuery"

When a combo box on the main form is updated...

    Dim strSQL As String
    Dim strEditMode As String
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Set db = Application.CurrentDb
    Set qdf = db.QueryDefs("WorkOutstandingQuery")
 
    strSQL = _
        "SELECT " & _
            "[UsedDueDate]-Date() AS DaysLeft, " & _
            "* " & _
        "FROM WorkOutstanding " & _
        "WHERE " & ClientNameSelected & " " & _
        "AND " & DirectorsNameSelected & " " & _
        "AND " & TaskDateSelected & " " & _
        "ORDER BY " & Ordering & ";"
    qdf.SQL = strSQL
 
    Me.WorkOutstandingViewTab1Form.Form.RecordSource = "WorkOutstandingQuery"

The slow part is Me.WorkOutstandingViewTab1Form.Form.RecordSource = "WorkOutstandingQuery". Instead of this I've tried the following:

    DoCmd.Requery
    Me.Refresh
    Me.Requery
    Me.WorkOutstandingViewTab1Form.Form.Refresh
    Me.WorkOutstandingViewTab1Form.Form.Requery
    Me.WorkOutstandingViewTab1Form.Form.Repaint
    Me.WorkOutstandingViewTab1Form.Form.Recalc
    Forms![WorkOutstandingViewMainFormBeta]![WorkOutstandingViewTab1Form].Refresh

But none of these work (no errors, it just doesn't update).

Other points that may be relevant:
- The 'Link Child Fields' and 'Link Parent Fields' properties of the subform are left blank
- The recordsource of the subform is left blank and is only set to the query when the main form is opened. When it is closed the recordsource is set to "".

Any thoughts or suggestions would be welcomed

Thanks in advance
Speedball

 

[GinaWhipp]

Okay, I'm trying to understand why? Why not just put the field from the Main form on the Criteria line in the query? Your way seems to be the long way around...

EDIT: What version of Access?

 

[speedball]

Thanks for looking at this GinaWhipp!

I'm using Access 2003.

Within the WHERE clause of my query is a string called ClientNameSelected. There is a combo box on my main form related to the client name. If this combo box is left blank, then ClientNameSelected = "1 = 1". If a particular client is selected from the combo box, then ClientNameSelected = "[The particular client chosen]".

This is done to ensure that all clients are displayed when the combo box is left blank, without having NULL problems.

So I think that I am putting the field from the main form (i.e. ClientNameSelected) into the criteria line of the query (which I assume is the same as the WHERE clause in the query's SQL). But please let me know if I've misunderstood what I'm doing / what you're asking.

Also, at the moment my main form has no underlying table or query that it is linked to. It just contains unbound combo boxes, unbound text boxes, some buttons, and the tab control which contains the subforms.

Thanks again
speedball

 

[GinaWhipp]

Are the subforms without a RecordSource? Is there a reason you chose to do it this way... Oh, and yes, that is what I meant with the query, place that in the WHERE clause.

 

[speedball]

I've set the subforms to be without a RecordSource, and then define the RecordSource to be the query called 'WorkOutstandingQuery' when the main form is opened.

I've read that doing this makes opening forms quicker than if the RecordSource is already set before the form is opened. Overall, my database is quite slow on some machines in our office so I'm willing to try anything to speed it up. If you think it would be better to set the RecordSource of the subforms using properties in design view so that it is predefined, I'll do it that instead.

However, I have tried this and it didn't help with my overall problem of refreshing the subforms when the query is updated.

Thanks
speedball

 

[GinaWhipp]

Before we go making to many changes... what version of Access? And...

Try looking into here…
http://www.granite.ab.ca/access/performancefaq.htm
Speed up Database…
http://www.blueclaw-db.com/access_consultant_rapid.htm

 

[speedball]

Hi Gina

I'm using Access 2003.

Thanks for the links. Actually, I think that the reason I set the subforms to be without a RecordSource was after reading the following section from the granite website that you have suggested
http://www.granite.ab.ca/access/performanceforms.htm

I'll definitely spend some time going through the other website and the other parts of the granite site.

I had a thought last night. Could my problem be with the way that I'm updating the SQL of my query?

If I were selecting certain records from a table I would just include a SQL statement in my VBA code, but here I have the following additional code before the SQL statement...

Dim strSQL As String
Dim strEditMode As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Set db = Application.CurrentDb
Set qdf = db.QueryDefs("WorkOutstandingQuery")

Could this be the problem? And if so, is there another way that I can update the SQL of the query?

Thanks
speedball

 

[GinaWhipp]

This could be the problem but perhaps you could explain what you mean by...

...update the SQL of the query?

And why are you trying to UPDATE the query?

 

[speedball]

Hi Gina

1. My understanding is that you can create a query in VBA, e.g. "SELECT Records FROM Table WHERE Condition", or you can create a query in Access, i.e. by double clicking on 'Create query in Design View'.

The query created using Design View can be viewed in 'SQL view' and this SQL statement can be changed in VBA.

When I say "update the SQL of the query" I mean using VBA to change the SQL statement that you see when you open the query in SQL view.

It could be a lack of my understanding about queries, but it seems as though there is a difference between creating a query in VBA and creating a query with Design View. For example, queries created in VBA don't show up in the Access Database window in the same way as "Design View created queries" do.


Actually, I've had some further thoughts about my problem (which I was about to post but then saw your most recent reply) and would I'd really appreciate your thoughts on them...

2. I've tried another way of getting the information to display in my subform...

What I was doing before
- Data stored in a table called 'WorkOutstanding'
- Query called 'WorkOutstandingQuery' that looks at WorkOutstanding
- Update the SQL of WorkOutstandingQuery each time a combo box on my main form is updated
- Reset the RecordSource of the subform to WorkOutstandingQuery

Alternative
- Data still stored in a table called 'WorkOutstanding'
- When a combo box is updated, reset the RecordSource of the subform to a SELECT SQL statement that filters out the records that I don't want

So my code now looks like this:

    SubFormSQL = _
        "SELECT " & _
            "[UsedDueDate]-Date() AS DaysLeft, " & _
            "* " & _
        "FROM WorkOutstanding " & _
        "WHERE " & ClientNameSelected & " " & _
        "AND " & DirectorsNameSelected & " " & _
        "AND " & TaskDateSelected & " " & _
        "ORDER BY " & Ordering & ";"
 
    Me.WorkOutstandingViewTab1Form.Form.RecordSource = SubFormSQL

This alternative method is a little quicker (e.g. with 5000 records in WorkOutstanding it takes 1.109 seconds to update vs 1.125 seconds with the previous method).

It seems that calculating a query on the fly in VBA is quicker than updating a predefined query. Does that sound right? (Intuitively, I would have thought that it would be the other way round!)

3. Regarding my initial question about why using .Refresh, .Requery, etc weren't working: Having thought about this some more, presumably it doesn't work because there aren't any changes to the underlying data, I'm just changing what what is being viewed by amending the RecordSource. And therefore the only way to update my subform is to refresh the subform's RecordSource? Does that sound right? I assume that .Refresh, etc are used when there is a change to the actual content on an underlying record?

4. If I'm right in thinking that the only way to update my subform is to refresh the subform's RecordSource, is there a quicker way to do this than my 'Alternative' method above? I'm not sure if what I have is the quickest method and I'm just being unrealistic with how fast it should update, or if there is a better way of updating the subform.


Thanks in advance and I really appreciate your help so far
speedball

 

[GinaWhipp]

Wow, that was alot of reading...

You can update the SQL of a query, not quite... You are actually changing the WHERE clause of a query whether from VBA or SQL. Updating the actual fields of the query is always going to cause an issue and why would you when that's what the WHERE clause is for. So, yes, you Alternate way is the way to go.

The way I do it when I have alot of records is...

a. Main form with UNBOUND Combo Box which will be used to filter my data.
b. Subform with RecordSource linked to Main form, see
http://www.access-diva.com/f2.html
c. Then the form will open blank and records will only show after a selection is made from the UNBOUND Combo Box

 

[speedball]

Thanks for that Gina

I've tried your suggestion for linking the subform with the unbound combo box on my main form. Nothing shows up when the form is first opened but as soon as I select an item from the combo box the subform populates, which is how I think it is intended to work.

However, I want all records to show when the form is first opened, and only to be filtered once the combo boxes are used. So I don't think that this method will be appropriate for my particular situation.

One final point, I've now had a look through the blueclaw website that you suggested. I need to work through the points in more detail for my database as a whole, but have noticed that point 3.d. suggests the following:

Use stored queries only. Don't create a form that has a select statement as the record source - the query will either not be optimized or optimized every time it runs.

I think that this is what I have now changed from as I suppose my query called WorkOutstandingQuery was a stored query, and now under my 'Alternative' method I'm using a SELECT statement as the RecordSource.

Any thoughts on this?

Thanks again
Speedball

 

[GinaWhipp]

Easy enough to fix, that is to have all the records open, do not use form/subform scenario, just use one form (main form). Put your UNBOUND Combo Box in the Header and have your query as the RecordSource, which is your alternate way. All the records will open and only filter when you make a selection.

Okay, that could open a huge debate... Depends on how many records, what you are trying to do, yada, yada, yada. You need to select the method that gives you the best performance AND the results you want.

 

[speedball]

Thanks for your help with this Gina.

I think that I'm stuck with the main form / subform situation. I have a tab control with a different subform on each of the 3 tabs and am using the tab control as a way of widening my main form i.e. each of the 3 subforms link to the same records, but just show different fields. I don't think I'll be able to achieve this with just one main form.

Overall it's working as I want, it's just a bit slower than I had hoped. I'll keep working through the other suggestions on the two links you sent me and hopefully that this help with overall speed.

Thanks
speedball

 

[GinaWhipp]

Well, you can still have it open with all the records and set the Link Master/Link Child After_Update of the Combo Box. Yes that means loading the form with the link fields NULL.