Base Tables do not show all records initially

English
,
#1

This appears to be a bug in Base, but whenever I first open a table or a form with a table control with a large number of records, the wrong total number is shown in the ‘# of #’. I also cannot get to the last record by scrolling down. The same problem exists for the results of a query. If I click the ‘go to last’ button on the navigation bar, it resolves. Has anyone else experienced this?

Version: 7.0.6.2 (x64)
Build ID: 144abb84a525d8e30c9dbbefa69cbbf2d8d4ae3b
CPU threads: 4; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: threaded

MySQL 5.7.19

#5

This is pretty common. MS Access did the same thing. It is a reasonable assumption that you don’t need the entire database table so they cut the overhead and increase the speed by only giving you a portion which is qualified by .fetchsize on your table’s object. It seems that most apps are designed to NOT give the user the big picture by
NOT giving them the whole contents of a table or query at one time. So most people seem fine with this because it does indeed speed up the app and prevent the server from being unnecessarily bogged down sending data you (in most cases) don’t need/want. But if you want it all at once you can get the rowcount and set the fetchsize to the rowcount and reload. It still will not give you the correct total records count at the bottom of the table on your display until you invoke .last then .first on your table object. By physically taking it to the last record it then knows what number to display for an accurate total. Weird…but typical and most of us figure this out early on. So it is not a bug…it is designed that way. i do wish it would do the .last .first internally and save us the trouble…but…then there would be no savings in speed and no relief to the server. What bothers me is that the .rowcount already knows the total records without retrieving the entire table…so, not sure why Libre does not use it for the: Record 1 of 22,341 display thing. Above my pay grade.

#2

There will be shown something like Record 1 of 85*. Note *. It says: There are more records, but I haven’t loaded all in the cache.

See bug 71275 as enhancement request.

#4

Interesting. I have never seen a note like that. I’m looking at a form with a table control right now, and it has the asterisk, as you mentioned, but when I hover over the total number on the form navigation toolbar of the window, the screentip says “Total No. of Records”. The number shown is 47, when really, there are 1,255.

#6

The “Tip-Help” is wrong at this position. Would only be right if you switch last row and then first row.

#3

t74714.odt (20.4 KB)

#8

I don’t get it. Perhaps the problem is I don’t have the underlying data of that form document, but it just looks like a demonstration of the behavior I described in the question.

#11

The yellow control always shows the actual record count without loading the entire row set.

#7

I lifted the macro code from @Ratslinger at this topic to increase the .FetchSize upon opening the form, and it did the trick for me. At first, it shows the wrong total number as before, but after about a second (I assume so all data can be fetched), it corrects.

#9

This may be better

    Dim oForm As Object
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_INTERNAL_FORM_NAME")
	oRS_clone  = oForm.createResultSet()
	oRS_clone.Last()
	oRS_clone.First()
#10

I don’t think it’s better. It did correct the count in the form navigation toolbar of the window immediately, but never the one in the navigation bar of the table control.

#12

Objective is to avoid needing to modify Fetch size when table grows. This will work with table grid:

Option Explicit
Sub reload_form
REM Attach to "When loading" event of form
  Dim oDrawPage As Variant
  Dim oForm As Variant
  oForm = ThisComponent.getDrawPage().getForms().getByName("YOUR_INTERNAL_FORM_NAME")
  oForm.last()
  oForm.first()
End sub
#13

So, if I first set the fetch size to 20000 instead of 2000, will that take longer to cache, even when the number of records is smaller than 2000?

#14

Never timed it.
.
The one without Fetch can be used for all forms by sending to the sub the form name.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.