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