Results 1 to 13 of 13
  1. #1
    PatrickKissane
    Guest

    Function as a Combo Box Source

    Hi folks,
    I know I have seen this somewhere, but I can't for the life of me remember where!

    Is it possible to set the source of a combo box to be the result of a function rather than the standard SQL/Table/Query/Value List/Field List? I have an ADO recordset that contains my data, and I am trying to find the best solution for displaying in a combo box.

    Past attempts include using the 'GetString' method of the Recordset object, using the ";" character to delineate the rows and columns. This makes a (long) string which can easily be used as the Row Source of a Value List. This works well, but there is a limit on the string length that can be applied.

    Anybody have any other ideas?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as a Combo Box Source

    You want code something like the following. Set the source of the combo to the name of the function then have a function as follows. (This is copied from a working app, you will need a static variable for the recordset.)

    Private Function HandleWeekCalendar(ctl As Control, lID As Long, lRow As Long, lCol As Long, iCode As Integer) As Variant
    Static lDisplayID As Long

    On Error GoTo Err_HandleWeekCalendar
    Select Case iCode
    Case acLBInitialize
    ' Record and return the lngID for this function.
    lDisplayID = Timer
    HandleWeekCalendar = lDisplayID

    Case acLBOpen
    HandleWeekCalendar = lDisplayID

    Case acLBGetRowCount
    ' Return number of weeks in the selected year
    On Error Resume Next
    HandleWeekCalendar = WeekInfo.MaxWeek

    Case acLBGetColumnCount
    ' Return number of fields (columns)
    HandleWeekCalendar = 3

    Case acLBGetColumnWidth
    HandleWeekCalendar = -1 'default width

    Case acLBGetValue
    Select Case lCol
    Case 0
    HandleWeekCalendar = lRow + 1
    Case 1
    HandleWeekCalendar = WeekInfo.WeekStart(lRow + 1)
    Case 2
    HandleWeekCalendar = WeekInfo.WeekFinish(lRow + 1)
    Case Else
    Beep
    ErrorMsgBox "HandleWeekCalendar called with lCol = " & lCol
    End Select

    Case acLBEnd
    lDisplayID = 0

    End Select

    Bye_HandleWeekCalendar:
    Exit Function

    Err_HandleWeekCalendar:
    ErrorMsgBox Err.Description, "HandleWeekCalendar"
    HandleWeekCalendar = False
    Resume Bye_HandleWeekCalendar
    End Function

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    Thanks for posting that, Andy. I couldn't find a representative VB/VBA callback procedure anywhere in my bag of tricks, since I never got into the habit of using the darn things. I'll borrow this one, if you don't mind, and stick it into my CodeLibrarian (properly credited, of course) for future reference.[img]/w3timages/icons/grin.gif[/img]
    Charlotte

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as a Combo Box Source

    Your welcome Charlotte,
    If I minded I wouldn't have posted the code in the first place. It's not rocket science stuff and similar things exist (I think) in Access help.

    With your other post concerning Report names. Nice idea for the custom property. (To me this implies you allow your users to select reports from a list/combo for some purpose.) I would be interested in the reasoning behind this custom property and the filling of List/Combo boxs.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    Thanks, Andy.

    You're right, I let users select reports from a popup form with a listbox. I use a custom property to hold the name that will make sense to the user, like "Quarterly Sales Report", rather than the actual object name of rptQtrSales. The custom property allows the description property of the report to remain available to hold information about the report. I enforce a naming convention for objects in my databases, so I do not give reports "user-friendly" names themselves. I don't assign display names to subreports or reports that are only run by a DBA from the database window because I don't want them to turn up in the list the users see. This way, when I add reports to an application, I can add them automatically to my reports list for the user to select.

    I usually give my users several ways to run reports. They can print the currently appropriate report from a print button on a form, or they can go to a reports menu from the main switchboard and select a report from there without opening other forms. That's where the DisplayName property has its use. I have a function (actually, two functions--one in DAO and one in ADO) that loops through the collection of reports and builds a semicolon delimited list of actual report names and display names. That list gets popped into the rowsource property of the listbox, which is set for 2 columns with the first column hidden. Then when the user selects a display name in the listbox, I can reference the actual report name in column(0) and open that report. It's tidy and open-ended.
    Charlotte

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Function as a Combo Box Source

    Charlotte,
    If you don't mind I'm going to borrow that idea too. I used to just select from the MSysObjects table and filter out any that started with rpt (any reports for the users to see had 'intelligible' names) but this always had the slight drawback of not all reports following a proper naming convention.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    Be my guest. If you need any code, let me know. I have both DAO and ADO versions of the code that creates the DisplayName property, as well as the routine that returns the list for the listbox.
    Charlotte

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as a Combo Box Source

    Nice idea. I usually print based on filter/sort criteria set on the form that calls the report.
    However I have done a similar thing for a C++ based app - to provide the user with a drop arrow next to the print button (in toolbar) for selecting the report to print when more than one is available.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    I try to keep stuff like that as independent as possible because I'm lazy! I want to be able to take the selection form and the code that drives it and pop it into another application without having to rewrite a bunch of specific calls.[img]/w3timages/icons/grin.gif[/img]
    Charlotte

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as a Combo Box Source

    With the amount of questions you answer I can't believe the first statement that you are lazy.[img]/w3timages/icons/laugh.gif[/img]

    How do you cope with selections for the report. An example from my side. The user has a form with three multi-selection list boxs and radio buttons for dates. (see attatched piccy).
    Attached Images Attached Images

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    Answer is, I don't. What I usually do is force the user through a wizard-like interface that uses a tab control with no visible tabs or buttons but with next and back buttons for navigation like a wizard. That way, I can control the flow and respond appropriately to one thing at a time.

    For example, in one application, if the user picks an option from a menu, they see a form that says "Update interest for all districts?" and yes and no buttons. If they click Yes, the operation goes ahead and runs. If they click no, they see another tab page on the same form that has comboboxes and OK and Cancel buttons and allows them to select a particular service area, which controls the contents of the second combo that allows them to select a district. If they don't select a district, the operation runs for the service area. If they do select the district, it runs for that district only.

    That's a much simpler instance than what's on your form, but you get the idea. No matter how much users may want to see everything at once, I hate the visual clutter and I think it leads to user confusion and error. I also use popup forms a lot to capture individual pieces of information. That way, I can reuse the popup with other selection processes rather than having to design a whole new form. I just mix and match the popups instead! Like I said, I'm lazy![img]/w3timages/icons/grin.gif[/img]
    Charlotte

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Function as a Combo Box Source

    It's now onto design philosophy. I agree with you that screen clutter can lead to mistakes and confusion. However my users seem to like having the overview of all the selected 'filters' rather than having them somewhere they have to navigate to. In a couple of places (not just for reports) I have asked if they would prefer a Tab form to organise sections, sometimes yes, sometimes no. One no is with a form that I can't display on my screen completely. (1280x1024 resolution!).
    For me the important point is that my customers are happy. I want them to come back with more business[img]/w3timages/icons/smile.gif[/img].

    Not a serious question - where is your 'toothy face' in the tags?

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Function as a Combo Box Source

    My philosophy is that customer who wants that kind of nonsense and refuses to be educated is welcome to take his business elsewhere. Life is too short (and I'm too old and cranky [img]/S/granny.gif[/img]) to spend it trying to accommodate ridiculous demands. Usually, if I show them a sample of what can be done, they see the possiblities and quite clinging to a paper form mentality.

    As to the "toothy face", that's the [ grin ] tag and you'll find it on the 1-Click TagPanel above the message box.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •