Ask Your Question

macro for highlighting sub-form row in Base

asked 2017-08-02 15:57:44 +0100

meakasteve gravatar image

updated 2017-08-02 15:58:50 +0100

Following my question what code do I need in a Base macro to access a field in highlighted row in a datasheet I received excellent code from Ratslinger which answered my question. I now have a further question based on that code.

I have altered the form 'Customers' so that it not only displays information about that customer but also contains a subform grid, In which details about all the items sent to that customer are displayed. This subform is based on a query into two tables, one listing all the items it is possible to send (with a primary key ItemsID ) and the other listing all the possible customers ( with the primary key CustID). I open the form by highlighting a particular postage in another form - say the postage of itemID = 5 (coffee) to CustID = 2 (Mr Smith). The form 'Customers' shows details about Mr Smith (his telephone numbers and addresses) and also in the subform the items he has been sent (row 1: cakes; row2: butter; row 3: coffee; row 4 milk).

All of this I have written and tested and it seems to work perfectly. When I highlight the row in 'Postage' the macro picks up the CustID and opens the form 'Customers' which contains also displays the Query showing the items this CustID received.

What I am trying to do is to modify the code further so that the row in the subform grid which says Mr Smith received coffee is highlighted. I want this highlighted because it was this that originated my search.

I think I need to do a couple of things:

First I must pick up the ItemID along with the CustID in GetCustID(). I presume this will be

oObj2 = oControl.getByName("itemID")

iItemID = oObj2.getCurrentValue() - where iItem is global.

Secondly I must highlight this item in the subform grid by modifying OpenFormAt Record().

Any help identifying the appropriate alterations would be much appreciated because I'm not making any progress:(

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2017-08-02 20:07:39 +0100

Ratslinger gravatar image

updated 2017-08-07 00:44:53 +0100

First you need to have available the specifics you are looking for in this grid. In your example, CustID & ItemID (these of course must columns be in the grid you are searching). Starting with the first record in the form (or subform), access column for CustID & see if a match for search item. If so, get column for ItemID & see if match for search item. If both = True you have the record. Get the bookmark & use to set highlight for row in grid.

Edit 8/6/2017 - corrected code to get the proper iCount value.

Sample code (your names will vary):

Sub SetSelectedRecord
    Dim oDoc         as Object
    Dim DocCtl       as Object
    Dim oForm        as Object
    Dim oColumns     as Object
    Dim oCell        as Object
    Dim oField       as Object
    Dim x            as Integer
    Dim iCount       as Integer
    Dim sel(0 to 1)
    oDoc = ThisComponent
    DocCtl = oDoc.getCurrentController()
    oForm = ThisComponent.Drawpage.Forms.getByName("YOUR_FORM_NAME")
    iCount = oForm.getRow()
    oColumns = oForm.getColumns()
    for x = 1 to iCount
        oCell = oColumns.getByName("COLUMN_NAME")
        If oCell.Value = VALUE_TO_CHECK then
            oCell = oColumns.getByName("COLUMN_NAME")
            If oCell.Value = VALUE_TO_CHECK then
                oField = oForm.getByName("GRID_CONTROL_NAME")
                sel(0) = oForm.getBookMark()
                CtlView = DocCtl.getControl(oField)
                Exit for
            end if
        end if
    next x
End Sub
edit flag offensive delete link more


Once again thank you for your help. The code runs perfectly and produces no errors. But testing it on a form of 3330 records iCount, for some reason, always counts 17 independently of how I sort the records. If the record to be identified is within the first 18 rows (0 to 17) then it is correctly highlighted otherwise the marker stops on the 19th row. Have you any idea why iCount could be restricted?

meakasteve gravatar imagemeakasteve ( 2017-08-05 12:42:38 +0100 )edit

It is probably because of the cache size. See your question here. Set the cache size first then you should get correct search.

Ratslinger gravatar imageRatslinger ( 2017-08-05 14:56:55 +0100 )edit

Placing the last two lines of code in your second solution to my previous question between the line oForm = ThisComponent.Drawpage.Forms.getByName and the line oForm.first() in the code above has no effect. iCount still remains at 17 despite many attempts to change the software environment. However since my present application requires sorting though less than 18 records and no one else seems to be afflicted, the problem is best assigned to the files!

meakasteve gravatar imagemeakasteve ( 2017-08-06 16:01:20 +0100 )edit

@meakasteve I am terribly sorry, but in my haste and lack of sufficient testing, I gave you the wrong information for iCount. Replace iCount = oForm.getCount() with:

iCount = oForm.getRow()

If you don't replace these lines you will eventually run into a problem. This should give you wanted results. Again, sorry for the inconvenience. Best to check out edited code in answer - also changed for statement.

Ratslinger gravatar imageRatslinger ( 2017-08-06 18:00:00 +0100 )edit

Now works perfectly! Thanks for your endurance.

meakasteve gravatar imagemeakasteve ( 2017-08-07 09:03:55 +0100 )edit
Login/Signup to Answer

Question Tools


Asked: 2017-08-02 15:57:44 +0100

Seen: 124 times

Last updated: Aug 07 '17