Queries in VBA

[MechEngr]

Ok… while I consider myself to be a real solid beginner with VBA programming,
this question is going to make me look like a real rookie… so I will just
belly up and get it over with.

I have several databases that function beautifully with many complex queries
that allow the user to filter through data with the click of a button. The
queries are tied to open forms using conditions like

“Forms!MyForms![formfield] or Forms!MyForms![formfield] is Null†or
something similar in appropriate query fields.

I have been using a series of queries that cascade into one another so that
after five or so queries, I have captured as many as 20 of these conditional
statements based upon input from the form. I use the last query as the data
source for a list box on the open form(s). In this way the user can narrow
the data search through easy filtering and then use the list box to select a
particular record for display on the form. The series of queries filter my
record set to a particular collection and until I requery the database the
list box will display the current collection.

My questions is: Can I user VB to generate a collection (record set) that
can be tied to the list box on my form without writing to a table or
generating a traditional query (that shows up on the Access query list)? I
know I can use SQL statements and get the record set I want in VB and then
write the data to the form, but can VB generate a collection that is
available to forms and controls just like the standard hard coded queries or
without having to run the VB query every time I click on the List Box?


Sorry if I did not get the DB vernacular correct and thanks for any advise
or instruction you may offer.

 

[Ken Snell \(MVP\)]

Not sure if this is what you seek, but it may give a few ideas. I have a
sample database with examples of how to use various controls (including a
multi-select listbox) to set filters for a query --- all via VBA.
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Take a look and let us know if this isn't what you're seeking.

 

[Klatuu]

Although you can create SQL statements in VBA that will do just about
anything you can do with a stored query except the OpenQuery method, and the
TransferSpreadsheet and TransferText methods and use VBA to set row sources
for combos and list boxes, it is not always the best way to do it.

The reason is that when you create a query using the query builder and save
it, Access uses Rushmore to optimize the query. In other words, it compiles
it for the optimin search capabilites so it runs as fast as possible. When
you create a query in VBA and execute it, it has to be optimized before it is
executed. Therefore, a stored query will execute faster than a saved query.

 

[MechEngr]

Yes, this is right on target, well 93% of the way there anyway (I can use the
information regardless). The code behind the form… queries a table by
building a Where string using input from the form. When the SQL statement is
executed in the procedure, the resulting record set is displayed in on the
form which makes visible text boxes to display the info in a spreadsheet
fashion.

This is close… What I am trying to do is have a combo box on my form that
uses the recordset just queried (or a copy of if) as its record set. How can
I set the combo box’s property to point to that recordset (or a copy of it)?

Thanks for your help.

Not sure if this is what you seek, but it may give a few ideas. I have a
sample database with examples of how to use various controls (including a
multi-select listbox) to set filters for a query --- all via VBA.
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Take a look and let us know if this isn't what you're seeking.

--

Ken Snell
<MS ACCESS MVP>

Ok. while I consider myself to be a real solid beginner with VBA
programming,
this question is going to make me look like a real rookie. so I will just
belly up and get it over with.

I have several databases that function beautifully with many complex
queries
that allow the user to filter through data with the click of a button.
The
queries are tied to open forms using conditions like

"Forms!MyForms![formfield] or Forms!MyForms![formfield] is Null" or
something similar in appropriate query fields.

I have been using a series of queries that cascade into one another so
that
after five or so queries, I have captured as many as 20 of these
conditional
statements based upon input from the form. I use the last query as the
data
source for a list box on the open form(s). In this way the user can
narrow
the data search through easy filtering and then use the list box to select
a
particular record for display on the form. The series of queries filter
my
record set to a particular collection and until I requery the database the
list box will display the current collection.

My questions is: Can I user VB to generate a collection (record set) that
can be tied to the list box on my form without writing to a table or
generating a traditional query (that shows up on the Access query list)?
I
know I can use SQL statements and get the record set I want in VB and then
write the data to the form, but can VB generate a collection that is
available to forms and controls just like the standard hard coded queries
or
without having to run the VB query every time I click on the List Box?


Sorry if I did not get the DB vernacular correct and thanks for any advise
or instruction you may offer.

 

[MechEngr]

You make a very good point.

My goal here would be to generate a procedure that satisfies my requirements
and only needs to be run infrequently so that a small drop in efficiency
would be un-noticed.

So, I believe the question I should be asking is… how to set the Row source
property for a combo or list box from a VB procedure so that it points to a
recordset that was created in the VB procedure?

Thanks

Although you can create SQL statements in VBA that will do just about
anything you can do with a stored query except the OpenQuery method, and the
TransferSpreadsheet and TransferText methods and use VBA to set row sources
for combos and list boxes, it is not always the best way to do it.

The reason is that when you create a query using the query builder and save
it, Access uses Rushmore to optimize the query. In other words, it compiles
it for the optimin search capabilites so it runs as fast as possible. When
you create a query in VBA and execute it, it has to be optimized before it is
executed. Therefore, a stored query will execute faster than a saved query.

Ok… while I consider myself to be a real solid beginner with VBA programming,
this question is going to make me look like a real rookie… so I will just
belly up and get it over with.

I have several databases that function beautifully with many complex queries
that allow the user to filter through data with the click of a button. The
queries are tied to open forms using conditions like

“Forms!MyForms![formfield] or Forms!MyForms![formfield] is Null†or
something similar in appropriate query fields.

I have been using a series of queries that cascade into one another so that
after five or so queries, I have captured as many as 20 of these conditional
statements based upon input from the form. I use the last query as the data
source for a list box on the open form(s). In this way the user can narrow
the data search through easy filtering and then use the list box to select a
particular record for display on the form. The series of queries filter my
record set to a particular collection and until I requery the database the
list box will display the current collection.

My questions is: Can I user VB to generate a collection (record set) that
can be tied to the list box on my form without writing to a table or
generating a traditional query (that shows up on the Access query list)? I
know I can use SQL statements and get the record set I want in VB and then
write the data to the form, but can VB generate a collection that is
available to forms and controls just like the standard hard coded queries or
without having to run the VB query every time I click on the List Box?


Sorry if I did not get the DB vernacular correct and thanks for any advise
or instruction you may offer.

 

[Ken Snell \(MVP\)]

After you build the SQL statement as a string, just set the RowSource
property of the combo box to that string.

Me.ComboBoxName.RowSource = "SQL Statement"

--

Ken Snell
<MS ACCESS MVP>

Yes, this is right on target, well 93% of the way there anyway (I can use
the
information regardless). The code behind the form. queries a table by
building a Where string using input from the form. When the SQL statement
is
executed in the procedure, the resulting record set is displayed in on the
form which makes visible text boxes to display the info in a spreadsheet
fashion.

This is close. What I am trying to do is have a combo box on my form that
uses the recordset just queried (or a copy of if) as its record set. How
can
I set the combo box's property to point to that recordset (or a copy of
it)?

Thanks for your help.

Not sure if this is what you seek, but it may give a few ideas. I have a
sample database with examples of how to use various controls (including a
multi-select listbox) to set filters for a query --- all via VBA.
http://www.cadellsoftware.org/SampleDBs.htm#FilterForm

Take a look and let us know if this isn't what you're seeking.

--

Ken Snell
<MS ACCESS MVP>

Ok. while I consider myself to be a real solid beginner with VBA
programming,
this question is going to make me look like a real rookie. so I will
just
belly up and get it over with.

I have several databases that function beautifully with many complex
queries
that allow the user to filter through data with the click of a button.
The
queries are tied to open forms using conditions like

"Forms!MyForms![formfield] or Forms!MyForms![formfield] is Null" or
something similar in appropriate query fields.

I have been using a series of queries that cascade into one another so
that
after five or so queries, I have captured as many as 20 of these
conditional
statements based upon input from the form. I use the last query as the
data
source for a list box on the open form(s). In this way the user can
narrow
the data search through easy filtering and then use the list box to
select
a
particular record for display on the form. The series of queries
filter
my
record set to a particular collection and until I requery the database
the
list box will display the current collection.

My questions is: Can I user VB to generate a collection (record set)
that
can be tied to the list box on my form without writing to a table or
generating a traditional query (that shows up on the Access query
list)?
I
know I can use SQL statements and get the record set I want in VB and
then
write the data to the form, but can VB generate a collection that is
available to forms and controls just like the standard hard coded
queries
or
without having to run the VB query every time I click on the List Box?


Sorry if I did not get the DB vernacular correct and thanks for any
advise
or instruction you may offer.

 

[Klatuu]

Me.MyCombo.RowSource = "SELECT SomeField FROM SomeTable WHERE ...
You can, of course, create a string in VBA where there may be variations in
the row source depending on other criteria.

Me.MyCombo.RowSource = strSQL

You make a very good point.

My goal here would be to generate a procedure that satisfies my requirements
and only needs to be run infrequently so that a small drop in efficiency
would be un-noticed.

So, I believe the question I should be asking is… how to set the Row source
property for a combo or list box from a VB procedure so that it points to a
recordset that was created in the VB procedure?

Thanks

Although you can create SQL statements in VBA that will do just about
anything you can do with a stored query except the OpenQuery method, and the
TransferSpreadsheet and TransferText methods and use VBA to set row sources
for combos and list boxes, it is not always the best way to do it.

The reason is that when you create a query using the query builder and save
it, Access uses Rushmore to optimize the query. In other words, it compiles
it for the optimin search capabilites so it runs as fast as possible. When
you create a query in VBA and execute it, it has to be optimized before it is
executed. Therefore, a stored query will execute faster than a saved query.

Ok… while I consider myself to be a real solid beginner with VBA programming,
this question is going to make me look like a real rookie… so I will just
belly up and get it over with.

I have several databases that function beautifully with many complex queries
that allow the user to filter through data with the click of a button. The
queries are tied to open forms using conditions like

“Forms!MyForms![formfield] or Forms!MyForms![formfield] is Null†or
something similar in appropriate query fields.

I have been using a series of queries that cascade into one another so that
after five or so queries, I have captured as many as 20 of these conditional
statements based upon input from the form. I use the last query as the data
source for a list box on the open form(s). In this way the user can narrow
the data search through easy filtering and then use the list box to select a
particular record for display on the form. The series of queries filter my
record set to a particular collection and until I requery the database the
list box will display the current collection.

My questions is: Can I user VB to generate a collection (record set) that
can be tied to the list box on my form without writing to a table or
generating a traditional query (that shows up on the Access query list)? I
know I can use SQL statements and get the record set I want in VB and then
write the data to the form, but can VB generate a collection that is
available to forms and controls just like the standard hard coded queries or
without having to run the VB query every time I click on the List Box?


Sorry if I did not get the DB vernacular correct and thanks for any advise
or instruction you may offer.