Ask Your Question

Base Macro - Click on Table Row to Open Record

asked 2018-11-11 14:48:11 +0200

PhLo gravatar image

I'm working on a home screen (aka switchboard) for my Base document that has button links to all the various forms of my database. In addition, there are search boxes with table control search results. My hope is to be able to write a macro to launch a record edit form when clicking on a row in the search results I can figure out everything except I don't know how to access the active row (the one with the green triangle to the left). If I can detect which row is clicked, I can get the row's ID and pass that as a parameter to the form I open. I already have that all figured out, just don't know the part about active table row. Since button controls can't be inserted into table controls, I can't have a "open" or "edit" button for each row. The whole row needs to trigger on click. The search result table controls are read only, so I don't need to be able to change any data in them.

Here's a visual example of a search form. The search logic is already in place. I already know how to open a form via a macro. I just need an expert's tip about how to determine the active table row via code, and therefore the "Act #" column of that active row in this case (because accountnumber is the primary key I will use to edit that record in the account form). I can search by any column (Act #, Name, Address, City, etc) to produce the search results... but I want the Act# of the clicked row.

image description

edit retag flag offensive close merge delete


I have asked a question like this previously. ( The answer seems to be that there is no obvious way to do it, but you could use mri to search all the properties of the table ... if you have the persistence. Otherwise, make a form from the table, and use the existing handles to attach the data there. If you find a way to do it, I'd be very interested.

Pansmanser gravatar imagePansmanser ( 2018-11-11 15:50:05 +0200 )edit


Please see answers in the post. Your customized keystroke can call the macro which accesses any of the data within the table control using Columns from the data source and the selected row of data. Since your linked question is now 3+ years old and your requirements may be somewhat different, if you have further need, please ask as new question.

Ratslinger gravatar imageRatslinger ( 2018-11-12 01:29:28 +0200 )edit

Thanks again, Ratslinger. I perhaps misunderstood the question. My original problem was to do something like this working directly form the table, rather than through a form. At that time I was not familiar with forms. After recently learning a good deal more, I have now achieved what I wanted three years ago by working with a form rather than the table. Life is good!

Pansmanser gravatar imagePansmanser ( 2018-11-12 11:40:58 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2018-11-11 21:02:31 +0200

Ratslinger gravatar image

Hello @PhLo,

Have attached a Q&D sample. Using the form CustomersGrid (again Q&D - no filter used) when you click on any row, that record will open form CustomerEdit at the record chosen.

Code can be improved upon for your needs and includes code to resize the newly opened form. The new form is opened at the required record by setting the filter to the ID value requested.

Sample ---- OpenFormFromGridSelection.odb

edit flag offensive delete link more


Note: Ignore comment in code about storing variable in Global. Mistakenly left in from copied code. No globals used in macro.

Ratslinger gravatar imageRatslinger ( 2018-11-11 21:49:06 +0200 )edit

This looks like exactly what I need. Awesome to see it working. I figured it was possible somehow. Thank you sir. Now to inspect the code and figure out how it's done. It's a good way for me to learn a few things.

PhLo gravatar imagePhLo ( 2018-11-11 23:11:42 +0200 )edit

There's one weakness to this approach. Strangely, a macro attached to a table control's click event triggers no matter where the click occurs, even on scrollbars, nav buttons at bottom, etc. If there are more records than can display, you can't scroll down without clicking to trigger a record edit launch. Too bad the developers didn't think of that and only trigger inside the cells. I think I'll not worry about it and tell users to use mouse scroll wheel instead of clicking on scrollbars.

PhLo gravatar imagePhLo ( 2018-11-13 08:42:20 +0200 )edit

One goofy way to get around this click problem is to attach the macro to the table's "when losing focus" event instead. Then it requires a click anywhere outside the table control. While that might seem strange, a "load" or "open" button (with no event) adjacent to the table might tempt a click and feel like a sensible user experience. Don't know why click events attached to table columns don't work. They literally do nothing.

PhLo gravatar imagePhLo ( 2018-11-13 08:48:41 +0200 )edit

No need for 'goofy' approaches. Just add oEvent ( Sub GetID(oEvent) ) and you can check for conditions. Assign to middle button, or left button with a modifier ( such as + Ctrl or Alt key) or even right button although this is mostly for context menus. You need more knowledge on using events and MRI to examine properties and methods.

See -> struct MouseEvent

Ratslinger gravatar imageRatslinger ( 2018-11-13 18:25:35 +0200 )edit

and -> constants group MouseButton

and -> struct InputEvent

So with oEvent present, then:

If oEvent.Buttons <> 4 Then exit sub

at the beginning of the routine will only execute the routine if the middle button was pressed.

Ratslinger gravatar imageRatslinger ( 2018-11-13 18:29:25 +0200 )edit

Also, click events in columns do work. What is probably confusing is the method in which they work. If you click in a table control field, it selects the field. They next click in that field activates the mouse click event. Now I can't say this is the way it should work but only that is the current process.

Ratslinger gravatar imageRatslinger ( 2018-11-14 00:58:23 +0200 )edit

In my case, the column clicks don't work at all, no matter how many times I click - 1, 2...30. If I attach the same Subroutine to the table control mouse click event, works every time. Even if I write a Sub that has only msgbox "Hello" and call it on column event mouse click... nothing. That's why I chose the table control click event instead. Your attached file also has table control click event, so I figured you encountered a similar problem. Otherwise column click makes far more sense.

PhLo gravatar imagePhLo ( 2018-11-14 08:12:22 +0200 )edit

Strange. I can get the column click event to work by switching it from the table control on YOUR file... but not in mine. Even if I write an entirely new subroutine, it won't trigger it on a column click. Can't think of any other explanation than a very obscure bug I'm encountering. Like you said, it takes two clicks on your file. But in mine, it just doesn't work. I'll just tolerate the table control click for now since it works.

PhLo gravatar imagePhLo ( 2018-11-14 08:29:00 +0200 )edit

Since I couldn't ever get the events attached to columns to work, I ended up making a separate "Edit" button that triggered the event instead. Works great and is probably more "expected" behavior than clicking inside the table control anyway.

PhLo gravatar imagePhLo ( 2018-11-18 08:48:19 +0200 )edit

answered 2018-11-11 17:14:47 +0200

DrewJensen gravatar image

updated 2018-11-11 17:29:33 +0200

here is an example of this. What you are interested in is the line Key =....

sub onClickViewEdit( oEv as object )
    dim aForm as object
    dim Key as integer
    ' get the inovice record
    ' the user just double clicked on
    Key = oEv.Source.Model.Parent.Columns.getByName( "InvoiceID" ).getInt
    ItemEntry.setKey(Key, "MSIS/frmItemEntry")
end sub

This grabs the PK from the datasource attached to a grid UI, sets the value in a different form "MSIS/frmItemEntry", launches that form, then hides the current form.

oEv.Source.Model.Parent gives you the datasource (resultset) the grid is connected to.

The UI control is repsonsible for moving the record pointer in the resultset, your code only needs to read the column value for the current record.

It is not true to say you can't get this by using the UI controls 'view' but that ends up being way more work than is necessary.

edit flag offensive delete link more


@DrewJensen Having trouble with last two lines of your code. ItemEntry gives:

BASIC runtime error.
Object variable not set.

and cannot find any info anywhere on it. Also 'hideMe():

BASIC runtime error.
Property or method not found: hideMe.

Your answer is interesting but can't figure out how to get last two lines working.

Ratslinger gravatar imageRatslinger ( 2018-11-11 18:39:27 +0200 )edit

They are db specific function. Hideme() is

sub hideMe()
    thisComponent.CurrentController.Frame.getContainerWindow.setVisible( FALSE ) 
end sub

which is easier to use than the full line.

Each form has a corresponding basic module, The Item Entry form has a module ItemEntry, which has a function setKey. Second parameter is optional... more

DrewJensen gravatar imageDrewJensen ( 2018-11-11 18:54:36 +0200 )edit

and looks like this:

sub setKey( aKey as variant, optional aFormName as string )
    dim oForm as object
    if isMissing( aFormName ) then
        oForm = thisComponent
        oForm = OpenDBForm(aFormName)
    end if
    with oForm.Drawpage.Forms(0)
        .filter = "InvoiceID = " & aKey
        .Applyfilter = True
    end with
    ItemEntry.setTaxControls(oForm, oForm.Drawpage.Forms.getByName("Standard").getByName("Items").getByName("Totals"))
end sub
DrewJensen gravatar imageDrewJensen ( 2018-11-11 18:58:24 +0200 )edit

To be complete setTaxControls expects a dataform object and will have one or more databound UI controls. In this case the db application has to support multiple tax fields, needed in some countries. The db includes a configuration function that lets the end user set different tax types and rates and allows for custom strings for field labels in the forms, etc.

DrewJensen gravatar imageDrewJensen ( 2018-11-11 19:20:18 +0200 )edit

Thank you. Didn't realize your routine needed code in each form.

Ratslinger gravatar imageRatslinger ( 2018-11-11 19:40:05 +0200 )edit

Need? It is how I designed it - actually I did misspeak there are some forms with no corresponding module, as the form is fairly simple. In the case of this db app there are no forms which do not have some call into the basic library though, as the user never has to see the Base default window, when the odb is opened the 'switchboard' if you will is opened and the Base window is hidden.

It all depends on what you are trying to accomplish.

DrewJensen gravatar imageDrewJensen ( 2018-11-11 19:43:19 +0200 )edit

@DrewJensen It turns out I misspoke. I believe your original answer simply is about obtaining the needed key from the table grid which I agree with. I thought the remainder (last two lines) was a simple method to open the new form at the record with the obtained key. Further examination of your additional code indicates you have other routines which accomplish this portion.

Sorry for the confusion.

Ratslinger gravatar imageRatslinger ( 2018-11-11 20:44:23 +0200 )edit

Thanks guys. I'm sure your back and forth discussion will help anyone in the future looking to do this kind of thing. I'll look through both answers and try to learn from your guys' experience. I accepted @Ratslinger 's answer simply because there was an attached working example. But I appreciate both answers.

PhLo gravatar imagePhLo ( 2018-11-11 23:09:20 +0200 )edit
Login/Signup to Answer

Question Tools



Asked: 2018-11-11 14:48:11 +0200

Seen: 1,017 times

Last updated: Nov 11 '18