|
 |
access thread: Duplicate combo boxes with non-identical values validation; SetFocus not working
Message #1 by simonds@m... on Wed, 16 Jan 2002 23:35:51
|
|
Hi folks,
I have a form with two drop-down combo boxes that both read from the same
(lookup) source table. I have written code to check to see if the two
boxes hold identical data, and if so warns the user that they are the same
(they should be two different pieces of data). The form inputs all data to
another (main) table. Here is the code:
Private Sub cboProcedure2_Change()
If cboProcedure2.Value = cboProcedure1.Value Then
msgbox "You cannot have the same value as the 1st Procedure !",
vbCritical, _
"Duplicate Entry for Both Procedures"
cboProcedure2.SetFocus
End If
End Sub
I have the same code for the LostFocus event. However, in either case it
seems that the SetFocus method does not work properly. I can mouse click
in another field, get the error message, but the focus goes to the control
that I was clicking into. In other words, the user could ignore the
warning and enter bad data (which is why I tried adding the LostFocus
event, because the onChange event would give an immediate reply). Is there
something wrong with the way I have set this up?
I actually have a validation rule in the combobox properties that goes:
cboProcedure2.Value <> cboProcedure1
and the validation text I entered appears. I am trying to use the code
because what happens when I delete the entry in cboProcedure2 and try to
move to another field (it can be empty) is that the validation text then
appears. I have to choose to another value and move off the control.
However, if I didn't want a value in cboProcedure2, I then have to go back
to the just-changed entry and delete it, then move on. I am trying to to
have the user end up having to do this when deleting the incorrect data
and leaving a blank field. I have a hunch that an empty string is still
left in the field, in the first scenario. Any way around this??
I could not get the validation rule to work on the table design entry for
such, only it should read: [Procedure2] <> [Procedure1]??
Is there a way in the table design to get this to work?
Thanks for any and all help.
Regards,
Eric
Message #2 by "John Ruff" <papparuff@c...> on Wed, 16 Jan 2002 16:11:02 -0800
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_0007_01C19EA8.65688ED0
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Instead of using the LostFocus or Change events try using the
BeforeUpdate event. Like this:
Private Sub cboProcedure2_BeforeUpdate(Cancel As Integer)
If cboProcedure2= cboProcedure1 Then
MsgBox "You cannot have the same value as the 1st Procedure !",
_
vbCritical, _
"Duplicate Entry for Both Procedures"
Cancel = True
End If
John Ruff - The Eternal Optimist :-)
-----Original Message-----
From: simonds@m... [mailto:simonds@m...]
Sent: Wednesday, January 16, 2002 11:36 PM
To: Access
Subject: [access] Duplicate combo boxes with non-identical values
validation; SetFocus not working
Hi folks,
I have a form with two drop-down combo boxes that both read from the
same
(lookup) source table. I have written code to check to see if the two
boxes hold identical data, and if so warns the user that they are the
same
(they should be two different pieces of data). The form inputs all data
to
another (main) table. Here is the code:
Private Sub cboProcedure2_Change()
If cboProcedure2.Value = cboProcedure1.Value Then
msgbox "You cannot have the same value as the 1st Procedure !",
vbCritical, _
"Duplicate Entry for Both Procedures"
cboProcedure2.SetFocus
End If
End Sub
I have the same code for the LostFocus event. However, in either case it
seems that the SetFocus method does not work properly. I can mouse click
in another field, get the error message, but the focus goes to the
control
that I was clicking into. In other words, the user could ignore the
warning and enter bad data (which is why I tried adding the LostFocus
event, because the onChange event would give an immediate reply). Is
there
something wrong with the way I have set this up?
I actually have a validation rule in the combobox properties that goes:
cboProcedure2.Value <> cboProcedure1
and the validation text I entered appears. I am trying to use the code
because what happens when I delete the entry in cboProcedure2 and try to
move to another field (it can be empty) is that the validation text then
appears. I have to choose to another value and move off the control.
However, if I didn't want a value in cboProcedure2, I then have to go
back
to the just-changed entry and delete it, then move on. I am trying to to
have the user end up having to do this when deleting the incorrect data
and leaving a blank field. I have a hunch that an empty string is still
left in the field, in the first scenario. Any way around this??
I could not get the validation rule to work on the table design entry
for
such, only it should read: [Procedure2] <> [Procedure1]??
Is there a way in the table design to get this to work?
Thanks for any and all help.
Regards,
Eric
Message #3 by simonds@m... on Thu, 17 Jan 2002 01:19:16
|
|
That worked like a charm, John. Thank-you very much for the help.
Regards,
Eric
> This is a multi-part message in MIME format.
>
> ------=_NextPart_000_0007_01C19EA8.65688ED0
> Content-Type: text/plain;
> charset="us-ascii"
> Content-Transfer-Encoding: 7bit
>
> Instead of using the LostFocus or Change events try using the
> BeforeUpdate event. Like this:
>
>
>
> Private Sub cboProcedure2_BeforeUpdate(Cancel As Integer)
>
>
>
> If cboProcedure2= cboProcedure1 Then
>
> MsgBox "You cannot have the same value as the 1st Procedure !",
> _
>
> vbCritical, _
>
> "Duplicate Entry for Both Procedures"
>
> Cancel = True
>
>
>
> End If
>
>
>
> John Ruff - The Eternal Optimist :-)
>
>
>
>
>
>
>
> -----Original Message-----
>
> From: simonds@m... [mailto:simonds@m...]
>
> Sent: Wednesday, January 16, 2002 11:36 PM
>
> To: Access
>
> Subject: [access] Duplicate combo boxes with non-identical values
> validation; SetFocus not working
>
>
>
> Hi folks,
>
>
>
> I have a form with two drop-down combo boxes that both read from the
> same
>
> (lookup) source table. I have written code to check to see if the two
>
> boxes hold identical data, and if so warns the user that they are the
> same
>
> (they should be two different pieces of data). The form inputs all data
> to
>
> another (main) table. Here is the code:
>
>
>
> Private Sub cboProcedure2_Change()
>
>
>
> If cboProcedure2.Value = cboProcedure1.Value Then
>
> msgbox "You cannot have the same value as the 1st Procedure !",
>
> vbCritical, _
>
> "Duplicate Entry for Both Procedures"
>
> cboProcedure2.SetFocus
>
> End If
>
>
>
> End Sub
>
>
>
> I have the same code for the LostFocus event. However, in either case it
>
>
> seems that the SetFocus method does not work properly. I can mouse click
>
>
> in another field, get the error message, but the focus goes to the
> control
>
> that I was clicking into. In other words, the user could ignore the
>
> warning and enter bad data (which is why I tried adding the LostFocus
>
> event, because the onChange event would give an immediate reply). Is
> there
>
> something wrong with the way I have set this up?
>
>
>
> I actually have a validation rule in the combobox properties that goes:
>
>
>
> cboProcedure2.Value <> cboProcedure1
>
> and the validation text I entered appears. I am trying to use the code
>
> because what happens when I delete the entry in cboProcedure2 and try to
>
>
> move to another field (it can be empty) is that the validation text then
>
>
> appears. I have to choose to another value and move off the control.
>
> However, if I didn't want a value in cboProcedure2, I then have to go
> back
>
> to the just-changed entry and delete it, then move on. I am trying to to
>
>
> have the user end up having to do this when deleting the incorrect data
>
> and leaving a blank field. I have a hunch that an empty string is still
>
> left in the field, in the first scenario. Any way around this??
>
>
>
> I could not get the validation rule to work on the table design entry
> for
>
> such, only it should read: [Procedure2] <> [Procedure1]??
>
> Is there a way in the table design to get this to work?
>
>
>
> Thanks for any and all help.
>
>
>
> Regards,
>
> Eric
>
> ---
>
>
>
>
>
>
> ------=_NextPart_000_0007_01C19EA8.65688ED0
> Content-Type: text/html;
> charset="us-ascii"
> Content-Transfer-Encoding: quoted-printable
>
> <html>
>
> <head>
> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html;
> charset=3Dus-ascii">
>
>
> <meta name=3DGenerator content=3D"Microsoft Word 10 (filtered)">
>
> <style>
> <!--
> /* Style Definitions */
> p.MsoNormal, li.MsoNormal, div.MsoNormal
> {margin:0in;
> margin-bottom:.0001pt;
> font-size:10.0pt;
> font-family:"Times New Roman";}
> a:link, span.MsoHyperlink
> {color:blue;
> text-decoration:underline;}
> a:visited, span.MsoHyperlinkFollowed
> {color:purple;
> text-decoration:underline;}
> p.MsoPlainText, li.MsoPlainText, div.MsoPlainText
> {margin:0in;
> margin-bottom:.0001pt;
> font-size:10.0pt;
> font-family:"Courier New";}
> @page Section1
> {size:8.5in 11.0in;
> margin:1.0in 77.95pt 1.0in 77.95pt;}
> div.Section1
> {page:Section1;}
> -->
> </style>
>
> </head>
>
> <body lang=3DEN-US link=3Dblue vlink=3Dpurple>
>
> <div class=3DSection1>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Instead of using the LostFocus or Change events try using the
> BeforeUpdate event. Like this:</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Private Sub cboProcedure2_BeforeUpdate(Cancel As
> Integer)</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> If cboProcedure2=3D cboProcedure1
> Then</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> MsgBox "You
> cannot have
> the same value as the 1st Procedure !", _</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
>
10.0pt'>  
> ;
> vbCritical, _</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
>
10.0pt'>  
> ;
> "Duplicate Entry for Both Procedures"</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> Cancel =3D
> True</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> End If</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoNormal><font size=3D2 face=3D"Times New Roman"><span
> style=3D'font-size:
> 10.0pt'>John Ruff - The Eternal Optimist :-)</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>-----Original Message-----</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>From: simonds@m... [mailto:simonds@m...]
> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Sent: </span></font>Wednesday, January 16, 2002 11:36 PM</p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>To: Access</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Subject: [access] Duplicate combo boxes with non-identical
> values
> validation; SetFocus not working</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Hi folks,</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>I have a form with two drop-down combo boxes that both read from
> the
> same </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>(lookup) source table. I have written code to check to see if
> the two </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>boxes hold identical data, and if so warns the user that they
> are the
> same </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>(they should be two different pieces of data). The form inputs
> all data
> to </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>another (main) table. Here is the code:</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Private Sub cboProcedure2_Change()</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> If cboProcedure2.Value =3D
> cboProcedure1.Value Then</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> msgbox "You
> cannot have
> the same value as the 1st Procedure !", </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>vbCritical, _</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
>
10.0pt'>  
> ;
> "Duplicate Entry for Both Procedures"</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>
> cboProcedure2.SetFocus</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> End If</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>End Sub</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>I have the same code for the LostFocus event. However, in either
> case
> it </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>seems that the SetFocus method does not work properly. I can
> mouse
> click </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>in another field, get the error message, but the focus goes to
> the
> control </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>that I was clicking into. In other words, the user could ignore
> the </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>warning and enter bad data (which is why I tried adding the
> LostFocus </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>event, because the onChange event would give an immediate
> reply). Is
> there </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>something wrong with the way I have set this
> up?</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>I actually have a validation rule in the combobox properties
> that goes:</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> cboProcedure2.Value <>
> cboProcedure1</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>and the validation text I entered appears. I am trying to use
> the code </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>because what happens when I delete the entry in cboProcedure2
> and try
> to </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>move to another field (it can be empty) is that the validation
> text
> then </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>appears. I have to choose to another value and move off the
> control. </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>However, if I didn't want a value in cboProcedure2, I then have
> to go
> back </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>to the just-changed entry and delete it, then move on. I am
> trying to
> to </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>have the user end up having to do this when deleting the
> incorrect data
> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>and leaving a blank field. I have a hunch that an empty string
> is still
> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>left in the field, in the first scenario. Any way around
> this??</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>I could not get the validation rule to work on the table design
> entry
> for </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>such, only it should read: [Procedure2]
> <> [Procedure1]??</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Is there a way in the table design to get this to
> work?</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Thanks for any and all help.</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Regards,</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>Eric</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>---</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>
> </span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'>
> $subst('Email.Unsub').</span></font></p>
>
> <p class=3DMsoPlainText><font size=3D2 face=3D"Courier New"><span
> style=3D'font-size:
> 10.0pt'> </span></font></p>
>
> </div>
>
> </body>
>
> </html>
>
> ------=_NextPart_000_0007_01C19EA8.65688ED0--
>
>
|
|
|
View Cart
