Pulling data from one field into another field, based on a yes/no choice

Paul_in_SF · January 8, 2023, 9:33pm

Brand new here, pretty new user of Base, with an ambitious request.

I have six fields, A B and C, and T U and V. Based on a yes/no choice by the data entry person (using either a check box or maybe a radio button?) I want to copy the data from field A to field T, from B to U and from C to V if the answer is Yes, and leave fields T U and V open for manual entry if the answer is No. Fields A B and C would always be entered first, before the yes/no choice. T U and V should still be editable even if the data is copied over. I hope that makes sense.

This is just to reduce excess steps in data entry, as T U and V are usually the same as A B and C, but not always. From other reading I have done on this board, I’m supposing this will require something like a macro to accomplish. I have not tried to use a macro in Base before.

RobertG · January 9, 2023, 7:46am

You are right: You will need macros.

First step: Create the body for a procedure called “Copy”

Start Tools → Macros → Organize Macros → Basic
Click in Macro from on you database file. Then click on New. Name for new module will be Module 1 → OK.
Editor opens with Sub Main, an empty row and End Sub
Change “Main” to “Copy(oEvent AS OBJECT)”

Now you have created a procedure and could start creating the content.
Checkbox is created in the form. You have to know the names of the fields for T, U and V in the form.

SUB Copy(oEvent AS OBJECT)
   oField = oEvent.Source.Model
   oForm = oField.Parent
   oField1 = oForm.getByName("txtA")
   oField2 = oForm.getByName("txtB")
   oField3 = oForm.getByName("txtC")
   oField4 = oForm.getByName("txtD")
   oField5 = oForm.getByName("txtE")
   oField6 = oForm.getByName("txtF")
   IF oField.State = 1 THEN
      oField4.BoundField.UpdateString(oField1.CurrentValue)
      oField5.BoundField.UpdateString(oField2.CurrentValue)
      oField6.BoundField.UpdateString(oField3.CurrentValue)
   END IF
END SUB

Might be the fields aren’t fields for a string. If it doesn’t work we should change it to needed type.
This macro should now be started by the check box.
Open the form for editing, not for input data.
Mark the check box. If it doesn’t exist create it. Right mouse click on the box and Control Properties → Events → Item Status Changed. Click on the button with the three points and such for procedure “Copy”.

That’s it. If you haven’t used macros before you have to switch Tools → Options → LibreOffice → Security → Macro Security. Press the button and change the security in the dialog to “Medium”. I have set a special path here as “Trusted Source”, so I’m only asked for executing macros, if the file isn’t saved in this path.

Paul_in_SF · January 9, 2023, 9:05pm

Thank you for the very thorough set of steps. I have gone through them, but I am running into trouble at this point:

When I click on the button with three dots next to “Item Status Changed” I get this screen:

I have changed the macro security as you directed. I wasn’t sure what the above quote meant, I didn’t see anything labeled “Copy.” I thought maybe the “assigned action” should be the macro, so I clicked on the Macro button, I get an “Exploit blocked” from my malware software called Malwarebytes. I have to search out how to allow macros from that software, but I want to make sure what my next action should be at that point in the instructions.

Thank you again.

Paul_in_SF · January 9, 2023, 9:15pm

I fixed the setting in Malwarebytes, so I was able to continue. On the Macro screen I was able to find the Module1 macro and I clicked on Copy and OK. Now on the Assign Action screen it says:
Item Status Change Standard.Module1.Copy

I tested it and it works. Many many thanks.

I do have another need for another macro, I presume it would be best if I start a new thread for that.

RobertG · January 10, 2023, 6:40am

Yes, I have forgotten this dialog. It shows the same events as in Properties → Events …