John, I'll try to be clearer next time. Thanks for trying! Connie
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Connie-
>
> Oh. I assumed this was all on one form. You can't control what happens on a
> subform. As soon as the user leaves the subform control (perhaps to click the
> outer form's Close button), the record gets saved. What you were missing before
> in your BeforeUpdate event code was setting Cancel = True to kill the update.
> Me.Undo does nothing.
>
> 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: Tuesday, May 01, 2012 6:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: BeforeUpdate in subform cancels update when Close
> button on main form is clicked
>
>
> John,
>
> You make mistakes??? Makes me happy! Don't have to be perfect :-)
>
> Anyways--another issue has cropped up. When compiled I get the message "variable
> not defined" for intCancel = True in the Form_BeforeUpdate event of the subform.
> Which tells me that intCancel needs to be defined for the subform and also be
> available for the form (where the close button resides). Does that mean I need
> to define it upon opening the database.
>
> Connie
>
> --- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@> wrote:
> >
> > Connie-
> >
> > I goofed. It should be Private Sub Form_Unload(Cancel As Integer)
> >
> > 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: Tuesday, May 01, 2012 5:35 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: BeforeUpdate in subform cancels update when Close
> > button on main form is clicked
> >
> >
> > 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
> > >
> >
>
Tidak ada komentar:
Posting Komentar