access 97 to 2000 conversion issue - combo box blank

[Tom R]

Dear all

Am currently converting my access 97 db to a2K. Most is
fairly fluffy
however have come across a few oddities...

I have an unbound combo box on a form. The form itself is
bound to an
EMPTY table (initially). When I try select data in the
combo box,
although it gets selected (I can see the consequences of
that in other
text boxes), it does not display!!! This was all fine in
A97.

I've mucked about with this and noticed the following:
1. If i set allow additions to yes, you can then see the
data in the
combo (this was not needed for A97, so what am I
misunderstanding?)
2. Even if the combo is a simple value list, this is still
not
displayed.

Please help, puzzled of waterloo

(And on a sidenote, I've had to change a few of my set Set
rst =
Me.RecordsetClone to Set rst = Me.Recordset.Clone to get
stuff to work. Any hints?)

Cheers
Tom

 

[Allen Browne]

Regarding the recordsets issue, Access 97 has a default reference to the DAO
library, but a new Access 2000 library defaults to the ADO library. Details:
http://users.bigpond.net.au/abrowne1/ser-38.html

If your form loads with no records and AllowAdditions set to No, its Detail
section will appear completely blank. If you place the unbound combo in the
Form Header section (View menu in Form Design view), it should be visible
when the form opens, and you should be able to respond to the combo's
AfterUpdate event to load/filter/move to the desired record.

Please post back if I have not understood your questions correctly.

 

[Allen Browne]

Access can be quirky about showing the values in the controls in the Form
Header section when the Detail section is completely blank. I've found that:
Me.Refresh
helps.

Another display issue involves combos that have the bound column zero-width,
and the RowSource changed dynamically--particularly in Form_Current. You can
finish up with text visibly selected the correct width, but not showing up
in the combo at all. If you Debug.Print the Len() of the combo's value or
text it's correct, but it's just not showing the actual text. You can
sometimes fudge this by overlapping another control over the combo with some
transparency involved, which seems to trick Access into repainting it
correctly, but that's not really a solution. It seems to be a timing issue
in that the combo's RowSource has not been loaded when Form_Current occurs,
and the repaint does not occur after the data is made available. Using a
combo with the bound column visible is the only real solution.

Both those issues arise in A97 as well as A2000 and A2002. If your issue
doesn't match these, it may be another one?

 

[Tom Roberts]

Dear Allen,

thanks a lot for your advice.

I tried the me.refresh, but that didn't sort it out.

Firstly, when the form is loaded, the default value should (well it
does in A97) appear in the combo in the header. it does not.
Secondly, upon selection, I would expect the combo to show the value
once it has been selected. Again it does work in A97.

I have created a very small test form that illustrates this. Its about
144kb. If it would be helpful to look at that I would be very
grateful, although I understand that all this help is straight off
your own back. Is there anything further I should do (in terms of
looking for advice and fiddling with the code), or perhaps I should
rebuild the whole thing from scratch?

Thanks a lot for your time, it has been very helpful.


Cheers
Tom

Access can be quirky about showing the values in the controls in the Form
Header section when the Detail section is completely blank. I've found that:
Me.Refresh
helps.

Another display issue involves combos that have the bound column zero-width,
and the RowSource changed dynamically--particularly in Form_Current. You can
finish up with text visibly selected the correct width, but not showing up
in the combo at all. If you Debug.Print the Len() of the combo's value or
text it's correct, but it's just not showing the actual text. You can
sometimes fudge this by overlapping another control over the combo with some
transparency involved, which seems to trick Access into repainting it
correctly, but that's not really a solution. It seems to be a timing issue
in that the combo's RowSource has not been loaded when Form_Current occurs,
and the repaint does not occur after the data is made available. Using a
combo with the bound column visible is the only real solution.

Both those issues arise in A97 as well as A2000 and A2002. If your issue
doesn't match these, it may be another one?

....Trunc

 

[Tom Roberts]

Thanks for the help. This is what Microsoft say also:

Hi Tom -

This is Steven from Microsoft Access Technical Support replying to your
Access/combobox issue.

The behavior that you see with comboboxes on a bound Access 2000 form
when AllowAdditions is set to No is a known problem. There are no
service releases or packs available for resolving the issue. You must
apply a workaround of your choice to avoid the problem.

Along with the workarounds that you may have already been given via the
newsgroups, another workaround is to create an unbound mainform that
contains your unbound combobox. Then place your other form that is based
on an empty table as a subform into the new mainform.

Because this is a known problem in Access 2000, your warranty or charge
for this incident has been rolled back.

Thank you for using Microsoft Access,

- Steven Parsons
- Microsoft Access Technical Support

Tom, this is definitely a bug in Access 2000.

I've just loaded your database into an unpatched version of Access 2000, the
the results are completely wrong. The combo appears blank as you say.

If you:
Debug.Print Forms.frmCombo.Combo2.Value
Access reports that it does have the value that is not shown.

If you ask for the Text of the combo in its Dbl Click event:
Private Sub Combo2_DblClick(Cancel As Integer)
Debug.Print "Len(Text): " & Len(Me.Combo2.Text)
End Sub
Access generates an error: you can't read the property unless the control
has focus. Well, it's right that the Text property is only available when
the control has the focus, but the Double Click event *cannot* occur without
the control having focus! It's thoroughly confused.

Because I know that a requery is sometimes needed to resolve some of these
display issues in a form that has no visible detail, I tried this:
Private Sub Combo2_AfterUpdate()
Me.Requery
End Sub
With that code, you can see the value in the combo flash momentarily during
the requery, but then the bug takes over and removes the display again.

Next I added a text box to the form header section, and gave it these
properties:
Name Text5
Control Source =[Combo2]
This *should* cause the text box to show the value of the combo, since the
value is available in the Debug window. The text box is also blank. If you
try this in the Immediate window:
? Forms.frmCombo.Text5.Value
Access says it has no value. Now that's correct for the non-existent text
boxes in the detail section, but wrong for the visible text box in the form
header.

In short, you have not done anything wrong. It is Access 2000 that is
confused.

I haven't tried searching the knowledgebase at support.microsoft.com to see
if one of the service patches has fixed this. You can try that if you like.
If not, your choices are to upgrade to Access 2002 where the bug has been
fixed, or to leave the AllowAdditions property to Yes (so Access has a row
to display) but cancel the BeforeInsert event if the user actually tries to
insert a record.

 

[Allen Browne]

Thanks, Tom.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

Thanks for the help. This is what Microsoft say also:

Hi Tom -

This is Steven from Microsoft Access Technical Support replying to your
Access/combobox issue.

The behavior that you see with comboboxes on a bound Access 2000 form
when AllowAdditions is set to No is a known problem. There are no
service releases or packs available for resolving the issue. You must
apply a workaround of your choice to avoid the problem.

Along with the workarounds that you may have already been given via the
newsgroups, another workaround is to create an unbound mainform that
contains your unbound combobox. Then place your other form that is based
on an empty table as a subform into the new mainform.

Because this is a known problem in Access 2000, your warranty or charge
for this incident has been rolled back.

Thank you for using Microsoft Access,

- Steven Parsons
- Microsoft Access Technical Support

Tom, this is definitely a bug in Access 2000.

I've just loaded your database into an unpatched version of Access 2000, the
the results are completely wrong. The combo appears blank as you say.

If you:
Debug.Print Forms.frmCombo.Combo2.Value
Access reports that it does have the value that is not shown.

If you ask for the Text of the combo in its Dbl Click event:
Private Sub Combo2_DblClick(Cancel As Integer)
Debug.Print "Len(Text): " & Len(Me.Combo2.Text)
End Sub
Access generates an error: you can't read the property unless the control
has focus. Well, it's right that the Text property is only available when
the control has the focus, but the Double Click event *cannot* occur without
the control having focus! It's thoroughly confused.

Because I know that a requery is sometimes needed to resolve some of these
display issues in a form that has no visible detail, I tried this:
Private Sub Combo2_AfterUpdate()
Me.Requery
End Sub
With that code, you can see the value in the combo flash momentarily during
the requery, but then the bug takes over and removes the display again.

Next I added a text box to the form header section, and gave it these
properties:
Name Text5
Control Source =[Combo2]
This *should* cause the text box to show the value of the combo, since the
value is available in the Debug window. The text box is also blank. If you
try this in the Immediate window:
? Forms.frmCombo.Text5.Value
Access says it has no value. Now that's correct for the non-existent text
boxes in the detail section, but wrong for the visible text box in the form
header.

In short, you have not done anything wrong. It is Access 2000 that is
confused.

I haven't tried searching the knowledgebase at support.microsoft.com to see
if one of the service patches has fixed this. You can try that if you like.
If not, your choices are to upgrade to Access 2002 where the bug has been
fixed, or to leave the AllowAdditions property to Yes (so Access has a row
to display) but cancel the BeforeInsert event if the user actually tries to
insert a record.