Ask Your Question

goto last record fails

asked 2019-10-11 12:32:37 +0100

LSemmens gravatar image

updated 2019-10-11 14:42:46 +0100

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
    oSubFsize = oForm.GetByName("SizeForm")                           'subform1
    oSubFsize1.FetchSize = 5000
    oSubFFV = oForm.GetByName("frmFV")                                  'subform2
    oSubFFV1.FetchSize = 5000
    oSubCol = oForm.GetByName("ColourFrm")                              'subform3
    oSubCol1.FetchSize = 5000

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 flag offensive close merge delete


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

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


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.

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

2 Answers

Sort by » oldest newest most voted

answered 2019-10-12 04:10:24 +0100

LSemmens gravatar image

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        

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

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

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

End Sub

edit flag offensive delete link more

answered 2019-10-11 19:16:19 +0100

Ratslinger gravatar image


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.FetchSize = 5000

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

REM         OR -

edit flag offensive delete link 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: 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

    oSubFsize = oForm.GetByName("SizeForm")             'subForm 1
    oSubFsize1=oSubFsize.GetByName("frm_SIZE")       'form inside subform1
    oSubFsize1.FetchSize = 5000
    Wait 200
LSemmens gravatar imageLSemmens ( 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?

Ratslinger gravatar imageRatslinger ( 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.

LSemmens gravatar imageLSemmens ( 2019-10-12 04:10:02 +0100 )edit
Login/Signup to Answer

Question Tools


Asked: 2019-10-11 12:32:37 +0100

Seen: 102 times

Last updated: Oct 12 '19