Page 1 of 1
Repeating multiple fields from the last record
Posted: Fri Jan 01, 2010 4:57 pm
by degilio
Sorry for the noob question -
I have a database of data that gets updated with hundreds of records weekly. The form has 5 fields, three of which are consistent for every entry on a given week but different from the previous week. For these fields I would like Base to remember the entry from the previous record. I have been doing this in Lotus for years and can't believe there isn't a way to do this in OO.
How do I get Base to default to the previous records data for that field?
Re: Repeating multiple fields from the last record
Posted: Sat Jan 02, 2010 2:27 am
by QuazzieEvil
you need a quick macro to get value of current controls, for those you want to repeat, and set it as the default for that control.
You can either have the default set whenever you move to a new record, or via a button.
Code to set default. This example has two controls for which the defaults are to be set. Change to suite your needs.
Code: Select all
Sub setControlDefaults(Form As Object)
Dim ControlNames() As String
Dim I As Integer
Dim model As Object
ControlNames=Array("txtFirstName","txtLastName")
For I=0 To UBound(ControlNames)
model=Form.getByName(ControlNames(I))
model.DefaultText=model.BoundField.getString()
Next I
End Sub
Now, to set via a button. This macro must be bound to the button's mouse pressed event
Code: Select all
Sub pbSetDefaults_MouseButtonPressed(Event As Object)
Dim Form As Object
Form=Event.Source.Model.Parent
setControlDefaults(Form)
End Sub
OR, to set before moving to the next record: Bind to Form's Before Record Change event
Code: Select all
Sub Form_BeforeRecordChange(Event As Object)
Dim Form As Object
If Not Event.Source.supportsService("com.sun.star.form.component.Form") Then Exit Sub
Form=Event.Source
setControlDefaults(Form)
End Sub
FINALLY, if you want to set it before record inserts only: Bind to Form's Before Record Action event
Code: Select all
Sub Form_BeforeRecordAction(Event As Object)
Dim Form As Object
'Action 1=INSERT
If Not (Event.Source.supportsService("com.sun.star.form.component.Form") And Event.Action=1) Then Exit Sub
Form=Event.Source
setControlDefaults(Form)
End Sub
Re: Repeating multiple fields from the last record
Posted: Wed Jan 06, 2010 6:46 pm
by Lecterman
Quazzie,
I am trying to use the code you provided (thanks very much for it), however when I open the form I am using, I get the following error for the setControlDefaults piece of code:
"BASIC runtime error. Object variable not set."
on the following line:
model.DefaultText=model.BoundField.getString()
Any ideas?
My total code for this is:
"Sub setControlDefaults(Form As Object)
Dim ControlNames() As String
Dim I As Integer
Dim model As Object
ControlNames=Array("txtCell")
For I=0 To UBound(ControlNames)
model=Form.getByName(ControlNames(I))
model.DefaultText=model.BoundField.getString()
Next I
End Sub"
txtCell is the control I am trying to repeat
Thanks in advance,
Fred
Re: Repeating multiple fields from the last record
Posted: Wed Jan 06, 2010 8:10 pm
by QuazzieEvil
Did you spell the control name correctly? is that control bound to a table column?
Re: Repeating multiple fields from the last record
Posted: Wed Jan 06, 2010 9:15 pm
by Lecterman
QuazzieEvil wrote:Did you spell the control name correctly? is that control bound to a table column?
Yes, it is spelled the same as in the 'Name' field in the properties for that control...and yes it is bound to a table column
Re: Repeating multiple fields from the last record
Posted: Sat Sep 21, 2013 2:39 pm
by patb
Hi all,
I have exactly the same problem as Lecterman. I simply want to have a tabular data entry form repeat entries from the previous record. I have searched and googled to no avail. Can anyone see the cause of the error I am getting?
Trying to run the macro below gives an error:
"BASIC runtime error.
Argument is not optional"
at the line:
"model=Form.getByName(ControlNames(I))"
Here is my complete code which, for the purposes of portability, is embedded into the actual database document.
Code: Select all
REM ***** BASIC *****
Option Explicit
Sub setControlDefaults(Form As Object)
Dim ControlNames() As String
Dim I As Integer
Dim model As Object
ControlNames=Array("CustomerID","District")
For I=0 To UBound(ControlNames)
model=Form.getByName(ControlNames(I))
model.DefaultText=model.BoundField.getString()
Next I
End Sub
The names of the two fields I want repeated are "CustomerID" and "District". These are the field names from the database table in use and are identical to the column names on the data entry form.
I would much appreciate any comments or leads. Thanks in advance
Cheers, Pat.
Re: Repeating multiple fields from the last record
Posted: Sat Sep 21, 2013 3:36 pm
by RPG
Hello
patb wrote:
"BASIC runtime error.
Argument is not optional"
You can not execute the sub direct from the IDE with clicking with the mouse. You have to call the sub from an event or an other sub. When I do understand the sub correct it must be called from an other sub.
When you want work with a grid control in your form then the gridcontrol is a container of other controls in you form. I think pass the gridcontrol object to the sub.
Code: Select all
oGridControl=oForm.getbyname("GridControl Name")
call setControlDefaults(oGridControl)
I have not test the code.
Romke
Re: Repeating multiple fields from the last record
Posted: Sat Sep 21, 2013 3:40 pm
by Villeroy
Extract the attachment to <profile>/Script/python/DefaultValues.py
Add a hidden field to your form, name it "DefaultCurrentFields" and set its value to a semicolon-separated list of field names (the fields to be copied).
Assign the form's "On Loading" event to MyMacros>DefaultValues>Form_onLoad
Reload the form document.
When you navigate to the form's new record all values from the fields specified in the hidden control are copied from the previously selected record to the new record.
I tried to implement this for all types of database fields but there are too many bugs in Base. I use this macro successfully to duplicate integer numbers (foreign keys). I don't recall which field types do work and which don't. Somehow I can't find my test document for this project.