Recordset based on subset of columns of another recordset

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?
Who is Participating?
NatchiketConnect With a Mentor Commented:
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

Correction to 23598235
RowSourceProperty = RowSourceType property
SteveCondAuthor Commented:
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.
SteveCondAuthor Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.