By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,584 Members | 3,447 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,584 IT Pros & Developers. It's quick & easy.

Screen Refresh Issue in A2002

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/a
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

Nov 13 '05 #4

P: n/a
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


Nov 13 '05 #5

P: n/a
"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
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.