PdcApplication.ChooseQuery

Declaration

ChooseQuery(SQL as String,Caption as String,KeyField as String,KeyID as String) as PdcDBTable

Description

With this function a record can be picked from a selfmade query. The result of the query is shown in a regular pick window.

KeyField is a string value that is used to enable saving grid settings and grouping of the list. DO NOT USE UNDERSCORES in the KeyField. This can be any 5 characters string. The resulting GridID = 'BROWS' + KeyField. KeyID is the column name that is returned when user selects a record.

Notes

Make sure that the KeyField and KeyID are populated. When not populated, things like grouping don't work.

Code example

The example below lets the user select a supplier for a material (with multiple supplier switched on) and return the text entered for this material/supplier record.

function fstrGetLMITxts(rstrMatNr)
dim strResult
dim strSQL
dim intLMIID
dim tblSQL

  strResult = ""
  strSQL    = "select * " &_
              "  from LMI_MAT " &_
              " where LMI_MATN = '" & rstrMatNr & "'" &_
              " order by LMI_LVNAAM"

  set tblSQL = pdc.ChooseQuery(strSQL, "Selecteer leverancier", "WNW01", "LMI_ID1")
  if IsValid(tblSQL) then
    strResult  = tblSQL.GetField("LMI_TEKST")

    fstrGetLMITxts = strResult 
  end if

end function 'fstrGetLMITxts

<source lang="vb.NET">
    dim SQL As String = $"select * from SRV_SERVICE"
    dim QrySQL = pdc.App.ChooseQuery(SQL, "Selecteer serviceoject", "SO1", "SRV_ID")

    if Not QrySQL Is Nothing then
      tbSRV_ID.Text = QrySQL.GetField("SRV_ID")
      releaseObject(QrySQL)
    end if

  Sub releaseObject(ByRef rObj As Object)
    Try
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(rObj)
      rObj = Nothing
    Catch ex As Exception
      rObj = Nothing
    Finally
      'This command is mandatory, because when using two prosesses of Excel, both stay running after release without GC.Collect
      GC.Collect()
    End Try
  End Sub