CODEWITHSUNDEEP
vbams-accessadodbrecordset
Glenn Angel
Glenn Angel

Reputation: 479

how to apply an ADODB recordset from returned function to a form

I have a Function which I have just made so that I can parametize my queries.

Below is the function used:

Public Function getLOTSRSTparam(strSQL As String, paramValue As Variant, _
                                Optional skip As Boolean) As ADODB.Recordset

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.parameter
    Dim RS As ADODB.Recordset
    Set db = CurrentDb

Set Cn = New ADODB.Connection
Cn.Open Right(LCon, Len(LCon) - 5)
Set Cm = New ADODB.Command
With Cm
    .ActiveConnection = Cn
    .CommandText = strSQL
    .CommandType = adCmdText

    For i = LBound(paramValue) To UBound(paramValue)
        .Parameters.Append .CreateParameter("ChemID", GetParameterType(paramValue(i)), adParamInput, Len(Nz(paramValue(i), " ")), paramValue(i))
    Next i

        Set getLOTSRSTparam = .Execute
  End With

End Function

I will be using this function for many reasons and for lots of queries however the returned ADODB recordset is getting an error when used to be the reocrdset of a form.

strSQL = "SELECT * FROM Person WHERE person.firstname LIKE ? AND person.lastname LIKE ? Order by person.lastname asc, person.firstname asc"

        Dim arrValue As Variant
        Dim qStrLastName As String
        Dim qStrFirstName As String
        qStrLastName = strLastName & "%"
        qStrFirstName = strFirstName & "%"

        arrValue = Array(qStrFirstName, qStrLastName)

        Set lotsRS = getLOTSRSTparam(strSQL, arrValue)
        If lotsRS.EOF Then

            MsgBox "No patients found, try again", vbExclamation, "Error"
            Forms!frmMediDrop.NavigationSubform.Form.txtpatient.SetFocus
            DoCmd.Close acForm, "frmPxSearch"
            Exit Sub
        Else
           Do Until lotsRS.EOF
               Debug.Print lotsRS!firstName & " " & lotsRS!lastName
               lotsRS.MoveNext
            Loop
            Set Me.subfrmPxSearchList.Form.Recordset = lotsRS
        end if

As you can see I do loop through and debug and yes, the recordset it returned as the names all pop up in the immediate window... however when I get to the last line I get:

The object you entered is not a valid Recordset property (7965)

I don't know where to go from here as I am more experienced with DAO recordsets however they cannot seem to have parametized queries using MYSQL and ODBC passthrough.

Any advice would be great, thanks

Upvotes: 1

Views: 168

Answers (1)

Glenn Angel
Glenn Angel

Reputation: 479

I found the answer here: Answer here

Pretty much (and i don't understand any of the theory of this) you need to set the connection cursor location to adUseClient befoer opening and doing the command.

New code:

Public Function getLOTSRSTparam(strSQL As String, paramValue As Variant, Optional skip As Boolean) As ADODB.Recordset

    Dim Cn As ADODB.Connection
    Dim Cm As ADODB.Command
    Dim Pm As ADODB.parameter
    Dim RS As ADODB.Recordset
Set db = CurrentDb

Set Cn = New ADODB.Connection
    Cn.Open Right(LCon, Len(LCon) - 5)
    Cn.CursorLocation = adUseClient   <-------This was added
    Set Cm = New ADODB.Command
    With Cm
        .ActiveConnection = Cn
        .CommandText = strSQL
        .CommandType = adCmdText

    For i = LBound(paramValue) To UBound(paramValue)
        .Parameters.Append .CreateParameter("ChemID", GetParameterType(paramValue(i)), adParamInput, Len(Nz(paramValue(i), " ")), paramValue(i))
       Next i

        Set getLOTSRSTparam = .Execute
    End With

End Function

Upvotes: 1

Related Questions