Combo goes blank on form requery

I don't remember this being that hard. Or maybe it's just early senility.

On a simple bound continuous form I have a combo for filtering the form by
customer. The form record source is a sql statement that checks the combo
and filters accordingly. The combo simply requeries the form.

More often than not the selection in the combo disappears after the requery,
though not always. The only consistence seems to be that if the result is no
records then the selection is gone, but if there are records then it may or
may not remain.

And sometimes if I click on the combo the selection will return.

Life gets stranger by the day. Is this all a test? Is someone really
watching and designing these little tests to see if we crack under the
pressure. I laugh...ha..ha..ha. I shall never break, though I may bend I
shall always return to an upright position. Am I raving. No. I am alright.
Everything is in place. I am home and still.......what was that?
Wait....footsteps....aaaargh........

Jeff
""
http://www.asken.com.au

Re: Combo goes blank on form requery

Hi Jeff

There are several variations on this little issue.

There's a valid case where the combo's Bound Column is not its display
column, and the RowSource is filtered in such a way that it does not have
the record it needs to display. (Given your experience, I doubt this is your
issue.)

Access does have some serious display problems where a form returns no
records (not even the new record), and so the Detail section goes completely
blank. In this case, it becomes bamboozled about any controls in the Form
Header or Form Footer. More info and a sample:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

There is also a timing bug in Access where it fails to display the data
correctly after a requery. This occurs in all versions of Access, but only
in combos where the bound column is not the display column. If the combo is
selected, the selection may even be the width of the characters, but the
characters are not visible. I don't know how to prevent this. A Requery in
Form_Current won't do it (seems to be too early.)

There was one quirky workaround that involved placing a text box over (or
was it partially behind?) the combo, which seemed to affect the way Access
updates the screen. Can't recall if you had to set the Transparent property
to get this to work.

It's darned annoying. The only real workaround I know of is not to make the
combo's bound column the first visible column. For example, intead of a
lookup table like this:
CategoryID AutoNumber
Category Text
use:
CategoryID Text
SortOrder Number
The foreign key is then a Text field, so you don't hide it, so the problem
doesn't occur.

HTH

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff" wrote in message
news:4725682b$0$17150$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>I don't remember this being that hard. Or maybe it's just early senility.
>
> On a simple bound continuous form I have a combo for filtering the form by
> customer. The form record source is a sql statement that checks the combo
> and filters accordingly. The combo simply requeries the form.
>
> More often than not the selection in the combo disappears after the
> requery, though not always. The only consistence seems to be that if the
> result is no records then the selection is gone, but if there are records
> then it may or may not remain.
>
> And sometimes if I click on the combo the selection will return.
>
> Life gets stranger by the day. Is this all a test? Is someone really
> watching and designing these little tests to see if we crack under the
> pressure. I laugh...ha..ha..ha. I shall never break, though I may bend I
> shall always return to an upright position. Am I raving. No. I am alright.
> Everything is in place. I am home and still.......what was that?
> Wait....footsteps....aaaargh........
>
> Jeff
> ""
> http://www.asken.com.au

Re: Combo goes blank on form requery

This is just strange. More timing than anything I think.

The combo is in the header and lookup has an alpha code for id. Combo is set
to display code.

Pick a customer and it may or may not display the selection (code). Even
when selecting the same code it can vary. If it is not shown clicking in the
combo text area usually shows the selection.

Anyway, got around it by showing each selection in an additional text box so
the user at least knows what was last selected if it disappears from the
combo.

Hate workarounds to bugs but they seem to be becoming the standard practice.

Years ago I started my career programming games in Assembler for 6502
processors (Commodore 64). This may be an old fools delusion but working
with Assembler was a hell of a lot easier than things like VB. We had more
control over the nuts and bolts, so to speak. Now too many assumptions are
made about what it thinks you really want to do, and gets it wrong a lot of
the time.

I'm rambling again.

Jeff
"Access dreaming"
www.asken.com.au

"Allen Browne" wrote in message
news:47256ef9$0$17174$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
> Hi Jeff
>
> There are several variations on this little issue.
>
> There's a valid case where the combo's Bound Column is not its display
> column, and the RowSource is filtered in such a way that it does not have
> the record it needs to display. (Given your experience, I doubt this is
> your issue.)
>
> Access does have some serious display problems where a form returns no
> records (not even the new record), and so the Detail section goes
> completely blank. In this case, it becomes bamboozled about any controls
> in the Form Header or Form Footer. More info and a sample:
> Incorrect display of data
> at:
> http://allenbrowne.com/bug-06.html
>
> There is also a timing bug in Access where it fails to display the data
> correctly after a requery. This occurs in all versions of Access, but only
> in combos where the bound column is not the display column. If the combo
> is selected, the selection may even be the width of the characters, but
> the characters are not visible. I don't know how to prevent this. A
> Requery in Form_Current won't do it (seems to be too early.)
>
> There was one quirky workaround that involved placing a text box over (or
> was it partially behind?) the combo, which seemed to affect the way Access
> updates the screen. Can't recall if you had to set the Transparent
> property to get this to work.
>
> It's darned annoying. The only real workaround I know of is not to make
> the combo's bound column the first visible column. For example, intead of
> a lookup table like this:
> CategoryID AutoNumber
> Category Text
> use:
> CategoryID Text
> SortOrder Number
> The foreign key is then a Text field, so you don't hide it, so the problem
> doesn't occur.
>
> HTH
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jeff" wrote in message
> news:4725682b$0$17150$5a62ac22@per-qv1-newsreader-01.iinet.n et.au...
>>I don't remember this being that hard. Or maybe it's just early senility.
>>
>> On a simple bound continuous form I have a combo for filtering the form
>> by customer. The form record source is a sql statement that checks the
>> combo and filters accordingly. The combo simply requeries the form.
>>
>> More often than not the selection in the combo disappears after the
>> requery, though not always. The only consistence seems to be that if the
>> result is no records then the selection is gone, but if there are records
>> then it may or may not remain.
>>
>> And sometimes if I click on the combo the selection will return.
>>
>> Life gets stranger by the day. Is this all a test? Is someone really
>> watching and designing these little tests to see if we crack under the
>> pressure. I laugh...ha..ha..ha. I shall never break, though I may bend I
>> shall always return to an upright position. Am I raving. No. I am
>> alright. Everything is in place. I am home and still.......what was that?
>> Wait....footsteps....aaaargh........
>>
>> Jeff
>> ""
>> http://www.asken.com.au
>