Continuous Form and scrolling past last record on an ADO recordset

[Banana]

Out of interest, has anyone found a solution to the behavior of continuous subform allowing user to keep scrolling even when the last record has been passed, leaving us with a blank screen as the last record moves upward and only stopping when the last record is at the top of the detail section?

It would be nice to somehow disable any further downward scrolling once the last record is fully visible in the continuous form.

The only way, AFAICT, is to set the recordset of the form to snapshot, and it must be done via the recordsource (e.g. the form's RecordsetType property doesn't seem to have any effects). However, linked table or ADO recordset doesn't seem to behave the same way. In case of a linked table, the behavior is weird. First time I tried it, it kept reverting to dynaset recordset until I made a saved query. Even so, the behavior persists. ADO's Locktype seems to have no effect on how it behave. (and why should it anyway? It's a form thing, not a recordset thing.)

Maybe I'm missing something obvious?

Thanks!

 

[missinglinq]

The standard way to do this is to set the form's AllowAdditions Property to No/False.

 

[Banana]

Thanks, but as indicated before, I did set AllowAdditions (as well as others) to no with no success.

Mind, the behavior were different between a local table compared to linked table & ADO recordset. With a local table, setting the recordsource's query to return a snapshot recordset stops the "extended scrolling". This is not true with linked table & ADO recordset.

I did the test and verified that AllowAddition was not relevant (e.g. it would continue scrolling past the last record). It only changes when the query's recordset type is set to snapshot.

 

[Banana]

A unconventional solution has been found.

Put the continuous form into a subform. Set scrollbars to "Neither".

On the parent form, set the scrollbar to "Vertical", and write a code to resize the subform container to be as big as many records there are in the continuous subform. It's a simple formula of SubForm.Height = Subform.Form.Section(acHeader).Height + Subform.Form.Section(acFooter).Height + (Subform.Form.Section(acDetail).Height * Subform.Form.Recordset.Recordcount + 1) for a subform where we want to add new records. If it's not supposed to allow additions of new part, strip the + 1 part from the formula.

It also goes without saying that this only works if Recordcount is valid in the context (e.g. linked table may return a recordcount of -1 so we may need a different method of accessing the count of records displayed) and the formula may need adjustment if there are no Header or Footer section or if there's Page sections as well. (It's bizarre because we cannot do a For Each... upon the Sections as it does not seem to be a collection.)