Reset & Requery a Must-Enter combobox based on answer of other com

[Nat Vascular]

Thanks, in advance, so much for your time and help!

I have two bound combo boxes on a for. They are referring_group and
referring_physician. After a selection of referring_group, a query runs that
returns a list of referring_physicians that belong to the referring_group
selected. Both combo boxes are set to must-enter and no zero-length string
and both are limited to the list in the combo box. What I am trying to get
to happen is when the referring_group is changed, the referring_physician is
reset to Null, and requeried based on the new selection in the referral_group
control. My approach is to place an event procedure at the AfterUpdate event
of the referring_group combo box:

me.referring_physician.requery 'this requeries the control and works fine
DoCmd.GoToControl "referring_physician 'this moves focus and work fine
me.referring_physician = Null 'to reset control and force re-selection
based on new list.

This one errors telling me that because the field is must enter it cannot be
set to Null. This doesn't make total sense to me. The control doesn't write
the record to the database until after the entire form is complete....why
would it check that now? Is there another way to reset this control so the
old selection is removed?

I looked for a command like:

me.referring_physician.reset

I tried:

me.referring_physician.undo - that didn't work I suspect because it was
issued from the other control and there was really nothing to undo. I would
think this is a pretty common thing to try and do and someone has had to
figured out an easy way to accomplish.

Thanks so much for your help!

Mike

 

[Klatuu]

Part of the problem is that the combos are bound controls. When you change a
value in a bound combo, it tries to update the field the combo is bound to.
It is usually better to use unbound combos for doing searches like this. I
would suggest you unbind the combos and crerate 2 text boxes to bind to the
recordset fields.
You can use the After Update event of each combo to populate the bound text
box.

Now, as to the base question. When you are using cascading combos, the
technique is to requery the 2nd combo in the After Update event of the first
query:

Me.referring_physician.Requery

 

[Nat Vascular]

Thanks so much....that worked sort of. While I can set the unbound control
to none it created a host of additional challenges. When scrolling through
old records, these unbound controls keep their latest value. I solved that
by adding the following code to the forms OnCurrent event:

Me.UB_Referring_Physician = Referring_Physician '

UB_Referring_Physician is the new unbound combo and Referring_Physician is
the new bound text box (that I have set as invisible....fyi)

That seems to do the trick. Now the issue I'm having is forcing a must
enter condition for the Unbound_Combo. What I have tried is the following
code at the OnExit event:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Forms![Case Data Form]![UB_Referring Physician].SetFocus

End If

This catches the null condition correctly, displays the message box
correctly and moves the focus but then the focus continues to move to the
control I clicked on or the next control in the tab order if I hit the tab or
enter key. How do I get the focus to stop and go back to the
UB_Referring_Physician control?

 

[Klatuu]

Well, you may not really like the answer, but the easiest way is to use the
form's Before Insert event (since this really will only apply to new records)
and do the checking there. You can use the code you posted, but rather than
checking the value of the combo box, check the value of the hidden text box.
You can also cancel the before insert. The record will not be updated and
you can trhow the focus back to the offending control:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Cancel = True
Forms![Case Data Form]![UB_Referring Physician].SetFocus
End If

Thanks so much....that worked sort of. While I can set the unbound control
to none it created a host of additional challenges. When scrolling through
old records, these unbound controls keep their latest value. I solved that
by adding the following code to the forms OnCurrent event:

Me.UB_Referring_Physician = Referring_Physician '

UB_Referring_Physician is the new unbound combo and Referring_Physician is
the new bound text box (that I have set as invisible....fyi)

That seems to do the trick. Now the issue I'm having is forcing a must
enter condition for the Unbound_Combo. What I have tried is the following
code at the OnExit event:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Forms![Case Data Form]![UB_Referring Physician].SetFocus

End If

This catches the null condition correctly, displays the message box
correctly and moves the focus but then the focus continues to move to the
control I clicked on or the next control in the tab order if I hit the tab or
enter key. How do I get the focus to stop and go back to the
UB_Referring_Physician control?

Part of the problem is that the combos are bound controls. When you change a
value in a bound combo, it tries to update the field the combo is bound to.
It is usually better to use unbound combos for doing searches like this. I
would suggest you unbind the combos and crerate 2 text boxes to bind to the
recordset fields.
You can use the After Update event of each combo to populate the bound text
box.

Now, as to the base question. When you are using cascading combos, the
technique is to requery the 2nd combo in the After Update event of the first
query:

Me.referring_physician.Requery

 

[Nat Vascular]

Thanks again for the suggestion. Two follow-up questions.

1) What does the Cancel = True do?

2) If I decide to use the BeforeInsert at the form level as you have
suggested....I believe I can go back to using bound controls and set the
field at the database level so that Nulls are OK allowing me to reset the
control to Null at the control level becuase now I will be forcing a not Null
at the form level. Correct?

Thanks again,

Nat

Well, you may not really like the answer, but the easiest way is to use the
form's Before Insert event (since this really will only apply to new records)
and do the checking there. You can use the code you posted, but rather than
checking the value of the combo box, check the value of the hidden text box.
You can also cancel the before insert. The record will not be updated and
you can trhow the focus back to the offending control:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Cancel = True
Forms![Case Data Form]![UB_Referring Physician].SetFocus
End If

Thanks so much....that worked sort of. While I can set the unbound control
to none it created a host of additional challenges. When scrolling through
old records, these unbound controls keep their latest value. I solved that
by adding the following code to the forms OnCurrent event:

Me.UB_Referring_Physician = Referring_Physician '

UB_Referring_Physician is the new unbound combo and Referring_Physician is
the new bound text box (that I have set as invisible....fyi)

That seems to do the trick. Now the issue I'm having is forcing a must
enter condition for the Unbound_Combo. What I have tried is the following
code at the OnExit event:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Forms![Case Data Form]![UB_Referring Physician].SetFocus

End If

This catches the null condition correctly, displays the message box
correctly and moves the focus but then the focus continues to move to the
control I clicked on or the next control in the tab order if I hit the tab or
enter key. How do I get the focus to stop and go back to the
UB_Referring_Physician control?

Part of the problem is that the combos are bound controls. When you change a
value in a bound combo, it tries to update the field the combo is bound to.
It is usually better to use unbound combos for doing searches like this. I
would suggest you unbind the combos and crerate 2 text boxes to bind to the
recordset fields.
You can use the After Update event of each combo to populate the bound text
box.

Now, as to the base question. When you are using cascading combos, the
technique is to requery the 2nd combo in the After Update event of the first
query:

Me.referring_physician.Requery
:

Thanks, in advance, so much for your time and help!

I have two bound combo boxes on a for. They are referring_group and
referring_physician. After a selection of referring_group, a query runs that
returns a list of referring_physicians that belong to the referring_group
selected. Both combo boxes are set to must-enter and no zero-length string
and both are limited to the list in the combo box. What I am trying to get
to happen is when the referring_group is changed, the referring_physician is
reset to Null, and requeried based on the new selection in the referral_group
control. My approach is to place an event procedure at the AfterUpdate event
of the referring_group combo box:

me.referring_physician.requery 'this requeries the control and works fine
DoCmd.GoToControl "referring_physician 'this moves focus and work fine
me.referring_physician = Null 'to reset control and force re-selection
based on new list.

This one errors telling me that because the field is must enter it cannot be
set to Null. This doesn't make total sense to me. The control doesn't write
the record to the database until after the entire form is complete....why
would it check that now? Is there another way to reset this control so the
old selection is removed?

I looked for a command like:

me.referring_physician.reset

I tried:

me.referring_physician.undo - that didn't work I suspect because it was
issued from the other control and there was really nothing to undo. I would
think this is a pretty common thing to try and do and someone has had to
figured out an easy way to accomplish.

Thanks so much for your help!

Mike

 

[Klatuu]

Thanks again for the suggestion. Two follow-up questions.

1) What does the Cancel = True do?

Stops the update from executing so the record is not added to the form's
recordset. The reason to do this is the data in the record is either
incorrect or incomplete and you want to prevent adding a record until it is
complete and correct.

2) If I decide to use the BeforeInsert at the form level as you have
suggested....I believe I can go back to using bound controls and set the
field at the database level so that Nulls are OK allowing me to reset the
control to Null at the control level becuase now I will be forcing a not Null
at the form level. Correct?

The problem with using bound controls for a lookup control is that when you
enter a value in the control to perform the search, you have now changed the
value in the current record. In most cases, this is probably not what you
want. The only time I use combos as bound controls is when I am looking up a
value for that specific field in the current record.

Thanks again,

Nat

Well, you may not really like the answer, but the easiest way is to use the
form's Before Insert event (since this really will only apply to new records)
and do the checking there. You can use the code you posted, but rather than
checking the value of the combo box, check the value of the hidden text box.
You can also cancel the before insert. The record will not be updated and
you can trhow the focus back to the offending control:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Cancel = True
Forms![Case Data Form]![UB_Referring Physician].SetFocus
End If

Thanks so much....that worked sort of. While I can set the unbound control
to none it created a host of additional challenges. When scrolling through
old records, these unbound controls keep their latest value. I solved that
by adding the following code to the forms OnCurrent event:

Me.UB_Referring_Physician = Referring_Physician '

UB_Referring_Physician is the new unbound combo and Referring_Physician is
the new bound text box (that I have set as invisible....fyi)

That seems to do the trick. Now the issue I'm having is forcing a must
enter condition for the Unbound_Combo. What I have tried is the following
code at the OnExit event:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Forms![Case Data Form]![UB_Referring Physician].SetFocus

End If

This catches the null condition correctly, displays the message box
correctly and moves the focus but then the focus continues to move to the
control I clicked on or the next control in the tab order if I hit the tab or
enter key. How do I get the focus to stop and go back to the
UB_Referring_Physician control?

:

Part of the problem is that the combos are bound controls. When you change a
value in a bound combo, it tries to update the field the combo is bound to.
It is usually better to use unbound combos for doing searches like this. I
would suggest you unbind the combos and crerate 2 text boxes to bind to the
recordset fields.
You can use the After Update event of each combo to populate the bound text
box.

Now, as to the base question. When you are using cascading combos, the
technique is to requery the 2nd combo in the After Update event of the first
query:

Me.referring_physician.Requery
:

Thanks, in advance, so much for your time and help!

I have two bound combo boxes on a for. They are referring_group and
referring_physician. After a selection of referring_group, a query runs that
returns a list of referring_physicians that belong to the referring_group
selected. Both combo boxes are set to must-enter and no zero-length string
and both are limited to the list in the combo box. What I am trying to get
to happen is when the referring_group is changed, the referring_physician is
reset to Null, and requeried based on the new selection in the referral_group
control. My approach is to place an event procedure at the AfterUpdate event
of the referring_group combo box:

me.referring_physician.requery 'this requeries the control and works fine
DoCmd.GoToControl "referring_physician 'this moves focus and work fine
me.referring_physician = Null 'to reset control and force re-selection
based on new list.

This one errors telling me that because the field is must enter it cannot be
set to Null. This doesn't make total sense to me. The control doesn't write
the record to the database until after the entire form is complete....why
would it check that now? Is there another way to reset this control so the
old selection is removed?

I looked for a command like:

me.referring_physician.reset

I tried:

me.referring_physician.undo - that didn't work I suspect because it was
issued from the other control and there was really nothing to undo. I would
think this is a pretty common thing to try and do and someone has had to
figured out an easy way to accomplish.

Thanks so much for your help!

Mike

 

[Nat Vascular]

Thanks so much for all your help. It has been very insightful and valuable.
I really appreciate it!!

Nat

Thanks again for the suggestion. Two follow-up questions.

1) What does the Cancel = True do?

Stops the update from executing so the record is not added to the form's
recordset. The reason to do this is the data in the record is either
incorrect or incomplete and you want to prevent adding a record until it is
complete and correct.

2) If I decide to use the BeforeInsert at the form level as you have
suggested....I believe I can go back to using bound controls and set the
field at the database level so that Nulls are OK allowing me to reset the
control to Null at the control level becuase now I will be forcing a not Null
at the form level. Correct?

The problem with using bound controls for a lookup control is that when you
enter a value in the control to perform the search, you have now changed the
value in the current record. In most cases, this is probably not what you
want. The only time I use combos as bound controls is when I am looking up a
value for that specific field in the current record.

Thanks again,

Nat

Well, you may not really like the answer, but the easiest way is to use the
form's Before Insert event (since this really will only apply to new records)
and do the checking there. You can use the code you posted, but rather than
checking the value of the combo box, check the value of the hidden text box.
You can also cancel the before insert. The record will not be updated and
you can trhow the focus back to the offending control:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Cancel = True
Forms![Case Data Form]![UB_Referring Physician].SetFocus
End If


:

Thanks so much....that worked sort of. While I can set the unbound control
to none it created a host of additional challenges. When scrolling through
old records, these unbound controls keep their latest value. I solved that
by adding the following code to the forms OnCurrent event:

Me.UB_Referring_Physician = Referring_Physician '

UB_Referring_Physician is the new unbound combo and Referring_Physician is
the new bound text box (that I have set as invisible....fyi)

That seems to do the trick. Now the issue I'm having is forcing a must
enter condition for the Unbound_Combo. What I have tried is the following
code at the OnExit event:

If IsNull(Me.UB_Referring_Physician) Then
MsgBox "Please Select A Referring Physician"
Forms![Case Data Form]![UB_Referring Physician].SetFocus

End If

This catches the null condition correctly, displays the message box
correctly and moves the focus but then the focus continues to move to the
control I clicked on or the next control in the tab order if I hit the tab or
enter key. How do I get the focus to stop and go back to the
UB_Referring_Physician control?

:

Part of the problem is that the combos are bound controls. When you change a
value in a bound combo, it tries to update the field the combo is bound to.
It is usually better to use unbound combos for doing searches like this. I
would suggest you unbind the combos and crerate 2 text boxes to bind to the
recordset fields.
You can use the After Update event of each combo to populate the bound text
box.

Now, as to the base question. When you are using cascading combos, the
technique is to requery the 2nd combo in the After Update event of the first
query:

Me.referring_physician.Requery
:

Thanks, in advance, so much for your time and help!

I have two bound combo boxes on a for. They are referring_group and
referring_physician. After a selection of referring_group, a query runs that
returns a list of referring_physicians that belong to the referring_group
selected. Both combo boxes are set to must-enter and no zero-length string
and both are limited to the list in the combo box. What I am trying to get
to happen is when the referring_group is changed, the referring_physician is
reset to Null, and requeried based on the new selection in the referral_group
control. My approach is to place an event procedure at the AfterUpdate event
of the referring_group combo box:

me.referring_physician.requery 'this requeries the control and works fine
DoCmd.GoToControl "referring_physician 'this moves focus and work fine
me.referring_physician = Null 'to reset control and force re-selection
based on new list.

This one errors telling me that because the field is must enter it cannot be
set to Null. This doesn't make total sense to me. The control doesn't write
the record to the database until after the entire form is complete....why
would it check that now? Is there another way to reset this control so the
old selection is removed?

I looked for a command like:

me.referring_physician.reset

I tried:

me.referring_physician.undo - that didn't work I suspect because it was
issued from the other control and there was really nothing to undo. I would
think this is a pretty common thing to try and do and someone has had to
figured out an easy way to accomplish.

Thanks so much for your help!

Mike