Combo Box gets focus but I can't enter data???

[Guest]

First, let me address the obvious questions: Yes, it's enabled. No, it's not
locked.

The combo box is in a subform on my main form. I'm using code that sets the
focus to the combo box if certain criteria are met. The combo box gets focus
(which is evident both by conditional formating I have set for it when it
gets focus and by a MsgBox testing message I have pop up in the GotFocus
event of the combo box), so I don't believe the problem has to do with
getting the focus to the combo box. But, when it is given focus, I cannot
enter any data. Normally I would be able to type in the combo box and have
it auto-fill the selection I am starting to type, but it does not receive
text when I type. If I hit tab, space bar, enter, etc...it does nothing -
but the focus is still on the combo box (it might be worth saying that the
combo box is the only enabled control in the subform - so "tab" really has no
where to go anyways). The combo box comes to life as usual if I click on the
area where the dropdown arrow usually is (prior to this though, the dropdown
arrow is not visible). It also works just fine if I have a code break
set...as a matter of fact, if I click on any other window and then come back
to the form, the combo box is working correctly. ????? This is truely weird.
Please, if anyone has any ideas, I would really appreciate the help, I've
tried everything I can think of at this point.

 

[Ken Snell \(MVP\)]

What is the Control Source of the combo box? Is the subform's Record Source
query updatable? Is AllowEdits property of the form serving as the subform
set to Yes?

 

[Guest]

Thank you for responding.
The Control Source for the combo box is a field named Specialty_Id from a
table that is related to the main form through a Facility_Id. I'm not sure
what you mean by "query updatable," but the AllowEdits property of the form
(and subform) is set to Yes.

 

[Ken Snell \(MVP\)]

An updatable query allows you to edit existing data or to add new records or
to delete existing records when the query is open in datasheet view. A
nonupdatable query will not let you do any of these things, and if you build
a form based on such a query the form also will not let you do such things.

See these articles for information about what can make a query nonupdatable
(watch for line-wrapping):

When can I update data from a query?
http://msdn.microsoft.com/library/d...l/acconDeterminingWhenCanUpdateDataQueryS.asp

Harnessing the Power of Updatable Queries
http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnaraccgen/html/msdn_harness.asp

ACC2000: Cannot Edit or Update Record on a Form
http://support.microsoft.com/default.aspx?scid=kb;en-us;209571&Product=acc

ACC2000: Status Bar Displays "Recordset Not Updateable" Message When You Try
to Update a Linked Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;304179&Product=acc

INFO: Troubleshooting Errors That May Occur When You Update Data in Queries
and in Forms
http://support.microsoft.com/default.aspx?scid=kb;en-us;328828&Product=acc

ACC: Update Query Based on Totals Query Fails
http://support.microsoft.com/default.aspx?scid=kb;en-us;116142&Product=acc


--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

OK, I get what you mean now...and thanks for all the resources. The record
source query is updatable. As I mentioned in my first post, the combo box
works just fine in most cases.
I have several different ways to invoke code that produces a MsgBox message
("Please select a Specialty") and then puts the focus on the combo box. The
code is invoked through clicking on the next record, previous record, new
record....buttons by way of the After Update event. That works just fine and
the combo box takes the focus and allows for text input.
I also have a combo box (Find_Facility_cbx) on the main form that allows me
to search facilities by name and go to the selected record. When I click on
the Find_Facility_cbx it also runs code through the GotFocus event
(surprisingly, I first tried it in the Enter Event and it would not work)
which displays the MsgBox message and sends focus to the Specialty_Id_cbx on
the subform. That bit of code also works just fine.
What does not work correctly is when I am entering data in the main form and
just click on the Specialty_Id_cbx to try and enter data. The After Update
code runs (just like it did in the scenario mentioned earlier) and the MsgBox
appears, and the Specialty_Id_cbx gets the focus, but I can't type in it
unless I click it again with my mouse....or click on a different window (even
the properties window) and then click back on the main form header bar (or
wherever). That's the strangeness I'm trying to solve. Why does it work
just fine in those other situations but not work the same way when I try to
directly select it?

Again, any help would be much appreciated.

 

[Ken Snell \(MVP\)]

The behavior you describe suggests that the focus is not being fully
released by the main form during the code that runs in the AfterUpdate
event. In a form / subform setup, what actually must happen when you want to
put the focus in a control in a subform while you're in the main form is
that first focus must be set to the subform control (the control that holds
the subform object) and then to the control in the subform. This is most
evident when you use VBA code to move the focus to a control on the subform
from, say, a command button in the main form:

Me.Subformname.SetFocus
Me.Subformname.Form.ControlName.SetFocus


Two code steps are needed.

However, ACCESS typically will handle the two set focus actions
automatically when you click into a control in the subform, and you only
need to do it once. So, I am guessing that something is "interfering" with
the normal operation by pausing the resetting of the focus. To give more
suggestions, I would need to know more about the AfterUpdate code that runs,
and perhaps other code/events that are possibly running in the main form.
Can you post more details?
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

I would be happy to provide you with whatever information may be helpful in
resolving this issue. The only code I can think of that would seem to be
directly affecting the situation where I click directly into the subform
would be the AfterUpdate code I have. This is the same code that works just
fine when I attempt to leave the record by clicking the next record button or
any of the other navigation buttons at the bottom of the main form. This
code is also invoked (along with some code in the GotFocus event) when I
click in the Find_Facility_cbx at the top of my main form - and again it
works fine in that setting too.

The code I am using in the AfterUpdate event of the main form is:
(the second line of code below is actually a continuation of the first line)

If Forms!frm_Facility_Info!Fac_Fax_txt <> "" And
Forms!frm_Facility_Info!sbf_Facility_Specialties!Specialty_Id_cbx = 0 Then
MsgBox ("You have not selected a Specialty. Please Select a
Specialty")
Cancel = True
Me.sbf_Facility_Specialties.SetFocus
Me.sbf_Facility_Specialties.Form.Specialty_Id_cbx.SetFocus
End If

I used to have all my code in the Forms!FormName format, but I have recently
tried the me.FormName just to see if it made any difference -- which it did
not.

Nathan

 

[RHutcheson]

You nedd to do a two slightly different 2 step process. You have to set
focus on the sub-form itself first and then you have to set the focus on the
combo-box on that sub-form. If you don't set focus on the sub-form first,
you will still set focus on the control, but you will be unable to type into
it. It's just an "Access Thing" .

Ken,

I would be happy to provide you with whatever information may be helpful in
resolving this issue. The only code I can think of that would seem to be
directly affecting the situation where I click directly into the subform
would be the AfterUpdate code I have. This is the same code that works just
fine when I attempt to leave the record by clicking the next record button or
any of the other navigation buttons at the bottom of the main form. This
code is also invoked (along with some code in the GotFocus event) when I
click in the Find_Facility_cbx at the top of my main form - and again it
works fine in that setting too.

The code I am using in the AfterUpdate event of the main form is:
(the second line of code below is actually a continuation of the first line)

If Forms!frm_Facility_Info!Fac_Fax_txt <> "" And
Forms!frm_Facility_Info!sbf_Facility_Specialties!Specialty_Id_cbx = 0 Then
MsgBox ("You have not selected a Specialty. Please Select a
Specialty")
Cancel = True
Me.sbf_Facility_Specialties.SetFocus
Me.sbf_Facility_Specialties.Form.Specialty_Id_cbx.SetFocus
End If

I used to have all my code in the Forms!FormName format, but I have recently
tried the me.FormName just to see if it made any difference -- which it did
not.

Nathan

The behavior you describe suggests that the focus is not being fully
released by the main form during the code that runs in the AfterUpdate

[quoted text clipped - 54 lines]

 

[Ken Snell \(MVP\)]

This code is in the main form's AfterUpdate event procedure, is that right?
If yes, you cannot Cancel the event, so the line "Cancel = True" is
meaningless here.

Also, once you move from a subform to a main form, the subform's data will
be saved before you are able to interact with the main form. You cannot undo
or reverse that data change. I am not 100% clear on the intent / use of this
code, but for it to work, the "specialty" value must not be a required
field/value for the subform's underlying table. Is this true?

The syntax for setting focus to a control on a subform is two-fold, as I
noted earlier:

Me.SubformName.SetFocus
Me.SubformName.Form.ControlName.SetFocus

where SubformName is the name of the subform control (the main form control
that actually holds the subform object).


--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Isn't that what I am accomplishing through the code I included before?
(listed below)

Me.sbf_Facility_Specialties.SetFocus
Me.sbf_Facility_Specialties.Form.Specialty_Id_cbx.SetFocus

You nedd to do a two slightly different 2 step process. You have to set
focus on the sub-form itself first and then you have to set the focus on the
combo-box on that sub-form. If you don't set focus on the sub-form first,
you will still set focus on the control, but you will be unable to type into
it. It's just an "Access Thing" .

Ken,

I would be happy to provide you with whatever information may be helpful in
resolving this issue. The only code I can think of that would seem to be
directly affecting the situation where I click directly into the subform
would be the AfterUpdate code I have. This is the same code that works just
fine when I attempt to leave the record by clicking the next record button or
any of the other navigation buttons at the bottom of the main form. This
code is also invoked (along with some code in the GotFocus event) when I
click in the Find_Facility_cbx at the top of my main form - and again it
works fine in that setting too.

The code I am using in the AfterUpdate event of the main form is:
(the second line of code below is actually a continuation of the first line)

If Forms!frm_Facility_Info!Fac_Fax_txt <> "" And
Forms!frm_Facility_Info!sbf_Facility_Specialties!Specialty_Id_cbx = 0 Then
MsgBox ("You have not selected a Specialty. Please Select a
Specialty")
Cancel = True
Me.sbf_Facility_Specialties.SetFocus
Me.sbf_Facility_Specialties.Form.Specialty_Id_cbx.SetFocus
End If

I used to have all my code in the Forms!FormName format, but I have recently
tried the me.FormName just to see if it made any difference -- which it did
not.

Nathan

The behavior you describe suggests that the focus is not being fully
released by the main form during the code that runs in the AfterUpdate

[quoted text clipped - 54 lines]

Again, any help would be much appreciated.

 

[Guest]

Thanks for the catch on the Cancel = True. That is still left over from when
I was playing with things in the BeforeUpdate event section.

As for the Subform, it is based on a Linking table with 2 fields.
Facility_Id and Specialty_Id. They are both required because the 2 of them
together make up the primary key. I'm not sure why you say that the field
cannot be a required field, it seems to be working just as intended for the
data entry side of things. I'm just trying to add a little error checking
with this code so that users can't leave important information out when
certain criteria is met. For more info on the intended use of this code
please see the link below, it is a previous post of mine.

http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

The code I have included in the AfterUpdate event is the best I've been able
to come up with in order to accomplish the results desired in the link above.
It works great when the navigation buttons like next record or last record
are pushed. It just has some kind of hang up when I try to click directly
into the Specialty_Id_cbx. It produces the MsgBox message and gives the
Specialty_Id_cbx focus, but I can't type into it until I click it again.

Something that may be of interest: I've played around quite a bit with my
code to see how things respond, and there are a couple things I've noticed
that may help you out.
1) The Specialty_Id_cbx gets focus whether or not I include
Me.sbf_Facility_Specialties.Form.Specialty_Id_cbx.SetFocus
Maybe it is because the Specialty_Id_cbx is the only visible control on the
subform, I don't know, but the second "step" of the SetFocus command has
never seemed to make a difference (it's almost like it's not even read).

2) When I comment out the Me.sbf_Facility_Specialties.SetFocus portion of
the code, clicking directly into the Specialty_Id_cbx work just fine. As
mentioned above, that line of code is the only one that seems to affect the
assignment of focus at all.

Let me know if I need to include/explain/try anything further. Thanks again.

 

[Ken Snell \(MVP\)]

Let me see if my mental image of your form/subform setup is correct:

1) Your main form is used to enter information about a physician (or a
facility?) -- name, address, phone number, etc.

2) Your subform is used to enter one or more specialties for each physician
(or a facility?).

3) You want to require the entry of at least one specialty if a phone number
is entered on the main form for a physician (or a facility?).

Is this correct?

I assume that the LinkMasterFields and LinkChildFields properties of the
subform control contain the name of the PhysicianID (or Facility_Id) field?
and your subform's underlying table contains a composite primary key:
Facility_Id and Specialty_Id fields. (My earlier comment about the empty
field in the subform not being a required field does not apply now; I didn't
understand what you were doing on the form -- I thought you were allowing
data entry in the subform, and the user might go back to the main form
before finishing all the data entry in the subform, and you wanted to be
sure that all the subform's data entry work had been completed. I think this
is not what you're doing now.)

If my concept of your database above is reasonably correct, I think I would
approach the problem a bit differently. Because the subform data entry is
optional except when a phone number has been entered, I would be inclined to
not use a subform for the entry of specialties, but rather would use a
"dialog" popup form that is opened by one of two methods:

(1) In the main form's AfterUpdate event, if the user has entered a phone
number, the main form's code tests the table to see if a specialty has been
entered for the main form's current record, and if not, it opens a popup
form in dialog mode so that the user must enter a specialty before he/she
can continue. Closing of this form can be disallowed until at least one
specialty has been entered.

(2) Put a button on the main form that can be clicked to open the specialty
popup form. Clicking of this button would have to save the current record in
the main form before it opens the popup window.

The above concept allows optional entry and mandatory entry without having
to be concerned with focus setting in the subform. But, I do not mean to say
that this approach is required in place of your approach.

In your setup, if Specialty_Id is the only enabled control in the subform,
then it will get the focus automatically once the subform receives focus.

One potential problem with your current code is that it might not see
already entered specialty values in the subform if the user were to have
entered a specialty value and then started a new record in the subform, and
then returned to the main form. Your code will look at the value of the
Specialty_Id textbox (Specialty_Id_cbx control) in the record that has the
focus in the subform. Note that a subform will have its own focus, in
addition to the focus on the main form. Only one is "active" at a time, but
the subform will not forget which record had the focus when you click into
the main form from the subform. Thus, your code should be modified at least
to test the RecordCount of the subform's RecordsetClone to see if it's
greater than zero, but even that might cause an erroneous result if a new
record has been started.

Focus setting in a subform can be tricky / a bit unpredictable, as you're
finding. It can be very dependent upon what other actions are occurring on
the main form when the code runs. It's most predictable if code that sets
the focus to a control on the subform runs when you click a button, or do
some other "non-focus-moving" action. For example, if the AfterUpdate event
of a textbox is going to set the focus to the subform, you may get
unpredictable results because the AfterUpdate event may be occurring because
you pressed Enter or Tab key after finishing your data entry, and the focus
is already moving in the main form when your code tries to redirect it,
usually unsuccessfully. Similar situation can occur for what triggers a
form's AfterUpdate event -- again, the focus may be "on the move" already.

This is why I suggest that you consider the popup window for your setup so
that there is a discrete, reproducible result.

As for why you're having to click twice, I can only assume it's because the
focus did not end up on the subform from your code, and your first click
sets focus on the subform and the second click sets focus on the
Specialty_Id_cbx control.
--

Ken Snell
<MS ACCESS MVP>

 

[Guest]

Ken,

I appreciate all the time you have spent trying to help me. You seem to
have a fairly accurate view of how I use the forms in my DB now (I actually
have separate forms for the Individuals and the Facilities, which each link
to a Individual/Facility_Specialty form/table and then link to the same
Specialties table).

I am so close to getting this thing to work as desired, I had really hoped
there was a way to accomplish it completely. But, I understand the things
you are saying about the complexity and unpredictability of shifting focus to
the subform.

Your idea about the popup is something I will look into further, however I
will also need to figure out how I want to display the existing specialty
links on the main form display because it's important to know what those are
at first glance.

Thanks again for all your help, you get the green.

 

[Ken Snell \(MVP\)]

Ken,

Your idea about the popup is something I will look into further, however I
will also need to figure out how I want to display the existing specialty
links on the main form display because it's important to know what those
are
at first glance.

You could use a subform to display the specialties already selected for the
main form's record -- same setup as you have for your subform right now,
except lock all the controls in the subform so that no one can enter / edit
data there -- it becomes a "read-only" subform for display purposes.