Recordset based on subset of columns of another recordset

Posted on 2009-02-09
Last Modified: 2012-05-06
I'm only just learning about vba and recordsets so I don't know if I'm looking at this in the right way. All help appreciated.

I've got two forms:

FormA has 20 data fields
FormB has a list box showing 2 of the data fields from FormA

I want to synchronise the Form B list box with the recordsource of FormA, which will change when filters are applied to it. I have achieved this quite simply with:

Set Forms!FormB.lstBox.Recordset = Me.Recordset

However, the application will end up running against tables on Oracle server and want to maximise speed and efficiency, as well as requiring only 2 columns from 20 for the list box.

I know I could create a new recordset based on an SQL statement to select just the columns I want from the base table, but I assume that would call the server again. Given that I've already got the FormA recordset loaded, is there any way I can take just 2 columns from it to poke at the listbox?
Question by:SteveCond
    LVL 17

    Accepted Solution

    I'd say it depends partly on the kind of recordset you're using.  If it's a read only or snapshot type recordset then no problem, you're fetching the data it's a once only job.  If however it's a keyset or dynamic cursor then populating the listbox still means traversing the recordset and fetching data from the server again no matter how few fields are being displayed.  

    Anyway ... one way you can populate a listbox is by using a function in the RowSourceType property.  At least this applies to Access 2000 through 2003 (dunno about 2007).  If you look up RowSourceType in VBA help you'll see that a specifcally written function can be used to populate a listbox from a recordset or whatever.

    I'm including a code snippet sample below

    Note that after the list box has been populated it can leave the recordset in an indeterminate state so you may need to address that

    The function below is used for populating a 5 column listbox from a recordset mrst, note that the listbox uses the function to populate itself all you have to do is make sure that the function retrieves the bits that the listbox needs and then set the RowSourceProperty to the name of the function

    Function esm(ctl As Control, ID As Variant, row As Variant, col As Variant, code As Variant) As Variant
    Dim ReturnVal As Variant
        ReturnVal = Null
        Select Case code
            Case acLBInitialize                ' Initialize.
                ReturnVal = msrst.RecordCount
            Case acLBOpen                        ' Open.
                ' Generate unique ID for control.
                ReturnVal = Timer
            Case 2
                ReturnVal = msrst.RecordCount
            Case acLBGetRowCount            ' Get number of rows.
                ReturnVal = msrst.RecordCount
            Case acLBGetColumnCount    ' Get number of columns.
                ReturnVal = 5
            Case acLBGetColumnWidth    ' Column width.
                ' -1 forces use of default width.
                Select Case col
                Case 0
                    ReturnVal = 0
                Case 1
                    ReturnVal = 1 * twpcm
                Case 2, 3
                    ReturnVal = 2.3 * twpcm
                Case 4
                    ReturnVal = 0.5 * twpcm
                End Select
            Case acLBGetValue                    ' Get data.
                msrst.AbsolutePosition = row + 1
                ReturnVal = msrst(col)
            Case acLBGetFormat
                ReturnVal = -1
            Case acLBEnd                        ' End.
        End Select
        esm = ReturnVal
    End Function

    Open in new window

    LVL 17

    Expert Comment

    Correction to 23598235
    RowSourceProperty = RowSourceType property

    Author Comment

    Thanks, Natchiket. I'll read up on the help you suggest. I won't have chance to try your code until the weekend, but your instructions sound easy enough for me to plug it in.

    Author Closing Comment

    Thanks. I ended up reframing my code to arrive at a solution that is a bit easier to implement in my application. However, the code snippet is interesting and potentially useful later.... Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now