Solved

onclose event

Posted on 2004-09-19
29
244 Views
Last Modified: 2008-02-26
I want to bring up a yes no confirmation dialog on close event.  Then on choice to Save I don't want to close the form but run a save command button on a subform.  The subform has a save button  with code.

Thanks
0
Comment
Question by:TimKestermont
  • 15
  • 14
29 Comments
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 125 total points
ID: 12098805
To execute a function on the subform, change the button's event procedure from Private Sub to Public Sub. You can then execute the button's OnClick code like this:

Forms!MyMainForm!MySubformControl.Form.MyButton_OnClick
0
 

Author Comment

by:TimKestermont
ID: 12099075
I understand the idea, I'm abit unsure of the SubformControl.Form portion.   You've answered several of my questions prior to this but I am not for sure what I am actually putting there.  

Thanks.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099102
Basically, replace MyMainForm and MySubformControl with the names of your main form and the subform control that actually holds your subform. MyButton is the name of the button on your subform whose code you want to run. Let me know if you need any clarification.
0
 

Author Comment

by:TimKestermont
ID: 12099141
I apologize but are you referring to a field when you say control?

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099157
No - let me explain a bit further. Subforms are held inside "subform controls". This acts as a container for the form that is acting as a subform. If you design your main form, then click once on the subform, click View->Properties, you'll see the subform control's name in the Name property on the Other page. This is often (but not always) the same as the name of the form it contains, if you created it using the wizard. Otherwise, it might have a name like "Child11". The form that the subform control "holds" is determined by the Source Object property.
0
 

Author Comment

by:TimKestermont
ID: 12099180
Forms!frmtelephone!subfrmtele.Form.command13_OnClick     What is wrong with this code?
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099193
That looks right - what error did it give?

Did you change the Private to Public?
0
 

Author Comment

by:TimKestermont
ID: 12099211
inproper reference to the property form/report.   I am using Access 2k and my library references  (not that it matters but I've come across this issue several times in my db and had to do some crazy stuff)
VB for apps
ms access 9.0 object library
ms access DAO 3.6 object library
ole automation
ms forms 2.0 object library
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099250
Sorry, try this, my error:

Forms!frmtelephone!subfrmtele.Form.command13_Click

(i.e. remove the On)
0
 

Author Comment

by:TimKestermont
ID: 12099269
Same error...
Private Sub Form_Close()

Select Case MsgBox("Do you want to save?", vbYesNo, "Save Changes")
Case vbYes
  Forms!frmtelephone!subfrmtele.Form.Command13_Click
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099283
Can you post the command button's code from the subform (including the Sub and End Sub lines)?
0
 

Author Comment

by:TimKestermont
ID: 12099297
Public Sub Command13_Click()

Dim TRN_Eff As String
 
DoCmd.SetWarnings False

If IsNull(Me.TELE_Date) = True Then
TRN_Eff = False
Else: TRN_Eff = True
End If

If TRN_Eff = False Then
Select Case MsgBox("Do you want to save?", vbYesNo, "Save Changes")
Case vbYes
MsgBox "Please Enter A Date", vbExclamation
Me.TELE_Date.SetFocus

Case vbNo
       If Dirty = True Then
       DoCmd.RunCommand acCmdUndo
             
   End If

  End Select
End If

If TRN_Eff = True Then
Select Case MsgBox("Do you want to save?", vbYesNo, "Save Changes")
Case vbYes
 
  DoCmd.SetWarnings False
 
  DoCmd.RunCommand acCmdSaveRecord
  DoCmd.OpenQuery "qrycra1"
  CurrentDb.Execute "Delete * From tblcra1"
   
  Me.Requery  
 
Case vbNo
 
    If Dirty = True Then
       DoCmd.RunCommand acCmdUndo      
 
End If
 
End Select
End If

End Sub
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099321
That all looks correct - the "Invalid reference to property form/report" seems to suggest that the name of the subform control is incorrect. Can you double check it? If not, there is another (uglier) way to do it, but I'm not certain why this doesn't work.
0
 

Author Comment

by:TimKestermont
ID: 12099348
both name and source object is subfrmtele.  
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099352
Ok, try this:

Me!subfrmtele.SetFocus
Me!subfrmtele.Form!Command13.SetFocus
SendKeys "{ENTER}"
0
 

Author Comment

by:TimKestermont
ID: 12099355
This is an issue throughout the DB and I was thinking the reference library maybe an issue.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099365
Ah - when you say it's an issue throughout the db, where does it tend to give you that error?

This *sometimes* indicates database corruption although that wouldn't be the first conclusion. Have you compacted the database recently?
0
 

Author Comment

by:TimKestermont
ID: 12099371
Yes I have compacted the db.  I shouldn't say that it's throughout the db.  It's everytime I refer to a subform.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099385
Could you try the snippet that I posted above,with SendKeys?
0
 

Author Comment

by:TimKestermont
ID: 12099386
I have tried it with another form and the same issue.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099398
That's very strange - are you using any of the Microsoft Forms 2.0 controls? Could you try unchecking that library and see if it works?
0
 

Author Comment

by:TimKestermont
ID: 12099408
It won't let me uncheck.  It says it is in use
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099427
Are you able to reference controls on the subform? For example, if you run this line of code somewhere on your main form:
MsgBox Me!subfrmtele.Form!SomeTextbox

Substitute MyTextBox with the name of a control on the subform - does it correctly give you a message box showing the value in the text box?
0
 

Author Comment

by:TimKestermont
ID: 12099486
Hey I think I have found out what the issue was.  My relationships were not there.  I have no idea why that happened.
I will try the reference again
0
 

Author Comment

by:TimKestermont
ID: 12099492
the reference you supplied is working now!  So now how do I keep the close event from happening
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099511
I missed that bit, sorry. You'll have to move the code to the form's Unload event. Then, you can prevent the form from closing with this line:
Cancel=True

This cancels the Unload event and keeps the form open.
0
 

Author Comment

by:TimKestermont
ID: 12099560
Well still having a bit of an issue ...my save record above isn't happening.  Hey I am done this evening.  I have to be up in a few hours.  Thanks and I will get you the points because you have spent an abnormal amount of time on this because of my relationships.

Thanks for everything
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12099562
OK, post back when you return and we can try to troubleshoot the remaining problem.
0
 

Author Comment

by:TimKestermont
ID: 12099577
Sure will.  Thanks
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now