Base Macro - Click on Table Row to Open Record

[PhLo]

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.

[Pansmanser]

I have asked a question like this previously. (access current record of table in macro) 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.

[Ratslinger]

@Pansmanser,

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.

[Pansmanser]

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!

[drewjensen]

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")
	hideMe()
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.

[Ratslinger]

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

[drewjensen]

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]

and looks like this:

sub setKey( aKey as variant, optional aFormName as string )
	dim oForm as object
	if isMissing( aFormName ) then
		oForm = thisComponent
	else
		oForm = OpenDBForm(aFormName)
	end if
	with oForm.Drawpage.Forms(0)
		.filter = "InvoiceID = " & aKey
		.Applyfilter = True
		.Reload
	end with
	ItemEntry.setTaxControls(oForm, oForm.Drawpage.Forms.getByName("Standard").getByName("Items").getByName("Totals"))
end sub

[drewjensen]

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.

[Ratslinger]

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

[drewjensen]

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.

[Ratslinger]

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

[PhLo]

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.

[Ratslinger]

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

[Ratslinger]

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

[PhLo]

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]

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]

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.

[Ratslinger]

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]

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.