# access current record of table in macro [closed]

I am trying to write a Basic macro in Base for the first time. Please bear with me!

I want to run my macro from a line of a table, called by a customised keystroke, which will access that current record of the table. I can make the macro do what I want once it finds the data, but I can't see how to say 'use this record.'

edit retag reopen merge delete

### Closed for the following reason question is not relevant or outdated by Alex Kemp close date 2020-08-27 12:25:46.373694

Sort by » oldest newest most voted

You can access the record in two ways, both of which require you to use a Form rather than the Table GUI. You can use a columnar form that looks exactly like the table GUI.

There probably is a way to explore down through the object hierarchy to figure out how to get the information from the table GUI, but I would not recommend spending the time trying to figure it out. If you still insist on using the Table GUI for this do MsgBox ThisComponent.DBG_properties and then explore up the object hierarchy (e.g. Parent) and down (e.g. getByIndex(0)) until you find whatever it is that has the data. You will find this to be time consuming and confusing, but if you are persistent eventually you may well find it.

If, on the other hand, you will switch to a Form:

Option A: alter the values of data in controls on the open form. For the simple principles involved, here is an example. Once you access a value from a Form Control, you can manipulate the data in Basic. To execute your vision, you could make all of the changes in the form controls, assuming you just want to change the current record. Note that numeric, date, and text controls all have different properties and methods, and date in particular is an UNO object, so look around for various recipes of how to access those objects, or explore the .DBG_methods and .DBG_properties of the objects. When you commit the whole record, the new values that committed to the various controls will be saved to the table.

Option B: alter the values directly in the underlying table using a SQL query, and then reload the form record. For an example of SQL queries in a macro, see this example. That query also shows the structure of a UNO date object. Instead of the SELECT query there, you would do an UPDATE query, or whatever was desired, with the limitation to the primary key of the record you want to change. Then .reload the form and move it back to the correct record if necessary, which is the last code example in this answer.

more

Thank you so much Doug, for taking the time to make a thorough answer. No time today, but I'll try it very soon, and I'm sure I'll be able to mark my question as solved.

( 2015-11-09 14:01:59 +0100 )edit

Doug, I have stumbled at the first hurdle. I have created my form to be identical to the underlying table. How do I read specific cells (controls?) in the form, and how do I address the current record in the form?

( 2015-11-09 17:03:12 +0100 )edit

It is a little detailed to explain the object hierarchy. See in each code example where it starts with ThisComponent and then creates a MainForm object and then a Ctrl object? When you open the form and look in the form navigator, the first object is what I sometimes call a container of forms, the second is the MainForm and from that are subforms and controls. Your data will be in a Table control (I think) and the sub-controls I think will be the columns of the current row.

( 2015-11-09 18:34:10 +0100 )edit

If you post your form as an attachment, I (or someone else) would (eventually) be able to give more specific tips.

( 2015-11-09 18:35:21 +0100 )edit