Make Access Form popup above other open apps? 1

[ReTheOff]

HI all, thanks for any help you may have.

I have a timer event running on form, when the event occurs, another form pops up to display statistics. This works perfectly, except for when another application is the active window. How do I tell Access to become the active window?

So, if the users are in MS Word or MS Outlook, Access will suddenly take over as the active application or window. (this is based on when the timer event is triggered within Access.) The event does indeed work, and the popup window does popup, just not "On Top" of other windows that are outside of Access.

Thanks!

Greg

 

[huskerdon]

I've done this before with VB, but never tried it with Access until just now. There may be a better way to do this, but it works for me - using the API's listed here:
(Put these declarations and constants in a module)

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Public Declare Function SetActiveWindow Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
Public Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
Public Declare Function IsIconic Lib "user32" (ByVal hwnd As Long) As Long

Public Const SW_SHOW = 5
Public Const SW_RESTORE = 9
'===================================

I created a test DB with Form1 and Form2. The CAPTION of the popup form (Form2) is "Looking For This". Form1 has the timer set, with the following code:

Private Sub Form_Timer()

Dim hwnd As Long
Me.TimerInterval = 0 ' turn off timer
Beep
DoCmd.OpenForm "Form2"
DoEvents ' may not need ?

hwnd = FindWindow("MS-SDIb", "Looking For This&quot
' this example uses the classname "MS-SDIb" and the CAPTION of the form that pops up
' THIS IS PROBABLY THE BEST CHOICE BECAUSE IT WILL GIVE FOCUS TO THE WINDOW THAT POPS UP

'hwnd = FindWindow(vbNullString, "Looking For This&quot
' this example uses a null classname and the CAPTION of the form that pops up

'hwnd = FindWindow("OMain", "DBontop&quot
' this example uses the classname "OMain"
' and the APPLICATION TITLE of the Database itself (set with Tools...Startup...)
' NOTE: this is not the filename

' ============================================================
' The classnames "MS-SDIb" and "OMain" were determined using a VB program I wrote
' using the EnumWindows function to look for a specific window caption, etc.
' You can do a search in these forums to learn more about this function.
' These were the classnames on my XP machine with Access 2000 -
' they may be different on other versions ???
' ============================================================

If hwnd > 0 Then

If IsIconic(hwnd) Then ' this is if it's minimized ??
ShowWindow hwnd, SW_RESTORE
Else
ShowWindow hwnd, SW_SHOW
End If

SetActiveWindow hwnd

SetForegroundWindow hwnd

Forms!form2.Detail.BackColor = vbRed ' just a visual I added while testing

End If

End Sub
'=================================
I haven't tested this completely, but it should give you some ideas. You may have to experiment with the "Show" vs "Restore" flag with the ShowWindow function. I think it might depend if you're looking for the handle of the popup form or the Database itself.

Good luck!

Don

 

[ReTheOff]

Wow! Thanks for all that!

I'll test it out and let you know how it works for me.

 

[ReTheOff]

Wow that rocks!! Thanks a bunch!

With your help (Thank you!) I created a Popup reminder system in Access/sqlsvr that works great! My users were using Outlook, which is good, but it's not integrated with our sales database. So with this I was able to setup a reminder system in place of the Outlook calendar. Not a full featured calendar in anyway, but it's a great follow-up reminder, and now it's integrated!

huskerdon (Don), thanks so much for taking the time to help!

Greg


Here is what I did:

====================================
OnTimer of form calls this function:

Private Function fRunMe()

If Me.chkTimerOn = False Then
GoTo exitall
End If

Dim af As Form

If Not FormIsLoaded("activityplanner&quot Then
'MsgBox "Activity Planner is not loaded. Loading now."
x = FormLoad("activityplanner&quot
End If

'if ap is current form, then skip all, no need to remind when already there
Set af = Screen.ActiveForm
If af.Name = "activityplanner" Then
GoTo exitall
End If

'---------------------
Dim db As Database, rs As Recordset, rsList As Recordset
Set db = CurrentDb()


Dim sql, sqlList, it
sql = "Select count(*) as TheCount from ActivityUserView_today " & _
"where partyresponsible = '" & Forms.PrefHide.RepName & "' " & _
"and complete = 0 " & _
&quot;and (DateDue <= #&quot; & Now & &quot;# and Datedue >= #&quot; & td & &quot; 12:00:00 AM#)&quot;


Set rs = db.OpenRecordset(sql)
Dim t
t = 0
Do While Not rs.EOF
t = rs(&quot;TheCount&quot
rs.MoveNext
Loop
'setup default date minus time
mo = DatePart(&quot;m&quot;, Now)
da = DatePart(&quot;d&quot;, Now)
yr = DatePart(&quot;yyyy&quot;, Now)
td = CDate(mo & &quot;/&quot; & da & &quot;/&quot; & yr)

If t = 0 Then
GoTo exitall
Else
sqlList = &quot;Select Datedue, Empname, Repname, Message from ActivityUserView &quot; & _
&quot;where partyresponsible = '&quot; & Forms.PrefHide.RepName & &quot;' &quot; & _
&quot;and complete = 0 &quot; & _
&quot;and (DateDue <= #&quot; & Now & &quot;# and Datedue >= #&quot; & td & &quot; 12:00:00 AM#) &quot; & _
&quot;Order by Datedue desc&quot;

'open the records and loop through them, creates text of records in var 'it' for print in txtItems
Set rsList = db.OpenRecordset(sqlList)
it = &quot;&quot;
l = &quot;&quot;
Do While Not rsList.EOF
it = it & &quot;____________________________________________________________&quot; & vbCrLf & _
rsList(&quot;Datedue&quot & &quot; &quot; & rsList(&quot;RepName&quot & _
&quot; &quot; & Nz(rsList(&quot;Empname&quot, &quot;None&quot & vbCrLf & _
&quot; &quot; & Left(rsList(&quot;Message&quot, 70) & vbCrLf
rsList.MoveNext
Loop

DoCmd.OpenForm &quot;frmTimerMsg&quot;, acNormal
Forms.frmTimerMsg.txtMsg1 = &quot;You have &quot; & t & &quot; activities!&quot;
Forms.frmTimerMsg.txtItems = it

'Once the form is setup, see what handle it has from win32
'if its minimized, restore it, if not just show it
'then last is setactivewindow and setforeground on the Access app (OMain) THEN the Access Form (OFormPopupNC)
'important to setfocus within Access BEFORE making active from win32
Dim Hwnd As Long, w As Integer, hwac As Long
Hwnd = FindWindow(&quot;OFormPopupNC&quot;, &quot;Activity Reminder&quot
hwac = FindWindow(&quot;OMain&quot;, vbNullString)
If hwac > 0 Then
If IsIconic(hwac) Then ' this is if it's minimized ??
ShowWindow hwac, SW_RESTORE
ShowWindow Hwnd, SW_RESTORE

'ShowWindow hwnd, SW_SHOW
'ShowWindow hwnd, SH_SHOWNORMAL
Else
ShowWindow hwac, SW_SHOW
ShowWindow Hwnd, SW_SHOW
End If
SetActiveWindow hwac 'Make access active
'SetForegroundWindow hwac

Forms.frmTimerMsg.SetFocus 'setfocus on form in Access first

SetActiveWindow Hwnd
SetForegroundWindow Hwnd ' then make it foreground
End If
End If
exitall:

End Function

 

[huskerdon]

Greg,

Glad it works for you! It helped me learn a new trick also.

Don

 

[ReTheOff]

Don,

Here's the final result after I did some testing. And I cant answer why, but I couldnt make the popup work IF Access was NOT minimized. So when calling SW_SHOWMAXIMIZED, the app would popup perfectly. You will notice in the code below that I test for IsIconic on the &quot;hwac&quot; (the Access application), then if it is, maximize it and setforeground. Now, if it's not minimized, I first minimize it, then maximize it to cause it to set to the foreground app. I dont know why, I tried all the SW_xxx options as listed on MSDN, and I coudnt make Access set to foreground app, unless I ran a SW_SHOWMAXIMIZED.

So the end result is sloppy, it seems a bit clunky and buggy, but it totally catches the users attention with that minimize and popup, and it works. I'd love to know why Access wont respond to SW_SHOW properly. It would work fine to SetActiveWindow, but would never SetForegroundWindow.

Oh well... the users like it. Thanks for you help.

Greg


Portion of the func below:
====================================
Dim Hwnd As Long, w As Integer, hwac As Long
hwac = FindWindow(&quot;OMain&quot;, vbNullString) 'access app (need this now cuz much code before open of the form popup)

If hwac > 0 Then
If IsIconic(hwac) Then
ShowWindow hwac, SW_SHOWMAXIMIZED
SetActiveWindow hwac
SetForegroundWindow hwac
Else
ShowWindow hwac, SW_MINIMIZE 'first min then max the window
ShowWindow hwac, SW_SHOWMAXIMIZED
SetActiveWindow hwac
SetForegroundWindow hwac
End If
End If


DoCmd.OpenForm &quot;frmTimerMsg&quot;
Forms.frmTimerMsg.SetFocus 'setfocus on form first

Hwnd = FindWindow(vbNullString, &quot;Activity Reminder&quot 'access form (couldnt do this till for was open)
If Hwnd > 0 Then
If IsIconic(Hwnd) Then
ShowWindow Hwnd, SW_SHOWNORMAL
SetActiveWindow Hwnd
SetForegroundWindow Hwnd ' then make it foreground
Else
ShowWindow Hwnd, SW_SHOWNORMAL
SetActiveWindow Hwnd
SetForegroundWindow Hwnd ' then make it foreground
End If
End If

Forms.frmTimerMsg.txtMsg1 = &quot;You have &quot; & t & &quot; activities! p:&quot; & Hwnd & &quot;a:&quot; & hwac
Forms.frmTimerMsg.txtItems = it