CODEWITHSUNDEEP
sqlvb.netms-accesscombobox
Alex
Alex

Reputation: 283

How to display multiple columns as a list in a ComboBox?

I want to display multiple, specific columns in a combo box and use it as a search parameter for a query. Any ideas if that's something I can do?

I'll give an example of my table below:

Company Name    Document    Notes    2nd Document    Notes 2
A               1           test     2               test
B               1           test     2               test
C               1           test     2               test
D               1           test     2               test

I have a combobox parameter set up that searches a query via the company name. So if I select 'A' in my first Combo Box; the results would return:

Company Name    Document    Notes    2nd Document    Notes 2
A               1           test     2               test

I want the next combo box to display the 'Document' column and the '2nd Document' column; essentially displaying a list of documents for a specific company.

Then once a document is selected; I would like a textbox to display the next column over from it.

For example

Combo box 1     Combo box 2           txt box:
'A' selected    '2' selected          Displays text from 'Notes 2'

So in a Nutshell, how can I get a combobox to display specific columns of a query result, if possible? - and how can I get a text box to display the column to the right of the selection in the previous combo box( e.g., selected '1' would display 'Notes' column, selecting '2' would display 'Notes 2' column If not, any alternatives? Thanks!

Edit: I had an idea - Perhaps there is a way for a query to convert multiple, specific columns into a single list; then display that list in a combobox?

Upvotes: 0

Views: 1790

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8404

It actually sounds like (unless I'm mistaken) what's referred to as Cascading Comboboxes.

So, your first combo box displays all unique options for Company Name:

MyCombo1.RecordSource = "SELECT Distinct CompanyName FROM MyTable"

The second combo box selects all unique options for the selected Company Name:

MyCombo2.RecordSource = "SELECT Distinct Document FROM MyTable 
    WHERE CompanyName = '" & MyCombo1.Value & "'"

You can do this infinitely if you need to. Just keep adding the previous combo box's value to the WHERE clause of the next combo box's RecordSource.

Lastly, in the AfterUpdate event of the last combo box, you set up the text displayed. So, in MyCombo2's AfterUpdate event, add this:

Dim db as Database
Dim rec as Recordset

Set db = CurrentDB
Set rec = db.OpenRecordset("SELECT Notes2 FROM MyTable 
    WHERE CompanyName = '" & MyCombo1.Value & "' AND Document = '" & MyCombo2.Value & "'"

MyTextbox.Text = rec(0)

Of course, it would make it a little easier (a lot easier, actually) if each record had some sort of ID number, so you can reference that instead of having to filter by both CompanyName and Document, but that's the general idea.

EDIT

I just realized your dataset isn't normalized and my answer won't work without some tweaking. If at all possible, you should normalize your dataset. Not to sound like a data snob, but there's a reason people go to school to learn this stuff; it's important. Your table should actually look more like this:

CompanyName      Document     Notes
A                1            Test
A                2            Test 2
B                1            Test
B                2            Test 2

Upvotes: 2

Related Questions