473,721 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Call query design view from code

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
6 13296
-----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.Creat eQueryDef()
2. Open that query in design view
- docmd.OpenQuery "qry name",acViewDes ign
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.DeleteObj ect 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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBP+JxW4echKq OuFEgEQLaHgCfaz AeQpeSFhxJ6Iy4h SXrOhuDo2AAnjmW
nD0i3xdmzxypF9x eY3aLXETJ
=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
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("tempQue ry") Then
CurrentDb.Query Defs.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
try something like

Public Function MyFunction()

Dim qdf As QueryDef
' CurrentDb.Query Defs.Delete ("tempQuery" )
' Set qdf = DBEngine(0)(0). CreateQueryDef( "Tempquery" , "select version
from tblversion")
' qdf.Close
' DoCmd.OpenQuery "Tempquery" , acViewDesign
' Else
Set qdf = CurrentDb().Cre ateQueryDef("Te mpquery", "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("tempQue ry") 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.goo gle.com...
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("tempQue ry") Then
CurrentDb.Query Defs.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
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.goo gle.com...
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("tempQue ry") Then
CurrentDb.Query Defs.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
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
> 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 "frmMenuMai n"
DoCmd.SelectObj ect acForm, "frmMenuMai n", 0
DoCmd.RepaintOb ject acForm, "frmMenuMai n"

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

"NB" <ni******@lycos .com> wrote in message
news:5c******** *************** ***@posting.goo gle.com... 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
4562
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too slow. I copied the SQL statement from the query and tried executing it from code which then ran in 1 second. To make sure that I didn't miss anything, I copied the SQL statement back into a query and tried running it again. It now also only took 1...
3
2312
by: Megan | last post by:
hi everybody- it seems like my update query should work. the sql view seems logical. but that might be up to discussion...lol...i'm a newbie! UPDATE , Issue SET .IssueID = . WHERE ((.=.));
6
4845
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID SalesManName AT Alan Time
6
2243
by: sghi | last post by:
Hi All, I'm new to this group and quite new to access/vba. So, shortly after beginning to write a simple application for my wife, I came across a blocking problem: I need to intercept the sql statement that stay behind a current, but not yet saved query. When I work on saved queries I use: strCurrentName = CurrentObjectName Dim dbsCurrent As Database Set dbsCurrent = CurrentDb
7
6882
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is expression should return the 4 leftmost characters of the FilmNo
4
7166
by: amy | last post by:
Hi to Everyone: I need big help on how to query the Age range. Age field is text data type, Age are from 0 wk, 1 wk, 2wk.....up to 15 wk. Try to set up query in Query desing mode with criteria is 0-4wk, i put in this: "0 wk" Or "1 wk" Or "2 wk" Or "3 wk" Or "4 wk". The query result includes ages between1-4wk but also10-15 wk. Even I change the Age field to number data type, still the same problem. I am not a access programmer but just...
22
31205
by: Stan | last post by:
I am working with Access 2003 on a computer running XP. I am new at using Access. I have a Db with a date field stored as mm/dd/yyyy. I need a Query that will prompt for the month, ie. 6 for June, and will return all records in that month.
3
7771
by: mnjkahn via AccessMonster.com | last post by:
I'm running Access 2003, modifying a query that has over 45 fields. When I right click on the field name in Query Design View, and then click Build, Access crashes before the Build window appears. It doesn't happen every time, and using the Zoom window works fine. It appears that it only happens when I want to modify an existing expression. This continues to happen even after the database is repaired and reopened. Anyone have any...
2
9840
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes to open in datasheet view. As an experiment, I deleted all rows in all tables; after that, the query took only seconds to open in both design view and datasheet view. From these facts, I conclude that Access is evaluating the query when I go to...
0
8852
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9373
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9227
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9081
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8020
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5992
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4497
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4761
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2590
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.