Refreshing a Continuous Form without Requerying

[JeffBuzz]

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

 

[Klatuu]

No, the Refresh updates the underlying table, but does not requery the
database, so you will have to use the requery to see your additions.

 

[JeffBuzz]

What do you mean by "updates the underlying table"? I have already
updated the underlying table myself on another form. Now I want the
first form (the continuous form) to update to show these changes. From
what you said, it sounds like I need to requery. I would like to avoid
that if possible because the query takes a while.

Is it possible to manually update the controls on the continuous form?
For example, if there are two textboxes for each record, can I just
manually set the values? That will probably change the values for all
the rows of the continuous form, won't it?

Is this possible at all?

 

[Klatuu]

I was not referring to what you are doing. I was only describing what the
Refresh method does. As I said before, the only way to get newly added
records into your forms recordset is with a requery.

 

[JeffBuzz]

HI Dave,
Actually, I am not adding new records. I am just updating....oh
wait....my original query is a left join between two tables. For some
records in the continuous form, all fields are populated (data exists
in both joined tables). In other records, only some of the fields are
populated (data exists only in the left table). Users can edit these
values, which will actually add a record to the joined table. So the
total number of records on the original query stays the same, but
there are new records being added to the joined table.

I am suspecting this is my problem. Is my only solution to requery? I
wish I could just somehow refresh the one table.

 

[Klatuu]

Okay, JeffBuzz. I see what you are doing, but unfortunately, there is no
going around a requery.

The problem is that only the data entered into the form will exist in the
form's recordset. When you add data outside the form, it doesn't know about
it. The only way for the form to know about the data added to the table is
with a requery.

 

[JeffBuzz]

Thanks for your help Dave. Looking throught the documentation, it
would appear that the "Unique Table" property is what would help solve
my problem. Unfortunately, I can seem to get that to work.

 

[Klatuu]

The Unique Table property only applies to adp files (an Access Project), not
to MDB files.
I sympathize with your frustration, but you are wasting your time trying to
get around this. If your form has an uneditable recordset and you add or
modify data in any of the underlying tables, the only way to get the modified
data into the form's recordset is with a Requery.

 

[JeffBuzz]

So it seems. Thanks again for your help (and your patience)

 

[Klatuu]

Wish I could give you the answer you want.
The only other thing you may consider is reforming your query so it executes
more quickly, but that may not be an option.