422,764 Members | 1,289 Online
Bytes IT Community

Home Questions Articles Browse Topics Latest Top Members FAQ

home > topics > microsoft access / vba > questions > how not to save a form or report
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,764 IT Pros & Developers. It's quick & easy.

How NOT to save a form or report

PhilOfWalton
Expert 100+
P: 1,116
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
Expand|Select|Wrap|Line Numbers
  1. PP_OpenForm(2,"RptSeminarbesucher", acPreview)
  2.  
into the Debug Window

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

Expand|Select|Wrap|Line Numbers
  1. Function PP_OpenForm(FormOrReport As Integer, FormReportName As String, Optional StrView As String = acNormal, _
  2.     Optional StrFilterName As String, Optional StrWhereCondition As String, _
  3.     Optional varDataMode As Variant = acFormPropertySettings, _
  4.     Optional varWindowMode As Variant = acWindowNormal, Optional varOpenArgs As Variant)
  5.  
  6.     'PP_OpenForm(2,"RptSeminarbesucher", acPreview)
  7.  
  8.     Dim TypeOfObject As Object
  9.     Dim Mdl As Module
  10.     Dim StartLine As Long, EndLine As Long
  11.     Dim StartCol As Long, EndCol As Long
  12.     Dim Increment As Integer
  13.     Dim HasMdl As Boolean
  14.     Dim ModuleAdded As Boolean
  15.     Dim PP_LoadAdded As Boolean
  16.     Dim ProcLineCount As Long
  17.  
  18.     ' Opens a form and insert PP_Load me.Name into the On open Event
  19.  
  20.     If FormOrReport = 1 Then
  21.         DoCmd.OpenForm FormReportName, acDesign
  22.         Set TypeOfObject = Forms!FormReportName
  23.     Else
  24.         DoCmd.OpenReport FormReportName, acDesign
  25.         Set TypeOfObject = Reports(FormReportName)
  26.     End If
  27.  
  28.     HasMdl = TypeOfObject.HasModule             ' Save has module
  29.     If TypeOfObject.HasModule = 0 Then
  30.         TypeOfObject.HasModule = -1
  31.     End If
  32.  
  33.     Increment = 2
  34.  
  35.     Set Mdl = TypeOfObject.Module
  36.  
  37.     If FormOrReport = 1 Then                    ' Form
  38.         Mdl.Find "Form_Load", StartLine, StartCol, EndLine, EndCol
  39.         If StartLine = 0 Then                       ' Sub Form_Load not found
  40.             StartLine = Mdl.CreateEventProc("Load", "Form")
  41.             ModuleAdded = -1
  42.         End If
  43.     Else                                        ' Report
  44.         Mdl.Find "Report_Load", StartLine, StartCol, EndLine, EndCol
  45.         If StartLine = 0 Then
  46.             StartLine = Mdl.CreateEventProc("Load", "Report")
  47.         End If
  48.     End If
  49.  
  50.     Mdl.Find "PP_Load", StartLine, StartCol, EndLine, EndCol
  51.  
  52.     'StartLine = 0
  53.     If StartLine = 0 Then
  54.         Mdl.InsertLines StartLine + Increment, "    PP_Load " & Chr$(34) & TypeOfObject.name & Chr$(34)
  55.         PP_LoadAdded = -1
  56.     End If
  57.  
  58.     If FormOrReport = 1 Then
  59.         DoCmd.OpenForm FormReportName, StrView, StrFilterName, StrWhereCondition, varDataMode, varWindowMode, varOpenArgs
  60.     Else
  61.         DoCmd.OpenReport FormReportName, StrView, StrFilterName, StrWhereCondition, varWindowMode, varOpenArgs
  62.     End If
  63.  
  64. CleanUp:
  65.     DoEvents
  66.  
  67.     Exit Function
  68.  
  69. End Function
  70.  
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
Oct 4 '17 #1
Share this Question
Share on Google+
10 Replies
NeoPa
Expert Mod 15k+
P: 30,743
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 :
Expand|Select|Wrap|Line Numbers
  1. Call DoCmd.Close(ObjectType:={acForm or acReport} _
  2.                , ObjectName:=Me.Name _
  3.                , Save:=acSaveNo)
This (How to Close a Main Form Without Saving Changes of any Subforms) may also help.
Oct 5 '17 #2
PhilOfWalton
Expert 100+
P: 1,116
Hi Neopa

Thanks for reply, but still no dice.

Here is the full code.
Expand|Select|Wrap|Line Numbers
  1. Function PP_OpenForm(FormOrReport As Integer, FormReportName As String, Optional StrView As String = acNormal, _
  2.     Optional StrFilterName As String, Optional StrWhereCondition As String, _
  3.     Optional varDataMode As Variant = acFormPropertySettings, _
  4.     Optional varWindowMode As Variant = acWindowNormal, Optional varOpenArgs As Variant)
  5.  
  6.     '?PP_OpenForm(2,"RptSeminarbesucher",acPreview)
  7.  
  8.     Dim TypeOfObject As Object
  9.     Dim Mdl As Module
  10.     Dim StartLine As Long, EndLine As Long
  11.     Dim StartCol As Long, EndCol As Long
  12.     Dim Increment As Integer
  13.     Dim HasMdl As Boolean
  14.     Dim ProcLineCount As Long
  15.     Dim InsertStr As String
  16.     Dim LoadStartLine As Long
  17.     Dim UnloadStartLine As Long
  18.  
  19.     ' Opens a form and insert PP_Load me.Name into the On open Event
  20.  
  21.     If FormOrReport = 1 Then
  22.         DoCmd.OpenForm FormReportName, acDesign
  23.         Set TypeOfObject = Forms!FormReportName
  24.     Else
  25.         DoCmd.OpenReport FormReportName, acDesign
  26.         Set TypeOfObject = Reports(FormReportName)
  27.     End If
  28.  
  29.     HasMdl = TypeOfObject.HasModule             ' Save has module
  30.     If TypeOfObject.HasModule = 0 Then
  31.         TypeOfObject.HasModule = -1
  32.     End If
  33.  
  34.     Increment = 2
  35.  
  36.     Set Mdl = TypeOfObject.Module
  37.  
  38.     If FormOrReport = 1 Then                    ' Form
  39.         Mdl.Find "Form_Load", StartLine, StartCol, EndLine, EndCol
  40.         If StartLine = 0 Then                       ' Sub Form_Load not found
  41.             StartLine = Mdl.CreateEventProc("Load", "Form")
  42.         End If
  43.     Else                                        ' Report
  44.         Mdl.Find "Report_Load", StartLine, StartCol, EndLine, EndCol
  45.         If StartLine = 0 Then
  46.             StartLine = Mdl.CreateEventProc("Load", "Report")
  47.         End If
  48.     End If
  49.  
  50.     LoadStartLine = StartLine
  51.  
  52.     StartLine = 0
  53.     Mdl.Find "PP_Load", StartLine, StartCol, EndLine, EndCol, True, True
  54.  
  55.     If StartLine = 0 Then
  56.         InsertStr = "    PP_Load  " & Chr$(34) & TypeOfObject.name & Chr$(34)
  57.         Mdl.InsertLines LoadStartLine + Increment, InsertStr
  58.     End If
  59.  
  60.     ' Unload procedure to stop it asking to save
  61.     StartLine = 0
  62.  
  63.     If FormOrReport = 1 Then                    ' Form
  64.         Mdl.Find "Form_Unload", StartLine, StartCol, EndLine, EndCol
  65.         If StartLine = 0 Then                       ' Sub Form_Load not found
  66.             StartLine = Mdl.CreateEventProc("Unload", "Form")
  67.         End If
  68.     Else
  69.         Mdl.Find "Report_Unload", StartLine, StartCol, EndLine, EndCol
  70.         If StartLine = 0 Then
  71.             StartLine = Mdl.CreateEventProc("Unload", "Report")
  72.         End If
  73.     End If
  74.  
  75.     UnloadStartLine = StartLine
  76.  
  77.     StartLine = 0
  78.  
  79.     Mdl.Find "PP_Unload", StartLine, StartCol, EndLine, EndCol
  80.  
  81.     If StartLine = 0 Then
  82.         InsertStr = "    PP_Unload  " & FormOrReport & ", " & Chr$(34) & TypeOfObject.name & Chr$(34)
  83.         Mdl.InsertLines UnloadStartLine + Increment, InsertStr
  84.     End If
  85.  
  86.     Debug.Print Mdl.Lines(1, 15)
  87.  
  88.     If FormOrReport = 1 Then
  89.         DoCmd.OpenForm FormReportName, StrView, StrFilterName, StrWhereCondition, varDataMode, varWindowMode, varOpenArgs
  90.     Else
  91.         DoCmd.OpenReport FormReportName, StrView, StrFilterName, StrWhereCondition, varWindowMode, varOpenArgs
  92.     End If
  93.  
  94. End Function
  95.  
  96. Function PP_Unload(ObjType As Integer, ObjName As String) As Boolean
  97.  
  98.     On Error GoTo PP_Onload_Err
  99.  
  100.     If ObjType = 1 Then                     ' Form
  101.         DoCmd.Close acForm, ObjName, acSaveNo
  102.     Else
  103.         DoCmd.Close acReport, ObjName, acSaveNo
  104.     End If
  105.  
  106. PP_Onload_Exit:
  107.     Exit Function
  108.  
  109. PP_Onload_Err:
  110.     If Err = 2585 Then
  111.         Resume PP_Onload_Exit
  112.     Else
  113.         LogError Err, Err.Description, "PP_Unload"
  114.     End If
  115.  
  116. End Function
  117.  
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
Oct 5 '17 #3
NeoPa
Expert Mod 15k+
P: 30,743
No further thoughts Phil, but you may like to reread what I said in my earlier post. LMK if further explanation is required.
Oct 6 '17 #4
PhilOfWalton
Expert 100+
P: 1,116
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
Oct 6 '17 #5
NeoPa
Expert Mod 15k+
P: 30,743
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.
Oct 6 '17 #6
PhilOfWalton
Expert 100+
P: 1,116
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
Oct 8 '17 #7
PhilOfWalton
Expert 100+
P: 1,116
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
Oct 9 '17 #8
NeoPa
Expert Mod 15k+
P: 30,743
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.
Oct 11 '17 #9
PhilOfWalton
Expert 100+
P: 1,116
Thanks Adrian, I really appreciate your offer. In the mean time, I have more than enough to keep me out of mischief

Cheers

Phil
Oct 11 '17 #10
NeoPa
Expert Mod 15k+
P: 30,743
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private blnAllowClose As Boolean
  5.  
  6. Private Sub cmdClose_Click()
  7.     blnAllowClose = True
  8.     Call DoCmd.Close(ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSaveNo)
  9. End Sub
  10.  
  11. Private Sub Form_Unload(Cancel As Integer)
  12.     Dim strMsg As String
  13.  
  14.     If Not blnAllowClose Then
  15.         Cancel = True
  16.         strMsg = "You may only close this form by clicking the Close button"
  17.         Call MsgBox(Prompt:=strMsg _
  18.                   , Buttons:=vbInformation Or vbOKOnly _
  19.                   , Title:=Me.Name)
  20.     End If
  21. End Sub
Please confirm this is what you were after.
Attached Files
File Type: zip PhilofWalton.ZIP (20.3 KB, 17 views)
Oct 15 '17 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Question stats

  • viewed: 2081
  • replies: 10
  • date asked: Oct 4 '17
BYTES.COM © 2018
Formerly "TheScripts.com" from 2005-2008
About Bytes | Advertise on Bytes | Contact Us
Sitemap | Microsoft Access / VBA Answers Sitemap | Microsoft Access / VBA Insights Sitemap

Follow us to get the Latest Bytes Updates