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

Call query design view from code

P: n/a
NB
Hi

Is there any way to call up the query design view from code?

In my compiled-as-MDE app everything is hidden from end users.
However, I want advanced user to have access to the query design view
so they can create, run and export their own query.

Another question is: can they be prevented from saving those
on-the-fly queries?

Thks
NB
Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm not sure, but I believe you have to do it in a round-about way:

In VBA:

1. Create a named QueryDef
- CurrentDb.CreateQueryDef()
2. Open that query in design view
- docmd.OpenQuery "qry name",acViewDesign
2a. Use a timer event to watch for the query to be closed
- use the Northwind IsLoaded() function
3. Hope the user doesn't save the query under another name ;-)
4. Delete the just-created query
- docmd.DeleteObject acQuery, "qry name"

Sorta kludgey, but it may work.

Otherwise - give the users (each of them) a .mdb file that is linked
to the data tables & allow them to create all the junk they want.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP+JxW4echKqOuFEgEQLaHgCfazAeQpeSFhxJ6Iy4hSXrOh uDo2AAnjmW
nD0i3xdmzxypF9xeY3aLXETJ
=JCg1
-----END PGP SIGNATURE-----

NB wrote:
Hi

Is there any way to call up the query design view from code?

In my compiled-as-MDE app everything is hidden from end users.
However, I want advanced user to have access to the query design view
so they can create, run and export their own query.

Another question is: can they be prevented from saving those
on-the-fly queries?

Thks
NB


Nov 12 '05 #2

P: n/a
NB
My solution so far has been

Create a temp query and open it in design view
The code below did that (where QExist is a function to check if
tempQuery exists)

Dim qdf As QueryDef
If QExist("tempQuery") Then
CurrentDb.QueryDefs.Delete ("tempQuery")
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
Else
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
End If

The problem is
- I can set focus to the design view that is opened (When the above
code is run from a command button in a form, screen flickers and then
focus returns to the calling form. The opened query design view stays
behind)
- I can't open a blank design grid. Opening the grid with a table does
not look very nice.

Anyone can give some hints?

Thks
NB
Nov 12 '05 #3

P: n/a
try something like

Public Function MyFunction()

Dim qdf As QueryDef
' CurrentDb.QueryDefs.Delete ("tempQuery")
' Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select version
from tblversion")
' qdf.Close
' DoCmd.OpenQuery "Tempquery", acViewDesign
' Else
Set qdf = CurrentDb().CreateQueryDef("Tempquery", "select RevisionNumber
from tblVersion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign

End Function

this puts up a design grid.
the only significant change I made was to change "DBEngine(0)(0)." for
"CurrentDB()." ( and comment out some lines about deleting it if it
already exists ( I don't have your function: QExist("tempQuery") to call )
You may also want to look at the repaintobject command

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
My solution so far has been

Create a temp query and open it in design view
The code below did that (where QExist is a function to check if
tempQuery exists)

Dim qdf As QueryDef
If QExist("tempQuery") Then
CurrentDb.QueryDefs.Delete ("tempQuery")
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
Else
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
End If

The problem is
- I can set focus to the design view that is opened (When the above
code is run from a command button in a form, screen flickers and then
focus returns to the calling form. The opened query design view stays
behind)
- I can't open a blank design grid. Opening the grid with a table does
not look very nice.

Anyone can give some hints?

Thks
NB

Nov 12 '05 #4

P: n/a
do you have some code in the form that puts the point of execution back into
the form after creating the query, firing the OnActivate and GotFocus
events? is there any offending code in those events?

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om...
My solution so far has been

Create a temp query and open it in design view
The code below did that (where QExist is a function to check if
tempQuery exists)

Dim qdf As QueryDef
If QExist("tempQuery") Then
CurrentDb.QueryDefs.Delete ("tempQuery")
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
Else
Set qdf = DBEngine(0)(0).CreateQueryDef("Tempquery", "select
version from tblversion")
qdf.Close
DoCmd.OpenQuery "Tempquery", acViewDesign
End If

The problem is
- I can set focus to the design view that is opened (When the above
code is run from a command button in a form, screen flickers and then
focus returns to the calling form. The opened query design view stays
behind)
- I can't open a blank design grid. Opening the grid with a table does
not look very nice.

Anyone can give some hints?

Thks
NB

Nov 12 '05 #5

P: n/a
NB
xzzy: What is the difference between the piece of code that you posted
and mine? I think with your code the query grid still shows up with a
table in it, and that's what I want to improve on (to make the look
cleaner)

I put my code in the Onclick event of a label on a menu screen
(frmMain)

To give the menu a consistent look (pointing hand) when user hovers
the mouse over items, I hyperlink that label to the form frmMain
itself. And I guess that's the problem: the Onclick event fires first,
then the app follows the hyperlink back to frmMain.

I couldn't think of a solution yet. Using api call to change cursor
icon is unecessarily awkward since I want my menu system to be real
light weight ...
NB
Nov 12 '05 #6

P: n/a
> xzzy: What is the difference between the piece of code that you posted
and mine? I think with your code the query grid still shows up with a
table in it, and that's what I want to improve on (to make the look
cleaner)
I misread your question, sorry
I put my code in the Onclick event of a label on a menu screen
(frmMain)

To give the menu a consistent look (pointing hand) when user hovers
the mouse over items, I hyperlink that label to the form frmMain
itself. And I guess that's the problem: the Onclick event fires first,
then the app follows the hyperlink back to frmMain.
look into "RepaintObject". This may help, it's a snip of code I use to get
a form or report to return to the previous menu. the following is from Case
Else of that function

DoCmd.OpenForm "frmMenuMain"
DoCmd.SelectObject acForm, "frmMenuMain", 0
DoCmd.RepaintObject acForm, "frmMenuMain"

John Bickmore
www.BicycleCam.com
www.Feed-Zone.com

"NB" <ni******@lycos.com> wrote in message
news:5c**************************@posting.google.c om... xzzy: What is the difference between the piece of code that you posted
and mine? I think with your code the query grid still shows up with a
table in it, and that's what I want to improve on (to make the look
cleaner)

I put my code in the Onclick event of a label on a menu screen
(frmMain)

To give the menu a consistent look (pointing hand) when user hovers
the mouse over items, I hyperlink that label to the form frmMain
itself. And I guess that's the problem: the Onclick event fires first,
then the app follows the hyperlink back to frmMain.

I couldn't think of a solution yet. Using api call to change cursor
icon is unecessarily awkward since I want my menu system to be real
light weight ...
NB

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.