VBA query

[alex]

Hello all,

Using Access ’03…

I have a form with a subform. The subform is based on a parameter
query that uses the results of three combo boxes on the main form.
This all works pretty well thanks to the help of this group.

I’m trying to add another control (this time a text box) that will
search all fields (kind of like a key word search). I’m finding,
however, that my query is getting a little complicated and I’d like to
use VBA to query the three combo boxes separate from the text box.

What I’d basically like to do is base the subform on a VBA query
instead of an actual object query.

How can I set the subform’s recordset to a VBA query?

Thanks,
alex

 

[Piet Linden]

Hello all,

Using Access ’03…

I have a form with a subform.  The subform is based on a parameter
query that uses the results of three combo boxes on the main form.
This all works pretty well thanks to the help of this group.

I’m trying to add another control (this time a text box) that will
search all fields (kind of like a key word search).  I’m finding,
however, that my query is getting a little complicated and I’d like to
use VBA to query the three combo boxes separate from the text box.

What I’d basically like to do is base the subform on a VBA query
instead of an actual object query.

How can I set the subform’s recordset to a VBA query?

Thanks,
alex

Me.Forms.SubformName.RowSource="select query name" (Or SQL statement)

 

[alex]

Me.Forms.SubformName.RowSource="select query name" (Or SQL statement)- Hide quoted text -

- Show quoted text -

Thanks Piet...Where do I put the code (in the open event of the
subform or form?) Also, I'd like to user to be able to change the
RowSource, let's say by checking a checkbox. I'm assuming it's
possible to change the RowSource dynamically "on-the-fly."

alex

 

[Armen Stein]

Me.Forms.SubformName.RowSource="select query name" (Or SQL statement)

Hi Alex,

We've written code to automatically replace the Where clause in a SQL
statement. It's hiding in our download called "Report Selection
Techniques" at www.JStreetTech.com/downloads.

Look in basSQLTools and try out the function ReplaceWhereClause. You
send in a whole SQL statement and the new desired Where clause, and it
locates and snips out the old one, inserts your new one, and gives you
back the new statement. It's one of the most useful functions we've
ever written - it's used in every single application we build.

Rebuilding a whole SQL Statement in code is laborious and often leads
to hard-to-diagnose errors. Our technique allows you to store the
whole query structure in a property or saved query without having to
rebuild it each time in code. All you have to do is rebuild the Where
clause.

By the way, there's also a ReplaceOrderByClause function that does the
same for sorting.

Cheers,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[alex]

Hi Alex,

We've written code to automatically replace the Where clause in a SQL
statement.  It's hiding in our download called "Report Selection
Techniques" atwww.JStreetTech.com/downloads.

Look in basSQLTools and try out the function ReplaceWhereClause.  You
send in a whole SQL statement and the new desired Where clause, and it
locates and snips out the old one, inserts your new one, and gives you
back the new statement.  It's one of the most useful functions we've
ever written - it's used in every single application we build.

Rebuilding a whole SQL Statement in code is laborious and often leads
to hard-to-diagnose errors.  Our technique allows you to store the
whole query structure in a property or saved query without having to
rebuild it each time in code.  All you have to do is rebuild the Where
clause.

By the way, there's also a ReplaceOrderByClause function that does the
same for sorting.

Cheers,

Armen Stein
Microsoft Access MVPwww.JStreetTech.com- Hide quoted text -

- Show quoted text -

Thanks Armen!
alex