Screen Refresh Issue in A2002

I have a situation where I am using an unbound dialog form to update data in
an Access 2002 split back end / front end scenario. The data update is done
via an ADO call (direct to the back end db) when the user clicks the save
button. The dialog then closes and the user should be able to see the
result of their edit in a list view that now has focus. This is where I run
into a problem. The list view wont refresh to pick up the data changes.
This seems to be a timing issue with the ODBC linked tables because not even
the following drastic solution works. Any ideas?

Gcn.Execute strSQL
Forms!frmoraltt!frmAllocations.Form.RecordSource = "" 'set
recordsource to null
Forms!frmoraltt!frmAllocations.Form.RecordSource =
"qryoralallocations" 'reset to what we want it be - and effect a refresh
of the data records.

re: Screen Refresh Issue in A2002

"Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=blue]
> I have a situation where I am using an unbound dialog form to update data[/color]
in[color=blue]
> an Access 2002 split back end / front end scenario. The data update is[/color]
done[color=blue]
> via an ADO call (direct to the back end db) when the user clicks the save
> button. The dialog then closes and the user should be able to see the
> result of their edit in a list view that now has focus. This is where I[/color]
run[color=blue]
> into a problem. The list view wont refresh to pick up the data changes.
> This seems to be a timing issue with the ODBC linked tables because not[/color]
even[color=blue]
> the following drastic solution works. Any ideas?
>
> Gcn.Execute strSQL
> Forms!frmoraltt!frmAllocations.Form.RecordSource = "" 'set
> recordsource to null
> Forms!frmoraltt!frmAllocations.Form.RecordSource =
> "qryoralallocations" 'reset to what we want it be - and effect a[/color]
refresh[color=blue]
> of the data records.[/color]

You have two separate issues - the recordsource of the form, and the
rowsource of the listbox. While your code above may refresh the form, it
does not requery the listbox. Use:

lst.Requery

Also, to refresh the Form, use one of the following (in order):

Me.Refresh
Me.Requery (should be sufficient)
Me.Recalc (usually not needed)

Just for educational purposes, you might want to read up on these, along
with Me.Repaint.


Darryl Kerkeslager

re: Screen Refresh Issue in A2002

Thanks Darryl, but we're obviously not on the same wavelength here. I tried
..Refresh and .Requery before resorting to the more drastic approach I
copied to my posting. None of these work. I am quite confident that the
issue is technical rather than educational.


"Darryl Kerkeslager" <Kerkeslager@comcast.net> wrote in message
news:qoidnelVh-ogXHbcRVn-ig@comcast.com...[color=blue]
> "Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=green]
> > I have a situation where I am using an unbound dialog form to update[/color][/color]
data[color=blue]
> in[color=green]
> > an Access 2002 split back end / front end scenario. The data update is[/color]
> done[color=green]
> > via an ADO call (direct to the back end db) when the user clicks the[/color][/color]
save[color=blue][color=green]
> > button. The dialog then closes and the user should be able to see the
> > result of their edit in a list view that now has focus. This is where I[/color]
> run[color=green]
> > into a problem. The list view wont refresh to pick up the data changes.
> > This seems to be a timing issue with the ODBC linked tables because not[/color]
> even[color=green]
> > the following drastic solution works. Any ideas?
> >
> > Gcn.Execute strSQL
> > Forms!frmoraltt!frmAllocations.Form.RecordSource = ""[/color][/color]
'set[color=blue][color=green]
> > recordsource to null
> > Forms!frmoraltt!frmAllocations.Form.RecordSource =
> > "qryoralallocations" 'reset to what we want it be - and effect a[/color]
> refresh[color=green]
> > of the data records.[/color]
>
> You have two separate issues - the recordsource of the form, and the
> rowsource of the listbox. While your code above may refresh the form, it
> does not requery the listbox. Use:
>
> lst.Requery
>
> Also, to refresh the Form, use one of the following (in order):
>
> Me.Refresh
> Me.Requery (should be sufficient)
> Me.Recalc (usually not needed)
>
> Just for educational purposes, you might want to read up on these, along
> with Me.Repaint.
>
>
> Darryl Kerkeslager
>
>
>
>
>[/color]

re: Screen Refresh Issue in A2002

But the code you have below does not show a requery of the listBox, only the
form.

Could you perhaps post the part where you requery the listbox too, with the
other code? Does the form reflect the update, and not the listbox, or do
both not reflect the update?


Darryl Kerkeslager


"Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=blue]
> Thanks Darryl, but we're obviously not on the same wavelength here. I[/color]
tried[color=blue]
> .Refresh and .Requery before resorting to the more drastic approach I
> copied to my posting. None of these work. I am quite confident that the
> issue is technical rather than educational.
>
>
> "Darryl Kerkeslager" <Kerkeslager@comcast.net> wrote[color=green]
> > "Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=darkred]
> > > I have a situation where I am using an unbound dialog form to update[/color][/color]
> data[color=green]
> > in[color=darkred]
> > > an Access 2002 split back end / front end scenario. The data update[/color][/color][/color]
is[color=blue][color=green]
> > done[color=darkred]
> > > via an ADO call (direct to the back end db) when the user clicks the[/color][/color]
> save[color=green][color=darkred]
> > > button. The dialog then closes and the user should be able to see the
> > > result of their edit in a list view that now has focus. This is where[/color][/color][/color]
I[color=blue][color=green]
> > run[color=darkred]
> > > into a problem. The list view wont refresh to pick up the data[/color][/color][/color]
changes.[color=blue][color=green][color=darkred]
> > > This seems to be a timing issue with the ODBC linked tables because[/color][/color][/color]
not[color=blue][color=green]
> > even[color=darkred]
> > > the following drastic solution works. Any ideas?
> > >
> > > Gcn.Execute strSQL
> > > Forms!frmoraltt!frmAllocations.Form.RecordSource = ""[/color][/color]
> 'set[color=green][color=darkred]
> > > recordsource to null
> > > Forms!frmoraltt!frmAllocations.Form.RecordSource =
> > > "qryoralallocations" 'reset to what we want it be - and effect a[/color]
> > refresh[color=darkred]
> > > of the data records.[/color]
> >
> > You have two separate issues - the recordsource of the form, and the
> > rowsource of the listbox. While your code above may refresh the form,[/color][/color]
it[color=blue][color=green]
> > does not requery the listbox. Use:
> >
> > lst.Requery[/color][/color]
[snip][color=blue][color=green]
> > Darryl Kerkeslager[/color][/color]

re: Screen Refresh Issue in A2002

Darryl,

Thanks for your continued interest. There isnt a list box involved. I
probably didnt explain myself properly in my first post. By 'list view', I
meant a datasheet view showing a listing of the records. This view is
actually in a subform, so the code (using a standard approach) would look
like this:

Gcn.Execute strSQL
Forms!frmoraltt!frmAllocations.Form.Requery
Forms!frmoraltt!frmAllocations.Form.Refresh

Typically, strSQL will contain an INSERT statement to create a new record.
Therefore the new record should appear in the subform after the Requery and
Refresh. But it doesnt. I have played around with this for a couple of
hours this morning and tried things such as refreshing the link to the
tabledef object, using a For...Next loop to repeat the Requery and Refresh
actions numerous times, and including a Doevents but all to no avail. There
appears to be a fault with MS Access that is causing this behaviour. If you
are interested, I can email you a model of the issue so you can see for
yourself.




"Darryl Kerkeslager" <Kerkeslager@comcast.net> wrote in message
news:IqydnfGLMb-8-3HcRVn-pA@comcast.com...[color=blue]
> But the code you have below does not show a requery of the listBox, only[/color]
the[color=blue]
> form.
>
> Could you perhaps post the part where you requery the listbox too, with[/color]
the[color=blue]
> other code? Does the form reflect the update, and not the listbox, or do
> both not reflect the update?
>
>
> Darryl Kerkeslager
>
>
> "Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=green]
> > Thanks Darryl, but we're obviously not on the same wavelength here. I[/color]
> tried[color=green]
> > .Refresh and .Requery before resorting to the more drastic approach I
> > copied to my posting. None of these work. I am quite confident that[/color][/color]
the[color=blue][color=green]
> > issue is technical rather than educational.
> >
> >
> > "Darryl Kerkeslager" <Kerkeslager@comcast.net> wrote[color=darkred]
> > > "Andrew Chanter" <helpme@radsolutions.com.au> wrote
> > > > I have a situation where I am using an unbound dialog form to update[/color]
> > data[color=darkred]
> > > in
> > > > an Access 2002 split back end / front end scenario. The data update[/color][/color]
> is[color=green][color=darkred]
> > > done
> > > > via an ADO call (direct to the back end db) when the user clicks[/color][/color][/color]
the[color=blue][color=green]
> > save[color=darkred]
> > > > button. The dialog then closes and the user should be able to see[/color][/color][/color]
the[color=blue][color=green][color=darkred]
> > > > result of their edit in a list view that now has focus. This is[/color][/color][/color]
where[color=blue]
> I[color=green][color=darkred]
> > > run
> > > > into a problem. The list view wont refresh to pick up the data[/color][/color]
> changes.[color=green][color=darkred]
> > > > This seems to be a timing issue with the ODBC linked tables because[/color][/color]
> not[color=green][color=darkred]
> > > even
> > > > the following drastic solution works. Any ideas?
> > > >
> > > > Gcn.Execute strSQL
> > > > Forms!frmoraltt!frmAllocations.Form.RecordSource = ""[/color]
> > 'set[color=darkred]
> > > > recordsource to null
> > > > Forms!frmoraltt!frmAllocations.Form.RecordSource =
> > > > "qryoralallocations" 'reset to what we want it be - and effect a
> > > refresh
> > > > of the data records.
> > >
> > > You have two separate issues - the recordsource of the form, and the
> > > rowsource of the listbox. While your code above may refresh the form,[/color][/color]
> it[color=green][color=darkred]
> > > does not requery the listbox. Use:
> > >
> > > lst.Requery[/color][/color]
> [snip][color=green][color=darkred]
> > > Darryl Kerkeslager[/color][/color]
>
>[/color]

re: Screen Refresh Issue in A2002

"Andrew Chanter" <helpme@radsolutions.com.au> wrote[color=blue]
> Thanks for your continued interest. There isnt a list box involved. I
> probably didnt explain myself properly in my first post. By 'list view',[/color]
I[color=blue]
> meant a datasheet view showing a listing of the records. This view is
> actually in a subform, so the code (using a standard approach) would look
> like this:
>
> Gcn.Execute strSQL
> Forms!frmoraltt!frmAllocations.Form.Requery
> Forms!frmoraltt!frmAllocations.Form.Refresh
>
> Typically, strSQL will contain an INSERT statement to create a new record.
> Therefore the new record should appear in the subform after the Requery[/color]
and[color=blue]
> Refresh. But it doesnt. I have played around with this for a couple of
> hours this morning and tried things such as refreshing the link to the
> tabledef object, using a For...Next loop to repeat the Requery and[/color]
Refresh[color=blue]
> actions numerous times, and including a Doevents but all to no avail.[/color]
There[color=blue]
> appears to be a fault with MS Access that is causing this behaviour. If[/color]
you[color=blue]
> are interested, I can email you a model of the issue so you can see for
> yourself.[/color]

Does the record show up in the underlying table after the INSERT? Are there
fields that are calculated based on control values that may require
Forms("frmoraltt").frmAllocations.Form.Recalc?

You can email me a copy of the file and I will look at it, at the address in
the header (not spam-blacked in any way).


Darryl Kerkeslager