Screen Refresh Issue in A2002

[Andrew Chanter]

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.

[Darryl Kerkeslager]

"Andrew Chanter" <he****@radsolutions.com.au> wrote

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.

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

[Andrew Chanter]

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" <Ke*********@comcast.net> wrote in message
news:qo********************@comcast.com...

"Andrew Chanter" <he****@radsolutions.com.au> wrote

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.

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

[Darryl Kerkeslager]

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" <he****@radsolutions.com.au> wrote

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" <Ke*********@comcast.net> wrote

"Andrew Chanter" <he****@radsolutions.com.au> wrote

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.

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 [snip] Darryl Kerkeslager

[Andrew Chanter]

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" <Ke*********@comcast.net> wrote in message
news:Iq********************@comcast.com...

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" <he****@radsolutions.com.au> wrote

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" <Ke*********@comcast.net> wrote

"Andrew Chanter" <he****@radsolutions.com.au> wrote
> 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.

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 [snip] Darryl Kerkeslager

[Darryl Kerkeslager]

"Andrew Chanter" <he****@radsolutions.com.au> wrote

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.

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