Showing query results on a form

[Don Starnes]

I have VBA code that creates a SQL query statement; I want to show the
results of the query in a tab on a form. I'm using Access 2K.

I tried using a subform ("ResultingQuery") bound to a query
("temp_SetFieldValues"), then changing the query with this code:
currentdb.QueryDefs!temp_SetFieldValues.SQL = strquery

The subform didn't refresh when the SQL string changed; I tried this
currentdb.QueryDefs.Refresh

and this:
Me!ResultingQuery.Requery

Neither refreshed the subform. I also didn't like the idea of a query with
potentially old table names being bound to my subform.

I tried binding the subform to a form ("query_view") set to datasheet view
and using this code:
Forms![query_view].RecordSource = strquery
which generated an error. I also don't know how I'd add fields to this form
from VBA.

Any advice on how I could show the results of a dynamic query on a form?

Thanks!
Don

 

[Stefan Hoffmann]

hi Don,

I have VBA code that creates a SQL query statement; I want to show the
results of the query in a tab on a form. I'm using Access 2K.

You can display it directly in your sub form, when you specify the query
as SourceObject.

I tried using a subform ("ResultingQuery") bound to a query
("temp_SetFieldValues"), then changing the query with this code:
currentdb.QueryDefs!temp_SetFieldValues.SQL = strquery

You need to reopen the query, thus you need to close and open the sub form.

The subform didn't refresh when the SQL string changed; I tried this
currentdb.QueryDefs.Refresh

This just refreshs the collection. Not the data of active queries.

Neither refreshed the subform. I also didn't like the idea of a query with
potentially old table names being bound to my subform.

I tried binding the subform to a form ("query_view") set to datasheet view
and using this code:
Forms![query_view].RecordSource = strquery

This is a possible way to go, but your new query must return the same
number of fields.

which generated an error. I also don't know how I'd add fields to this form
from VBA.

Which error message do you get?


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Don,

There is no close or open method with subforms. How do you do this?

I tried
Me!ResultingQuery.Form.Close
Me!ResultingQuery.Form.Open

and got an error.

ResultingQuery is your sub form control?

Dim SubFormName As String

SubFormName = ResultingQuery.SourceObject
ResultingQuery.SourceObject = ""
ResultingQuery.SourceObject = SubFormName



mfG
--> stefan <--

 

[Robert Morley]

Alternatively, you should just be able to do

Me!ResultingQuery.Form.Requery



Rob

 

[Stefan Hoffmann]

hi Robert,

Alternatively, you should just be able to do

Me!ResultingQuery.Form.Requery

Have you tested it? This shouldn't work. Using Access 2003 it doesn't work.


mfG
--> stefan <--

 

[Robert Morley]

Really? I would've thought it would. What about doing a QueryDefs.Refresh
then a Subform Refresh?

I don't usually use dynamic queries; if I need dynamic SQL, I just put it in
the record source instead of a query. I don't think I've ever had a case
where I've needed to have dynamic numbers of fields displaying in a form,
either.

Sorry if I was off-track...I should've read the thread more closely.



Rob

 

[Don Starnes]

Stefan--

That worked beautifully! Thanks.

Don

hi Don,


ResultingQuery is your sub form control?

Dim SubFormName As String

SubFormName = ResultingQuery.SourceObject
ResultingQuery.SourceObject = ""
ResultingQuery.SourceObject = SubFormName



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Robert,

Really? I would've thought it would. What about doing a QueryDefs.Refresh
then a Subform Refresh? Nope.

I don't usually use dynamic queries; if I need dynamic SQL, I just put it in
the record source instead of a query. I don't think I've ever had a case
where I've needed to have dynamic numbers of fields displaying in a form,
either.

Changing the SQL of a query often bloats the database, so in most cases
it is better to avoid it.

Sorry if I was off-track...I should've read the thread more closely.

This happens very often to me, no need for excuses.


mfG
--> stefan <--

 

[Robert Morley]

Changing the SQL of a query often bloats the database, so in most cases it

is better to avoid it.

True enough, though I used to do it anyway and just make a point of
compacting regularly. I've been working in ADP mode for a couple of years,
so it hasn't really been a concern for me.



Rob

 

[Stefan Hoffmann]

hi Robert,

True enough, though I used to do it anyway and just make a point of
compacting regularly. I've been working in ADP mode for a couple of years,
so it hasn't really been a concern for me.

Ahh. It's vice versa to me, never used ADPs

mfG
--> stefan <--