none
Trouble Displaying Record Set RRS feed

  • Question

  • Hi Folks -

    I have te following function:

    Public Function SFDC_GetPartner()
    
        Dim dbs As DAO.Database
        Dim strSQL As String
        
        Set ws = DBEngine.Workspaces(0)
        Set dbs = ws.Databases(0)
        
        Dim value As String
        value = "0013600001uIMv9AAG"
    
        strSQL = "SELECT DISTINCT [SFDC_PARTNER].[MDM_PARTNER_ID] FROM [SFDC_PARTNER], [SFDC_PARTNERSHIP] WHERE [SFDC_PARTNER].[SALESFORCE_PARTNER_ID] = "" & value & "" AND [SFDC_PARTNER].[MDM_PARTNER_ID] IS NOT NULL"
        Set lkpRS = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    
        MsgBox lkpRS
       
    End Function

    But having trouble displaying lkpRS.  Can anyone let me know where I am going wrong?

    Thank you!

    Wednesday, September 18, 2019 8:54 AM

All replies

  • Recordset is an object...what you expect to return when you pass it to Msgbox. ?

    A common "use" of recordset along with Msgbox is

    Msgbox rst.Recordcount

    but we cannot just Msgbox the recordset...


    Wednesday, September 18, 2019 9:13 AM
  • Try MsgBox lkpRS!MDM_PARTNER_ID

    Groeten, Peter http://access.xps350.com/

    Wednesday, September 18, 2019 10:28 AM
  • You can't display a recordset.  You can either iterate through it to get each individual record's field values, or you could assign it to a form which in turn could display it.

    Here's an example of iterating through a recordset

    Public Function SFDC_GetPartner()
        Dim dbs                   As DAO.Database
        Dim lkpRS                 As DAO.Recordset
        Dim strSQL                As String
        Dim value                 As String
        
        Set Ws = DBEngine.Workspaces(0)
        Set dbs = Ws.Databases(0)
    
        value = "0013600001uIMv9AAG"
        strSQL = "SELECT DISTINCT [SFDC_PARTNER].[MDM_PARTNER_ID] " & vbCrLf & _
                 "FROM [SFDC_PARTNER], [SFDC_PARTNERSHIP] " & vbCrLf & _
                 "WHERE [SFDC_PARTNER].[SALESFORCE_PARTNER_ID]='" & value & "' AND [SFDC_PARTNER].[MDM_PARTNER_ID] IS NOT NULL"
        Set lkpRS = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
        With lkpRS
            If .RecordCount <> 0 Then
                Do While Not .EOF
                    MsgBox ![MDM_PARTNER_ID]
                    .MoveNext
                Loop
            Else
                MsgBox "There are no records matching your criteria"
            End If
        End With
    
        lkpRS.Close
        Set lkpRS = Nothing
        Set dbs = Nothing
    End Function

    As always, do not forget proper error handling.

    If you want to get the count, ie .RecordCount, be sure to perform a .MoveLast beforehand.  Then use a .MoveFirst before the iteration.


    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net


    Wednesday, September 18, 2019 11:58 AM
  • If you simply want to open the result table of an SQL statement as a datasheet, the following function, into which the SQL statement is passed as a string expression, will do this:

    Public Function OpenTempQuery(strSQL As String)

        Dim qdf As DAO.QueryDef
        Static n As Integer
        
        n = n + 1
                   
        ' delete temporary querydef object if exists
        On Error Resume Next
        CurrentDb.QueryDefs.Delete "Temp" & n
        Select Case Err.Number
            Case 0
            ' no error
            Case 3265
            ' temporary querydef does not exist,ignore error
            Case Else
            ' unknown error
            MsgBox Err.Number, vbExclamation, "Error"
        End Select
        ' create temporary querydef object
        Set qdf = CurrentDb.CreateQueryDef("Temp" & n)
        CurrentDb.QueryDefs("Temp" & n).SQL = strSQL
        ' open query and then delete temporary querydef object
        DoCmd.OpenQuery "Temp" & n
        CurrentDb.QueryDefs.Delete "Temp" & n

    End Function

    The real intended purpose of this function is to allow simple 'what if' comparisons by calling the function multiple times with variations in the WHERE clause each time.  By virtue of the Static variable n each datasheet will remain open while others are opened.  A more developed method of doing the same thing is to open multiple instances of the same bound form.  You'll find an example of how to do this in MultiForms.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, September 18, 2019 12:57 PM Typo corrected.
    Wednesday, September 18, 2019 12:47 PM
  • It seems like only one value will be shown, thus DLookup will do:

    Public Function SFDC_GetPartner()
    
        Dim Filter As String
        Dim Value As String
    
        Value = "0013600001uIMv9AAG"
    
        Filter = "[SALESFORCE_PARTNER_ID] = '" & Value & "' AND [MDM_PARTNER_ID] IS NOT NULL"
        MsgBox Nz(DLookup("[MDM_PARTNER_ID]", "[SFDC_PARTNER]", Filter))
       
    End Function


    Gustav Brock

    Thursday, September 19, 2019 8:24 AM