onclose event

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
TimKestermontAsked:
Who is Participating?
 
shanesuebsahakarnConnect With a Mentor Commented:
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
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
TimKestermontAuthor Commented:
I apologize but are you referring to a field when you say control?

0
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
Forms!frmtelephone!subfrmtele.Form.command13_OnClick     What is wrong with this code?
0
 
shanesuebsahakarnCommented:
That looks right - what error did it give?

Did you change the Private to Public?
0
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
Sorry, try this, my error:

Forms!frmtelephone!subfrmtele.Form.command13_Click

(i.e. remove the On)
0
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
Can you post the command button's code from the subform (including the Sub and End Sub lines)?
0
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
both name and source object is subfrmtele.  
0
 
shanesuebsahakarnCommented:
Ok, try this:

Me!subfrmtele.SetFocus
Me!subfrmtele.Form!Command13.SetFocus
SendKeys "{ENTER}"
0
 
TimKestermontAuthor Commented:
This is an issue throughout the DB and I was thinking the reference library maybe an issue.
0
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
Could you try the snippet that I posted above,with SendKeys?
0
 
TimKestermontAuthor Commented:
I have tried it with another form and the same issue.
0
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
It won't let me uncheck.  It says it is in use
0
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
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
 
TimKestermontAuthor Commented:
the reference you supplied is working now!  So now how do I keep the close event from happening
0
 
shanesuebsahakarnCommented:
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
 
TimKestermontAuthor Commented:
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
 
shanesuebsahakarnCommented:
OK, post back when you return and we can try to troubleshoot the remaining problem.
0
 
TimKestermontAuthor Commented:
Sure will.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.