Canceling an OnClick Event

C

Claire

Hi all,
Is it possible for a BeforeUpdate event of one control to cancel the OnClick
event of another?

Details:
I have an unbound form that allows for editing and creating sites.
txtSiteNo has a BeforeUpdate event that checks if the site already exists,
and if it does, asks if you want to edit it. If so, the rest of the unbound
text boxes update to what's already in the database. ctlAddSite has an
OnClick Event which adds/edits the table info to the values of the rest of
the unbound text boxes. Everything works fine, except that if someone enters
an existing site into txtSiteNo and exits the textbox by clicking on
ctlAddSite (without going to another control first), the beforeUpdate event
runs, but the ctlAddSite does immediately after without giving a person the
chance to cancel the edit or edit the info.

I thought about adding a boolean to the txtSiteNo BeforeUpdate event that
would tell me if the site already exists so that I could change the procedure
of the OnClick event. However, if someone moves from the txtSiteNo to
another control, it effectively means that you have to click twice to edit a
site, or I would have to ask a person again if they would like to edit it
(which just seems silly).

Is there a way to tell which text box was changed before an OnClick event?
And then if the site exists and someone moved straight from txtSiteNo to
click on ctlAddSite it would react differently? Or can the BeforeUpdate
event of txtSiteNo cancel the ctlAddSite OnClick event?

And in case you'd like to read the code:

Option Compare Database
Option Explicit

Dim mstrCallingForm As String
Dim blnEditSite As Boolean 'variable to keep site from adding before it is
editing

Private Sub cmdAddSite_Click()
On Error GoTo ErrorHandler

Dim Msg As String
Dim Style As String
Dim Title As String
Dim response As String

If Trim(Me.txtCustNo & "") = "" Then
Debug.Print 1
Msg = "The customer number can not be blank."
Style = vbOKOnly
Title = "Missing Customer Number"
MsgBox Msg, Style, Title
Me.txtCustNo.SetFocus
GoTo ExitProcedure
ElseIf blnEditSite = True Then
Debug.Print 1.5
blnEditSite = False
GoTo ExitProcedure
ElseIf Trim(Me.txtSiteNo & "") = "" Then
Debug.Print 2
Msg = "The site number can not be blank."
Style = vbOKOnly
Title = "Missing Site Number"
MsgBox Msg, Style, Title
Me.txtSiteNo.SetFocus
GoTo ExitProcedure
Else
GoTo CheckCustomer
End If

CheckCustomer:
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblCustomers where
CustNo=" & _
"'" & Me.txtCustNo & "'")

If Not (rst.BOF And rst.EOF) Then 'edit customer if it already exists
Debug.Print 3
rst.MoveFirst
rst.Edit
rst!CustName = Me.txtCustName
rst!CustAddress = Me.txtCustAddress
rst!CustCity = Me.txtCustCity
rst!CustContact = Me.txtCustContact
rst!CustPhone = Me.txtCustPhone
rst!CustState = Me.txtCustState
rst!CustZip = Me.txtCustZip
rst.Update
GoTo CheckSite
Else 'add customer if it does not exist yet
Debug.Print 4
rst.AddNew
rst!CustNo = Me.txtCustNo
rst!CustName = Me.txtCustName
rst!CustAddress = Me.txtCustAddress
rst!CustCity = Me.txtCustCity
rst!CustContact = Me.txtCustContact
rst!CustPhone = Me.txtCustPhone
rst!CustState = Me.txtCustState
rst!CustZip = Me.txtCustZip
rst.Update
GoTo CreateSite
End If

CheckSite:
Dim rst2 As DAO.Recordset

Set rst2 = dbs.OpenRecordset("Select * from tblSites where CustNo= "
& _
"'" & Me.txtCustNo & "' and SiteNo= '" & Me.txtSiteNo & "'")

If Not (rst2.BOF And rst2.EOF) Then 'If the site already exits,
update to form's info
Debug.Print 5
rst2.MoveFirst
rst2.Edit
rst2!SiteName = Me.txtSiteName
rst2!SiteAddress = Me.txtSiteAddress
rst2!SiteCity = Me.txtSiteCity
rst2!SiteState = Me.txtSiteState
rst2!SiteZip = Me.txtSiteZip
rst2!CustType = Me.txtCustType
rst2.Update
GoTo ClearForm
Else
Debug.Print 6
rst2.AddNew
rst2!CustNo = Me.txtCustNo
rst2!SiteNo = Me.txtSiteNo
rst2!SiteName = Me.txtSiteName
rst2!SiteAddress = Me.txtSiteAddress
rst2!SiteCity = Me.txtSiteCity
rst2!SiteState = Me.txtSiteState
rst2!SiteZip = Me.txtSiteZip
rst2!CustType = Me.txtCustType
rst2.Update
GoTo ClearForm
End If

CreateSite:
Dim rst3 As DAO.Recordset

Set rst3 = dbs.OpenRecordset("Select * from tblSites")
Debug.Print 7
rst3.AddNew
rst3!CustNo = Me.txtCustNo
rst3!SiteNo = Me.txtSiteNo
rst3!SiteName = Me.txtSiteName
rst3!SiteAddress = Me.txtSiteAddress
rst3!SiteCity = Me.txtSiteCity
rst3!SiteState = Me.txtSiteState
rst3!SiteZip = Me.txtSiteZip
rst3!CustType = Me.txtCustType
rst3.Update
GoTo ClearForm

ClearForm:
ClearAll
GoTo ExitProcedure

ExitProcedure:
On Error Resume Next
Exit Sub

ErrorHandler:
DisplayUnexpectedError Err.Number, Err.Description
Resume ExitProcedure
Resume


End Sub

Private Sub ClearAll()
Dim ctl As Access.Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Value = Null
End If
Next ctl

End Sub


Private Sub cmdCancel_Click()
DoCmd.Close acForm, "frmNewSite"
End Sub

Private Sub txtSiteNo_BeforeUpdate(Cancel As Integer)

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblSites where CustNo= " & _
"'" & Me.txtCustNo & "' and SiteNo= '" & Me.txtSiteNo & "'")

blnEditSite = False

If Not (rst.BOF And rst.EOF) Then
blnEditSite = True

Dim Msg As String
Dim Style As String
Dim Title As String
Dim response As String

Msg = "This site is already in the database. Would you like to edit
it?"
Style = vbOKCancel + vbDefaultButton2 + vbApplicationModal
Title = "Edit Site?"
response = MsgBox(Msg, Style, Title)

If response = vbOK Then
rst.MoveFirst
Me.txtSiteName = rst!SiteName
Me.txtSiteAddress = rst!SiteAddress
Me.txtSiteCity = rst!SiteCity
Me.txtSiteState = rst!SiteState
Me.txtSiteZip = rst!SiteZip
Me.txtCustType = rst!CustType

Else
Me.txtSiteName = Null
Me.txtSiteAddress = Null
Me.txtSiteCity = Null
Me.txtSiteState = Null
Me.txtSiteZip = Null
Me.txtCustType = Null
Me.txtCustNo.SetFocus
Me.txtSiteNo = Null
Me.txtSiteNo.SetFocus
End If
Else
Me.txtSiteName = Null
Me.txtSiteAddress = Null
Me.txtSiteCity = Null
Me.txtSiteState = Null
Me.txtSiteZip = Null
Me.txtCustType = Null
'Me.txtSiteName.SetFocus
End If

End Sub
 
A

Arvin Meyer [MVP]

Bound forms have an OldValue property to each bound textbox. So saving that
to a variable, then comparing the variable to the current value would tell
you if it had been changed. You don't even need to use a variable, but it
makes the code easier to understand. So:

Dim intWhatever As Integer

intWhatever = Me.txtSiteNo.OldValue

Then you can do something like:

If Me.txtSiteNo = intWhatever Then
 
C

Claire

Arvin,
Thank you for the suggestion, and I imagine it would work beautifully if it
was a bound form, and it's great to know, but sadly, nothing is bound (nor do
I want it to be). Are there any other suggestions for how to cancel the
onClick event if someone exits the textbox with a BeforeUpdate by clicking on
a button with an OnClick event?

Thanks!
~Claire
 
A

Arvin Meyer [MVP]

If the form is unbound, there should never be a value in a textbox unless
you put it there. So that's easier. All you need to do is to fill a form
level variable at the same time you initially fill the textbox. Be sure to
clear the variable in the Form's AfterUpdate event.

Dim intWhatever As Integer

intWhatever = rst!SiteNo

(or however you use a value fill the unbound textbox)

Then you can do something like:

If Me.txtSiteNo = intWhatever Then
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

me.requery 3
Error: an object cannot be found 2
Canceling Workbook Before_Save event 2
Event Procedures: Event on Worksheet to fire Event on another Worksheet 2

Top