Refreshing a Continuous Form without Requerying

[JeffB]

Hello All,
Is it possible to Refresh a Continuous Form without Requerying the entire data set?

Here is what I am trying to do, I have a form that displays records in a Continuous form. The data source is an existing query that joins a few different tables. It takes a while to run (20 seconds or so) because one of the tables has a lot of records in it.

The form allows the user to double click one of the rows and opens a new form where the user can edit a few of the fields. The fields in the seconds form are not bound to anything. I am manually updating a table using SQL and the values of the form fields. The table that gets updated is NOT the large table from the original query.

After the user closes the second form, I would like to REFRESH the continous form to see the changes I just made to the current selected record. I do not want to Requery because that will run the whole query over and it takes too long to run (although it does have the desired effect). Is there anyway to just update the current form fields with the changes I just made? Isn't that what the Form.Refresh method is for?

Is Form.Refresh not working because my original data source is a query and thus not updatable? Any other ideas? Thanks for your help.

Jeff

 

[SeaRox]

Instead of

Form.Refresh

Try

Me.Refresh

 

[JeffB]

I am using Me.Refresh. It does not seem to do anything.

Could it be because the Form's record source is a query, while the second form updates a table in that query?

 

[gemma-the-husky]

me.refresh just requeries (ie refreshes) the current item - it doesnt requery the whole data set- so it probably wont do much, if anything

me.requery, requeries the whole record set which is what takes the time presumably

 

[JeffB]

All I want to do is refresh the current item (underlying data was changed by the other form). I don't want to requery the entire record set because it takes too long and I know the only thing I changed was data shown on the current record.

Calling Refresh doesn't do anything though. I am wondering if its because the recordset was loaded from an Access Query so its not update-able. I don't know if this matters or not.

 

[JeffB]

That is exactly what I want to do - update the current record. The underlying data has already been updated via the other form. Now I want to refresh just the current record instead of requerying the entire dataset because that takes a while.

 

[klynch0803]

maybe some code that requeries only the query related to the record edited instead of all the queries (the time consuming one).

I'm not exactly sure how to write it without a sample to work with but maybe something like:

"afterupdate" event for the text field edited?

 

[JeffB]

I thought about manually updating the fields in the continuous form because I have the new values entered in the second form. My guess is that would update the fields for every record in the continuous form though.

Is it possible to manually modify the Recordset (or RecordsetClone?) with the new values from the form, then call Refresh?

 

[namliam]

Any change made in the (source) table should automagicaly be replicated in the query.

Exception would be an append and/or a group by query but changing an excisting query should update the query.

 

[JeffB]

Hi namliam. I am not seeing the original source recordset update. I think it might be because of the nature of my data source. Here is what I have:

Form1 - continuous form. Data source is an existing query that inner joins a really large table with a smaller transaction table.

Form2 - unbound data entry form. User can update the fields and save changes to the smaller transaction table.

After Form2 updates the smaller table and closes, I am not seeing the new data on Form1 or on in the recordset for Form1. I do see the change if I Requery form1, but as I said, that takes a long time and I want to avoid that.

Any ideas?

 

[JeffB]

I just made a very interesting discovery. After my Form2 closes and updates the table, Form1.Recordset property does not show the change. But Form1.RecordsetClone DOES! Why is this? How can I get the changes in RecordsetClone to show up in the continuous form?

 

[namliam]

Like I said, changes should replicate without problem...

Try scrowling the data on and off (visible) screen (so up and down). That sometimes helps

 

[JeffB]

Actually....I take that back. Neither the Recordset or the RecordsetClone have been updated. The RecordsetClone was at position 0 and had similar data to the record I modified at position 4. So I mistakenly thought RecordsetClone had gotten updated. It did not.

So now my question is WHY ISN'T THE RECORDSET GETTING UPDATED WHEN I CHANGE THE DATA IN THE UNDERLYING TABLE?

This is driving me crazy.

 

[gemma-the-husky]

me.refresh just requeries (ie refreshes) the current item - it doesnt requery the whole data set- so it probably wont do much, if anything

my post - i am not sure exactly what effect refresh has, thinking about it

--------
possibly the query won't resolve itself automatically - i suppose its a bit like a combo box - you have to force those to requery, dont you. And if a whole dataset DID try to refresh itself it would hit performance, wouldnt it.

 

[JeffB]

Hi Gemma. Yes, you described my problem. Any ideas for a solution?

 

[gemma-the-husky]

is there no way of reducing the size of the dataset?

 

[vgersghost]

try this

Do this after update
Granted it goes back to the beginning of the line but it stays on the same record

Dim recordnum As Integer

recordnum = Me.CurrentRecord

Me.Requery

DoCmd.GoToRecord , , acGoTo, (recordnum)

 

[Rich]

Try saving the record first

 

[vgersghost]

Clarifiaction

What I posted before I use on a datasheet only. I put the code in any control after update. It takes you back to the record you change, beginning of the row, but then you can do a set focus to the control you just changed in that record. Hope this helps