ACCESS FEED: [MS_AccessPros] Re: BeforeUpdate in subform cancels update when Close button on main form is clicked

Selasa, 01 Mei 2012

[MS_AccessPros] Re: BeforeUpdate in subform cancels update when Close button on main form is clicked

Thanks Bill!

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Connie
>
> It should be the Form_Unload event. Close has no parameters.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, mrsgoudge <no_reply@> wrote:
> >
> > John,
> > I've put the code in and when I compile I get the following message for the line Private Sub Form_Close(Cancel As Integer)--because it doesn't recognize (Cancel As Integer)???
> >
> > "Procedure declaration does not match event or procedure having the same name"
> >
> > Really appreciate all you help!
> > Connie
> >
> > --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> > >
> > > Connie-
> > >
> > > If you want to kill the close, you need some more code:
> > >
> > > Option Compare Database
> > > Option Explicit
> > > Dim intCancel As Integer
> > >
> > > Private Sub Form_Close(Cancel As Integer)
> > > ' If Before Update has canceled,
> > > If intCancel Then
> > > ' Cancel the close
> > > Cancel = True
> > > End If
> > > End Sub
> > >
> > > Private Sub Form_AfterUpdate()
> > > ' Make sure intCancel if off
> > > intCancel = False
> > > End Sub
> > >
> > > Private Sub Form_BeforeUpdate(Cancel As Integer)
> > > Dim db As DAO.Database, rstP As DAO.Recordset
> > >
> > > 'Prevent double entries for Me.Ordr = 1
> > > If Me.Ordr = 1 Then
> > > ' Point to this database
> > > Set db = CurrentDb
> > > 'Open ListingContacts to find priorities for this listing that are the same as
> > > Me.Priority
> > > Set rstP = db.OpenRecordset("SELECT * FROM SalesBuyers " & _
> > > "WHERE SaleID = " & Me.SaleID & " And Ordr = " & Me.Ordr)
> > > If rstP.RecordCount > 0 Then
> > > Msgbox "Another contact has this order." _
> > > & vbNewLine & "Change the other contact's order and then enter this contact"
> > > ' Cancel the update
> > > Cancel = True
> > > ' Set cancel in progress in case form is trying to close
> > > intCancel = True
> > > End If
> > > End If
> > > 'Clean Up
> > > rstP.Close
> > > Set rstP = Nothing
> > > Set db = Nothing
> > > End Sub
> > >
> > > John Viescas, author
> > > Microsoft Office Access 2010 Inside Out
> > > Microsoft Office Access 2007 Inside Out
> > > Building Microsoft Access Applications
> > > Microsoft Office Access 2003 Inside Out
> > > SQL Queries for Mere Mortals
> > > http://www.viescas.com/
> > > (Paris, France)
> > >
> > > -----------------------------
> > >
> > > From: MS_Access_Professionals@yahoogroups.com
> > > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of mrsgoudge
> > > Sent: Monday, April 30, 2012 10:01 PM
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Subject: [MS_AccessPros] BeforeUpdate in subform cancels update when Close
> > > button on main form is clicked
> > >
> > >
> > > Good afternoon or evening :-),
> > >
> > > If the user is in the middle of entering data on a subform and decides to close
> > > the form using the Close button on the main form, the BeforeUpdate event of the
> > > subform gives the message that the something needs to be changed and cancels the
> > > update, but the DoCmd.Close continues so the form is closed with no opportunity
> > > to change the data.
> > >
> > > Don't think you need it but here's the code behind the BeforeUpdate event.
> > >
> > > 'Prevent double entries for Me.Ordr = 1
> > > If Me.Ordr = 1 Then
> > > ' Point to this database
> > > Set db = CurrentDb
> > > 'Open ListingContacts to find priorities for this listing that are the same as
> > > Me.Priority
> > > Set rstP = db.OpenRecordset("SELECT * FROM SalesBuyers " & _
> > > "WHERE SaleID = " & Me.SaleID & " And Ordr = " & Me.Ordr)
> > > If rstP.RecordCount > 0 Then
> > > Msgbox "Another contact has this order." _
> > > & vbNewLine & "Change the other contact's order and then enter this contact"
> > > Me.Undo
> > > End If
> > > End If
> > > 'Clean Up
> > > rstP.Close
> > > Set rstP = Nothing
> > > Set db = Nothing
> > >
> > > Thanks!
> > > Connie
> > >
> >
>