# goto last record fails

I have a database that consists of a master form with subforms, that works as intended, along with using a macro to navigate to the last record. I then have a button on that master form which closes it and opens another form which consists of four tables. Each table is a lookup for the master form but that is irrelevant. What I need this second form to open each table at the last record I'm certain that I had it working at some stage but I had a major database failure and lost several days work, including the working code. (That'll teach me not to keep my backups up to the minute when making code changes).

In my testing over the last couple of hours I have gotten the following macros working........sort of.

Sub oLast
Dim oForm As Object, oSubFsize As Object, oSubFsize1 As Object
Dim                  oSubFFV As Object,   oSubFFV1 As Object
Dim                  oSubCol As Object,   oSubCol1 As Object
oForm = ThisComponent.DrawPage.Forms.getByIndex(0)          'main form
oForm.Last()
oSubFsize = oForm.GetByName("SizeForm")                           'subform1
oSubFsize1=oSubFsize.GetByName("frm_SIZE")
oSubFsize1.FetchSize = 5000
oSubFsize1.moveToInsertRow()
oSubFsize1.Last()
oSubFFV = oForm.GetByName("frmFV")                                  'subform2
oSubFFV1=oSubFFV.GetByName("frm_FACE_VALUE")
oSubFFV1.FetchSize = 5000
oSubFFV1.moveToInsertRow()
oSubFFV1.Last()
oSubCol = oForm.GetByName("ColourFrm")                              'subform3
oSubCol1=oSubCol.GetByName("frmColour")
oSubCol1.FetchSize = 5000
oSubCol1.moveToInsertRow()
oSubCol1.Last()


End Sub

If I step through the macro each form goes to last record as intended. if, however, I let the macro run , only the first form (Which contains the three other subforms) ends at the last record, all others return to the first.

edit retag close merge delete

With further testing, I've managed to solve some of this issue so Have edited the above severely.

( 2019-10-11 14:41:25 +0100 )edit

Hello,

Please when posting, with each question include OS, specific LO version and in the case of Base the actual database used. Even if you have posted this in other questions it may have since changed or the person answering needs to search through your previous questions or simply ask again in a comment. This can save all of us time.

( 2019-10-11 19:02:22 +0100 )edit

Sort by » oldest newest most voted

I redesigned the form structure so each table is a subform of the main form. (intitally the main form had one table and the others were subforms of that. SO now the stucture is Main form (linked to a table) that table as a subform and all other tables as subforms of the main form.

Sub aLast

Dim aForm As Object, aSubFsize As Object
Dim                  aSubFFV As Object
Dim                  aSubCol As Object
Dim                  aMain   As Object

aForm = ThisComponent.DrawPage.Forms.getByIndex(0)

aSubFsize = aForm.GetByName("frm_SIZE")
aSubFsize.FetchSize = 5000
Wait 200
aSubFsize.Last()

aSubFFV = aForm.GetByName("frm_FACE_VALUE")
aSubFFV.FetchSize = 5000
Wait 200
aSubFFV.Last()

aSubCol = aForm.GetByName("frmColour")
aSubCol.FetchSize = 5000
Wait 200
aSubCol.Last()

aMain = aForm.GetByName("frmPERF")
aMain.FetchSize = 5000
Wait 200
aMain.Last()


End Sub

more

Hello,

There are a couple of points about your macro. For each subform you have a statement to go to a new record AND to go to the last record. Only the final statement will be ultimately used. Eliminate one or the other.

Using this section as an example:

oSubFsize = oForm.GetByName("SizeForm")                           'subform1
oSubFsize1=oSubFsize.GetByName("frm_SIZE")
oSubFsize1.FetchSize = 5000
oSubFsize1.moveToInsertRow()
oSubFsize1.Last()


Don't know why you are retrieving something from sub form to set FetchSize. This is part of subform itself. Also, to my recollection, once set a reload needs to be done to take effect. Finally there is a timing issue. Since the main form is being positioned at the last record, you must Wait for it to do so and then the instruction will take. Revised section (tested):

oSubFsize = oForm.GetByName("SizeForm")                           'subform1
oSubFsize.FetchSize = 5000
Wait 200
oSubFsize.moveToInsertRow()

REM         OR -

oSubFsize.Last()

more

My apologies for not stating OS or Database type. I was trying everything to to get to last record, and forgot to remove the "insert row command. I even tried the wait command, but that did not seem to work at all evne when I tried Wait 5000 (which, I presume should have made me wait 5 seconds) I'll add the reload line and see if that helps.

Libre Office Version: 6.0.7.3 Build ID: 1:6.0.7-0ubuntu0.18.04.10

Desktop: Cinnamon 4.2.4 Distro: Linux Mint 19.2 Tina

JDBC hsqldb

edit: Just added wait and reload, removed the insert row command and it has no effect. The code now looks like

        oForm = ThisComponent.DrawPage.Forms.getByIndex(0)  'Main Form
oForm.Last()

oSubFsize = oForm.GetByName("SizeForm")             'subForm 1
oSubFsize1=oSubFsize.GetByName("frm_SIZE")       'form inside subform1
oSubFsize1.FetchSize = 5000
Wait 200
oSubFsize1.Last()

( 2019-10-12 02:33:46 +0100 )edit

So this is a subsubform. Not clear in question. It may help to understand the construction of the form. Code should work (have tested as stated) regardless of subform or subsubform.

Also, how is this macro executed? Mine was tested with a button.

May be a problem with using 'Open Document' event.

Edit: Nope, no problems. Thought there was but failed to see "Wait 200" was commented out. Button execution worked without the wait but 'Open document' only worked with the wait.

Edit #2:

This last part of your question makes me suspect something else is going on:

If I step through the macro each form goes to last record as intended. if, however, I let the macro run , only the first form (Which contains the three other subforms) ends at the last record, all others return to the first.


Possibly some other macros in the form? Filtering?

( 2019-10-12 03:03:03 +0100 )edit

Thanks for your suggestions. I have JUST finally solved it. See Solution below, Yes there were other macros in play but, none were related to the data in question.

I re-inserted the moveToInsertRow before the Last statement just to make the display of the last line in the table clearer. Otherwise it was hard up against the bottom of the window.

( 2019-10-12 04:10:02 +0100 )edit