Results 1 to 15 of 15
  1. #1
    rick44 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    6

    Programming Navigation Pane Width in VBA

    I am attempting to write some VBA code for an Access (2010) database command button that will force the Navigation Pane to a fixed size (width).

    It is my preference to have the Navigation Pane “maximized” at all times (but to a specific width, see further explanation).


    I occasionally will “drag” the size (width) of the Navigation Pane so I can read some of longer names of forms, tables, etc. in that pane. The forms that I have already created have numerous controls on them and whenever I resize the Navigation Pane it of course resizes the forms too. This causes problems (mostly aesthetic) with some of the controls on the forms until I resize the Navigation Pane back to the original size. BUT, it’s a PIA toget that size back to the exact width I’d like it to be for optimum viewing of the forms.


    Somewhere, somehow, Access “knows” what the previous width of the Navigation Pane was when going from minimized to maximized using the Shutter Bar buttons. Where is that information stored and/or how can I access it in code to manipulate the pane size?


    The following code toggles the Navigation Pane between open and close (maximum/minimum) without any problems but this is not what I’m looking for.


    Private Sub pbTest_Click

    DoCmd.NavigateTo “acNavigationCategoryObjectType”
    DoCmd.Minimize
    DoCmd.Maximize


    End Sub

    Thanks, Rick

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    You say the forms are resized - are you use Tabbed Documents mode? I always use Overlapping Windows.

    AFAIK, there is no way to programmatically set Navigation Pane width.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    rick44 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    6
    Quote Originally Posted by June7 View Post
    You say the forms are resized - are you use Tabbed Documents mode? I always use Overlapping Windows.

    AFAIK, there is no way to programmatically set Navigation Pane width.

    Yes, I do use/prefer Tabbed mode. I did try Overlapping Windows after reading your reply and the problem, while not as bad, is still there. I.e., I'd like to return the Navigation Pane back to the exact width that makes the form view optimal without fiddling around dragging it with the mouse.

    After doing a fairly extensive Google search on the topic, it appears you may be correct in that there is no programming solution; at least not a straightforward one.

    But that just makes me even more curious: somewhere that Navigation Pane size (width or position) info must be stored, because it always returns to the same place it was last sized at. My thought is if I could find where that info is stored at I might be able to cobble together some code to force the pane size by manipulating that info. The only three places I can think of would be: somewhere within the database itself, an external file, or possibly the Windows Registry. In any of those cases I don't have enough expertise to know how to search for that location.

    Again, I haven't been able to find any info even on where this info is kept (assuming it must be kept somewhere, right?).

    Thanks for responding.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the issue you have is not the width of the form, but the width of the access window. You would need code to change the width of that which I think would be disconcerting for the user.

    You can fake different form sizes by creating a full height box control to the right side of the form with the left edge of the box at the point you want your form to stop. The width of the box doesn't matter since the code below will set this. Fill the box with the backcolor of your choice and ensure the back style is set to solid. Lets say you call this box 'rightBox'. Then in the form resize event put

    on error resume next 'in case user reduces window width to less than the rightbox.left value
    rightbox.width=insidewidth-rightbox.left
    I don't know if this will work with navigation forms, I don't use them because of their limitations.

    If you are using form header/footer then you will need a box for each section and modify the code for each of them.

    With overlapping windows, the form should open at the window size you have when you save the form. You can also set to autoresize property to yes which should then open to the size of the form.

  5. #5
    rick44 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    6
    Quote Originally Posted by Ajax View Post
    the issue you have is not the width of the form, but the width of the access window. You would need code to change the width of that which I think would be disconcerting for the user.

    You can fake different form sizes by creating a full height box control to the right side of the form with the left edge of the box at the point you want your form to stop. The width of the box doesn't matter since the code below will set this. Fill the box with the backcolor of your choice and ensure the back style is set to solid. Lets say you call this box 'rightBox'. Then in the form resize event put



    I don't know if this will work with navigation forms, I don't use them because of their limitations.

    If you are using form header/footer then you will need a box for each section and modify the code for each of them.

    With overlapping windows, the form should open at the window size you have when you save the form. You can also set to autoresize property to yes which should then open to the size of the form.
    Unless I'm missing something, the main issue I'm having is that the Navigation Pane width seems to take priority over all other things: it will always return to its last setting no matter what size I set the Access window or the form to. I already size my forms and Access window via code, but sizing the Navigation Pane programmatically is what I'd like to accomplish now.

    I'm assuming that somewhere there is a parameter stored that represents the width of the Navigation Pane. I'd like to know where that parameter is stored at.

    Thanks for the feedback.

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by rick44 View Post
    But that just makes me even more curious: somewhere that Navigation Pane size (width or position) info must be stored, because it always returns to the same place it was last sized at. My thought is if I could find where that info is stored at I might be able to cobble together some code to force the pane size by manipulating that info. The only three places I can think of would be: somewhere within the database itself, an external file, or possibly the Windows Registry. In any of those cases I don't have enough expertise to know how to search for that location.
    Again, I haven't been able to find any info even on where this info is kept (assuming it must be kept somewhere, right?).

    It's a property of each individual database called 'NavPaneWidth'
    If you go to Database Tools ...Database Documenter, then tick Properties in the options you will see the value displayed.

    Click image for larger version. 

Name:	Capture.PNG 
Views:	27 
Size:	34.7 KB 
ID:	36913

    So you need code to edit that property.
    I think Allen Browne has something you may be able to adapt. Let us know how you get on
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    77 is a strange size, too small for twips, too large for inches or cm?

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Ajax
    Yes I agree about the value being odd.

    Having said that I have similar code to check if the ribbon is minimised.

    Code:
    Public Function IsRibbonMinimized() As Boolean    'Result: 0=normal (maximized), -1=autohide (minimized)
        IsRibbonMinimized = (CommandBars("Ribbon").Controls(1).Height < 100)
    End Function
    i.e. minimised if height<100

    The navpane width = 77 was for a navigation pane where I had reduced the width manually.
    The NavPane Closed property is also relevant.
    Here are 3 results using a fresh database:

    Click image for larger version. 

Name:	NavPanePropertyData.PNG 
Views:	29 
Size:	4.9 KB 
ID:	36914

    Rick44
    I haven't looked into setting the property value using code
    However these 2 functions by Allen Browne should hopefully provide what you need: HasProperty & SetPropertyDAO - at http://allenbrowne.com/func-DAO.html
    Last edited by isladogs; 01-13-2019 at 01:57 PM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps it is pixels - in which case size will vary depending on the monitor size and resolution.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    In the Immediate window:

    ?Application.Version
    14.0

    I do not see reference to the other properties in intellisense. I also looked under CurrentProject as well CurrentDb

    I used Allen's ShowDatabaseProps function to list the properties and modified to show values of the NavPane set. The output:

    NavPane Category:0
    NavPane Closed:0
    NavPane Width:177
    NavPane View By:0
    NavPane Sort By:1

    Then I tried:

    If prp.Name = "NavPane Width" Then prp.Value = 500

    Nothing happens, not even an error. However, next time I list the properties it shows value of 500. I manually changed the NavPane size then list properties and the value does not change until I close db and the value saved is the current width of the Nav Pane, not what I used in code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Probably a dumb question, but won't be the first.
    Once the split db is released for use, the nav pane usually isn't allowed to be seen by the user anyway, and all activities that need to be invoked should come from forms, menus or the like. Why worry about this, then?
    I've never heard of the nav pane affecting the layout or size of tabbed forms - just that it would introduce scroll bars if all of a sudden the form didn't fit in the available Access window space. Maybe scroll bars or AutoCenter are turned off? Maybe the plan is to allow users to see the pane after db is released?

    I've used tabbed forms quite a bit and haven't noticed resizing issues. If all the controls are left justified, I just end up with a lot of background space around the control area. Opening the nav pane only shifts the form to the right, but then I've never had so many controls on a form that there was no window real estate left. Again, for me, opening or altering the nav pane only comes into play during development, and if I were to publish it with tabbed forms, users wouldn't get to see the nav pane anyway.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    rick44 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    6

    Hi Ridders52, Ajax, June7, & Micron

    While I have a fair amount of programming experience it is almost exclusively with programmable logical controllers (PLC’s) and human machine interfaces (HMI’s). Very little of I’ve done in the past is considered OOP and I confess to being a novice with VBA and Access. I am trying to expand my horizons by self teaching myself VBA and Access and everything I do is for personal use at this point (I’m retired). I’d especially appreciate any advice or suggestions being given at the “newbie” level. E.g.; Micron commented on “splitting” the database… I didn’t have a clue what that meant. I do now, thank you Google ha ha

    I’ll probably never “split” this database as it is in a continuous state of evolving as I learn more VBA, it won’t be on any network, security isn’t an issue and I am the end user.

    My response is kind of long and other than some experimentation with some of Allen Browne’s code I didn’t know what else to do. I’ve attached a Word.doc in a .zip with some screen shots

    I’m still open to any suggestions, as I’m rapidly running out of ideas.

    Thanks to everyone for responding, Rick

    For the following discussion whenever I refer to the width of the Navigation Pane numerically I received that value from of NavPane Width by running a modified version Allen Browne’s ShowDatabaseProps function.

    What I have found so far:

    The value of NavPane Width is likely reported in pixels on my PC. I program my project so it opens up to a default width of 960 pixels on my 1920 x 1080 monitor which is exactly half of my monitor. I use the other half for my debug window. I expanded the Navigation Pane to about as wide as I could drag it out to in the default window and NavPane Width comes out to something close to 960 (maybe 20 – 30 pixels short, I didn’t spend a lot of time trying to make sure I had it maxed out. Prior to that is was about 185 pixels which is about the “normal” width I use to view all the things in that window;, that value makes sense in terms of scale.

    The parameter for the width of the Navigation Pane is almost for certain stored in the .accdb file and not in any external file or the registry. I concluded this by expanding the Navigation Pane out to about 2/3 of my default window (624 pixels to be exact). I then shut the program down and copied the .accdb file to a thumb drive. I stuck the thumb drive in another PC and launched the .accdb file from the thumb drive. It opened up with the Navigation Pane at 624 pixels wide.


    Access appears to write to NavPane Width only when shutting down the database and reads that location only when opening the database. I tested this by forcing different values into that location and it had no affect on the size of the Navigation Pane. Here’s the test I did:


    Step 1: I read the value of NavPane Width = 181

    Step 2: I forced NavPane Width = 500. The Navigation Pane did not change size.
    Step 3: I checked NavPane Width again and it read 500.
    Step 4: I collapsed and expanded the Navigation Pane using the Shutter Button (<< and>> ). Visually it returned to the width of 181, clearly not 500.
    Step 5: Without doing anything else, I checked NavPane Width and it still read 500 though the window itself was clearly was at 181.
    Step 6: With NavPane Width still reading 500, I shut the database down and reopened it. The Navigation Pane window was still at 181 and when I checked NavPane Width it was at 181

    I did some related testing and included to some screenshots in the attached (nav1.doc).zip.


    From this I concluded that NavPane Width only comes into play when opening and closing the database. However, there must be another location involved besides NavPane Width because when using the Shutter Button to minimize (<<) the Navigation Pane it always returns to the same width you last had it (prior to minimizing it) at when you maximize (>>) it again. Or is there a way of accomplishing that without storing a value somewhere?


    I was not able to gain access to NavPane Width using either SetPropertyDAO or HasPropertyDAO of Allen Browne. Nor could I read NavPane Width directly using debug.print. (I admit that I am a novice when it comes to VBA & Access, so I may have had some syntax errors that I couldn’t find.) By that I mean I was able to read other properties (that show up with Intellisense) such as CollatingOrder (but not NavPane Width) “directly” using:


    Debug.Print CurrentDb.CollatingOrder


    For setting and viewing NavPane Width I modified Allen Browne’s function as below. (Note: I’d comment out prp.Value during some of the testing as appropriate so that I wouldn’t change NavPane Width’s value when I was just checking it.)


    Function ShowDatabaseProps()

    'Purpose: List the properies of the current database.
    Dim db As DAO.Database
    Dim prp As DAO.Property

    Set db = CurrentDb()
    For Each prp In db.Properties
    If (prp.Name = "NavPane Width") Then
    If prp.Value = 185 Then
    prp.Value = 50
    Else
    prp.Value = 185
    End If
    Debug.Print prp.Name & " " & prp.Value
    End If
    Next

    Set db = Nothing
    End Function
    Attached Files Attached Files

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Hi Rick

    I can confirm that Access saves the NavPane Width property when it closes ready for the next time the file is opened.
    I've also now attempted unsuccessfully to modify the NavPane Width using variations of the SetPropertyDAO code by Allen Browne.
    I think you should abandon this idea and go for a much simpler idea - hide or minimise the navigation pane - both of which do work.Place thiscode in a standard module.

    Code:
    Public Function ShowNavigationPane()
    DoCmd.SelectObject acForm, , True
    End Function Public Function HideNavigationPane()
    DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.RunCommand acCmdWindowHide
    End Function Public Function MinimizeNavigationPane()
    DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.Minimize
    End Function
    Then just run e.g. MimimizeNavigationPane in the Form_Load event or on a button click
    You can see this code in use in various example apps of mine. For example: http://www.mendipdatasystems.co.uk/c...ace/4594365418

    That also includes code you may find useful including:
    a) minimize/maximize/hide the ribbon
    b) hide/show the taskbar
    c) hide/show the Access application window

    I'd also recommend changing to overlapping windows as that gives far more control over the display

    Finally its still good practice to split databases even if you are the only user.
    This makes it easier to backup the data / recover from corruption / update the user interface (forms/reports/code etc ) without affecting your data ... etc

    HTH
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    rick44 is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2015
    Posts
    6
    Quote Originally Posted by ridders52 View Post
    Hi Rick

    I can confirm that Access saves the NavPane Width property when it closes ready for the next time the file is opened.
    I've also now attempted unsuccessfully to modify the NavPane Width using variations of the SetPropertyDAO code by Allen Browne.
    I think you should abandon this idea and go for a much simpler idea - hide or minimise the navigation pane - both of which do work.Place thiscode in a standard module.

    Code:
    Public Function ShowNavigationPane()
    DoCmd.SelectObject acForm, , True
    End Function Public Function HideNavigationPane()
    DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.RunCommand acCmdWindowHide
    End Function Public Function MinimizeNavigationPane()
    DoCmd.NavigateTo "acNavigationCategoryObjectType" DoCmd.Minimize
    End Function
    Then just run e.g. MimimizeNavigationPane in the Form_Load event or on a button click
    You can see this code in use in various example apps of mine. For example: http://www.mendipdatasystems.co.uk/c...ace/4594365418

    That also includes code you may find useful including:
    a) minimize/maximize/hide the ribbon
    b) hide/show the taskbar
    c) hide/show the Access application window

    I'd also recommend changing to overlapping windows as that gives far more control over the display

    Finally its still good practice to split databases even if you are the only user.
    This makes it easier to backup the data / recover from corruption / update the user interface (forms/reports/code etc ) without affecting your data ... etc

    HTH
    Hi Ridders52,

    I guess this thread is getting kind of long because the min/max code you kindly suggested is where I started at: see my first post. I was hoping to improve on that. But I was also hoping to hit the lottery as well. I appreciate you looking into it and confirming there apparently is no easy fix. It's good to know I just wasn't missing something due to my limited knowledge of VBA.

    Thanks again! Rick

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Ah yes. Sorry about that.
    When I found the relevant property, i thought it might be possible.
    The fact that there seems to be no solution online does suggest otherwise.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Programming Text Width in Access
    By KAR in forum Programming
    Replies: 8
    Last Post: 12-27-2013, 10:46 PM
  2. Replies: 2
    Last Post: 04-21-2013, 08:03 AM
  3. Navigation Pane
    By Patrick.Grant01 in forum Programming
    Replies: 11
    Last Post: 01-08-2013, 04:55 PM
  4. Navigation Bar Width Issue
    By RayMilhon in forum Access
    Replies: 4
    Last Post: 07-25-2012, 05:39 PM
  5. Navigation Pane Help Please...
    By Kristena in forum Programming
    Replies: 2
    Last Post: 01-13-2012, 03:03 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums