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

Programmatically positioning query output on the screen

P: n/a
Does anyone know of a way, via VBA, to set the screen position of
query results to a particular top, left position? I've glanced at API
techniques but cannot find exactly what will do the trick.

I have a database with large amounts of historical data for which many
queries have been built. If I used Access reports for output, I'd have
to build many reports that I don't want to do. For this and other
reasons, my approach to inquiries is to display the results in a
(non-updateable) query. I've added formatting to numeric columns to
get the desired look to the data and, generally, I'm happy with using
query output. But, for visual consistence, I'd like to all query
output to be positioned in exactly the same place without having to
position them manually.

As long as I'm at it, setting the width and height via VBA would be a
bonus.

Any insights would be appreciated.

Thanks

May 22 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"GeorgeSmiley" <pm********@gmail.com> schreef in bericht news:11*********************@j73g2000cwa.googlegro ups.com...
Does anyone know of a way, via VBA, to set the screen position of
query results to a particular top, left position? I've glanced at API
techniques but cannot find exactly what will do the trick.

I have a database with large amounts of historical data for which many
queries have been built. If I used Access reports for output, I'd have
to build many reports that I don't want to do. For this and other
reasons, my approach to inquiries is to display the results in a
(non-updateable) query. I've added formatting to numeric columns to
get the desired look to the data and, generally, I'm happy with using
query output. But, for visual consistence, I'd like to all query
output to be positioned in exactly the same place without having to
position them manually.

As long as I'm at it, setting the width and height via VBA would be a
bonus.

Any insights would be appreciated.

Thanks


I have been playing with the DataSheet property a while ago.
I created A form with a subformcontrol for the purpose you describe (analyse query's/tables).
In the subformcontrol one can load tables and/or query's from a combo.

In my form the width of the columns is automatically adjusted.
You can choose forecolor, backcolor, font, fontsize and such in the view.

I can mail this to you if you are interested. It's only one form actually.
You will need to translate a few items I guess.

Arno R
May 22 '06 #2

P: n/a
DFS
What you probably want to do is something like this:

* create a form (single form view)
* put a subform inside it sized to take up most or all of the form space
* use code to open the form - using MoveSize to position it
* use code to set the form caption
* use code to set the subform sourceobject to "Query.QueryName"

You can also write code to set the width of each column in the query.

GeorgeSmiley wrote:
Does anyone know of a way, via VBA, to set the screen position of
query results to a particular top, left position? I've glanced at API
techniques but cannot find exactly what will do the trick.

I have a database with large amounts of historical data for which many
queries have been built. If I used Access reports for output, I'd
have to build many reports that I don't want to do. For this and
other reasons, my approach to inquiries is to display the results in a
(non-updateable) query. I've added formatting to numeric columns to
get the desired look to the data and, generally, I'm happy with using
query output. But, for visual consistence, I'd like to all query
output to be positioned in exactly the same place without having to
position them manually.

As long as I'm at it, setting the width and height via VBA would be a
bonus.

Any insights would be appreciated.

Thanks

May 22 '06 #3

P: n/a
If you have dozens or more queries, the easiest thing (using VBA) is to
combine the queries and parametes either in an array, or a table. For
my explanation, I will put the sql code in one table that will have 2
columns, an RowNumber column (you can use autonum) and a sql column -
where you will store your sql code - this would be a memo column so you
don't have to worry about the 255 char limit of a text column. Another
table that I will call ParamTable will contain the Where Clauses and
parameters. You can then loop through the table using DAO code. Then
you display the results of all the queries throug one form (or report).

In the Display form you will have the maximum number of textboxes that
the largest query will return. Display the form in datasheet view.
Generally, this will be a subform on a main form. You invoke the
queries through the main form and display the results of each query in
the subform.

the subform will be based on a results table. As you loop through your
parameter list in the main form, you will populate the results table
with the results of the current query and display those results in the
subform. You can hide the columns that are not in use in the subform by
looping through the controls collection of the subform and hide the
textboxes that are not in use. Here is some sample code:

Sub RunQueries()
Dim DB As DAO.Database
Dim RSsql As DAO.Recordset, RSparam As DAO.Recordset
Dim strSql As String, i As Integer, j As Integer
Dim RSresult As DAO.Recordset, RSresultTable As DAO.Recordset

Set DB = CurrentDB
Set RSsql = DB.OpenRecordset("Select sqlcolumn from sqlTable Where
IdentityCol = " & queryNumberOfyourChoice)
Set RSparam = DB.OpenRecordset("Select paramcolumn from ParamTable Where
paramNum = " & paramNumberOfyourChoice
strSql = RSsql(sqlcolumn) & RSparam

Set RSresult = DB.OpenRecordset(strSql)
Set RSresultTable = DB.OpenRecordset("ResultTable")
Do While Not RSresult.EOF
RSresultTable.AddNew
For i = 0 To RSresult.Fields.Count - 1
RSresultTable(i) = RSresult(i)
Next
RSresultTable.Update
RSresult.MoveNext
Loop
RSsql.Close
RSparam.Close
RSresult.Close
RSrestultTable.Close
End Sub

You can list the main body of the queries (the main sql) in sqlTable.
You list the Where clauses in the ParamTable. This is just the main
guts to give you an idea of how you can easily reduce the work of
hundreds of queries to a short simple routine as above. I left out
details like using a static number so you can individually iterate
through each query.

If you are not currently using these kinds of techniques in your VBA
coding, then hopefully this will give you an idea of the things you can
do with VBA. There are tons of examples on using these techniques on
the internet.

hth
Rich

*** Sent via Developersdex http://www.developersdex.com ***
May 22 '06 #4

P: n/a
If you're just opening, positioning and sizing, you could try this:
Dim lngWH As Long
Const TWIPSPERINCH As Long = 1440

DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly
DoCmd.SelectObject acQuery, "MyQuery", False

Screen.ActiveDatasheet.Move 0, 0, 4 * TWIPSPERINCH, 2 * TWIPSPERINCH

That would put the data sheet at far top, far left, 4 inches wide, 2 inches
tall.

HTH
May 22 '06 #5

P: n/a
docmd.movesize

in the onopen event

May 23 '06 #6

P: n/a
I think he's actually opening queries, which don't have an OnOpen event that
I know of.
May 23 '06 #7

P: n/a
Good Point.....

Oh Well.....

May 23 '06 #8

P: n/a
To All responders

Thanks for your feedback.

The solution for me was in Rick Wannall's posting, with one change.
Instead of the statement "Screen.ActiveDatasheet.Move ...", I
substituted a "Docmd.Movesize ..." and that did the trick.

Some of the other suggestions were interesting. Rich P's suggestion
was similar to a technique I used in a project a while back. I'll be
sure to study it later.

One of the ideas I've had about managing hundreds of queries is like
Rich's, i.e., put all sql in a table, and run the sql from an
appropriate entry in that table while applying some record selection
code. This would let me manage all my queries fairly nicely despite
the performance hit of not being able to pre-compile the queries.

However, using a generic approach seems to take away one of the
features I want to use. That is, using a color like [blue] to
emphasize certain columns in the output. I've tried placing a Format$
command in the sql in my attempt to solve this problem, but the color
designation is ignored. For example,
ABC: format$([abcde],"0.0[blue]")
does not display the value in blue.

Any thoughts or suggestions would be appreciated.

Thanks
GeorgeSmiley



On Mon, 22 May 2006 22:36:19 GMT, "Rick Wannall" <cw******@yahoo.com>
wrote:
If you're just opening, positioning and sizing, you could try this:
Dim lngWH As Long
Const TWIPSPERINCH As Long = 1440

DoCmd.OpenQuery "MyQuery", acViewNormal, acReadOnly
DoCmd.SelectObject acQuery, "MyQuery", False

Screen.ActiveDatasheet.Move 0, 0, 4 * TWIPSPERINCH, 2 * TWIPSPERINCH

That would put the data sheet at far top, far left, 4 inches wide, 2 inches
tall.

HTH


May 23 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.