Combo Box Problem

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field in a form where I want users to use a combo box drop down list
to select their own name to enter it in the field. I have a short piece of
code triggered by the On Change event in this same field so that when users
select their name the code runs and another field is updated. This works fine
when users actually select their name from the drop down list but I know many
users will tab into the field, enter the first letter of their name and
expect the Auto Expand facility to fill in rest. Problem is this doesn't
happen - with Auto Expand set to Yes for the field when I enter a letter the
code from the On Change event runs and updates my second field but Auto
Expand has not filled the combo box field so this field gets left blank. How
do I correct this? I know I can trigger the code after the combo box field
has lost the focus but I cannot guarantee users will tab out of the first
field after selecting their name so I don't see this as an option.
TIA
 
Alan said:
I have a field in a form where I want users to use a combo box drop
down list to select their own name to enter it in the field. I have a
short piece of code triggered by the On Change event in this same
field so that when users select their name the code runs and another
field is updated. This works fine when users actually select their
name from the drop down list but I know many users will tab into the
field, enter the first letter of their name and expect the Auto
Expand facility to fill in rest. Problem is this doesn't happen -
with Auto Expand set to Yes for the field when I enter a letter the
code from the On Change event runs and updates my second field but
Auto Expand has not filled the combo box field so this field gets
left blank. How do I correct this? I know I can trigger the code
after the combo box field has lost the focus but I cannot guarantee
users will tab out of the first field after selecting their name so I
don't see this as an option.
Click to expand...

Don't use the Change event for this! As you've discovered, that fires
any time the text displayed in the combo changes in any way, which means
it fires for each keystroke.

All you really want is to take an action when the *value* of the combo
box changes. Therefore, use the AfterUpdate event instead. That only
fires once, whether the combo is updated by mouse action or keyboard
action.
 
Thanks for this but it's not quite what I want - as I said in my original
post "I cannot guarantee users will tab out of the first field" this is
likely because the combo box field is the last on the form to be completed by
the user. So as I understand it After Update will not trigger my code and
users will not see the second field updated. I don't want the users leaving
the form without seeing the second filed updated. Any other suggestions?
 
Alan said:
Thanks for this but it's not quite what I want - as I said in my
original post "I cannot guarantee users will tab out of the first
field" this is likely because the combo box field is the last on the
form to be completed by the user. So as I understand it After Update
will not trigger my code and users will not see the second field
updated. I don't want the users leaving the form without seeing the
second filed updated. Any other suggestions?
Click to expand...

That would be true regardless of whether you use the Change or the
AfterUpdate event. If you want to keep the users from saving the record
without causing this second field to be updated, use the *form's*
BeforeUpdate event and put code there to cancel the update if the field
hasn't been updated.

If it's just that the field mustn't be Null, your code might look like
this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.YourField) Then
Cancel = True ' prevent update
MsgBox "You must enter your name!"
Me.YourComboBox.SetFocus
End If

End Sub
'----- end of example code -----

If you need to make sure that the value has been changed from whatever
it was originally, which may not have been Null, you would use a
variation on the above code:

'----- start of example code #2 -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me.YourField
If (.Value & vbNullString) <> (.OldValue & vbNullString) Then
Cancel = True ' prevent update
MsgBox "You must change this value!"
Me.YourComboBox.SetFocus
End If
End With

End Sub
'----- end of example code #2 -----
 
Thank you. Problem solved.

Dirk Goldgar said:
That would be true regardless of whether you use the Change or the
AfterUpdate event. If you want to keep the users from saving the record
without causing this second field to be updated, use the *form's*
BeforeUpdate event and put code there to cancel the update if the field
hasn't been updated.

If it's just that the field mustn't be Null, your code might look like
this:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me.YourField) Then
Cancel = True ' prevent update
MsgBox "You must enter your name!"
Me.YourComboBox.SetFocus
End If

End Sub
'----- end of example code -----

If you need to make sure that the value has been changed from whatever
it was originally, which may not have been Null, you would use a
variation on the above code:

'----- start of example code #2 -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

With Me.YourField
If (.Value & vbNullString) <> (.OldValue & vbNullString) Then
Cancel = True ' prevent update
MsgBox "You must change this value!"
Me.YourComboBox.SetFocus
End If
End With

End Sub
'----- end of example code #2 -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I don't want the users leaving
the form without seeing the second filed updated.
Click to expand...

One disadvantage of the Form_BeforeUpdate event is that the user thinks he
has finished and then gets an error message. If you can guide him into not
making the mistake in the first place, then tempers remain cooled. I simply
_hate_ systems that let me do things wrong and then send back an error.

An alternative might be to leave the command button Disabled, and to use
the ComboBox_AfterUpdate or ComboBox_Exit event to validate its own
contents and only then set the command button to Enabled.

Just a thought,
Best wishes


Tim F
 
Tim Ferguson said:
One disadvantage of the Form_BeforeUpdate event is that the user
thinks he has finished and then gets an error message. If you can
guide him into not making the mistake in the first place, then
tempers remain cooled. I simply _hate_ systems that let me do things
wrong and then send back an error.

An alternative might be to leave the command button Disabled, and to
use the ComboBox_AfterUpdate or ComboBox_Exit event to validate its
own contents and only then set the command button to Enabled.
Click to expand...

I agree in principle, but I don't remember that Alan mentioned any
command button, to be enabled or disabled. And there are so many ways
to save a record, I don't feel it's worth trying to handle them all.

And I _hate_ applications that force me to fill out forms in a
particular order. I may well want to jump around.
 
Back
Top