By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,247 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,247 IT Pros & Developers. It's quick & easy.

Query on the fly?

P: n/a
I'm trying to do something which seems like it should be really easy, but I
can't seem to figure it out. Basically I have an unbound form with the
usual combo boxes, option groups text boxes, etc. Depending on what the
user selects and types in it will build a SQL. But one query could be
drastically different from another (different tables, fields) so I can't
just use a form. I can build the SQL statement based on the input, but I
can't figure out how to display it. All I need is a datasheet of the the
fields, much like one would get if you were click on a saved query. What
sort of code would just show the results of the SQL statement? I thought
perhaps a db.execute might work, but it doesn't seem to. Any help is
appreciated.

TIA,
Bill
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
TC
Save the SQL as a query (through code), then open it with docmd.openquery.
This will display a datasheet-like view of the saved query.

HTH,
TC
"Bill" <zi*****@hotmail.com> wrote in message
news:vm************@corp.supernews.com...
I'm trying to do something which seems like it should be really easy, but I can't seem to figure it out. Basically I have an unbound form with the
usual combo boxes, option groups text boxes, etc. Depending on what the
user selects and types in it will build a SQL. But one query could be
drastically different from another (different tables, fields) so I can't
just use a form. I can build the SQL statement based on the input, but I
can't figure out how to display it. All I need is a datasheet of the the
fields, much like one would get if you were click on a saved query. What
sort of code would just show the results of the SQL statement? I thought
perhaps a db.execute might work, but it doesn't seem to. Any help is
appreciated.

TIA,
Bill

Nov 12 '05 #2

P: n/a
Create a query - "qryDummy" -it can do anything.
When you have built your SQL string, set the SQL property of qryDummy to
your string. Run the query using docmd.openquery.

Something like:

Dim strSQL
strSQL = "Select [companyname], [contactname] from Table1 order by
[contactname]"
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL
DoCmd.OpenQuery ("qryDummy")
Set qdf = Nothing
Regards

Peter Russell
Bill previously wrote:
I'm trying to do something which seems like it should be really easy,
but I
can't seem to figure it out. Basically I have an unbound form with the
usual combo boxes, option groups text boxes, etc. Depending on what the
user selects and types in it will build a SQL. But one query could be
drastically different from another (different tables, fields) so I can't
just use a form. I can build the SQL statement based on the input, but
I
can't figure out how to display it. All I need is a datasheet of the
the
fields, much like one would get if you were click on a saved query.
What
sort of code would just show the results of the SQL statement? I
thought
perhaps a db.execute might work, but it doesn't seem to. Any help is
appreciated.

TIA,
Bill


Nov 12 '05 #3

P: n/a
Wow...beautiful. Worked just as I wanted. Thanks a lot!
"Peter Russell" <pr******@russellscott.co.uk> wrote in message
news:me***********************@russellscott.btinte rnet.com...
Create a query - "qryDummy" -it can do anything.
When you have built your SQL string, set the SQL property of qryDummy to
your string. Run the query using docmd.openquery.

Something like:

Dim strSQL
strSQL = "Select [companyname], [contactname] from Table1 order by
[contactname]"
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL
DoCmd.OpenQuery ("qryDummy")
Set qdf = Nothing
Regards

Peter Russell
Bill previously wrote:
I'm trying to do something which seems like it should be really easy,
but I
can't seem to figure it out. Basically I have an unbound form with the
usual combo boxes, option groups text boxes, etc. Depending on what the
user selects and types in it will build a SQL. But one query could be
drastically different from another (different tables, fields) so I can't
just use a form. I can build the SQL statement based on the input, but
I
can't figure out how to display it. All I need is a datasheet of the
the
fields, much like one would get if you were click on a saved query.
What
sort of code would just show the results of the SQL statement? I
thought
perhaps a db.execute might work, but it doesn't seem to. Any help is
appreciated.

TIA,
Bill

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.