Refreshing Recordsource

[Cloonalt]

I create a SQL string (called str) and use it to create a query for the recordsource on a subform. The reason I do this is because the SQL string was too long.

It works, but I can get the form to refresh to show the new data. Below is what I'm doing. Any ideas?

Thanks.

Set qdf = db.CreateQueryDef("qryRecordsource", str)

[frmSub].Form.RecordSource = ""
[frmSub].Form.RecordSource = qryRecordsource
db.QueryDefs.Refresh
Me.frmSub.Requery

 

[rubbernilly]

Try:

[frmSub].Form.RecordSource = "Query.qryRecordsource

 

[Cloonalt]

Rubernilly,

Runtime error 3078. Microsoft Jet database cannot find table or query....

Any ideas?

Thanks for your reply.

 

[rubbernilly]

Any querydef you create this way is temporary, so that was a mistake on my part in that last post.

However, if you have the SQL of the query (which you do have because you are feeding it into the CreateQueryDef command), you should be able to set that as the recordsource for the form without having to create a QueryDef.

HTH

 

[Cloonalt]

In my first post I wrote that the reason I used the querydef is because the SQL string was too long.

The querydef works up until the point where I want to refresh the form to show the data.

Thanks for your help.

 

[rubbernilly]

Sorry, you read so many posts and you think you've kept them all straight only to realize that you're covering ground already mapped out. My bad.

But, you might be able to work with a query in the database. If you assign the Form to have a recordsource of a particular query whose sql you change, you would basically be doing what you want to do.

Instead of using CreateQueryDef, use:

set qdf = currentdb.QueryDefs("YourDynamicQuery")
qdf.sql = sYourSQLString

 

[lesw1433]

Shouldn't you refresh the qrydef before setting the forms recordsource to the qrydef? Just look like it was out of sequence to me.

 

[Cloonalt]

Thanks, but that gave the same result. The query works, sets to the recordsource correctly, but the form doesn't refresh with the new data event if I use:

me.refresh
me.requery
me.repaint

But, if I set the sql to the recordsource it does refresh the form.

What a pain.

Thanks for your help.