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