Why aren't the fields in my datasheet subform refreshing?

[tetsuo2030]

Greetings.

I've read a lot about this, but nothing seems to be solving my problem. I have an unbound main frm_reporting with a subform (child_reportin g) whose sourceobject is Query.qry_repor ting_all. There are three text boxes in the header of frm_reporting, all unbound, and a few buttons. Each button has an SQL statement assigned to it. The idea is for the user to enter data into the boxes (date ranges) then click the button of their choice which redefines the qry_reporting_a ll's QueryDef and displays the data they want in the subform frame.

This didn't work:

Set dbo = CurrentDb
    Set qdf = dbo.QueryDefs("qry_reporting_all")
    qdf.sql = strSQL
    Me.[child_reporting].Requery

This sort of worked:

Set dbo = CurrentDb
    Set qdf = dbo.QueryDefs("qry_reporting_all")
    qdf.sql = strSQL
    Me.child_reporting.Form.RecordSource = "qry_reporting_all"
    Me.[child_reporting].Requery

If I change the date ranges, the data changes. But when I decide to click a different button that SELECTs different fields, the fields don't refresh--neither add nor disappear; the data is still updating, just not the fields shown. If I close the form and reopen, the fields are represented properly.

Aside from closing/reopening the form, how do I get the fields displayed to reflect the fields in the querydef??

[TheSmileyCoder]

First off, whenever something "does not work", its very important that you both describe the behavior you expected as well as the behavior that happened. Does not work can mean everything from Errors being generated, to nothing happening to incorrect data. It might also be in some cases that its working as it should, but it is your expectations that are in-correct.


In your first bit of code, I expect that Access has stored the query, and subsequently changing the queries SQL has no effect.

Instead you can just set the recordsource of the form directly without the use of a query object.

Me.child_reporting.Form.RecordSource=strSQL

There is no need to add a .Requery after this, as access automatically requires if the recordsource is changed.

the fields don't refresh--neither add nor disappear; the data is still updating, just not the fields shown

This makes no sense. The fields contain the data. Either they update or they don't update. You will have to explain in more detail.

[Rabbit]

I believe the OP means that the SQL selects a different set of fields than the ones originally on the form.

[NeoPa]

How does this question make sense if both forms are unbound?

If the subform has a Record Source (as implied but denied), then where is the code that the OP expects to change which fields are displayed in which controls?

It seems to me the question suffers from some information which is inaccurately explained, and other information which isn't included at all.

Tetsuo:
Each button has an SQL statement assigned to it.

What does this mean? Buttons can trigger macros or VBA procedures. Not SQL statements. None of the posted code indicates where or how strSQL is set.

As it happens, Smiley has already posted what I would have suggested to handle this issue. Use the SQL directly (in the .RecordSource) rather than trying to do it via a QueryDef object.

How well the form responds to the changes of the Record Source I can't guess. It may well be that when such changes are made Access is not responsive enough to check for such changes and assumes it's safe to carry on with the original fields.

[tetsuo2030]

Rabbit is correct: I'm selecting different fields than the ones originally in the form. And I think NeoPa might be onto something with "...Access is not responsive enough to check for such changes and assumes it's safe to carry on with the original fields."

The OnClick event of each button has code that changes the SQL behind qry_reporting_a ll's querydef. A generic version of my SQL statements would be as follows.

Dim myValue as String

myValue = Me!UnboundTextBox1.Value

Button one:

"SELECT Field1, Field2, Field3 FROM Table1 WHERE Field1 = '" & myValue & "';"

Button two:  "SELECT * FROM Table1 WHERE Field1 = '" & myValue & "';"

The buttons' OnClick events are changing the query properly; after the code runs, I can open the query and it shows the fields/values I want. I thought setting the SourceObject of the subform to that query would mean the subform would refresh as I changed the querydef.

Per SC and NeoPa's comments, I see how the QueryDef is needless, so I cut out the middleman and changed the code to:

Me.child_reporting.Form.RecordSource = strSQL

The behavior, which I'll try to explain more clearly, remains: the values in the subform are changing as desired, but the field names are not. If the user types "donkey" into UnboundTextBox1 , the OnClick events update the subform to show only the "donkey" records. If they change the value in UnbountTextBox1 to "chicken" then the "chicken" records show. But notice Button1 only includes the first three fields, and Button2 selects all the fields. When I click Button2, the data changes but it still only shows the first three fields. However, if I close the entire form and reopen it, all the fields show. If I then click Button1, again the data changes, but all the fields remain (and the values beneath fields other than Field1, Field2, and Field3 are blank, presumably because the updated SQL isn't selecting those fields).

Ultimately, I want to click a button to display a desired dataset then have a "Send to Excel" and a "Print Data" button, but I don't want to build a separate form or subform for each new query I come up with.

I suppose a solution would be to kill the subform altogether, Open the query acReadOnly and use the External Data Ribbon.

Any ideas on how to get those Field Names to refresh? Another roundabout way would be Application.Ech o = False, run the update, close the form, open the form, repopulate UnboundTextBox1 with myValue, then Application.Ech o = True. I tested this and it works, but it's going in the opposite direction than this amateur needs to be going.

[NeoPa]

I have to say you're making a lot of sense. Most of what you say I'm agreeing with, and fundamentally, I see no way around this issue other than what you've already described.

However, I'm still struggling to understand what you're doing with the Command Buttons. Your explanation is good, but it doesn't make sense to me. Clearly you've put in the effort to try to explain though. I suspect if you posted the whole of just one of the _Click() procedures I would have enough information to work with. Normally, I find myself telling people they need to take more effort actually explaining the situation, but I think this is a case where you've done as much as you can in that area. Just the code is missing to help me put it into perspective.

[tetsuo2030]

Okay. I changed a little, but the premise is the same, and the behavior is the same. Instead of a bunch of command buttons, I just used a Combo Box (pre-populated value list; values represent the desired dataset i.e. SQL); on the After_Update event of that combobox, I put this code:

    
Dim strSQL As String
    Dim mySite As String
    Dim myReport As String
    
    If IsNull(Me!cmb_desired_report.Value) Then
        Exit Sub
    Else
        myReport = Me!cmb_desired_report.Value
    End If
    
    If IsNull(Me!cmb_reports_siteselect.Value) Then
        mySite = "tbl_calendar.SiteName Like ""*"";"
    Else
        mySite = "tbl_calendar.SiteName = '" & Me!cmb_reports_siteselect.Value & "';"    
    End If
    
               
    Select Case myReport
        Case "Events"
            strSQL = "SELECT tbl_calendar.InputDate, tbl_calendar.FullName, tbl_calendar.SiteName, tbl_calendar.InputText FROM tbl_calendar " & _
                        "WHERE " & mySite         
Case "Danger Days"
            strSQL = "SELECT * FROM tbl_calendar " & _
                        "WHERE " & mySite        
Case Else
            MsgBox "Error."
            Exit Sub
    End Select
          
    Me.child_reporting.Form.RecordSource = strSQL

This code produces the aforementioned behavior: if the user enters a site, it will show the lines associated with that site, but no matter the desired report, it always shows the fields from when the form was originally opened.

[NeoPa]

That's good. We can assume that the code you posted has lines immediately above and below it that define the procedure (although, it's actually very helpful to include those lines as they have information we like to see and it just makes it much easier to read without having to assume anything).

Unfortunately though, you have changed the design such that the code is no longer all we need, to know what's actually happening (and to be able to reproduce the issue in our heads). Now, with the new design, we need to see what some of the values are in your ComboBox control. It seems also, that you have more than one ComboBox that is referred to in the code. This makes the absence of the procedure header critical. We have no way of knowing which of the two this code is triggered by. In general, it's good policy to work on a static problem. Even if your understanding progresses during the discussion of the problem (which is good), working on a moving situation is always much more complicated than dealing with a single static issue.

Having said all that, and hopefully you will take it all on board for future questions, because you are actually a pretty decent member to work with as far as it goes, the code is pretty solid. I would certainly advise that this same code is run for both ComboBoxes, as it's all-encompassing - just as it should be, but the way to do that would be to get both to call a separate procedure defined in the same module. That way any maintenance is not complicated by needing to be done twice over.

A couple of small points :

1. You needn't add the WHERE clause to the SQL if it's just ([X] Like '*'). Add it in (with the preceeding space of course) within the If code where it's required only.
2. Line #13 uses double-double-quotes ("") where single-quotes (') are adequate, and slightly better (more standard). Both will work equally well of course (See Quotes (') and Double-Quotes (") - Where and When to use them).

PS. There's no real need to go to the bother now of posting the contents of the ComboBoxes. It's always a good idea to include all the relevant information, but in this case I'm sure there is enough information indicated by the code, now I've read it all through carefully. Always easier to read it as posted information of course.