goto last record fails

English
, , ,
#1

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.

#2

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

#3

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.

#4

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
oSubFsize.Reload()
Wait 200
oSubFsize.moveToInsertRow()

REM         OR -

oSubFsize.Last()
#5

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
	oSubFsize1.Reload()
	Wait 200
	oSubFsize1.Last()
#6

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?

#7

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.

#8

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
	aSubFsize.Reload()
	Wait 200		
	aSubFsize.Last()


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


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

End Sub

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