Recordset based on subset of columns of another recordset

Posted on 2009-02-09
Medium Priority
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
  • 2
  • 2
LVL 17

Accepted Solution

Natchiket earned 375 total points
ID: 23598235
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

ID: 23598309
Correction to 23598235
RowSourceProperty = RowSourceType property

Author Comment

ID: 23604143
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

ID: 31544562
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.

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 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