Selasa, 26 Agustus 2014

RE: [MS_AccessPros] widths and heights

 

Hi Crystal,

 

Here is the original message I posted back on Friday (Thursday your time):

 

Hi Stuart

Below are two functions (and a whole bunch of supporting code) to return you the width of the nav pane and the height of the ribbon (note that this includes the QAT and the title bar!)

Just call GetNavPaneWidth() and GetRibbonHeight()

All the best,
Graham

Copy and paste this code into a new module:
============== Start Code =================
Option Compare Database
Option Explicit

Private Const LOGPIXELSX    As Long = 88
Private Const LOGPIXELSY    As Long = 90

Private Const nTwipsPerInch As Long = 1440

Private dTwipsPerPixelX     As Double
Private dTwipsPerPixelY     As Double

Private Type winRECT
  Left As Long
  Top As Long
  Right As Long
  Bottom As Long
End Type

Private Declare Function GetDC _
  Lib "user32" ( _
    ByVal hWnd As Long _
  ) As Long

Private Declare Function ReleaseDC _
  Lib "user32" ( _
    ByVal hWnd As Long _
  , ByVal hDC As Long _
  ) As Long

Private Declare Function GetDeviceCaps _
  Lib "gdi32" ( _
    ByVal hDC As Long _
  , ByVal nIndex As Long _
  ) As Long
 
Private Declare Function GetWindowRect _
  Lib "user32" _
  ( _
    ByVal hWnd As Long _
  , lpRect As winRECT _
  ) As Long

Private Declare Function FindWindowEx _
  Lib "user32" _
  Alias "FindWindowExA" ( _
    ByVal hWndParent As Long _
  , ByVal hwndChildAfter As Long _
  , ByVal lpszClass As String _
  , ByVal lpszWindow As String _
  ) As Long

Private Function TwipsPerPixelX() As Double
  If dTwipsPerPixelX = 0 Then GetScreenMetrics
  TwipsPerPixelX = dTwipsPerPixelX
End Function

Private Function TwipsPerPixelY() As Double
  If dTwipsPerPixelY = 0 Then GetScreenMetrics
  TwipsPerPixelY = dTwipsPerPixelY
End Function

Private Sub GetScreenMetrics()
Dim hDC As Long
  hDC = GetDC(0)
  dTwipsPerPixelX = nTwipsPerInch / GetDeviceCaps(hDC, LOGPIXELSX)
  dTwipsPerPixelY = nTwipsPerInch / GetDeviceCaps(hDC, LOGPIXELSY)
  hDC = ReleaseDC(0, hDC)
End Sub

Public Function GetRibbonHeight() As Long
' Return height of the Ribbon in twips
' Note that the result includes the QAT and the application title bar
Dim hWnd As Long, xRect As winRECT
  hWnd = FindWindowEx(hWndAccessApp, 0, "MsoCommandBarDock", "MsoDockTop")
  If hWnd <> 0 Then
    hWnd = FindWindowEx(hWnd, 0, "MsoCommandBar", "Ribbon")
  End If
  If hWnd = 0 Then
    MsgBox "Ribbon not found"
  Else
    If GetWindowRect(hWnd, xRect) = 0 Then
      MsgBox "Cannot determine Ribbon dimensions"
    Else
      With xRect
        GetRibbonHeight = (.Bottom - .Top) * TwipsPerPixelY
      End With
    End If
  End If
End Function

Public Function GetNavPaneWidth() As Long
' Return width of the Nav Pane in twips
Dim hWnd As Long, xRect As winRECT, dHeight As Double
  hWnd = FindWindowEx(hWndAccessApp, 0, "NetUINativeHWNDHost", "Navigation Pane Host")
  If hWnd = 0 Then
    MsgBox "Nav Pane not found"
  Else
    If GetWindowRect(hWnd, xRect) = 0 Then
      MsgBox "Cannot determine Nav Pane dimensions"
    Else
      With xRect
        GetNavPaneWidth = (.Right - .Left) * TwipsPerPixelX
      End With
    End If
  End If
End Function
================  End Code =========================


 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 19:58
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

... and other messages that went to spam?

 

thx ~ Crystal

 

On Tuesday, August 26, 2014 1:56 AM, Crystal <strive4peace2008@yahoo.com> wrote:

 

My spam box gets too many messages each day to go though them ... 

 

Jeff, perhaps you can post Graham's code since you have it?

 

thanks

 

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

Through sharing, we will all get better
~  have an awesome day ~

 

On Monday, August 25, 2014 7:08 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Graham, I never saw the code either ... I'd like to though!

 

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

Through sharing, we will all get better
~  have an awesome day ~

 

On Monday, August 25, 2014 4:07 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Jeff

It must be because of that particularly obscene phrase, "Private Declare Function"  :-)

Cheers,

Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 09:24
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

I didn't originally but I do have 3 from you and 1 from Stuart in my Spam folder.  They include the code you mentioned.  Not sure why they went to spam since the rest of the email from you two has been in my inbox.

 

Cheers,

 

Jeff

 

From: "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, August 25, 2014 2:16 PM
Subject: RE: [MS_AccessPros] widths and heights

 

 

I'm getting a bit paranoid now :-)

Did anyone else see the code I posted on this thread?

Did you look at the forum online, Stuart, or only in your email?  If you receive messages by email, have you checked your spam folder?

Cheers,

Graham

 

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Tuesday, 26 August 2014 07:47
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

You're apparently right.  I'd love to see it.....

 

Stuart

 

On Saturday, August 23, 2014 8:09 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Crystal

 

I don't think Stuart is seeing any of my posts.  The only ones he replies to are ones that you have quoted :-/

 

I wonder if his email has blacklisted me as a spammer or something :-(

 

Anyway, could you please repost my message from a few days ago with the API code?  I spent a couple of hours on it so it would be a shame to see it wasted :-)

 

Cheers,

G

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, 24 August 2014 07:58
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

Hi Stuart,

Allen Browne has a great reference page with code for reading and setting database properties.

http://www.allenbrowne.com/ser-69.html

 

 

 

 

 

 

How to ensure Access 2007 users cannot modify the database schema (field structure), forms and reports in databases created in previous versions.

Preview by Yahoo

 

 

hope this helps ~


Warm Regards,
Crystal

 *
   (: have an awesome day :)
 *

 

On Friday, August 22, 2014 7:49 PM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

I am now hopelessly confused.  I don't see any code in any of the messages previously posted.

 

Stuart

 

On Friday, August 22, 2014 6:52 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Stuart,

 

the Database Analyzer for Microsoft Access picks up some of the properties -- you can run it and then check the a_DBs table, which has some of the CurrentDb and file properties.  Bill Mosca contributed code to this too.  The Design view of the a_DBs table tells the property name.  A lot of the information has been discovered by enumerating through properties.

 

Analyzer for Microsoft Access on CodePlex (open source)
http://analyzer.codeplex.com/

 

 

image

 

 

 

 

 

The Analyzer for Microsoft Access documents and analyzes what is in your Access databases. It is useful for anyone using or building Microsoft Access databases.

Preview by Yahoo

 

 

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

Through sharing, we will all get better

~  have an awesome day ~

 

On Friday, August 22, 2014 3:40 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Stuart

Did you not see the code I posted for you?  It's message #8 in this thread.
https://groups.yahoo.com/neo/groups/MS_Access_Professionals/conversations/topics/108692

All the best,
Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Saturday, 23 August 2014 08:49
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

Crystal, this works really well!   Only problem is, I am in a multi-user environment, and each user may have his/her own preference for where the form will be.

 

I also wonder if you could tell me where I could get documentation on currentDB properties.  Access help doesn't seem to know about them.

 

Thanks, Stuart

 

On Friday, August 22, 2014 3:35 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Stuart,

 

Graham had the same problems with the code to check Nav Pane stuff ... maybe the property names changed ... or maybe the property values are read when Access opens and they don't change.

 

Window location:

save where you want the window to be and how big you want it to be in the form DESIGN VIEW.  Set form property 'Close Button' to No.  Put a close button on your form that closes withOUTsaving.  Make sure you always save your changes in Design view before testing.

 

DoCmd.Close acform, me.name, acSaveNo 'closes the form code that code is behind without saving changes (data of course is saved).

 

> "by the width of the navigation pane"

 

yes, this is how Access works -- you are only setting where you want a window to be in the area to the right of the nav pane

 

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

 

Visual Basic for Applications language reference for Office 2013
http://msdn.microsoft.com/en-us/library/office/gg264383%28v=office.15%29.aspx

 

 

 

 

 

 

Last modified: November 20, 2012 Applies to: Office 2013 | VBA Welcome to the Visual Basic for Applications (VBA) language reference for Office 2013.

Preview by Yahoo

 


Through sharing, we will all get better
~  have an awesome day ~

 

 

On Friday, August 22, 2014 9:12 AM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Crystal, let me be more specific.  The routine you suggest for navpane width returns a number, but the results are puzzling.  The number, assuming it is supposed to be Twips, is much too small.   If I choose a navpane about 2 inches wide, it returns a number of about 250.  And it doesn't change if you resize the navpane.  You have to exit Access and come back in to see the size recalculated.

 

Perhaps I am on the wrong track.  What I am trying to do is open a moveable window in Access and have the program remember the location of the window so that the next time it is opened it opens in the same place. It seems that storing the location of the window by using the form's WindowLeft and WindowTop properties stores values that exclude the ribbon and the Navigation pane, while reopening the form with the stored values with MoveSize includes them.  So the form always reopens left of where I wanted it by the width of the navigation pane if the navigation pane is visible. Analogous problem for the ribbon.

 

By the way code that was recommended for ribbon height is

 

Application.CommandBars.Item("Ribbon").Height

 

but that doesn't seem to work well either. 

 

stuart

 

 

On Friday, August 22, 2014 9:59 AM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

I think my reply got lost somewhere.  I have tried the NavPane properties, but they don't work for me. No matter what I do to the width of the pane, it always returns the same number, 209.  And it always says the pane is hidden even when it isn't.  Am I doing something wrong?

 

Stuart

 

On Thursday, August 21, 2014 10:12 PM, "Crystal strive4peace2008@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Graham,

 

uh, you're welcome? that's too bad ... bug? bad documentation? ~ on a happy note, I added more to the Code Documenter and just uploaded a new version :)  Just ran it on the Analyzer ... now I'm running the web page report to post with the code :) ... well we'll see how fast it loads! (whether it will be one or more pages) I like using the jumps and the Back button and having everything all together but some people have asked for the code to be separated.

 

Warm Regards,
Crystal

 

Remote Programming & Training -- connect to me, let's build it together

 

 

Through sharing, we will all get better
~  have an awesome day ~

 

 

 

 

 

 

Download Crystal's Code Documenter Please send me your comments and ideas! This also documents MDB files. Watch for updates. Access 2007+ version Crystals_CodeDocumenter_120204_2p_BETA_ACCDE_TXT.zip

Preview by Yahoo

 

 

 

On Thursday, August 21, 2014 5:40 PM, "'Graham Mandeno' graham@mandeno.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Hi Crystal

Thank you!  I just learned about some more properties :-)

However … the results stay the same, whether I show/hide the nav pain, or change its width, or show/hide the search bar :-/

-- Graham

 

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Friday, 22 August 2014 11:29
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] widths and heights

 

 

Sub NavPane()
   With CurrentDb
      Debug.Print "Show Navigation Pane Search Bar = "; .Properties("Show Navigation Pane Search Bar")
      Debug.Print "NavPane Closed: "; .Properties("NavPane Closed")
      Debug.Print "NavPane Width: "; .Properties("NavPane Width")
   End With
End Sub

 

someone else can jump in on the ribbon part of your question ...

 

Warm Regards,
Crystal

Remote Programming & Training -- connect to me, let's build it together

 


Through sharing, we will all get better
~  have an awesome day ~

 

 

 

 

 

 

Download Crystal's Code Documenter Please send me your comments and ideas! This also documents MDB files. Watch for updates. Access 2007+ version Crystals_CodeDocumenter_120204_2p_BETA_ACCDE_TXT.zip

Preview by Yahoo

 

 

 

On Thursday, August 21, 2014 1:11 PM, "Stuart Schulman stoughy@yahoo.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

 

 

Is there any way (Access 2007) to determine the width of the navigation pane?  Or the height of the ribbon?

 

Thanks, Stuart

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

__._,_.___

Posted by: "Graham Mandeno" <graham@mandeno.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (25)

.

__,_._,___

Tidak ada komentar:

Posting Komentar