I am working on a user interface for a lab database in Access 2007.
The 9 tables with the actual data are in a back-end database and the
forms are completely separate. My forms reference local ADO
recordsets.

I want to create a query wizard--a form that allows the user to set
criteria for a query, then run it. I've succeeded in doing that, to
some extent. My VBA code checks information on the form and builds a
SQL statement, then uses the SQL statement to open a new recordset.
The "results" recordset is then displayed in a form in table format.

The problem is that it has already taken tons of code to make fairly
simple queries work, and I want my "wizard" to be able to deal with
queries of arbitrary complexity. I feel like I'm reinventing the
wheel, since Access can already do query wizards and a graphical
interface for query design. Is there some way that I can harness that
capability for use in my interface with local recordsets? Or should I
just keep slogging away at what I've started?

Let me know if any more details would be helpful--I stated my question
rather broadly.

Thanks,
Shevaun

On Feb 25, 2:53 pm, Shevaun <shevaun.le... (AT) gmail (DOT) com> wrote:
By local ADO recordsets I'm guessing you are talking about those saved
in adtg or xml format.
A continuous form in an ADP (probably mdb too) can have its
recordsource set to an ADO recordset. So why not create the form, open
the recordset and instead of using SQL to filter the records use a
Filter statement?, and then set the continuous form's recordset to
that recordset?

On Feb 25, 12:21*pm, lyle <lyle.fairfi... (AT) gmail (DOT) com> wrote:

As far as I know (which isn't very far), the recordsets are never
"saved"--they exist only as objects in my VBA code. I establish a
connection to the databse with the data tables, create a recordset
based on one or more tables, and then close the connection. The data
tables are updated in batches from the local recordsets. When I close
the application, the recordsets disappear--they're not saved. They
have to be recreated next time the application is opened.

I do set the recordsource of the form that contains the query results
to the query recordset.

As far as using Filter statements instead of SQL, I don't think it
would make the problem any more straightforward. Generating Filter
statements would take just as much code as generating SQL statements.
Furthermore, I need the SQL statement so I can specify which tables
and fields the recordset will be based on, as well as the
relationships between the tables. The problem isn't with SQL itself;
the problem is generating the SQL based on the users choices in a
bunch of combo boxes and check boxes.

Does that make sense?
-Shevaun

Hello,

I you are trying to achieve dynamic query display here is something
simple you can do. My explanation is based on the following scenario:
You have a main form with a subform that displays data in datasheet view
and you want to be able to display different query results - like one
query may contain 3 columns, another query may contain 7 columns...

In your subform you will have say 20 generic textboxes - txt0, txt1,
txt2, txt3,... txt19

In your query button on the main form you will have code like this:

Me.yourSubfrm.Form.Recordsource = "Select * From tblx t1 Join tbly t2 On
t1.ID = t2.ID Where some conition..."
Me.yourSubfrm!txt0.ControlSource = 'fld0 from this query'
Me.yourSubfrm!txt1.ControlSource = 'fld1 from this query'
...

The only catch is that you will have to know how many fields are being
retrieved so that you can set the controlsources accordingly.

In .Net (say VB2005 -- or C#) this is a snap with the Datagridview
control

dataAdapter1.SelectCommand.CommandText = "Select whatever"
dataAdapter1.Fill(dataset1, "tblx")
datagridview1.DataSource = dataset1.Tables("tblx")

tblx here is dynamic (tblx exists only in memory) and can dynamically
change the number of columbs returned by the query.


Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Feb 25, 4:52 pm, Shevaun <shevaun.le... (AT) gmail (DOT) com> wrote:
Not to me, but then I only understand about 2% of what people post
here.