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

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

"Claire" <(E-Mail Removed)> wrote in message
news:4AF23126-ABED-4A50-8766-(E-Mail Removed)...
> 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
>
>

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
"Arvin Meyer [MVP]" wrote:

> 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
>
> "Claire" <(E-Mail Removed)> wrote in message
> news:4AF23126-ABED-4A50-8766-(E-Mail Removed)...
> > 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
> >
> >
>
>
> .
>

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


"Claire" <(E-Mail Removed)> wrote in message
news:3BC54887-7E57-4C42-B943-(E-Mail Removed)...
> 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
> "Arvin Meyer [MVP]" wrote:
>
>> 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
>>
>> "Claire" <(E-Mail Removed)> wrote in message
>> news:4AF23126-ABED-4A50-8766-(E-Mail Removed)...
>> > 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
>> >
>> >
>>
>>
>> .
>>