combo box value used to filter list

  • Thread starter Thread starter Teresa Thomas via AccessMonster.com
  • Start date Start date
T

Teresa Thomas via AccessMonster.com

Greetings,

I have several forms where I use an unbound combo box selection to filter
the results on the page. 'On change' of the combo box I set the filter
for the form. If the filter results in no values on the form, the combo
box value doesn't show up (even though the value is truly there because I
can reference (and use it) in building a sql statement and subsequent
insert (as a result of another cmd button to add values on the same form).

I am doing something wrong, but doing it consistently as the behavior is
very similar on all of these forms...any ideas?
Thanks so much in advance!
Teresa
 
Access keeps the original value in memory even if you have change the filter,
you need to add this to your after update event in the combo box:

me.MyFieldForCriteria=null
 
Thank you for your reply, but I'm sorry I don't follow your answer.

Say my combo field name is comboManagerList

and based on selection of the manager in the combo, I then set the filter
to:
 
Thank you for your reply, but I'm sorry I don't follow your answer.

Say my combo field name is comboManagerList

and based on selection of the manager in the combo, I then set the filter
to:
 
Thank you for your reply, but I'm sorry I don't follow your answer.

Say my combo field name is comboManagerList and the page lists the
employees for the manager selected.

and based on selection of the manager in the combo, I then set the filter
to:
Me.Filter = "[tblUserID].[ManagerID] = " & me.ComboManagerList
Me.FilterOn = True

The problem is that if the filter results in no values (i.e. that manager
has no employees), then the comboManagerList doesn't display the value that
was selected and is just blank. Suffice it to say that I would like it to
continue displaying the value that was selected so that I can allow the
user to make other selection on the page( such as adding employee's to that
manager). Like I mentioned previously, the value is indeed in
me.Combomanagerlist, because I am able to successfully use it in the add
process..I just would also like it to display it in the combo.

Thanks again for your assistance.
Teresa
 
I found my own answer!!!

Flaws in MS Access
Provided by Allen Browne, January 2004
----------------------------------------------------------------------------
----

Incorrect display of data
The detail section of a form will be completely blank if a) there are no
records to display, and b) no new records can be added. As a design
decision, that is acceptable. However, controls in the Form Header or Form
Footer sections are visible but do not display correctly.

To demonstrate the problem with AccessFlaws.zip, open the MissingDisplay
form in design view. Its Record Source matches no records, and Allow
Additions is set to No. That means we will not see the text boxes in the
detail section, but look at the two text boxes in the Form Header section:

Text0 is bound to the expression, "Hello world";
Text1 is unbound, so can accept a value.
Open the form in form view. Text0 is visible, but does it display its
expression? Attempting to read the value of the text box results in a
nonsense message that the expression does not have a Value (Error 2427).
Attempting to read the Text in the text box results in another message
(Error 2185) that it must have focus to read the Text property - even
though the control does have focus (as demonstrated by the Show Text0
command button). Access is thoroughly confused.

Click the command button Set Text1. The message, "There IS data here."
should appear in Text1. It does not display. Clicking Show Text1 proves the
data is in the control, though Access does not display it. You may even be
able to click in Text1 and move the cursor along the invisible characters.

There are other cases where Access fails to display data also. Another
common one involves a combo box with a zero-width bound column and a
RowSource assigned dynamically in the form's Current event. Again, the
combo displays completely white even though the value can be read from its
Column() property. If the combo has focus, the reverse-video selection is
the right number of characters wide, but Access is not displaying the text.
This appears to be a timing issue, i.e. the RowSource provides the Column
data after the screen has been drawn. (It is different from the perfectly
valid case where the Row Source is restricted in such a way that there is
nothing in the Column for Access to display.)

Access 2000 and 2002 had another display problem if a memo field was placed
in the footer of a continuous form. For an example of this bug, download
BadMemoFooter.zip. This problem seems to be fixed in Acc. 2003.
 
Back
Top