Manage QBE with VBA code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I would like to present the QBE to users, allowing them to build an sql
statement. Then when they have completed their actions in the QBE and Closed
it, saving their changes, I would take additional actions.

Is there any way to know when a user has closed the QBE?

Mr B
 
Hey Mr. B

One Option would be to Loop thru the Query definitions looking for the name
of the query that has been added. This pre-supposes that you would know what
they would name the query.

Here is code that will loop there all of the Query Definitions:

'*********************************************************
Public Sub DeleteQuery(strQueryName As String)
'*********************************************************
Dim db As Database
Dim qdfLoop As QueryDef

Set db = CurrentDb

For Each qdfLoop In db.QueryDefs
If qdfLoop.Name = strQueryName Then
Message Box("Warning, a new query has been added")
Exit For
End If
Next
End Sub
 
Ross,
Thanks for the reply, but, I am needing to know when they close the QBE. I
will already know the query that they have modified. I will be opening the
QBE for them with an existing query as the sql.

Is there some way to know when they close the QBE that I opened for them?

Mr. B
 
Mr said:
Ross,
Thanks for the reply, but, I am needing to know when they close the
QBE. I will already know the query that they have modified. I will
be opening the QBE for them with an existing query as the sql.

Is there some way to know when they close the QBE that I opened for
them?
Click to expand...

You keep mentioning "sql". Are they going to be writing SQL or using the design
grid? If the former then you can give them a form of your own to write the SQL
in and modify the query using that, You can certainly tell when they close a
form.

If they are using the grid then I don't know. Unless you write a substitute
form for that!
 
Rick,

Users will be making changes to the query via the QBE. In doing so, they
will be selecting the fields that they want to use in a merge to Word. I
will then use the sql of the modified query when creating a document and
performing the merge. I wish to allow users to create a set of fields for a
new document or modify the existing set of fields for an existing document.

I just thought that someone might be aware of some way to know when the user
closes the QBE window.

Thanks for the input.

Mr. B
 
Mr said:
Rick,

Users will be making changes to the query via the QBE. In doing so,
they will be selecting the fields that they want to use in a merge to
Word. I will then use the sql of the modified query when creating a
document and performing the merge. I wish to allow users to create a
set of fields for a new document or modify the existing set of fields
for an existing document.

I just thought that someone might be aware of some way to know when
the user closes the QBE window.
Click to expand...

Are they constructing a "real" query with calculated expressions, totals,
criteria, etc., or are they just selecting the desired fields for output?
For the latter you can just present them with a ListBox in a form and let
them select the fields they want and then you can build the query based on
that.
 
There is a possibility that they may in fact want to build a query with
something more than just the fields. That is why I have been looking at a way
to do this rather than just present a list of fields from the various tables.

I have considered placing a button on the form from which the QBE is
presented. Then when the user elects to open the QBE, I would make all other
objects disabled except that button. I would then be able to use that button
events to continue with my other actions. This is pretty much just a work
around but it is my only idea at the moment.

I know that if you have the QBE open to an evisting query and try to rename
the query, Access will notify you that you cannot rename the object. I just
thought that because Access is aware of the QBE being open, that there might
be a way to programtically deal with this.

Thanks your your input.

Br. B
 
Back
Top