A2K3 SubForm: Refresh, Requery? Arrrgh!

[Tim Marshall]

Here's the situation. A form, frmSetUp, with a subform control called
subExplain with a source object form frmSetUpSubDefineSides. The source
object is a bound form, displaying a few records, no edit, adds,
filters, etc, are permitted on the subform.

An add or edit button on the subform is clicked opening an unbound form,
frmSetUpSideAdd, (populated with values from the above subform if this
is an edit of an existing record) for data entry. The command in both
cases is DoCmd.OpenForm "frmSetUpSideAdd", acNormal, , , , acDialog

Data is added/changed on this dialog mode form and then an OK button
creates an insert or update statement, and I run the execute method in
ADODB. Works just fine.

However, the subform on frmSetUp does not refresh until I physically
click on the subform or prress F9: I've tried the following and several
setfocus type fiddling around:

docmd.close 'for the acDialog add/edit form, frmSetUpSideAdd

Forms!frmSetup.subExplain.Requery

Forms!frmSetup.subExplain.SetFocus

Forms!frmSetup.subExplain.Form.Refresh

I've also tried tossing in SendKeys "{F9}" and a Recalc and neither
work. I've also tried respecifying the source object, ie:

Forms!frmsetup.subExplain.SourceObject = "frmSetUpSubDefineSides"

But that won't work either.

Please help. A2K3 on Win XP SP1

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Eric Schittlipz]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cj**********@coranto.ucs.mun.ca...

Here's the situation. A form, frmSetUp, with a subform control called
subExplain with a source object form frmSetUpSubDefineSides. The source
object is a bound form, displaying a few records, no edit, adds,
filters, etc, are permitted on the subform.

An add or edit button on the subform is clicked opening an unbound form,
frmSetUpSideAdd, (populated with values from the above subform if this
is an edit of an existing record) for data entry. The command in both
cases is DoCmd.OpenForm "frmSetUpSideAdd", acNormal, , , , acDialog

Data is added/changed on this dialog mode form and then an OK button
creates an insert or update statement, and I run the execute method in
ADODB. Works just fine.

However, the subform on frmSetUp does not refresh until I physically
click on the subform or prress F9: I've tried the following and several
setfocus type fiddling around:

docmd.close 'for the acDialog add/edit form, frmSetUpSideAdd

Forms!frmSetup.subExplain.Requery

Forms!frmSetup.subExplain.SetFocus

Forms!frmSetup.subExplain.Form.Refresh

I've also tried tossing in SendKeys "{F9}" and a Recalc and neither
work. I've also tried respecifying the source object, ie:

Forms!frmsetup.subExplain.SourceObject = "frmSetUpSubDefineSides"

But that won't work either.

Please help. A2K3 on Win XP SP1

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Looks like one option you haven't tried is:

Forms!frmSetup.subExplain.Form.Requery

[Tim Marshall]

Eric Schittlipz wrote:

Looks like one option you haven't tried is:

Forms!frmSetup.subExplain.Form.Requery

Thanks, Eric, but that by itself, doesn't work either. 8(

I'm really hoping I've hit on some kind of Win XP themes bug - either
that or I've messed things up beyond belief! 8)

Thanks again - any other ideas?
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Eric Schittlipz]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cj**********@coranto.ucs.mun.ca...

Eric Schittlipz wrote:

Looks like one option you haven't tried is:

Forms!frmSetup.subExplain.Form.Requery

Thanks, Eric, but that by itself, doesn't work either. 8(

I'm really hoping I've hit on some kind of Win XP themes bug - either
that or I've messed things up beyond belief! 8)

Thanks again - any other ideas?
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Just in case you are confusing the name of the subform control and the name
of the form itself, can you verify this:
Open the main form, then press Ctrl+G and type the following into the
immediate window and hit return.
?Forms!frmSetup.subExplain.Form.Name
This should return "frmSetUpSubDefineSides", and if it does, I can't see why
Forms!frmSetup.subExplain.Form.Requery fails to requery the form.
Considering that you are opening forms using acDialog, you may find the line
of code is not even run - at least not when you expect it to. Perhaps you
could step through the code to be sure this is running but 'does not work'.

As a general comment, I would add that in these sorts of situations, I get
the popup form to be bound. The code finds the ID of the main record and
the currently selected related record (or if creates a new record and gets
this ID). These two numbers are passed to the popup form as comma-separated
OpenArgs. Now I am able to have my popup form show all related records and
automatically go the selected (or new) record by using
RecordsetClone.FindFirst method. The code in the main form would then be:

DoCmd.OpenForm "frmSetUpSideAdd", acNormal, , , , acDialog
Me.subExplain.Form.Requery

This might be something you could consider.

[Tim Marshall]

Eric Schittlipz wrote:

can you verify this:
Open the main form, then press Ctrl+G and type the following into the
immediate window and hit return.
?Forms!frmSetup.subExplain.Form.Name
This should return "frmSetUpSubDefineSides",
Yes, this works as one would expect... 8(
Forms!frmSetup.subExplain.Form.Requery fails to requery the form.
Considering that you are opening forms using acDialog, you may find the line
of code is not even run - at least not when you expect it to. Perhaps you
could step through the code to be sure this is running but 'does not work'.

What's interesting is that when I step through the code and then turn
off the watches, the sub form behaves as expected/designed with
Forms!frmSetup.subExplain.Form.Requery. Then when the application is
turned on again, I have the same problem with requery - it does not work.

I'm not sure if your use of a bound popup would work in my case? I have
table constraints which won't let me just create a record and then open
the pop up. I could be misinterpreting what you're saying though.

But I don't understand why this is a problem - I did this many times in
A97 without a problem except with respect to forms where the
recordsource was an Oracle passthrough query and even then, the changing
of the subform's sourceobject took care of things.

This is extremely depressing - A2K3 should come with a complimentary
bottle of happy pills...

Another thought - are there any service packs for A2K3? My help just
says Access 2003 (11.5614.5606).
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

[Eric Schittlipz]

"Tim Marshall" <TI****@antarctic.flowerpots> wrote in message
news:cj**********@coranto.ucs.mun.ca...

Eric Schittlipz wrote:

can you verify this:
Open the main form, then press Ctrl+G and type the following into the
immediate window and hit return.
?Forms!frmSetup.subExplain.Form.Name
This should return "frmSetUpSubDefineSides",
Yes, this works as one would expect... 8(

Forms!frmSetup.subExplain.Form.Requery fails to requery the form.
Considering that you are opening forms using acDialog, you may find the line of code is not even run - at least not when you expect it to. Perhaps you could step through the code to be sure this is running but 'does not

work'.
What's interesting is that when I step through the code and then turn
off the watches, the sub form behaves as expected/designed with
Forms!frmSetup.subExplain.Form.Requery. Then when the application is
turned on again, I have the same problem with requery - it does not work.

I'm not sure if your use of a bound popup would work in my case? I have
table constraints which won't let me just create a record and then open
the pop up. I could be misinterpreting what you're saying though.

But I don't understand why this is a problem - I did this many times in
A97 without a problem except with respect to forms where the
recordsource was an Oracle passthrough query and even then, the changing
of the subform's sourceobject took care of things.

This is extremely depressing - A2K3 should come with a complimentary
bottle of happy pills...

Another thought - are there any service packs for A2K3? My help just
says Access 2003 (11.5614.5606).
--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

Perhaps I have seen something like that before... I have dim memories of
getting around an issue like this by re-specifying the subform's
recordsource property which forced it to requery. I saw from your earlier
post that you had re-specified the SourceObject, but you didn't say you'd
tried this one:

Forms!frmSetup.subExplain.Form..RecordSource = "qryMyQuery"

Worth a last try?

[Tim Marshall]

Eric Schittlipz wrote:

Forms!frmSetup.subExplain.Form..RecordSource = "qryMyQuery"

Worth a last try?

I was excited about this one, but alas, no. Also I installed the 2003
SP1 to no avail either. 8(

I really appreciate the trouble you've gone through to help me out, and
everything you've suggested _should_ have worked AFAIK. I've just
broken down and called MS with my credit card to see if they can help.

If we can get it working I'll post a synopsis again and how it was fixed.

--
Tim - http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto