Lag at bottom of subform.

[aqua404]

I know this has been discussed, but I can't find a resolution. I have
a subform on a form. The table with the data for the main form has
15,000 records. I am opening and then setting recordsource so as to
only pull 1 record.

The table where the subform data comes from has 130,000 records. There
is a parent/child link. Indexed on those fields. I have set the
datasource to the table, SQL statements and a number of different
queries to limit the data. Anyway I do it, it is SLOW. But only
slow if the new record * is visible. If the new record indicator is
scrolled off the bottom of the page, there performance is fine. But if
you scroll down, there is a big 5 second lag to display the new record.

Adding new records as a result is very slow and aggravating for the
users.

If I open a query with the same data, the results are instantaneous.
Also if I set the datamode to snapshot it is fast, but then you can't
add records which is where the problem is anyway.

By the way the typical display of subform records is only about 12.

Does anyone know of a way around this quagmire?

[salad]

aq*****@gmail.com wrote:

I know this has been discussed, but I can't find a resolution. I have
a subform on a form. The table with the data for the main form has
15,000 records. I am opening and then setting recordsource so as to
only pull 1 record.

The table where the subform data comes from has 130,000 records. There
is a parent/child link. Indexed on those fields. I have set the
datasource to the table, SQL statements and a number of different
queries to limit the data. Anyway I do it, it is SLOW. But only
slow if the new record * is visible. If the new record indicator is
scrolled off the bottom of the page, there performance is fine. But if
you scroll down, there is a big 5 second lag to display the new record.

Adding new records as a result is very slow and aggravating for the
users.

If I open a query with the same data, the results are instantaneous.
Also if I set the datamode to snapshot it is fast, but then you can't
add records which is where the problem is anyway.

By the way the typical display of subform records is only about 12.

Does anyone know of a way around this quagmire?

If it's been discussed but no solution provided, it must be one of those
left-field type problems.

Maybe create a new form, copy all of the controls from the old main form
into it, copy all the code from old to new, insert the subform. Maybe
do the same for the subform.

Also, do you step through the code when a new record is added? Maybe
something is being done in the background that you forgot about.

If worse gets to worse, create an add button on the main form. Set the
subform to AddNewRecs to False. When the AddButton is pressed, create a
new record via DAO/ADO...whatever your preference. Then requery the
subform and set the bookmark of the subform to the new record.

[aqua404]

Thanks

I have cleared out all of the code. Just the raw access forms. Has
anyone had experience with 100k rows in a table that was used for a
subform.

These are line items on invoices.

Preston
salad wrote:

aq*****@gmail.com wrote:

I know this has been discussed, but I can't find a resolution. I have
a subform on a form. The table with the data for the main form has
15,000 records. I am opening and then setting recordsource so as to
only pull 1 record.

The table where the subform data comes from has 130,000 records. There
is a parent/child link. Indexed on those fields. I have set the
datasource to the table, SQL statements and a number of different
queries to limit the data. Anyway I do it, it is SLOW. But only
slow if the new record * is visible. If the new record indicator is
scrolled off the bottom of the page, there performance is fine. But if
you scroll down, there is a big 5 second lag to display the new record.

Adding new records as a result is very slow and aggravating for the
users.

If I open a query with the same data, the results are instantaneous.
Also if I set the datamode to snapshot it is fast, but then you can't
add records which is where the problem is anyway.

By the way the typical display of subform records is only about 12.

Does anyone know of a way around this quagmire?

If it's been discussed but no solution provided, it must be one of those
left-field type problems.

Maybe create a new form, copy all of the controls from the old main form
into it, copy all the code from old to new, insert the subform. Maybe
do the same for the subform.

Also, do you step through the code when a new record is added? Maybe
something is being done in the background that you forgot about.

If worse gets to worse, create an add button on the main form. Set the
subform to AddNewRecs to False. When the AddButton is pressed, create a
new record via DAO/ADO...whatever your preference. Then requery the
subform and set the bookmark of the subform to the new record.

[salad]

aq*****@gmail.com wrote:

Thanks

I have cleared out all of the code. Just the raw access forms. Has
anyone had experience with 100k rows in a table that was used for a
subform.

These are line items on invoices.

Preston

When you cleared the code out did that change the speed?

I doubt few people would ever want 100K rows returned in a subform

You could do something like this from the MainForms OnCurrentEvent.
Assumes the link between both form and subform are linked by a field
called ID
Me("SubFormName").Form.Filter = "ID = " & Me.ID
Me("SubFormName").Form.FilterOn = True
and in the SubForms OnOpen event have something like
Me("SubFormName").Form.Filter = "ID = -1"
Me("SubFormName").Form.FilterOn = True

You might need to make an adjustment to this if a new record...depends
if you are using an autonumber that hasn't been generated by the time
the OnCurrent event finishes executing.

>

salad wrote:

>>aq*****@gmail.com wrote:

>>>I know this has been discussed, but I can't find a resolution. I have
a subform on a form. The table with the data for the main form has
15,000 records. I am opening and then setting recordsource so as to
only pull 1 record.

The table where the subform data comes from has 130,000 records. There
is a parent/child link. Indexed on those fields. I have set the
datasource to the table, SQL statements and a number of different
queries to limit the data. Anyway I do it, it is SLOW. But only
slow if the new record * is visible. If the new record indicator is
scrolled off the bottom of the page, there performance is fine. But if
you scroll down, there is a big 5 second lag to display the new record.

Adding new records as a result is very slow and aggravating for the
users.

If I open a query with the same data, the results are instantaneous.
Also if I set the datamode to snapshot it is fast, but then you can't
add records which is where the problem is anyway.

By the way the typical display of subform records is only about 12.

Does anyone know of a way around this quagmire?

If it's been discussed but no solution provided, it must be one of those
left-field type problems.

Maybe create a new form, copy all of the controls from the old main form
into it, copy all the code from old to new, insert the subform. Maybe
do the same for the subform.

Also, do you step through the code when a new record is added? Maybe
something is being done in the background that you forgot about.

If worse gets to worse, create an add button on the main form. Set the
subform to AddNewRecs to False. When the AddButton is pressed, create a
new record via DAO/ADO...whatever your preference. Then requery the
subform and set the bookmark of the subform to the new record.

[aqua404]

We I cleared out the code, nothing changed. So I created a new
subform, great performance. (only returning about 12 rows on the
subform.)

I pasted the old controls into new form and the performance drastically
went down.

I finally found a function in the default value of a combo box. It was
the culprit. It is now re-written and perfance is very good, even
great. The function was designed to get the last value of the combo
box by this user with a top n ADO statement. I used a variable to
store the last value used in the new code, much faster, no network
traffic.

Thanks for pointing me in the right direction guys.

Preston
salad wrote:

aq*****@gmail.com wrote:

Thanks

I have cleared out all of the code. Just the raw access forms. Has
anyone had experience with 100k rows in a table that was used for a
subform.

These are line items on invoices.

Preston

When you cleared the code out did that change the speed?

I doubt few people would ever want 100K rows returned in a subform

You could do something like this from the MainForms OnCurrentEvent.
Assumes the link between both form and subform are linked by a field
called ID
Me("SubFormName").Form.Filter = "ID = " & Me.ID
Me("SubFormName").Form.FilterOn = True
and in the SubForms OnOpen event have something like
Me("SubFormName").Form.Filter = "ID = -1"
Me("SubFormName").Form.FilterOn = True

You might need to make an adjustment to this if a new record...depends
if you are using an autonumber that hasn't been generated by the time
the OnCurrent event finishes executing.

salad wrote:

>aq*****@gmail.com wrote:
I know this has been discussed, but I can't find a resolution. I have
a subform on a form. The table with the data for the main form has
15,000 records. I am opening and then setting recordsource so as to
only pull 1 record.

The table where the subform data comes from has 130,000 records. There
is a parent/child link. Indexed on those fields. I have set the
datasource to the table, SQL statements and a number of different
queries to limit the data. Anyway I do it, it is SLOW. But only
slow if the new record * is visible. If the new record indicator is
scrolled off the bottom of the page, there performance is fine. But if
you scroll down, there is a big 5 second lag to display the new record.

Adding new records as a result is very slow and aggravating for the
users.

If I open a query with the same data, the results are instantaneous.
Also if I set the datamode to snapshot it is fast, but then you can't
add records which is where the problem is anyway.

By the way the typical display of subform records is only about 12.

Does anyone know of a way around this quagmire?
If it's been discussed but no solution provided, it must be one of those
left-field type problems.

Maybe create a new form, copy all of the controls from the old main form
into it, copy all the code from old to new, insert the subform. Maybe
do the same for the subform.

Also, do you step through the code when a new record is added? Maybe
something is being done in the background that you forgot about.

If worse gets to worse, create an add button on the main form. Set the
subform to AddNewRecs to False. When the AddButton is pressed, create a
new record via DAO/ADO...whatever your preference. Then requery the
subform and set the bookmark of the subform to the new record.