[Solved] Initialize form's ID field to MAX+1

[mohanvarier]

Dear all
I am a beginner in open office data base. My requirement is...

While loading a form the default value of a number field in that form should be +1 of the last record of the corresponding table.
I was trying to get using a macro...the algorithm of my macro is as said below.

While loading form should display last record.
Then the number field value should take to a variable.
increment the variable.
form will jump to new record.
assign the number field to the value of the variable..

My macro codes are shown below.

Code: Select all

REM  *****  BASIC  *****

Sub Main

End Sub


sub Startt
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
dim oForm as object
dim oControl as object
dim iopnum as integer
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:LastRecord", "", 0, Array())
oForm = ThisComponent.CurrentController.Model.DrawPage.Forms
oControl = oForm.getByName("MainForm").getByName("fmtOPNumber")
iopnum = oControl.getCurrentValue()
iopnum = iopnum+1

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:NewRecord", "", 0, Array())
oControl.updateInteger(iopnum)

end sub

The macro is not giving the required result...
kindly help me

[F3K Total]

Hello,
if you use an integer column as AutoValue, the database does this job for you for every new record.

Witch record you see on loading can be adjusted by sorting the form correctly.
R

[mohanvarier]

sir,

Thank you for your response....
May be because of my language you was mislead-ed.....
1. My requirement is not in the ID field
2. This is most important.....I want an entry option also in that field, ie default value should be MAX+1 at the same time an entry option for any value also should be there, that is why i used the word "default".
The mentioned solution will not give entry option in that field..

Sorry for my poor language...which mis guided you.

waiting for you help
thanks

[Villeroy]

Which type of database? Embedded HSQL?
In case of a database server: with multi-user access?
Is that incremented integer referenced by some other field?
In most cases this kind of requirement indicates bad database design.

[UnklDonald418]

See if this example helps with what you are attempting.

Demo51_AutoIncrementNonKeyField.odb

(13.66 KiB) Downloaded 162 times

If you are going to be programming macros you need to install an object inspection tool.
[Tutorial] Introduction into object inspection with MRI

[Villeroy]

I prefer the event driven approach, aka "Hollywood programming" ("Don't call us! We call you!").

1. Install my Python macro from viewtopic.php?f=21&t=88831. Just open the attached document and push the macro button to install the Python macro to the right place.
2. Now you can test my input form in the here attached database document.

In order to find out how it works:
1. open the form in design mode
2. get the forms navigator from toolbar "Form Design". The navigator shows the forms collection with 2 independent logical forms.

-- The form "NewOPNum" just queries the max+1 value from the OPNum and displays this value in the green control. You may delete that green control if you like. No macro code makes use of the displayed form control.

-- The MainForm has an additional hidden control "AutoRefresh". Hidden controls store configuration values for macros. This one stores the value ../NewOPNum.
../ refers to this form's parent item (the forms container) which has a refreshable form "NewOPNum".

-- The main form's event "after record action" calls the Python macro you have installed whenever this form updates, inserts or deletes any record. The Python macro refreshes all items that are listed in the hidden control "AutoRefresh" (multiple items would be semicolon separated).

-- The main form's event "before record action" calls a macro that is embedded in the database document (Standard.Module2.before_RecordAction). It reads the integer value from colun #1 of form "NewOPNum" and writes it to column #3 of the calling form when a new record is inserted.

One macro takes care of the correct max+1 value whenever something may have been changed in the MainForm (after record action). The other macro inserts the max+1 value whenever a new record is going to be inserted into the MainForm (before record action).

P.S. the embedded macro might fail when you start with an empty table. I did not test this special case.
P.P.S you get the calling source and additional elements of any event if you bind the event to macro MriLib.Module1.mri of your installed MRI object inspector. Whenever the event is triggered, a new MRI window pops up from which you can dive into the object hierarchy.