Open Form with instance method & wait to close? (1 Viewer)

[GeorgeBakoyannis]

Hello,
I am creating forms instances in code, using.

Set frm = New Form_frmBase (http://allenbrowne.com/ser-35.html)
frm.Visible = True
Now frm is visible but code continues run.

The DoCmd.OpenForm xxx,,,,,acDialog pause execution of code but dot works this form instances.

When I use below "while wend" loop seems to use up a lot of processor time - the task manager shows access as using 99% of processor time.
Set frm = New Form_frmDialog
frm.Visible = True
While frm.Visible
DoEvents
Wend
Who to make a solution to combine "acDialog" & instances without using 99% of processor time;

Any idea for this;

Many thanks
George Bakoyannis

from Greece


P.S.
I work with MS ACCESS 2000

 

[vbaInet]

I've read your post twice but finding it difficult to make heads or tails from it. Could you please further explain?

 

[Banana]

I am not aware of any way to open an instance in a dialog mode. The closest you can accomplish is a modal + pop up which will give you all the look and appearance of a dialog form but this, IINM, does not pause code execution either. One option, if you must insist on using form instance, is to not depend on code execution pausing but have your instance call a block of code that should execute after "closing" the form.

If I may ask, though, why do you want to use instance methods? I'm not a big fan of DoCmd, but this is one of few cases where using DoCmd tends to work smoothly.

 

[GeorgeBakoyannis]

I've read your post twice but finding it difficult to make heads or tails from it. Could you please further explain?

[FONT=&quot]I am newbie for this forum and I make error.

[/FONT]

 

[vbaInet]

[FONT=&quot]I am newbie for this forum and I make error.

[/FONT]

Welcome to the forum. I think Banana understood your request and has given you the answer.

Good luck!

 

[GeorgeBakoyannis]

I need instances because I open same forms many times from my app. When from close I can raise an event and execute the specific block of code. DoCmd not works with instances. And my specific question is same. How to open form with instance method & wait to close without using 99% of processor time? Or How DoCmd not works, exist an API to replace DoCmd with instances?

 

[vbaInet]

Try and reread Banana's post. There's nothing you can do except CALLING the remaining code AFTER you close the form.

The thing is once you do this, Set frm = New Form_frmBase, an instance is created and the form is "opened" in its Default view. I don't know of an API but I don't think there's one.

 

[April15Hater]

Hey guys,

I have an situation where i need to instance a form in lieu of docmd.

I'm trying to make a general purpose date form that loads on report_open when there is no date already in the report's openargs property (mainly for when it is called on it's own from the Nav Bar and not from another form's docmd call). I'd like to pass the report's name into a class property of the form so that I can use it amongst multiple reports. With a docmd.openform, I could use the openargs property, but that prohibits me from opening multiple instances of this form. Here's the kicker: I need to cancel the report in the report's open event code, so I can reopen it with an openargs property passed from the date input form. The instanced form then loses scope and terminates because it was called from the report. Any ideas? My code is below. I'm debugging it now so forgive me for it being a little sloppy and having other potential issues.

Thanks,

Joe

Report Code

'******************************************************************************
'*
'* rptDailySnapShot Class Module
'*
'******************************************************************************
Private Sub Report_Open(Cancel As Integer)
    If Nz(Me.OpenArgs, "") = "" Then
        Dim frmInputReportDate As Form_frmInputReportDate
        Set frmInputReportDate = New Form_frmInputReportDate
        frmInputReportDate.ReportName = Me.Name
        Cancel = True
    End If
End Sub

Form Code

'******************************************************************************
'* Name              :   frmInputReportDate Class Module                      *
'* Purpose           :   Input Form to assign a date to a report              *
'* Rules             :   1.  Form can be opened independently or called by a  *
'*                              report                                        *
'*                       2.  Report must have a textbox control named         *
'*                              txtReportName                                 *
'*                                                                            *
'* UsageExample                                                               *
'* ~~~~~~~~~~~~~~~~~                                                          *
'* Private Sub Report_Open(Cancel As Integer)                                 *
'*    If Nz(Me.OpenArgs, "") = "" Then                                        *
'*         Dim frmInputReportDate As Form_frmInputReportDate                  *
'*         Set frmInputReportDate = New Form_frmInputReportDate               *
'*         frmInputReportDate.ReportName = Me.Name                            *
'*         Cancel = True                                                      *
'*     End If                                                                 *
'* End Sub                                                                    *
'*                                                                            *
'* Revision History  :                                                        *
'* Rev#      Date(yyyy/mm/dd)        Description                              *
'* ~~~~      ~~~~~~~~~~~~~~~~        ~~~~~~~~~~~                              *
'* 1         2012/11/16              Initial Release                          *
'******************************************************************************
Option Compare Database
Option Explicit
Private pReportName As String

Public Property Get ReportName() As String
    ReportName = pReportName
End Property
Public Property Let ReportName(Value As String)
    pReportName = Value
End Property

Private Sub Form_Load()
    Me.txtDate = Date
    Me.Visible = True
End Sub
Private Sub Form_Open(Cancel As Integer)
    'Me.OpenArgs = Me.ReportName
End Sub
Private Sub cmdOK_Click()
    SetReportDate
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub SetReportDate()
    If Not IsReportOpen(Me.ReportName) Then
        DoCmd.OpenReport _
            ReportName:=Me.ReportName, _
            View:=acViewPreview, _
            WindowMode:=acDialog, _
            OpenArgs:=txtDate.Value
    Else
        Reports(Me.ReportName).txtReportDate = Me.txtDate
    End If
End Sub

Public Function IsReportOpen(strReportName As String) As Boolean
'******************************************************************************
'* Purpose          :   Check whether a report is open or not                 *
'*                                                                            *
'* Input Variables                                                            *
'* ~~~~~~~~~~~~~~~~                                                           *
'* strReportName    :   Name of the report being checked if open or not       *
'*                                                                            *
'* UsageExample                                                               *
'* ~~~~~~~~~~~~~~~~                                                           *
'* IsReportOpen("MyReportName")                                               *
'*                                                                            *
'* Revision History                                                           *
'* ~~~~~~~~~~~~~~~~                                                           *
'* Rev#      Date(yyyy/mm/dd)        Description                              *
'* ~~~~      ~~~~~~~~~~~~~~~~        ~~~~~~~~~~~                              *
'* 1         2012/11/16              Initial Release                          *
'******************************************************************************
On Error GoTo Err1
    If Application.CurrentProject.AllReports(strReportName).IsLoaded Then
        IsReportOpen = True
    Else
        IsReportOpen = False
    End If
 
Err_Exit:
    On Error Resume Next
    Exit Function
 
Err1:
    MsgBox _
        Prompt:="Error Number: " & Err.Number & vbCrLf & _
        "Error Description: " & Err.Description, _
        Buttons:=vbCritical, _
        Title:="Error"
    Resume Err_Exit
End Function

 

[nanscombe]

Why not use a Global variable to hold the report date?

' Module level code
Global gVarReportDate as Variant

When you open the report load the global variable with the contents of OpenArgs. If it is empty then open the date form as Modal so the code in the report is paused until the date form is closed again.

' Report level code

Private Sub Report_Open(Cancel As Integer)

    ' Set global variable from OpenArgs
    gVarReportDate = Nz(Me.OpenArgs, "")

    If gVarReportDate = "" Then
      ' If global variable is empty open date form as modal so it stop report code
      Docmd.OpenForm "Form_frmInputReportDate",,,,acDialog
    End If

End Sub

When the date form is opened load the value entered into the date box in to the global variable. When the form is closed the report will continue where it left off.

' Form_frmInputReportDate code

Private Sub txtReportDate_AfterUpdate()

  ' After an entry for date has been made store it in the global variable
  gVarReportDate =nz(txtReportDate, "")

End Sub

Private Sub cmdExit_Click()

  ' Close form so calling code can resume
  DoCmd.Close

End Sub

So the way it works.

1) Load global variable with the value in OpenArgs.
2) If global variable is not empty jump to step 8
3) Open form Form_frmInputReportDate as Modal to pause report code
4) Enter date in form
5) Update global variable with the date
6) Close form
7) Resume report code
8) Produce report using the date stored in the global variable.

 

[nanscombe]

This should illustrate what I'm talking about...

 

[April15Hater]

But wouldn't there be ambiguity in the global variable's value once multiple instances of the form were opened?

 

[ChrisO]

That is true, so if you want to have multiple instances of the input Form opened concurrently you would need to keep the instances of the Form on a stack such as in a Public Collection. The Key into the Public Collection would need to be the Hwnd of the instance of the Form not the name of the Form instance.

So it becomes a push and pull onto and off that public stack which is somewhat esoteric but quite simple.

The main problem with the multiple instances is you stated a need to open the Report in WindowMode:=acDialog. That requirement can mean that the code will suspend when the Report is opened and thereby prevent multiple instances of the Date input Form from being opened.

Without addressing the WindowMode:=acDialog problem, there is a simplified attached A2003 demo which holds instances of the Date Entry Form on a stack in a Public Collection.

Chris.

 

[nanscombe]

But wouldn't there be ambiguity in the global variable's value once multiple instances of the form were opened?

With the example that I wrote yes but that was just a quick method to show how it is possible to pause the execution of the report code to go and get a variable from elsewhere.

' Report level code
Dim myDate as variant

Private Sub Report_Open(Cancel As Integer)

    ' Set local variable from OpenArgs
    myDate = Nz(Me.OpenArgs, "")

    If myDate = "" Then

      ' If myDate is empty open date form as modal so it stop report code
      Docmd.OpenForm "Form_frmInputReportDate",,,,acDialog

      ' Transfer date to report variable
      myDate = gVarReportDate

    End If


End Sub

Would you need multiple instances of the form open? The form that I am suggesting opens. You enter the date. You close the form. It is acting like a glorified InputBox.

I am using the global variable as a method of transferring the data between the report and the form. With a small tweak the lifespan of the global variable is only the time that the form is open (as a modal popup) and the next line of code in the report.

 

[nanscombe]

If all you needed to do was pass in a single date, if one wasn't supplied in OpenArgs, you could use an InputBox.

' Report level code
Dim myDate as variant

Private Sub Report_Open(Cancel As Integer)

    ' Set local variable from OpenArgs
    myDate = Nz(Me.OpenArgs, "")

    ' If myDate is empty then prompt for a date
    If myDate = "" Then myDate = CDate(InputBox ("Please enter a date"))

    ' If myDate is still empty then cancel the opening of the report
    If myDate = "" Then Cancel = TRUE
 
End Sub

No need for other forms or global variables at all.

 

[ChrisO]

Attached is the general flow of things if the Reports can be set to Pop Up and opened with WindowMode:=acWindowNormal.

I’m shutting down due to an electrical storm.

Edit
Removed references from attachment.

Chris.

 

[nanscombe]

It's been a while since I worked with reports.


A simplified version using an InputBox to prompt for a missing date.

No classes, instances, module code or global variables. All reports totally self contained.

 

[ChrisO]

And another with instantiated pop up calendars derived from Allen Browne's version.
http://allenbrowne.com/ser-51.html

It’s a modified version of the calendar found here:-
http://www.access-programmers.co.uk/forums/showthread.php?t=219428


Chris.

 

[GeorgeBakoyannis]

'FINALLY Eureka !
'------------------------------------------------------------------------------------------------------------------------
Private Const GW_HWNDNEXT = 2
Private Const SYNCHRONIZE = &H100000
Private Const WAIT_TIMEOUT = &H102
'
Private Type PROCESS_INFORMATION
hProcess As Long
hThread As Long
dwProcessId As Long
dwThreadID As Long
End Type
'
Private Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function GetParent Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetWindowThreadProcessId Lib "user32" (ByVal hwnd As Long, lpdwprocessid As Long) As Long
Private Declare Function GetWindow Lib "user32" (ByVal hwnd As Long, ByVal wCmd As Long) As Long
Private Declare Function IsWindow Lib "user32.dll" (ByVal hwnd As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, ByVal dwProcessId As Long) As Long
Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal hHandle As Long, ByVal dwMilliseconds As Long) As Long
'
Public Sub WaitFormToClose(frm As Form)
Dim h As Long, p As Long, pi As PROCESS_INFORMATION
h = frm.hwnd: p = OpenProcess(SYNCHRONIZE, True, ProcIDofHwnd(h))
Do While IsWindow(h)
If WaitForSingleObject(p, 10) <> WAIT_TIMEOUT Then Exit Do Else DoEvents
Loop
CloseHandle p
End Sub
'
Private Function ProcIDofHwnd(ByVal hwnd As Long) As Long: Dim idProc As Long: GetWindowThreadProcessId hwnd, idProc: ProcIDofHwnd = idProc: End Function
Private Function HwndofProcID(hwnd As Long) As Long
Dim h As Long: h = FindWindow(vbNullString, vbNullString)
Do Until h = 0
If GetParent(h) = 0 Then If hwnd = ProcIDofHwnd(h) Then HwndofProcID = h: Exit Do Else h = GetWindow(h, GW_HWNDNEXT)
Loop
End Function
'------------------------------------------------------------------------------------------------------------------------
Public Sub TestOfUsage()
Dim f As Form_Form1
Set f = New Form_FormTest
f.SetFocus
WaitFormToClose f
MsgBox "Form closed"
End Sub
'------------------------------------------------------------------------------------------------------------------------
George Bakoyannis
software engineer

 

[April15Hater]

Sorry it took so long for me to get back to this.

Nigel, That is a pretty easy way to get it done. At first, I was concerned with the situation where a date range would need to be used, however, an easy solution to that would just to use two inputboxes :banghead: and combine the dates with a separator and parse the data when you run the report (I'm guessing openargs would have to use the same parsing method).

George, that is a pretty elaborate solution! I've had to prioritize another project, but when I get back on this, I'm going to give it a try. I've seen the "Private Declare Function XXXXX" in several places, and I'm not quite sure of what exactly it is. The student in me makes me wonder what exactly this is...?

Chris, Good ol' Allen Browne to the rescue. I can't begin to explain how much I've learned from him and Chip Pearson.

Thanks,

Joe

 

[nanscombe]

You could actually manage it with a single InputBox and tell the users to enter both dates separated by a "-" (dd/mm/yyyy - dd/mm/yyyy), ie 01/01/2012 - 01/03/2012 and parse that.