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

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
>