Repeating multiple fields from the last record

[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?

[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

[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

[QuazzieEvil]

Did you spell the control name correctly? is that control bound to a table column?

[Lecterman]

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

[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.

[RPG]

Hello

"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

[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.