Re: Queries in VBA

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>

"MechEngr" <MechEngr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:40474211-C9DA-4BBE-BF1F-8877D99AA795@xxxxxxxxxxxxxxxx
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 spread***
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.


"Ken Snell (MVP)" wrote:

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>

"MechEngr" <MechEngr@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0634CA94-CFA2-4933-BE86-9B0935BDA2A9@xxxxxxxxxxxxxxxx
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.






.

Loading