unbound subform and record set

[Guest]

i have built a SQL string (union query) that will pass to a recordset and i
want the string to be passed to a form.

the string is built because there are parameters the user will enter (Date,
location) which will query the records with these two parameters...

would the code to pass sqlstr be:

Me!FormControl.SourceOject = sqlstr

OR

Me!FormControl.Form.RecordSource = sqlstr

OR is it not possible.

Thanks for any help..

 

[John W. Vinson]

i have built a SQL string (union query) that will pass to a recordset and i
want the string to be passed to a form.

the string is built because there are parameters the user will enter (Date,
location) which will query the records with these two parameters...

would the code to pass sqlstr be:

Me!FormControl.SourceOject = sqlstr

OR

Me!FormControl.Form.RecordSource = sqlstr

The latter. The query will not be updateable (no UNION query ever is).

Why can't you just use a parameter query, say referencing a form control?


John W. Vinson [MVP]

 

[Guest]

Thanks John.

I did have it as a parameter query, but it is EXTREMELY slow. I was
thinking that having VBA create the sQL string for the union query may
increase the speed when the user changes one of the two parameters....any
thoughts on this one?

 

[John W. Vinson]

Thanks John.

I did have it as a parameter query, but it is EXTREMELY slow. I was
thinking that having VBA create the sQL string for the union query may
increase the speed when the user changes one of the two parameters....any
thoughts on this one?

Post the SQL of the query. Are you using UNION or UNION ALL? The latter can be
faster as Access need not spend the time to find and remove duplicates. Are
the fields you're using for criteria (or for sorting) indexed? If not index
them, that will make a dramatic difference in speed.

UNION queries are by their nature slower; if you can restructure your data so
that the information is all in one table, you will probably get better
performance (and updateable queries as well, if that's important).

John W. Vinson [MVP]

 

[Guest]

thanks. I think the two tips are a good starting point. I will try them out
first to see.