Unable to pass filter string to form

English
#1 
Sub OpenFormWithFilter(oEvent As Object) '***********************************************************
    Dim oDoc As Object
    Dim oMainForm As Object
    Dim oNewForm As Object
    Dim sFormName As String
    Dim sFilter As String
    Dim sRecordID As String

    sFormName = "JobTakeOffF" ' Name of the form to open
    'sRecordID = "123" ' Or get this from a control like oEvent.Source.Parent.Controls("YourTextBox").Text
	'sRecordID =  oEvent.Source.Parent.Controls("txtSearchValue").Text
	sRecordID = oEvent.source.Model.Parent.getByName("txtSearchValue").Text
    oDoc = ThisDatabaseDocument
    'oMainForm = oEvent.Source.Parent

    ' Open the form
    oNewForm = oDoc.FormDocuments.getByName(sFormName).open()

    ' Set the filter on the new form
    sFilter = " ""Job_ID"" =  '" & sRecordID & "' "
        msgbox sFilter
    oNewForm.CurrentController.getForm().Filter = sFilter
    oNewForm.CurrentController.getForm().FilterOnLoad = True
    oNewForm.CurrentController.getForm().reload()
End Sub

I think the problem is the sFilter formatting. The subroutine stops at:
oNewForm.CurrentController.getForm().Filter = sFilter

General Error - Unable do to Nothing
Opening a form from a macro no record data visiable
#2

Which form do you want to get?
oNewForm is a document, which contains a drawpage and on this drawpage are available different forms.
Here is a pice of code, which sets such a filter in an opened form:

oForm = thisComponent.Drawpage.Forms.getByName("MainForm")
oFeld = oForm.getByName("Filter")
stFilter = oFeld.Text
oForm.filter = " UPPER(""Name"") LIKE '%'||'" + UCase(stFilter) + "'||'%'"
oForm.ApplyFilter = TRUE
oForm.reload()
#3

Why not simply use a subform showing the details on the same form document and get rid of all the silly macro dazzle?

Anyway, let’s try an event driven approach:
PowerFiltering_Macro_OpenDetail.odb (512.1 KB)

Open form “Filter Data” and select some record on the blue grid. Then push the button or double-click on the record selector to open the details form with the single record identified by its ID.
You can go back to the filter and repeat the process with another record without closing the details form.

#4

The JobTakeOffF form is the form I need to filter. It has Job_ID for the primary key. The JobTakeOffF has two subforms on it with associated infomation for jobs.

#5

JobTakeOffF is the name that shows in the GUI. It’s not the name showing in the form navigator, MainForm is the name there.

#6

The JobTakeOffF does open when I run the subroutine.

#8

If you don’t know SQL, you can’t wrap SQL into Basic code.

  1. Open the form in question for editing.
  2. Turn off design mode.
  3. Apply some filter on “Job_ID”.
  4. Turn on design mode.
  5. Have a look at the form properies. There you find the working filter string you need to generate in your Basic code.

You can also generate and test any SQL SELECT statement in the query editor. The form’s filter property is just a WHERE clause.

Without any database attached, I can only guess that your “Job_ID” is an integer column and that your WHERE clause fails because of the quoting.

#7

I think you need a (“Sub”)routine to pass the filter as parameter.
Along the line of

Sub OpenFormFiltered(form As String, filtro As String)
    Dim F As Object
    [...]
    F.filter = filtro
    F.reload
End Sub

 
And so call it like

OpenFormFiltered(sFormName, sFilter)
#9

@RobertG @Villeroy
I’m able to open the form SelectedRecord, but unable to get it’s .filter()
:grey_question:

Sub OpenFilteredForm(filtro As String)
	GlobalScope.BasicLibraries.LoadLibrary("MRILib")
	Dim F As Object
	On Error GoTo Erro
	F = ThisDatabaseDocument.FormDocuments.SelectedRecord
	F.open()
	MRI F
	'F.filter = filtro
	'F.reload
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Subtype or paste code here

I understand need do get SelectedRecord > fDetalhes, but how?
Neither F.fDetalhes nor .getComponent(“fDetalhes”) works.
 
DetalhesOpened

SelectedRecordForm
SelectedRecordForm940×375 72.8 KB

SelectedRecordTree

#10

The forms collection on the draw page of your form document can have zero, one or many main forms, each main form with zero, one or many subform.

REM the open() method returns the opened document
oFormDocument = ThisDatabaseDocument.FormDocuments.getByName("Form1").open()
REM this should be good enough if the form document contains only one logical main form
oForm = oFormDocument.DrawPage.Forms.getByIndex(0)
intID = 1
oForm.Filter = """Job_ID"" = " & intID
oForm.reload() 'also reloads any subforms of oForm

Debugging can be as simple as:

Sub form_when_loading(ev)
msgbox ev.Source.Filter
End Sub

this reports the current filter string of the calling form.

#11

:+1:

Option Explicit
Sub SelectRecord(Evt As Object)
	'Globalscope.BasicLibraries.LoadLibrary("MRILib")
	Dim filtro As String
	On Error GoTo Erro
	filtro = """Table1"".""ID"" = " & Evt.Source.Text
	AFF("JobTakeOffF", filtro)
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub
REM=========================================================
REM TO OPEN A FORM WITH FILTER
Sub AFF(form As String, filtro As String)
	'Globalscope.BasicLibraries.LoadLibrary("MRILib")
	Dim FD As Object, FF As Object
	On Error GoTo Erro
	FD = ThisDatabaseDocument.FormDocuments.getByName(form).open()
	FF = FD.Drawpage.Forms.getByIndex(0) ' the filtered form
	FF.filter = filtro
	FF.reload()   
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & "na linha " & Erl
End Sub

 
SelectionForm
SelectedRecord

#12

Here we go again. The attached document with embedded HSQL tries to nail down this problem in plain, stupid StarBasic with some configuration based on a hidden control named “OpenParameterForm”.

Having a pair of form documents, one loading the other with well defined parameters, you add a hidden control to the form navigator of the calling form, name it “OpenParameterForm” and add a semicolon separated list of configuration values, assign some form event, control event or mouse click to macro onFormAction, onControlAction or onMouseRelease respectively. The called form’s event “fill parameters” is assigned to macro “Substitute_Params”. That’s it. You don’t need to understand the code nor adjust the code to your specific needs. The StarBasic module can be moved to the global scope (“My Macros”) without any changes. You can use the exact same code with all your databases.

The loaded form document is linked to some parameter query such as SELECT * FROM "TBL" WHERE "Column" = :paramName.
This is an exception to the rule that we use to use parameter queries with subforms, because the parameters are supposed to be substituted by the parent form.
In this particular case, the macro code called by the calling form document stores criteria values in a global array variable and the routine assigned to the “fill parameters” event of the called form document reads those values from the variable to substitute the parameters.

How to set up the value of the hidden control “OpenParameterForm”? In case of one parameter, you enter ColumnName;Form Doc Name. “ColumnName” is the name of the column (not the control name) having the filter criterion in the calling form. “Form Doc Name” is the name of the form to be loaded. The form name may also be a hierarchical one if you store forms in folders, such as Folder Name/Form Doc Name.

My test document contains 3 form documents plus a folder of 2 form documents:

  1. “Filter Data1” is the original form document with subforms, which does not use any macros. The filtering main form is bound to filter row 1. The filtered subform is bound to parameter query “ParamQuery” with 4 parameters.

  2. “Filter Data2” is another instance of the same form document. The filtering main form is bound to filter row 2 in order to be independet from the other forms’ filter criteria (just for testing and debugging).

  3. “Filtering Form1” is stripped down to the filtering main form and bound to the same filter row #1 as in “Filter Data1”.

  4. In a subfolder “Param Forms” resides:
    4.1. “Param Filter”, which is the filtered subform on a separate form document. It consumes the 4 filter criteria from “Filtering Form1” and shows the records filtered by the criteria entered into the calling form “Filtering Form1”. Missing criteria are ignored. Comparing the result set with the original form document “Filter Data1” (free of macros), the results should always be the same.
    When you double-click the table control or when you click the big button, the current record is opened in
    4.2. form document “Param Detail” based on the primary key “ID”.

Glitch: When using more than one parameter, the sequential order in which parameters are substituted is somewhat obscure. If your calling loaded form has more than one parameter, assign the form’s event “fill parameters” to the routine “Substitute_Params_DBG” and load the form. A message box pops up from where you can read the right order of parameters. Note the parameter names, assign “Substitute_Params” to the same event and note the corresponding 4 column names of the calling form to substitute the parameters.
For example, my “Param Filter” form is bound to query “ParamFilter” with 4 parameters. “Substitute_Params_DBG” reveals, that the parameters are queried in the order paramC;paramD1;paramD2;parmamP. The corresponding column names in the calling form are INT2;D1;D2;INT1 (as can be seen in the Master/Slave relation of the original “Filter Data” form).
The resulting configuration string to call “Param Filter” with 4 parameters is INT2;D1;D2;INT1;Param Forms/Param Filter, that is 4 columns in right order of param substitution plus hierarchical name of the form to be called.

The second sample, calling “Param Detail” from “Param Filter” uses the configuration string ID;Param Forms/Param Detail, loading “Param Forms/Param Filter” and substituting one parameter with the value of this form’s ID. This is the easy case with one column name and one document name.
OpenParamForms_Embedded_Macro.odb (466.7 KB)

#13

In other words:

  1. Copy my module somewhere to any library in document or in global scope.
  2. Open the form navigator, right-click the form having the control named “txtSearchValue”, choose New>Hidden control, get the properties of the hidden control, enter OpenParameterForm as control name and ColumnName;JobTakeOffF as hidden value where ColumnName is the name of the column “txtSearchValue” is linked to and “JobTakeOffF” is the form document to be loaded.
  3. Switch the source of the main form in form document “JobTakeOffF”, to SELECT * FROM "TableName" WHERE "Job_ID" = :paramJID. Assign event “fill parameters” to routine “Substitute_Params”.
  4. On the calling form, assign any control event, form event or mouse event to the respective routine on my module.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.