ACCESS 2000 - Combo multi-column question

Posted on 2007-09-28
Last Modified: 2012-06-21
A form has several combo boxes.  The user wants two of these to be combined into one combo.

Each of the two fields presently has a distinct query as a Row Source:
   PhaseCode query accesses  Job, Company, Vendor to return the PhaseCode.
   CostCode query accesses the same 3 fields plus PhaseCode.

Making a single query is no problem (I think) but how do I get the separate elements into the cols of the proper column.  

And what do I put into Control Source, which now obviously contains only a single field name from a table?

Moreover, these are actually input fields.  Can that be handled by a multi-column combo?  (I don't think so - and if the answer is No, then the whole thing is a bad idea to be discarded.)

And what other problems are there with this idea?
Question by:ljcor
    LVL 84

    Accepted Solution

    I don't follow the user request ... what's the purpose of combining the two combos?

    When you say "input fields", do you mean  that these are textboxes on your form? If so, you can write values to those textboxes in the AfterUpdate event of your combo:

    Sub MyCombo_AfterUPdate()
      Me.Textbox1 = Me.MyCombo.Column(0)
      Me.Textbox2 = Me.MyCombo.Column(1)
      etc etc
    End Sub

    Combo columns are zero-based, so column 0 is actually the first column.
    LVL 11

    Assisted Solution

    I think you are asking to individually set each of Job, Company and Vendor. These will need to be seperate controls. Each column in a multi column CBO is locked to the values in the same row. You can't for example select row 1 in the first column and row 3 in the second column. You could have a CBO with rows like this:

      Job  |  Company  |  Vendor  |  Phase Code
        1   |        1         |       1        |        1
        2   |        1         |       1        |        2
        3   |        1         |       1        |        3
        1   |        2         |       1        |        4
        2   |        2         |       1        |        5
        3   |        2         |       1        |        6

    but I think it would make much more sense to have three CBOs, one for each of Job, Company and Vendor and to calculate the Phase Code amd Cost Code from the user entries for these.

    Author Comment

    Thanks, guys.  

    While I was waiting for your response to this question I had already decided to have an early Monday morning flash for him:  It's just too much trouble for too little money.  I could explain more but now we are all wasting our time.


    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    746 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

    13 Experts available now in Live!

    Get 1:1 Help Now