Unbound combobox invalid datatype error

[DanKnight]

I have 3 unbound comboboxes in the form header that I'm using in sequence to
act as a filtering device for a form.
The first combobox allows the user to select the field for filtering from an
autogenerated list based on the controls in the form's detail section.
The second allows the user to select the criteria based on a hardwired list.
The value list of the 3rd combobox is auto-generated based on the data
entered in chosen field of the form's recordsource. When this combobox is
updated (ie: a value selected) it creates a filter string that filters both
the form and a list box displaying the ID field of the form.

The process works fine when the form is the primary form (ie: opened by
itself), but the form is to reside (unbound) on a main form. In that
situation, the user can select the field and the criteria, but when they
select the value, they receive "The value you selected isn't valid for this
field" error, which may be the case IF the combobox was bound to a field; but
it isn't.

Suggestions...

 

[Jeanette Cunningham]

When using subforms, to get a value from a field/ control on the subform use
=Me.SubformControlName.Form.ControlName

Note that the name of the subform control on the main form may be different
from the actual form that is in the subform control.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[DanKnight]

Jeanette,
Thanks for your advice. However, I may have mislead in my original post, I'm
not using a subform in a parent-shild relationship. The main form is simply a
switchboard device with labels that allow navigation between various unbound
subforms (aka Outlook's navigation pane). The filtering comboboxes are
controls on a particular form that are used to filter the data of that
particular form.

The error message activates prior to any of the code in the Before Update or
After Update events of the combobox are fired. What is odd is that the
combobox is not bound to any data field, so it seams odd that the DataType
invalid error would fire.

When using subforms, to get a value from a field/ control on the subform use
=Me.SubformControlName.Form.ControlName

Note that the name of the subform control on the main form may be different
from the actual form that is in the subform control.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have 3 unbound comboboxes in the form header that I'm using in sequence
to

[quoted text clipped - 20 lines]

Suggestions...

 

[Jeanette Cunningham]

You might consider that combo has become corrupt.
Create a new one to do the same thing and if the new one works ok, delete
the one with the errors.

So does the error message appear when the combo gets focus? or do you know
when the error gets triggered?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
Thanks for your advice. However, I may have mislead in my original post,
I'm
not using a subform in a parent-shild relationship. The main form is
simply a
switchboard device with labels that allow navigation between various
unbound
subforms (aka Outlook's navigation pane). The filtering comboboxes are
controls on a particular form that are used to filter the data of that
particular form.

The error message activates prior to any of the code in the Before Update
or
After Update events of the combobox are fired. What is odd is that the
combobox is not bound to any data field, so it seams odd that the DataType
invalid error would fire.

When using subforms, to get a value from a field/ control on the subform
use
=Me.SubformControlName.Form.ControlName

Note that the name of the subform control on the main form may be
different
from the actual form that is in the subform control.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have 3 unbound comboboxes in the form header that I'm using in sequence
to

[quoted text clipped - 20 lines]

Suggestions...

 

[DanKnight via AccessMonster.com]

Jeanette,
The error is triggered as soon as a selection is made, but before the
BeforeUpdate event. Perhaps my understanding of the Event sequence is
incorrect, but, I thought the BeforeUpdate is the first event after selection
and before the control's value is updated.

I use the control's value in the AfterUpdate event, along with the other
combo's values to create a filter string to filter the form's recordset.

I don't think it's corrupt because when I open the form by itself (not part
of the Main Switchboard form), everything functions perfectly.

You might consider that combo has become corrupt.
Create a new one to do the same thing and if the new one works ok, delete
the one with the errors.

So does the error message appear when the combo gets focus? or do you know
when the error gets triggered?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
Thanks for your advice. However, I may have mislead in my original post,

[quoted text clipped - 28 lines]

 

[John W. Vinson]

Jeanette,
The error is triggered as soon as a selection is made, but before the
BeforeUpdate event. Perhaps my understanding of the Event sequence is
incorrect, but, I thought the BeforeUpdate is the first event after selection
and before the control's value is updated.

I use the control's value in the AfterUpdate event, along with the other
combo's values to create a filter string to filter the form's recordset.

I don't think it's corrupt because when I open the form by itself (not part
of the Main Switchboard form), everything functions perfectly.

Please post the actual code in your Before and AfterUpdate events. It's
possible that you have an invalid form or subform reference.

 

[DanKnight via AccessMonster.com]

The solution is found, and as usual, it is a silly minor - but significant -
issue.
The criteria cbo had a default value set to "equals", yet it also has code
for the "AfterUpdate" event that adds the criteria to the Filter string. By
having the default value set to "equals" the AfterUpdate isn't triggered and
the criteria is missing from the filter string. I'm not sure why that
produces a datatype violation error, but...

So now my solution is to not have a default criteria value, requiring the
user to select a criteria (the 3rd cbo - the filter value is disabled until a
criteria is selected). An alternative is to call the "AfterUpdate" for the
criteria when the filter value cbo gets focus, but that requires code to
confirm that the AfterUpdate for the criteria hasn't been triggered yet. I
felt it best - and easiest - to simply have no default criteria and require
the user to select one.

The other good thing from this problem is I found some interesting filtering
samples on Allen Browne's site as well.

Jeanette, thanks for your assistance on this.

Dan

You might consider that combo has become corrupt.
Create a new one to do the same thing and if the new one works ok, delete
the one with the errors.

So does the error message appear when the combo gets focus? or do you know
when the error gets triggered?

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Jeanette,
Thanks for your advice. However, I may have mislead in my original post,

[quoted text clipped - 28 lines]