Is this a bug in Access2007?

[Mark Andrews]

I have a button on a form that I want to bring up another little form (that
is a simple datasheet) for the user to add new items etc....

My code is:
Private Sub cmdEditEmployees_Click()
DoCmd.OpenForm "frmLookupEmployee",acFormDS , , , , acDialog
Me.ComboEmployee.Requery
End Sub

When the code runs it gets to the second line before the user had time to
add a new item, so after the user adds a new item in frmLookupEmployee and
comes back the ComboEmployee control does not show the
new employee (because the refresh happened before the table was updated).


If I take out the acFormDS the form opens in normal form mode (even though
I only allow datasheet and the default is datasheet), however the running of
the code works differently and the second line runs after the form is
closed, so the refresh works fine.

Private Sub cmdEditEmployees_Click()
DoCmd.OpenForm "frmLookupEmployee", , , , , acDialog
Me.ComboEmployee.Requery
End Sub

What's the best solution? I can refresh in the close event of the datasheet
form (however I call this form from numerous places, so I would rather not
do it that way).

Any help is appreciated,
Mark

 

[Dirk Goldgar]

I have a button on a form that I want to bring up another little form (that
is a simple datasheet) for the user to add new items etc....

My code is:
Private Sub cmdEditEmployees_Click()
DoCmd.OpenForm "frmLookupEmployee",acFormDS , , , , acDialog
Me.ComboEmployee.Requery
End Sub

When the code runs it gets to the second line before the user had time to
add a new item, so after the user adds a new item in frmLookupEmployee and
comes back the ComboEmployee control does not show the
new employee (because the refresh happened before the table was updated).


If I take out the acFormDS the form opens in normal form mode (even
though I only allow datasheet and the default is datasheet), however the
running of the code works differently and the second line runs after the
form is closed, so the refresh works fine.

Private Sub cmdEditEmployees_Click()
DoCmd.OpenForm "frmLookupEmployee", , , , , acDialog
Me.ComboEmployee.Requery
End Sub

That is *very* interesting. It works that way in Access 2003 also: if I
open the form in normal view, the acDialog WindowMode argument is applied,
but if I open it in datasheet view it isn't. I never noticed that before.
My guess is that Microsoft would say "This behavior is by design," but I
can't find any documentation of it in the help file.

What's the best solution? I can refresh in the close event of the
datasheet form (however I call this form from numerous places, so I would
rather not do it that way).

One way would be to pass frmLookupEmployee an argument via OpenArgs that
would specify the form and (optional) control name to be requeried. Then it
would be the responsibility of frmLookupEmployee to check to see if that was
passed, and then if it was, parse out the form and control name and requery
it upon closing.

Another option would be to design frmLookupEmployee to be in Continuous
Forms view, formatted to look like a datasheet if that's what you prefer,
and then open it in normal view so that the WindowMode argument works
properly.

Another option would be to make the current frmLookupEmployee into a
datasheet subform on a main form that serves solely as a wrapper, and then
open that wrapper form in dialog mode.

Another (not so good) option would be to leave frmLookupEmployee as it is,
but have your calling code loop (with DoEvents) until the form is closed.

So there's a smorgasbord of options for you. Thanks for calling this quirky
behavior to my attention.

 

[Mark Andrews]

Yea I guess I never noticed that it did that in Access 2003 either.

Currently I just went with requerying on form_close. I like your idea of
using openargs to pass in the
form/control to requery, at this point I just hardcoded the values and put
OnError Resume Next (because some forms
can be called from a couple places).

Well the best option would be for Microsoft to fix the behavior and make
things consistant.

Thanks for the help,
Mark

 

[david]

My guess is that Microsoft would say "This behavior is by design," but I

can't find any documentation of it in the help file.

No, not by design. Worked correctly in A2 and A97. Just another
misplaced 'optimisation', or a broken part of the implementation
of VBA as a separate component. It's at a point where the control
has to pass back and forth between the VBA object and the Access
object.

One solution is to use a datasheet subform on an acDialog main form.
More work, more problems, more bugs, but allows you to put extra
information and do lots of things as controls on the main form.

(david)