0

Access 2016 Popup UserForm Obscured by Word Document

I have an Access 2016 database that contains authors and references that I use in my Word Documents. The authors are contained in one table which is joined to the references table.

If the data I need to input into Word isn't in the database, I have an Access UserForm that pops up to enter the data into the database. The problem is that I work with 2 Word documents opened side by side which takes up the full screen and the Access Userform is covered behind the Word documents. Due to the UserForm being popup and modal, it freezes everything. The only workaround I have come up with is to force the Word windows to minimize when the popup is called, but this is not optimal.

This Sub in Word calls the Access UserForm:

Sub OpenDataEntryForm(stAuthor As String)
Dim acc As Access.Application
Dim lngAuthor As Long
Dim stOpenArgs As String

Application.WindowState = wdWindowStateMinimize
Set acc = New Access.Application
    With acc

        .Visible = False
        .OpenCurrentDatabase stAccPath 'stAccPath is a constant
        lngAuthor = .DLookup("[ID]", "[tblAuthors]", "[Authors] = '" & _
              stAuthor & "'")
        stOpenArgs = CStr(lngAuthor)
       .DoCmd.OpenForm "frmDataEntry", acNormal, , , acFormAdd, _
                 acDialog, stOpenArgs
       .Quit   
End With
Application.WindowState = wdWindowStateMaximize
End Sub

This is the Load Event Sub of the Userform:

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        Me!Authors = CLng(Me.OpenArgs)
        Me.SetFocus
        Me!OriginalRef.SetFocus  
Else: Me!Authors.Locked = False
End If
End Sub

With the above code, the Word windows minimize so that data can be entered into the Access UserForm. I need to find a way to bring the Access UserForm to the front of the documents when it's called so that I don't have to minimize the document windows.

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43
Wayne Whittenberg
  • 13
  • 4
  • **Popup** property ensure that the form appears on top of the others when you open it. **Modal** ensure that the form stay on top whatever else element in your screen you click on. It seems that you have set both properties to true so I don't see anything else to do. I don't really understand your "it freezes everything" issue. I have no experience yet with Access 2016 though... – Thomas G Jan 05 '19 at 11:01
  • Even though the form is set to Popup, when opened the form is buried behind the Word document that triggered the form to pop up. And since the form is Modal, I can't use the controls on the Word document to minimize it manually so that I can get at the form (the cursor is round and spinning as when Word is not responding) which is why I termed it as "freezing" because that is the effect it has. – Wayne Whittenberg Jan 05 '19 at 15:12
  • See if the information in this discussion has what you need: https://stackoverflow.com/questions/34481674/vba-api-declarations-bring-window-to-front-regardless-of-application – Cindy Meister Jan 06 '19 at 17:24
  • Try changing .Visible = False to .Visible = True – macropod Jan 07 '19 at 04:14

1 Answers1

0

Using this link from Cindy Meister: VBA API declarations. Bring window to front , regardless of application, I was able to add this line of code to my Access UserFrom Form_Load Event:

AppActivate Me.Caption

This now brings the UserForm to the front whenever it is called.

Wayne Whittenberg
  • 13
  • 4
  • The first time I tried the above fix it worked. But now am getting run-time error 5, Invalid Procedure Call. I've tried closing everything down and rebooting, but am constantly getting error 5 whenever I run this code. Any suggestions? – Wayne Whittenberg Jan 07 '19 at 05:11
  • Couldn't find a fix for the run-time error 5 when using AppActivate function, so went back to https://stackoverflow.com/questions/34481674/vba-api-declarations-bring-window-to-front-regardless-of-application and used the API Function SetForegroundWindow instead and it now works. – Wayne Whittenberg Jan 07 '19 at 14:15