got rid of Access UI, now a blank screen

[Jaron]

Hi.

I've implemented the suggestion in the following link: http://www.vbaexpress.com/kb/getarti...kb_id=74#instr

...and it works nearly perfectly.
What I've done is set up my DB to open without the main application window showing, so that the user can run everything from forms. Then, on the main menu form (loads on DB open), I've got a command button labeled "Developer View". Clicking it prompts for a password, and the password form 'ok' button shows everything again. At the same time, the "Developer View" button changes to a "User View" button, which when clicked hides everything again.



Like I said, it almost works. The switch to "user view" is good, but when I switch back to "developer" the access application window shows itself, except it is empty...no ribbon, no navpane. I can use F11 to get the navpane back, but I can't get the ribbon to show, even if I put a DoCmd.ShowToolbar "Ribbon", acToolbarYes into the code.

However, if I then close the DB and reopen, it is in my "Developer View" and everything is normal.

If it wasn't for this, I could toggle back and forth between user and developer view with only a password prompt to slow me down.

Any ideas on what could be causing this?

[Jaron]

Ok, I see that I didn't post that as a hyperlink, so no one could view it. Anyway, here is the function that I'm calling to show or hide the access window:

Option Compare Database
Option Explicit
 
Global Const SW_HIDE = 0
Global Const SW_SHOWNORMAL = 1
Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWMAXIMIZED = 3
 
Private Declare PtrSafe Function apiShowWindow Lib "user32" _
Alias "ShowWindow" (ByVal hWnd As LongLong, _
ByVal nCmdShow As LongLong) As LongLong
 
Function fSetAccessWindow(nCmdShow As LongLong)
     
    Dim loX As LongLong
    Dim loForm As Form
    On Error Resume Next
    Set loForm = Screen.ActiveForm
     
    If Err <> 0 Then
        loX = apiShowWindow(hWndAccessApp, nCmdShow)
        Err.Clear
    End If
     
    If nCmdShow = SW_SHOWMINIMIZED And loForm.Modal = True Then
        MsgBox "Cannot minimize Access with " _
        & (loForm.Caption + " ") _
        & "form on screen"
    ElseIf nCmdShow = SW_HIDE And loForm.PopUp <> True Then
        MsgBox "Cannot hide Access with " _
        & (loForm.Caption + " ") _
        & "form on screen"
    Else
        loX = apiShowWindow(hWndAccessApp, nCmdShow)
    End If
    fSetAccessWindow = (loX <> 0)
    
End Function

to hide, I use:

Call fSetAccessWindow(0)

, and after that I put in a line that opens my main menu form so that the user can navigate through the DB. This part works well.

Then to re-show, I've got:

Call fSetAccessWindow(3)

At this point, I get the blank screen. I can close and restart and everything is back to normal, but I can't get it right in code.

What I've tried so far is, after my 'restore' line above, I run:

DoCmd.ShowToolbar "Menu Bar", acToolbarYes

Nothing changes

Then I tried:

Run "EnableMenuBar"

to call

Option Compare Database
Sub EnableMenuBar()
Dim cbr1 As CommandBar
   
For Each cbr1 In CommandBars
    If cbr1.Name = "Menu Bar" Then
        cbr1.Enabled = True
    End If
Next cbr1
   
End Sub

What I get is "Method 'Visible' of object 'Commandbar' failed"

Then I tried another one I googled:

Run "RestoreMissingToolbars"

to call:

Option Compare Database
Sub RestoreMissingToolbars()
Dim cbr As CommandBar
For Each cbr In Application.CommandBars
cbr.Enabled = True
cbr.Reset = True
Next
End Sub

Same basic problem: "Method 'Reset' of object 'CommandBar' failed"

I'm beginning to wonder if maybe the 'Hide' routine corrupts the Menu Bar...But I can't figure out why it would do that.


Any ideas?

[Jaron]

Ok, I've solved this...maybe a little unorthodox, but it works. If the DB seems to work after I reopen it, then I just figured I could program a restart, which, you wouldn't think would be possible since the code dies with the program. But there seems to be some sort of time delay the way I've got it so that it all works out. Anyway, here's what I've got:

a recap:
On my 'Main Menu' form, which loads on DB open, I've got this button that closes the whole DB, and another button that toggles to 'Developer View'. Once I switch to 'Developer View', I want my 'Developer View' button to change to a 'User View' button, and I want my 'close DB' button to change to a 'close window' button so that I can easily close the Main Menu and navigate through my objects with the NavPane. That part is easy; just overlay one button on another and switch between them using the 'Visible' property. Also, I want the shortcut menu to be available during 'Developer View', but not in 'User View', so that I can easily right-click on my forms to go to Design view, but the user can't. And finally, I wanted my Access main application window to go away for 'User View', but come back for 'Developer View'.

So here's the finished Module1 ( I had to tweak it for 64-bit by adding "PtrSafe" and changing Long to LongLong):

Option Compare Database
Option Explicit
 
Global Const SW_HIDE = 0
Global Const SW_SHOWNORMAL = 1
Global Const SW_SHOWMINIMIZED = 2
Global Const SW_SHOWMAXIMIZED = 3
 
Private Declare PtrSafe Function apiShowWindow Lib "user32" _
Alias "ShowWindow" (ByVal hWnd As LongLong, _
ByVal nCmdShow As LongLong) As LongLong
 
Function fSetAccessWindow(nCmdShow As LongLong)
     
    Dim loX As LongLong
    Dim loForm As Form
    On Error Resume Next
    Set loForm = Screen.ActiveForm
     
    If Err <> 0 Then
        loX = apiShowWindow(hWndAccessApp, nCmdShow)
        Err.Clear
    End If
     
    If nCmdShow = SW_SHOWMINIMIZED And loForm.Modal = True Then
        MsgBox "Cannot minimize Access with " _
        & (loForm.Caption + " ") _
        & "form on screen"
    ElseIf nCmdShow = SW_HIDE And loForm.PopUp <> True Then
        MsgBox "Cannot hide Access with " _
        & (loForm.Caption + " ") _
        & "form on screen"
    Else
        loX = apiShowWindow(hWndAccessApp, nCmdShow)
    End If
    fSetAccessWindow = (loX <> 0)
    
End Function

....and here's the finished Module2:

Option Compare Database
Sub Reopen()
Dim strPathName As String
strPathName = CurrentProject.Path & "\" & CurrentProject.Name
Dim accapp As Access.Application
 
Set accapp = New Access.Application
 
accapp.OpenCurrentDatabase (strPathName)
DoCmd.Quit acQuitSaveAll
End Sub

...and here's the code for the 'Main Menu':

Option Compare Database
Private Sub Developer_View_Button_Click()
    
    DoCmd.Close acForm, "MainMenu", acSaveYes
    DoCmd.OpenForm "Developer Password", acNormal, "", "", , acNormal
    DoCmd.GoToControl "Password"
End Sub

Private Sub User_View_Button_Click()
DoCmd.Close acForm, "MainMenu", acSaveYes

'A version of the following needs written for every form in the DB:
            'DoCmd.OpenForm "Double_click_here_to_change_name", acDesign, , , , acHidden
            'Forms![Double_click_here_to_change_name].ShortcutMenu = True
            'DoCmd.Close acForm, "Double_click_here_to_change_name", acSaveYes           
                      
            DoCmd.OpenForm "Developer Password", acDesign, , , , acHidden
            Forms![Developer Password].ShortcutMenu = False
            DoCmd.Close acForm, "Developer Password", acSaveYes
          
            DoCmd.OpenForm "MainMenu", acDesign, , , , acHidden
            Forms![MainMenu].ShortcutMenu = False
            DoCmd.Close acForm, "MainMenu", acSaveYes
            
DoCmd.OpenForm "MainMenu", acDesign, , , , acHidden
Forms![MainMenu].Developer_View_Button.Visible = True
Forms![MainMenu].[User View Button].Visible = False
Forms![MainMenu].[Close Window Button].Visible = False
Forms![MainMenu].[Close Access Button].Visible = True
DoCmd.Close acForm, "MainMenu", acSaveYes
DoCmd.OpenForm "MainMenu", acNormal, , , , acWindowNormal

Call fSetAccessWindow(0)
End Sub

...and here's what I've got in my Password form:

Option Compare Database
Private Sub Cancel_Button_Click()
    DoCmd.Close acForm, "Developer Password", acSaveYes
    DoCmd.OpenForm "MainMenu", acNormal, , , , acWindowNormal
End Sub
Private Sub Ok_Button_Click()
    Dim strPasswd
    strPasswd = Me.Password
    If strPasswd = "whatever_your_password_is" Then
        
        DoCmd.Close acForm, "Developer Password", acSaveYes
        
        'A version of the following needs written for every form in the DB:
            'DoCmd.OpenForm "Double_click_here_to_change_name", acDesign, , , , acHidden
            'Forms![Double_click_here_to_change_name].ShortcutMenu = True
            'DoCmd.Close acForm, "Double_click_here_to_change_name", acSaveYes
            
            DoCmd.OpenForm "Developer Password", acDesign, , , , acHidden
            Forms![Developer Password].ShortcutMenu = True
            DoCmd.Close acForm, "Developer Password", acSaveYes
           
            DoCmd.OpenForm "MainMenu", acDesign, , , , acHidden
            Forms![MainMenu].ShortcutMenu = True
            DoCmd.Close acForm, "MainMenu", acSaveYes
           
                                              
        DoCmd.OpenForm "MainMenu", acDesign, , , , acHidden
        Forms![MainMenu].[User View Button].Visible = True
        Forms![MainMenu].[Developer View Button].Visible = False
        Forms![MainMenu].[Close Window Button].Visible = True
        Forms![MainMenu].[Close Access Button].Visible = False
        DoCmd.Close acForm, "MainMenu", acSaveYes
        
        Call fSetAccessWindow(1)
        
        Run "Reopen"       
        
    Else
        MsgBox "You do not have developer access", _
               vbOKOnly, "Important Information"
        DoCmd.Close acForm, "Developer Password", acSaveYes
        DoCmd.OpenForm "MainMenu", acNormal, , , , acWindowNormal
        Exit Sub
    End If
End Sub

And that's it. I'm not sure why it works...I guess the part where it opens the current DB takes long enough that the command to quit get's done in time for it not to have the lock on.
Actually, I tried this at first in a separate 'Utility' DB...that is, run code to open a different DB then close, then in the other DB have code to open the first DB then close itself. But then I realized that it works all from within itself just fine...which is good, because then I don't have to pack around a separate DB just to make it work. Best part is, it returns it's own filepath so I don't have to change it if I install it somewhere else.

Anyway, there it is if anyone else needs it...and if anyone sees any potential problems, let me know.


thanks.

[Jaron]

oops, here's an edit:

I noticed that after I switched to "User View" and then close the program, when I reopen it opens in "User View", but with the Access Application window showing.

To fix it, I added an invisible textbox to the Main Menu, then in the Password form code, right before running the "Reopen" routine, I modified the code as follows:

 DoCmd.OpenForm "MainMenu", acNormal, , , , acHidden
        Forms![MainMenu].Text16 = "Developer"
        DoCmd.Close acForm, "MainMenu", acSaveYes
        
        Call fSetAccessWindow(1)
        
        Run "Reopen"

Then, in the Main Menu load event, I put in a check routine to see what state the DB should be in:

Private Sub Form_Load()
If Text16.Value = "Developer" Then
Exit Sub
    Else
    Call fSetAccessWindow(0)
    Exit Sub
End If
End Sub

That seems to do it.