Opening a Parameter Query in ADO

[LuRSH]

Hi Folks,

The following function returns the Absolut Position of a record in a Recorset.

It works pretty well with common queries or tables. For those I use the parameter strQryTblRecordsource to pass to the function (as the name indicates) the Record Source of the Query or Table being used by the Form (me.RecordSource).

But now I have a form which is connected to a parameter query (I use the parameter to filter the records I want to be shown in the form, when the user select a button in a frame I change the parameter value).

The question is how to change the record source to reflect the presence of a parameter in the query? I tried looking to the SQL structure of the query but it didn't help. Sure enough the parameter is there (Form!myform.txtfilter) but this is the structure to call the query but how the parameter is passed to it?


Function fncFindPosition(strValuetoSearch As String, _
strFldtoBeSaught As String, _
strQryTblRecordSource As String) As String
On Error GoTo Err_fncFindPosition
Dim rst As ADODB.Recordset
Dim strSQL As String

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open strQryTblRecordSource
rst.Close
strSQL = "[" & strFldtoBeSaught & "] = " & strValuetoSearch
rst.Find strSQL

If rst.EOF Then
fncFindPosition = "Record not Found!"
Else
fncFindPosition = rst.AbsolutePosition
End If

rst.Close
Set rst = Nothing

Exit_fncFindPosition:
Exit Function

Err_fncFindPosition:
MsgBox Err.Description
Resume Exit_fncFindPosition

End Function

Thanks in advance for any help!

 

[JimHorton]

With ADO, you use a '?' in place of the [someparameter], and then the recordset's source is the Command object's commandText. Then set (in exact order) the command.parameter to the value for each '?'. I never use ado for the Access provider (if I'm using Jet, DAO is quite sufficient), but for the microsoft's Oracle provider, this is the way.
--Jim