Require Combobox Entry

[ApexData@gmail.com]

Hello

I have a single form, and want to require the user to make a selection
from a serialnumber ComboBox before being allowed to enter any other
textboxes. Also, the user should be allowed to click on an Exit button
I created and get a response.

The following code works fine without the ExitButton issue:

Private Sub SerialCombo_Exi t(Cancel As Integer)
Dim Ctl As Control
If IsNull(SerDevCo mbo) Then
MsgBox "You must enter a serial#"
Me.AnyOtherFiel d.SetFocus 'acts as a refresh
Me.SerialCombo. SetFocus 'return to field since it is empty
End If
End Sub

MY PROBLEM IS INCLUDING THE CHECK FOR a change of focus when
clicking on the ExitButton. I tried many different methods.

If Not (Screen.ActiveC ontrol.Name <> "ExitButton ") Then

ThankYou

[salad]

Re: Require Combobox Entry

ApexData@gmail. com wrote:[color=blue]
> Hello
>
> I have a single form, and want to require the user to make a selection
> from a serialnumber ComboBox before being allowed to enter any other
> textboxes. Also, the user should be allowed to click on an Exit button
> I created and get a response.
>
> The following code works fine without the ExitButton issue:
>
> Private Sub SerialCombo_Exi t(Cancel As Integer)
> Dim Ctl As Control
> If IsNull(SerDevCo mbo) Then
> MsgBox "You must enter a serial#"
> Me.AnyOtherFiel d.SetFocus 'acts as a refresh
> Me.SerialCombo. SetFocus 'return to field since it is empty
> End If
> End Sub
>
> MY PROBLEM IS INCLUDING THE CHECK FOR a change of focus when
> clicking on the ExitButton. I tried many different methods.
>
> If Not (Screen.ActiveC ontrol.Name <> "ExitButton ") Then
>
> ThankYou
>[/color]
I created a combo box and a command button to close/exit. Here's the
code for my combo using the OnExit method.

Private Sub Combo0_Exit(Can cel As Integer)
If IsNull(Me.Combo 0) Then
Cancel = True
MsgBox "Select an item"
End If
End Sub

This works...except if I press the X button to close the window.

Now if you wanted to allow the person to exit even if the serial no is
null, what you could set, in design mode, all of the textbox controls to
Enabled = True/Locked = True. This permits only the combo to be updated
or exit button to be pressed.

In the AfterUpdate event for the combo you could do something like
If Not IsNull(Me.Combo 0) Then
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = False
Endif
Next
End If

[tina]

Re: Require Combobox Entry

nice solution, salad. there is a gap, though - the user could enter
something in the combo box and exit the control, then go back and remove the
value and again exit the control. at that point, all the other controls are
still unlocked. how about modifying your code a little, to remove the outer
If statement, as

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = IsNull(Me.Combo 0)
End If
Next

hth


"salad" <oil@vinegar.co m> wrote in message
news:Oh2_f.917$ An2.121@newsrea d2.news.pas.ear thlink.net...[color=blue]
> ApexData@gmail. com wrote:[color=green]
> > Hello
> >
> > I have a single form, and want to require the user to make a selection
> > from a serialnumber ComboBox before being allowed to enter any other
> > textboxes. Also, the user should be allowed to click on an Exit button
> > I created and get a response.
> >
> > The following code works fine without the ExitButton issue:
> >
> > Private Sub SerialCombo_Exi t(Cancel As Integer)
> > Dim Ctl As Control
> > If IsNull(SerDevCo mbo) Then
> > MsgBox "You must enter a serial#"
> > Me.AnyOtherFiel d.SetFocus 'acts as a refresh
> > Me.SerialCombo. SetFocus 'return to field since it is empty
> > End If
> > End Sub
> >
> > MY PROBLEM IS INCLUDING THE CHECK FOR a change of focus when
> > clicking on the ExitButton. I tried many different methods.
> >
> > If Not (Screen.ActiveC ontrol.Name <> "ExitButton ") Then
> >
> > ThankYou
> >[/color]
> I created a combo box and a command button to close/exit. Here's the
> code for my combo using the OnExit method.
>
> Private Sub Combo0_Exit(Can cel As Integer)
> If IsNull(Me.Combo 0) Then
> Cancel = True
> MsgBox "Select an item"
> End If
> End Sub
>
> This works...except if I press the X button to close the window.
>
> Now if you wanted to allow the person to exit even if the serial no is
> null, what you could set, in design mode, all of the textbox controls to
> Enabled = True/Locked = True. This permits only the combo to be updated
> or exit button to be pressed.
>
> In the AfterUpdate event for the combo you could do something like
> If Not IsNull(Me.Combo 0) Then
> Dim ctl As Control
> For Each ctl In Me.Controls
> If ctl.ControlType = acTextBox Then
> ctl.Locked = False
> Endif
> Next
> End If[/color]

[salad]

Re: Require Combobox Entry

tina wrote:
[color=blue]
> nice solution, salad. there is a gap, though - the user could enter
> something in the combo box and exit the control, then go back and remove the
> value and again exit the control. at that point, all the other controls are
> still unlocked. how about modifying your code a little, to remove the outer
> If statement, as
>
> Dim ctl As Control
>
> For Each ctl In Me.Controls
> If ctl.ControlType = acTextBox Then
> ctl.Locked = IsNull(Me.Combo 0)
> End If
> Next
>
> hth
>[/color]
Much better.

[PC D]

Re: Require Combobox Entry

This solution still does not meet the OP's requirements !!

<< before being allowed to enter any other textboxes>>

The user can still enter any other textbox and he probably will become
confused because he can not enter data in the textbox. The solution that
meets the OP's requirements is:

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = IsNull(Me.Combo 0)
ctl.Enabled = Not IsNull(Me.Combo 0)
End If
Next

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
Over 1175 users have come to me from the newsgroups requesting help
resource@pcdata sheet.com


"salad" <oil@vinegar.co m> wrote in message
news:Wyb_f.981$ An2.642@newsrea d2.news.pas.ear thlink.net...[color=blue]
> tina wrote:
>[color=green]
>> nice solution, salad. there is a gap, though - the user could enter
>> something in the combo box and exit the control, then go back and remove
>> the
>> value and again exit the control. at that point, all the other controls
>> are
>> still unlocked. how about modifying your code a little, to remove the
>> outer
>> If statement, as
>>
>> Dim ctl As Control
>>
>> For Each ctl In Me.Controls
>> If ctl.ControlType = acTextBox Then
>> ctl.Locked = IsNull(Me.Combo 0)
>> End If
>> Next
>>
>> hth
>>[/color]
> Much better.[/color]

[StopThisAdvertising]

Re: Require Combobox Entry


"PC D" <false@email.co m> schreef in bericht news:WRd_f.5039 $i41.4778@newsr ead1.news.atl.e arthlink.net...
[color=blue]
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications 'Resource ????
> Over 1175 users have come to me from the newsgroups requesting help '1175 users ????
> resource@pcdata sheet.com
>
> [/color]

--
To Steve:

Why PC D this time? Why not PCD anymore, or Access Resource or Help available or why not just PC DataSheet?
You think that changing names is vital for you?
Why don't you just get lost? No-one wants your advertising/job hunting here!
Over 600!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:

http://home.tiscali.nl/arracom/whoissteve.html

Arno R

[tina]

Re: Require Combobox Entry

well, considering that both salad and i suggested that the code be run in
the combo box control's AfterUpdate event - you're correct, it doesn't meet
the OP's requirements. there's nothing to stop the user from tabbing through
that control without making any changes, thus leaving the other controls
unlocked. but your revised code doesn't address that problem, either; it
simply enables/disables the other controls *again, only when the user first
makes a change in the combo box control*. disabling controls does nothing
more to make them unusable than simply locking them, though i'd agree with
you that it gives the user a visual cue that the controls unavailable.

i'd say that all three of us missed the boat here, to one degree or another.
the code i posted should be added to the form's Current event, in addition
to the combo box control's AfterUpdate event. that should take care of the
issue, unless i'm overlooking something - again.

hth


"PC D" <false@email.co m> wrote in message
news:WRd_f.5039 $i41.4778@newsr ead1.news.atl.e arthlink.net...[color=blue]
> This solution still does not meet the OP's requirements !!
>
> << before being allowed to enter any other textboxes>>
>
> The user can still enter any other textbox and he probably will become
> confused because he can not enter data in the textbox. The solution that
> meets the OP's requirements is:
>
> Dim ctl As Control
>
> For Each ctl In Me.Controls
> If ctl.ControlType = acTextBox Then
> ctl.Locked = IsNull(Me.Combo 0)
> ctl.Enabled = Not IsNull(Me.Combo 0)
> End If
> Next
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> Over 1175 users have come to me from the newsgroups requesting[/color]
help[color=blue]
> resource@pcdata sheet.com
>
>
> "salad" <oil@vinegar.co m> wrote in message
> news:Wyb_f.981$ An2.642@newsrea d2.news.pas.ear thlink.net...[color=green]
> > tina wrote:
> >[color=darkred]
> >> nice solution, salad. there is a gap, though - the user could enter
> >> something in the combo box and exit the control, then go back and[/color][/color][/color]
remove[color=blue][color=green][color=darkred]
> >> the
> >> value and again exit the control. at that point, all the other controls
> >> are
> >> still unlocked. how about modifying your code a little, to remove the
> >> outer
> >> If statement, as
> >>
> >> Dim ctl As Control
> >>
> >> For Each ctl In Me.Controls
> >> If ctl.ControlType = acTextBox Then
> >> ctl.Locked = IsNull(Me.Combo 0)
> >> End If
> >> Next
> >>
> >> hth
> >>[/color]
> > Much better.[/color]
>
>[/color]

[ApexData@gmail.com]

Re: Require Combobox Entry

I agree totally with your last comment Tina. Having the same concerns
and realizing that I would have had to alter a substantial number of
things in
my form to go your suggested route. I instead choose to repost an
alternate
idea. But it really surprised me that this idea would be so difficult
to implement.
Others have made suggestions, but I just can't seem to make it happen!

My alternate Post was:
If I am in a combobox, is there are way of determining which control
was clicked outside the combobox, before I actually leave the combobox?

[tina]

Re: Require Combobox Entry

yes, i've been following that thread too, Apex. and the answer is: AFAIK,
no. when you click, or tab, from one control to another, the events for the
first control (such as BeforeUpdate, AfterUpdate, and Exit) run before the
events for the second control (such as the second control). so the identity
of the "next" control isn't available at the time that the events of the
"current" control run.

my (and salad's) alternate solution, when presented in its' entirety, is
really simple enough. i'd suggest a private procedure in the form's module,
as

Private Sub isLocked()

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = IsNull(Me.Combo 0)
End If
Next

End Sub

if you need to manage other controls such as combo boxes, beside the textbox
controls, you can add them to the If Statement, such as

If ctl.ControlType = acTextBox Or _
ct.ControlType = acComboBox Then

in the form's Current event, and the combo box's AfterUpdate event, simply
call the sub, as

isLocked

this is a pretty standard solution to the issue of managing controls on a
form based on a value in one of them. if it doesn't suit your needs, i'm
afraid i have no other suggestions to offer.

hth


<ApexData@gmail .com> wrote in message
news:1144625357 .996768.32900@i 39g2000cwa.goog legroups.com...[color=blue]
> I agree totally with your last comment Tina. Having the same concerns
> and realizing that I would have had to alter a substantial number of
> things in
> my form to go your suggested route. I instead choose to repost an
> alternate
> idea. But it really surprised me that this idea would be so difficult
> to implement.
> Others have made suggestions, but I just can't seem to make it happen!
>
> My alternate Post was:
> If I am in a combobox, is there are way of determining which control
> was clicked outside the combobox, before I actually leave the combobox?
>[/color]

[ApexData@gmail.com]

Re: Require Combobox Entry

Tina

Thankyou for your help!

Greg

[tina]

Re: Require Combobox Entry

you're welcome, good luck! :)


<ApexData@gmail .com> wrote in message
news:1144628028 .582071.75960@e 56g2000cwe.goog legroups.com...[color=blue]
> Tina
>
> Thankyou for your help!
>
> Greg
>[/color]

[Bri]

Re: Require Combobox Entry

1. Does the Combo have to be entered first?
2. Or is it sufficient to say that it must be entered before the record
can be saved?

No 2 is the easiest one to deal with. In the Forms BeforeUpdate event:

Private Sub Form_BeforeUpda te(Cancel As Integer)
If IsNull(Me!MyCom bo) Then
MsgBox "Enter a value in MyCombo"
Cancel=True
End If
End Sub

No 1 is a bit trickier, as the others trying to answer this have shown.
What I have done in similar scenarios is (you could use Visible or
Locked depending on what you want to see visually):

Private Sub Form_Current()
SetControls
End Sub

Private Sub MyCombo_AfterUp date()
SetControls
End Sub

Sub SetControls()
'Each control separately or a loop if all controls to be
'Invisible/locked. Make sure if you loop to exclude
'MyCombo from being locked
If IsNull(Me!MyCom bo) Then
Me!Control1.Vis ible = False
Me!Control2.Vis ible = False
....
Me!Controlx.Vis ible = False
Else
Me!Control1.Vis ible = True
Me!Control2.Vis ible = True
....
Me!Controlx.Vis ible = True
End If
End Sub

HTH

--
Bri

[tina]

Re: Require Combobox Entry

your #2 solution is a good, standard solution to this type of situation,
Bri. to save yourself all the code-writing involved in listing each control
explicitly (and twice) in an If statement, though, you might want to give
salad's looping code a try, with the "True/False" toggle that i added to it.
the end result is the same, it's just the difference in time saved between
writing 6 lines of code vs a potentially large number of lines depending on
the number of controls. also, code maintenance is easier with the looping
code; if you add a new control to the form, you don't need to change the
looping code at all, vs needing to remember to add that control to the If
statement.

hth


"Bri" <not@here.com > wrote in message news:XEw_f.1445 8$gO.8016@pd7tw 3no...[color=blue]
> 1. Does the Combo have to be entered first?
> 2. Or is it sufficient to say that it must be entered before the record
> can be saved?
>
> No 2 is the easiest one to deal with. In the Forms BeforeUpdate event:
>
> Private Sub Form_BeforeUpda te(Cancel As Integer)
> If IsNull(Me!MyCom bo) Then
> MsgBox "Enter a value in MyCombo"
> Cancel=True
> End If
> End Sub
>
> No 1 is a bit trickier, as the others trying to answer this have shown.
> What I have done in similar scenarios is (you could use Visible or
> Locked depending on what you want to see visually):
>
> Private Sub Form_Current()
> SetControls
> End Sub
>
> Private Sub MyCombo_AfterUp date()
> SetControls
> End Sub
>
> Sub SetControls()
> 'Each control separately or a loop if all controls to be
> 'Invisible/locked. Make sure if you loop to exclude
> 'MyCombo from being locked
> If IsNull(Me!MyCom bo) Then
> Me!Control1.Vis ible = False
> Me!Control2.Vis ible = False
> ...
> Me!Controlx.Vis ible = False
> Else
> Me!Control1.Vis ible = True
> Me!Control2.Vis ible = True
> ...
> Me!Controlx.Vis ible = True
> End If
> End Sub
>
> HTH
>
> --
> Bri
>[/color]

[salad]

Re: Require Combobox Entry

ApexData@gmail. com wrote:[color=blue]
> I agree totally with your last comment Tina. Having the same concerns
> and realizing that I would have had to alter a substantial number of
> things in
> my form to go your suggested route. I instead choose to repost an
> alternate
> idea. But it really surprised me that this idea would be so difficult
> to implement.
> Others have made suggestions, but I just can't seem to make it happen!
>
> My alternate Post was:
> If I am in a combobox, is there are way of determining which control
> was clicked outside the combobox, before I actually leave the combobox?
>[/color]
I totally disagree with your comments. It is not difficult to
implement. You're just to lazy.

I supplied a suggestion without knowing anything about your process or
form so I provided something minimal. I write mostly aircode in
responses to people, and my response to your problem was aircode. Some
posters like you expect that people put aside their lives and provide
free advice like some Mother Thresa to people like you that are too lazy
to figure something out themselves...es pecially when provided a method
or solution. The only reason you can't make it happen is due to the
fact you aren't qualified to do the work.

You got some ideas from a few of us. Use your imagination and make them
work for you.

[tina]

Re: Require Combobox Entry

wow, salad, take it down a notch, hon. personal attacks aren't appropriate
in this forum.


"salad" <oil@vinegar.co m> wrote in message
news:i0z_f.2137 $Fy2.786@newsre ad3.news.pas.ea rthlink.net...[color=blue]
> ApexData@gmail. com wrote:[color=green]
> > I agree totally with your last comment Tina. Having the same concerns
> > and realizing that I would have had to alter a substantial number of
> > things in
> > my form to go your suggested route. I instead choose to repost an
> > alternate
> > idea. But it really surprised me that this idea would be so difficult
> > to implement.
> > Others have made suggestions, but I just can't seem to make it happen!
> >
> > My alternate Post was:
> > If I am in a combobox, is there are way of determining which control
> > was clicked outside the combobox, before I actually leave the combobox?
> >[/color]
> I totally disagree with your comments. It is not difficult to
> implement. You're just to lazy.
>
> I supplied a suggestion without knowing anything about your process or
> form so I provided something minimal. I write mostly aircode in
> responses to people, and my response to your problem was aircode. Some
> posters like you expect that people put aside their lives and provide
> free advice like some Mother Thresa to people like you that are too lazy
> to figure something out themselves...es pecially when provided a method
> or solution. The only reason you can't make it happen is due to the
> fact you aren't qualified to do the work.
>
> You got some ideas from a few of us. Use your imagination and make them
> work for you.[/color]