By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,833 Members | 2,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,833 IT Pros & Developers. It's quick & easy.

Require Combobox Entry

P: n/a
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_Exit(Cancel As Integer)
Dim Ctl As Control
If IsNull(SerDevCombo) Then
MsgBox "You must enter a serial#"
Me.AnyOtherField.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.ActiveControl.Name <> "ExitButton") Then

ThankYou

Apr 9 '06 #1
Share this Question
Share on Google+
17 Replies


P: n/a
Ap******@gmail.com wrote:
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_Exit(Cancel As Integer)
Dim Ctl As Control
If IsNull(SerDevCombo) Then
MsgBox "You must enter a serial#"
Me.AnyOtherField.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.ActiveControl.Name <> "ExitButton") Then

ThankYou

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(Cancel As Integer)
If IsNull(Me.Combo0) 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.Combo0) Then
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = False
Endif
Next
End If
Apr 9 '06 #2

P: n/a
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.Combo0)
End If
Next

hth
"salad" <oi*@vinegar.com> wrote in message
news:Oh***************@newsread2.news.pas.earthlin k.net...
Ap******@gmail.com wrote:
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_Exit(Cancel As Integer)
Dim Ctl As Control
If IsNull(SerDevCombo) Then
MsgBox "You must enter a serial#"
Me.AnyOtherField.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.ActiveControl.Name <> "ExitButton") Then

ThankYou

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(Cancel As Integer)
If IsNull(Me.Combo0) 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.Combo0) Then
Dim ctl As Control
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
ctl.Locked = False
Endif
Next
End If

Apr 9 '06 #3

P: n/a
tina wrote:
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.Combo0)
End If
Next

hth

Much better.
Apr 9 '06 #4

P: n/a
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.Combo0)
ctl.Enabled = Not IsNull(Me.Combo0)
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
re******@pcdatasheet.com
"salad" <oi*@vinegar.com> wrote in message
news:Wy***************@newsread2.news.pas.earthlin k.net...
tina wrote:
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.Combo0)
End If
Next

hth

Much better.

Apr 9 '06 #5

P: n/a

"PC D" <fa***@email.com> schreef in bericht news:WR*****************@newsread1.news.atl.earthl ink.net...
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 ????
re******@pcdatasheet.com



--
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
Apr 9 '06 #6

P: n/a
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" <fa***@email.com> wrote in message
news:WR*****************@newsread1.news.atl.earthl ink.net...
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.Combo0)
ctl.Enabled = Not IsNull(Me.Combo0)
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 re******@pcdatasheet.com
"salad" <oi*@vinegar.com> wrote in message
news:Wy***************@newsread2.news.pas.earthlin k.net...
tina wrote:
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.Combo0)
End If
Next

hth

Much better.


Apr 9 '06 #7

P: n/a
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?

Apr 9 '06 #8

P: n/a
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.Combo0)
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
<Ap******@gmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
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?

Apr 9 '06 #9

P: n/a
Tina

Thankyou for your help!

Greg

Apr 10 '06 #10

P: n/a
you're welcome, good luck! :)
<Ap******@gmail.com> wrote in message
news:11*********************@e56g2000cwe.googlegro ups.com...
Tina

Thankyou for your help!

Greg

Apr 10 '06 #11

P: n/a
Bri
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_BeforeUpdate(Cancel As Integer)
If IsNull(Me!MyCombo) 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_AfterUpdate()
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!MyCombo) Then
Me!Control1.Visible = False
Me!Control2.Visible = False
....
Me!Controlx.Visible = False
Else
Me!Control1.Visible = True
Me!Control2.Visible = True
....
Me!Controlx.Visible = True
End If
End Sub

HTH

--
Bri

Apr 10 '06 #12

P: n/a
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" <no*@here.com> wrote in message news:XEw_f.14458$gO.8016@pd7tw3no...
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_BeforeUpdate(Cancel As Integer)
If IsNull(Me!MyCombo) 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_AfterUpdate()
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!MyCombo) Then
Me!Control1.Visible = False
Me!Control2.Visible = False
...
Me!Controlx.Visible = False
Else
Me!Control1.Visible = True
Me!Control2.Visible = True
...
Me!Controlx.Visible = True
End If
End Sub

HTH

--
Bri

Apr 10 '06 #13

P: n/a
Ap******@gmail.com wrote:
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?

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...especially 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.
Apr 10 '06 #14

P: n/a
wow, salad, take it down a notch, hon. personal attacks aren't appropriate
in this forum.
"salad" <oi*@vinegar.com> wrote in message
news:i0****************@newsread3.news.pas.earthli nk.net...
Ap******@gmail.com wrote:
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?

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...especially 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.

Apr 10 '06 #15

P: n/a

Salad. I am very grateful to you for your suggestion. It was an
excellent option,
just not one suitable for my application because of the intended
behavior of my
form, and what would be required to re-engineer what I was already
happy with.
I believe you misunderstood my comments.

I can assure you that I am not lazy. Just notice that same of my posts
are beyond
1:00AM. Further, I am new to Access (2 Months) and do need the help of
the group,
and will in time return the favors. And finally, I do not liken you to
Mother Thresa.
You behave more like Mother Fxxxxx.

Sorry To Have Bothered You!

Apr 10 '06 #16

P: n/a
Bri

tina wrote:
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.

'Each control separately or a loop if all controls to be
'Invisible/locked. Make sure if you loop to exclude
'MyCombo from being locked


I did mention the loop in the code comment above, but as I had seen that
it had been demonstrated in another post, I only wanted to point out
that the controlling control needed to be excluded from the action
inside the loop. I offered the alternative in case it was only some
specific controls that needed to be invisible/locked.

--
Bri

Apr 11 '06 #17

P: n/a
Ap******@gmail.com wrote:
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_Exit(Cancel As Integer)
Dim Ctl As Control
If IsNull(SerDevCombo) Then
MsgBox "You must enter a serial#"
Me.AnyOtherField.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.ActiveControl.Name <> "ExitButton") Then

ThankYou


I tried to come up with something that would handle as many of the
posted objections as possible. Here's some code behind
frmHoldingPattern:

'Begin code behind form----------
Option Compare Database
Option Explicit

Dim boolNormalExit As Boolean

Private Sub cmdExit_Click()
boolNormalExit = True
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()
SerialCombo.SetFocus
boolNormalExit = False
End Sub

Private Sub Form_Unload(Cancel As Integer)
'Force the user to exit using the command button only
If boolNormalExit = False Then
MsgBox ("You must use the Exit button to exit.")
Cancel = True
End If
End Sub

Private Sub OtherCombo_GotFocus()
'Call FocusMove is optional for any comboboxes
End Sub

Private Sub txtOne_GotFocus()
Call FocusMove
End Sub

Private Sub txtTwo_GotFocus()
Call FocusMove
End Sub

Private Sub FocusMove()
If IsNull(SerialCombo.Value) Then
MsgBox ("You must supply a serial number before entering a text box
unless exiting.")
SerialCombo.SetFocus
End If
End Sub
'End code behind form--------

It's not as automatic as looping through the form's controls but may be
close to what you want. Tabbing into or clicking on a textbox gives
the user a message before going back to the combobox. It doesn't
involve locking or disabling controls. It satisfies Tina's revisit
objection. Clicking the X button to close the window results in a
message that along with the other message seems to allay PC D's fears
about the user becoming confused. It allows other comboboxes to be
edited before continuing on with its functionality. It doesn't deal
with saving the record though. Also, you'd need to remember to put the
GotFocus event on new textboxes. Perhaps this code can be a starting
point for something. Disclaimer: I'm not positive I understood the
question.

James A. Fortune
CD********@FortuneJames.com

Though this be madness, yet there is method in 't. :-)

-- Polonius' Aside From Hamlet (II, ii, 206)

Apr 20 '06 #18

This discussion thread is closed

Replies have been disabled for this discussion.