How NOT to save a form or report

[PhilOfWalton]

I use the following routine to open a form or report, check if there is an OnLoad event, if not I add it.

Experimentall I type

PP_OpenForm(2,"RptSeminarbesucher", acPreview)
 

into the Debug Window

I then add a line of code to translate the form into a foreign language. All works perfectly ... nearly.

Function PP_OpenForm(FormOrReport As Integer, FormReportName As String, Optional StrView As String = acNormal, _
    Optional StrFilterName As String, Optional StrWhereCondition As String, _
    Optional varDataMode As Variant = acFormPropertySettings, _
    Optional varWindowMode As Variant = acWindowNormal, Optional varOpenArgs As Variant)
 
    'PP_OpenForm(2,"RptSeminarbesucher", acPreview)
 
    Dim TypeOfObject As Object
    Dim Mdl As Module
    Dim StartLine As Long, EndLine As Long
    Dim StartCol As Long, EndCol As Long
    Dim Increment As Integer
    Dim HasMdl As Boolean
    Dim ModuleAdded As Boolean
    Dim PP_LoadAdded As Boolean
    Dim ProcLineCount As Long
 
    ' Opens a form and insert PP_Load me.Name into the On open Event
 
    If FormOrReport = 1 Then
        DoCmd.OpenForm FormReportName, acDesign
        Set TypeOfObject = Forms!FormReportName
    Else
        DoCmd.OpenReport FormReportName, acDesign
        Set TypeOfObject = Reports(FormReportName)
    End If
 
    HasMdl = TypeOfObject.HasModule             ' Save has module
    If TypeOfObject.HasModule = 0 Then
        TypeOfObject.HasModule = -1
    End If
 
    Increment = 2
 
    Set Mdl = TypeOfObject.Module
 
    If FormOrReport = 1 Then                    ' Form
        Mdl.Find "Form_Load", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then                       ' Sub Form_Load not found
            StartLine = Mdl.CreateEventProc("Load", "Form")
            ModuleAdded = -1
        End If
    Else                                        ' Report
        Mdl.Find "Report_Load", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then
            StartLine = Mdl.CreateEventProc("Load", "Report")
        End If
    End If
 
    Mdl.Find "PP_Load", StartLine, StartCol, EndLine, EndCol
 
    'StartLine = 0
    If StartLine = 0 Then
        Mdl.InsertLines StartLine + Increment, "    PP_Load " & Chr$(34) & TypeOfObject.name & Chr$(34)
        PP_LoadAdded = -1
    End If
 
    If FormOrReport = 1 Then
        DoCmd.OpenForm FormReportName, StrView, StrFilterName, StrWhereCondition, varDataMode, varWindowMode, varOpenArgs
    Else
        DoCmd.OpenReport FormReportName, StrView, StrFilterName, StrWhereCondition, varWindowMode, varOpenArgs
    End If
 
CleanUp:
    DoEvents
 
    Exit Function
 
End Function
 

The problem is when the form or report is closed I am prompted whether to save it. The answer is always "NO"

Now I do not want to modify any forms or reports so is there a way or preventing the question and just closing the form or report unchanged?

Phil

[NeoPa]

Hi Phil.

In the Form_Unload() event procedure have it set the Cancel parameter based on whether or not it's been closed by your special closing code. In your special closing code (probably behind a Close button) ensure you close the Form or Report with code similar to :

Call DoCmd.Close(ObjectType:={acForm or acReport} _
               , ObjectName:=Me.Name _
               , Save:=acSaveNo)

This (How to Close a Main Form Without Saving Changes of any Subforms) may also help.

[PhilOfWalton]

Hi Neopa

Thanks for reply, but still no dice.

Here is the full code.

Function PP_OpenForm(FormOrReport As Integer, FormReportName As String, Optional StrView As String = acNormal, _
    Optional StrFilterName As String, Optional StrWhereCondition As String, _
    Optional varDataMode As Variant = acFormPropertySettings, _
    Optional varWindowMode As Variant = acWindowNormal, Optional varOpenArgs As Variant)
 
    '?PP_OpenForm(2,"RptSeminarbesucher",acPreview)
 
    Dim TypeOfObject As Object
    Dim Mdl As Module
    Dim StartLine As Long, EndLine As Long
    Dim StartCol As Long, EndCol As Long
    Dim Increment As Integer
    Dim HasMdl As Boolean
    Dim ProcLineCount As Long
    Dim InsertStr As String
    Dim LoadStartLine As Long
    Dim UnloadStartLine As Long
 
    ' Opens a form and insert PP_Load me.Name into the On open Event
 
    If FormOrReport = 1 Then
        DoCmd.OpenForm FormReportName, acDesign
        Set TypeOfObject = Forms!FormReportName
    Else
        DoCmd.OpenReport FormReportName, acDesign
        Set TypeOfObject = Reports(FormReportName)
    End If
 
    HasMdl = TypeOfObject.HasModule             ' Save has module
    If TypeOfObject.HasModule = 0 Then
        TypeOfObject.HasModule = -1
    End If
 
    Increment = 2
 
    Set Mdl = TypeOfObject.Module
 
    If FormOrReport = 1 Then                    ' Form
        Mdl.Find "Form_Load", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then                       ' Sub Form_Load not found
            StartLine = Mdl.CreateEventProc("Load", "Form")
        End If
    Else                                        ' Report
        Mdl.Find "Report_Load", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then
            StartLine = Mdl.CreateEventProc("Load", "Report")
        End If
    End If
 
    LoadStartLine = StartLine
 
    StartLine = 0
    Mdl.Find "PP_Load", StartLine, StartCol, EndLine, EndCol, True, True
 
    If StartLine = 0 Then
        InsertStr = "    PP_Load  " & Chr$(34) & TypeOfObject.name & Chr$(34)
        Mdl.InsertLines LoadStartLine + Increment, InsertStr
    End If
 
    ' Unload procedure to stop it asking to save
    StartLine = 0
 
    If FormOrReport = 1 Then                    ' Form
        Mdl.Find "Form_Unload", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then                       ' Sub Form_Load not found
            StartLine = Mdl.CreateEventProc("Unload", "Form")
        End If
    Else
        Mdl.Find "Report_Unload", StartLine, StartCol, EndLine, EndCol
        If StartLine = 0 Then
            StartLine = Mdl.CreateEventProc("Unload", "Report")
        End If
    End If
 
    UnloadStartLine = StartLine
 
    StartLine = 0
 
    Mdl.Find "PP_Unload", StartLine, StartCol, EndLine, EndCol
 
    If StartLine = 0 Then
        InsertStr = "    PP_Unload  " & FormOrReport & ", " & Chr$(34) & TypeOfObject.name & Chr$(34)
        Mdl.InsertLines UnloadStartLine + Increment, InsertStr
    End If
 
    Debug.Print Mdl.Lines(1, 15)
 
    If FormOrReport = 1 Then
        DoCmd.OpenForm FormReportName, StrView, StrFilterName, StrWhereCondition, varDataMode, varWindowMode, varOpenArgs
    Else
        DoCmd.OpenReport FormReportName, StrView, StrFilterName, StrWhereCondition, varWindowMode, varOpenArgs
    End If
 
End Function
 
Function PP_Unload(ObjType As Integer, ObjName As String) As Boolean
 
    On Error GoTo PP_Onload_Err
 
    If ObjType = 1 Then                     ' Form
        DoCmd.Close acForm, ObjName, acSaveNo
    Else
        DoCmd.Close acReport, ObjName, acSaveNo
    End If
 
PP_Onload_Exit:
    Exit Function
 
PP_Onload_Err:
    If Err = 2585 Then
        Resume PP_Onload_Exit
    Else
        LogError Err, Err.Description, "PP_Unload"
    End If
 
End Function
 

After running the code, the Debug statement shows the correct code for the "Modified report". The Report opens correctly in the desired language, but still asks me to save it when I close the report.

Any further thoughts

Phil

[NeoPa]

No further thoughts Phil, but you may like to reread what I said in my earlier post. LMK if further explanation is required.

[PhilOfWalton]

I have a vague idea that it may be possible to create a "virtual" copy of the form or report and append it to the database AllForms or AllReports collections.

Then make the necessary changes to this virtual report, display it, and on closing just remove remove it from the Allforms or AllReprts collection.

Is this likely to work?
Is there likely to be database bloat issues?

Phil

[NeoPa]

Phil:
Is this likely to work?

No. I may be mistaken but I don't see any reason to assume that would have the effect you're after.

Phil:
Is there likely to be database bloat issues?

No. If you were to try it I don't see any reason that it would cause any more bloat than opening Forms/Reports in the usual way.

I'm afraid I have a very busy weekend ahead Phil. When I can though, I'll try to fit in posting more details about how I use the technique i outlined in my earlier post. I'm surprised that Access doesn't treat changes made while in display view as temporary automatically myself. That would leave you with nothing to do. However, they don't, so I've developed a fairly straightforward technique to handle exactly that which I use in many of my Forms. Some Forms and most Reports don't need it but for those that do I use the technique explained earlier. It's always worked perfectly for me.

[PhilOfWalton]

Thanks to all for advice

The problem is solved don't ask how, but involves class modules & virtual forms. Anyway it works for forms so I think I can use the same technique for reports.

Here goes ...

Phil

[PhilOfWalton]

Spoke too soon. The routine works perfectly for forms, but I can't get it to work for Reports.

To Clarify, this is a program to translate any database into a different language. Obviously it has limitations but it translates Labels, Captions, Command Buttons, Check Boxes, Option Buttons, Control Tip Text, Status Bar, Combo & List boxes where there is a value list, some Combo & List boxes where there is a query on both forms or reports.

An output language is selected and what should happen is that you either have a command button open the form or report (or even use the debug window).

The form if the command is for Form, use the code
PLP_OpenFormReport 1, "FormName"

and for Reports
PLP_OpenFormReport 2, "ReportName"

It works perfectly for forms, but have been struggling for a week using pretty well identical code to get it to work for Reports, but with no joy.

If anyone has any time to spare, I would be grateful for assistance, but it may or may not be a 5 minute job

Thanks for any volunteers

Phil

[NeoPa]

I'd certainly love to help you through this Phil. Unfortunately, for now, I'm up to my eyeballs on various different levels so working hard simply to avoid drowning.

Maybe at the weekend I may get some time to talk you through it. In the meantime, when I get a little less time as it will take less, I'd like to flesh out my earlier suggestion so that it's understandable to a larger audience. My earlier comments were certainly kept brief but it seems clear now there were probably a little too cryptic.

I haven't forgotten you or this particular issue.

-Ade.

[PhilOfWalton]

Thanks Adrian, I really appreciate your offer. In the mean time, I have more than enough to keep me out of mischief

Cheers

Phil

[NeoPa]

Hi Phil.

I've attached a very quick and very basic database that illustrates what I was trying to say earlier. One Form, one Command Button on that Form. Simple as you like.

I include the code below :

Option Compare Database
Option Explicit
 
Private blnAllowClose As Boolean
 
Private Sub cmdClose_Click()
    blnAllowClose = True
    Call DoCmd.Close(ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSaveNo)
End Sub
 
Private Sub Form_Unload(Cancel As Integer)
    Dim strMsg As String
 
    If Not blnAllowClose Then
        Cancel = True
        strMsg = "You may only close this form by clicking the Close button"
        Call MsgBox(Prompt:=strMsg _
                  , Buttons:=vbInformation Or vbOKOnly _
                  , Title:=Me.Name)
    End If
End Sub

Please confirm this is what you were after.