• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

ACCESS 2000 - Combo multi-column question

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?
2 Solutions
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
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.
ljcorAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now