not-updated results from a query 2

[u29gr]

Hello folks,
I have an application which writes runtime an SQL statement (defined by the user by means of appropriate controls) and assigns it to the DataSource property of a subform of the main form. Thus, the results of the query are automatically displayed in the subform every time I perform that property assignment.
I was wondering why sometimes the data displayed does not get updated (especially when the query does not retrieve any records: the datasheet view does not gey empty and still displays the results of the last search). But, if I do the same assigning the string to another form and then invoking that external form, the results are always correct. Probably when I invoke the external form there is some code behind that form that does better...
Do you know any way to do the same with the subform?
Thanks,
Giulio

 

[RickSpr]

Try this: After assigning to the DataSource property, call the subform's Repaint method. That should cause it to draw itself again.

If that doesn't work, you might try calling its Refresh method instead. Rick Sprague

 

[u29gr]

I am afraid it's not a matter of refreshing or repainting: both these methods do not work in the case I told you....

 

[RickSpr]

How about, after setting the RecordSource (you do mean RecordSource, not DataSource, don't you?), you set the subform's Visible property to False, DoEvents, and then set Visible to True again? If that doesn't work, I'd have to wonder whether you are mistaken about the value of the RecordSource property. Rick Sprague

 

[u29gr]

Yes -sorry about that!- it was the RecordSource property. Unfortunately there's no way to make it update, unless I assign directly the SQL statement to the RecordSource property, instead of linking it to the query itself.

What I don't understand is why the chart, based on the same query, works.

Ok, the links between the chart and the query were established by Access itself, but those with the subform were established by Access, as well...

Thanks,

Giulio

 

[RickSpr]

You're changing the problem on me! At first you said you build an SQL statement and assign it to the form's DataSource (meaning RecordSource), and that doesn't always update. But now you you say it doesn't update UNLESS you do this. This is the exact opposite of what you said at first!

Did you try what I said in my last post?
Rick Sprague

 

[u29gr]

You're right: sorry about that!I tried what you had said, and in my case it didn'twork. But let me explain clearly what I was doing, it's my fault!
The first way I was following, was that of creating dynamically the SQL statement, assigning it to a Query (by means of Querydef's SQL property), and linking the subform's RecordSource to that Query. This way, both the subform and the chart were based on the same Query, and all the modifications were carried on to that Query. This way does not do fine, in that there are cases in which the data-refresh is not performed (either automatically or manually!).
The only way I could find out to have the data-refresh working (automatically!), was that of assigning the SQL statement to the RecordSource property of the various objects (without any Query to be referred to). This way does fine, though each time I carry on any modifications to the SQL statement, I must re-assign that statement to the RecordSource property. And I must to do the assignment for all the objects based on that statement (subform and Chart, but maybe other objects, as well).

Sorry about my very poor initial explanation of the problem.
Regards,

Giulio

 

[u29gr]

P.S.: Back to the initial question, I don't understand why the chart based on the Query is always refreshed while the subform sometimes is not.
Now, I cannot tie the chart to the query and update the RecordSource property for the subform, when the SQL statement is the same. Since I prefer to use always the same technique, eventually I had to adopt the way of updating the RecordSource property for both...

Giulio

 

[mac318]

Giulio,
Revisit your error handling routines within the appropriate modules and code. I have a feeling that you have trapped errors in such a way that you are not seeing an error code presented by Access. I'm the world's champion for doing this. Comment out your error handling statements and allow Access to present you with any errors.

mac

 

[RickSpr]

Giulio, whatever you can get to work is good. But maybe I can add something to help, still.

When you modify a QueryDef (or any other database object) in code, you're supposed to do a RefreshDatabaseWindow call afterward to ensure that the Database Window is updated to show the change. If you don't do that, some parts of Access may not "see" the changes until an indeterminate time later. My hunch is that if you do a RefreshDatabaseWindow after updating the query, it will appear the same everywhere. Rick Sprague

 

[u29gr]

Mac, Rick:

thank you very much for your last tips. I voted them as expert posts, since I think that the all that you told me will turn out to be very helpful in the future. Unfortunately it was not in this occasion... it's more than three months now I work full-day with Access, and beyond all the good qualities that software has, I started to see also some sublime vagaries it's almost impossible to work out. In these cases, usually the approach seems to be that of achieving the results following another way. This is what I did, coming back to the way that was working.
Nevertheless I wanna say again that I found your tips very helpful.
Thanks again for those!
Giulio