Macro for highlighting sub-form row in Base

[meakasteve]

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:(

[Ratslinger]

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")
    oForm.last()
    iCount = oForm.getRow()
    oForm.first()
    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)
    	        CtlView.Select(sel)
        	    Exit for
	        end if
	    end if
        oForm.next()
    next x
End Sub

[meakasteve]

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?

[Ratslinger]

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

[meakasteve]

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!

[Ratslinger]

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

oForm.last()
iCount = oForm.getRow()
oForm.first()

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.

[meakasteve]

Now works perfectly! Thanks for your endurance.