Solved multiple combox (1 Viewer)

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
Hello,

I hope someone can help me.

I have a combobox with an on focus event that is equal to "customer name", "customer age", "customer address", these are all column names in the Customer table.

I would like a second combobox that will query the customer table based on the value that is entered in combobox1.

If customer name is chosen, combobox 2 will show all the customer names, if customer age is selected, it will show the customer ages etc.

Many thanks.
 

Ranman256

Well-known member
Local time
Today, 11:42
Joined
Apr 9, 2015
Messages
4,339
if combo1 shows customer name,
why does combo2 show customer name too?
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
if combo1 shows customer name,
why does combo2 show customer name too?
Thank you for the reply.

What I want to do is apply a filter on the table based on the value I enter into combobox1.

If I choose "name" in combobox1, I can then filter the table on the value I select in combox2.

Sorry I'm still in school and not too technical, I am just learning Access in my free time.

Thank you.
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
so combo box1 has the column names and combox 2 will apply the filter
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
I don't think I explained myself too well.

I have a combo box that has fields a, b ,c ,d ,e...

These are column names in table A

In combobox1 - I will select a or b or c or d or e

combobox 2 will then show me

if a - customer names in Table A
if b - customer addresses in Table A
….

Thank you
 

moke123

AWF VIP
Local time
Today, 11:42
Joined
Jan 11, 2013
Messages
3,851
DO a search for Cascading Comboboxes
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
Thank you I have searched everywhere but unfortunately I am unsuccessful in my attempts.

Is there a way I can pass the value from combobox1 to a select query in combobox2?

i.e. if combobox1 = 'a' "select a from Table A", if combobox1 = 'b' select b from Table A.

I'm really stuck on what to do.

thank you.
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
Thanks for taking the time to reply.

I did and either it's different to my problem or I'm just not smart enough to get it.

In combobox 1, I have a list of column names in Table A, a,b,c,d,e - these are not linked to anything.

I need combobox 2 to "read" combobox1.

if the answer to combobox1 is "a" select distinct a from Table A"
If "b" select distinct "b" from Table A

thanks again, sorry if I'm wasting your time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Can you show us what you've tried so far? Also, you might consider posting a sample db to make sure we're telling you something you can immediately use for your project.
 

moke123

AWF VIP
Local time
Today, 11:42
Joined
Jan 11, 2013
Messages
3,851
Its still not really clear but sounds like you need to build the rowsource of combo 2 in after update of combo 1

if me.combo1 = "A" then
Me.combo2.rowsource = "select distinct .......
elseif me.combo1 = "B" then
Me.combo2.rowsource = "select distinct .......
elseif . . .
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:42
Joined
Oct 29, 2018
Messages
21,358
Its still not really clear but sounds like you need to build the rowsource of combo 2 in after update of combo 1
I agree, and that's exactly what the FMS website shows how to do in the link provided in Post #8.
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
See if this is what you mean...

It is! Thank you DBGuy, your help is much appreciated.

May I ask how you managed to do it and if it would work if I had 10 column names and I only want a subset of them to be included?
 

moke123

AWF VIP
Local time
Today, 11:42
Joined
Jan 11, 2013
Messages
3,851
Another option to DBG's solution is to set the properties of the combobox

Code:
Private Sub Combo14_AfterUpdate()

    Select Case Me.Combo14

    Case "CustomerName"

        Me.Combo16.ColumnWidths = "1in,0,0,0,0"
        Me.Combo16.BoundColumn = 0

    Case "CustomerAge"

        Me.Combo16.ColumnWidths = "0,1in,0,0,0"
        Me.Combo16.BoundColumn = 1

    Case "CustomerAddress"

        Me.Combo16.ColumnWidths = "0,0,1in,0,0"
        Me.Combo16.BoundColumn = 2

    Case "CustomerPhone"

        Me.Combo16.ColumnWidths = "0,0,0,1in,0"
        Me.Combo16.BoundColumn = 3

    Case "CustomerNotes"

        Me.Combo16.ColumnWidths = "0,0,0,0,1in"
        Me.Combo16.BoundColumn = 4

    End Select

End Sub
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
Thank you all for the replies!

I just noticed DBGuy that I can select and view the values but it doesn't filter the list. It's not acting as a filter as I would have expected. Any ideas?

Thanks again
 

ProgramRasta

Member
Local time
Today, 15:42
Joined
Feb 27, 2020
Messages
98
Another option to DBG's solution is to set the properties of the combobox

Code:
Private Sub Combo14_AfterUpdate()

    Select Case Me.Combo14

    Case "CustomerName"

        Me.Combo16.ColumnWidths = "1in,0,0,0,0"
        Me.Combo16.BoundColumn = 0

    Case "CustomerAge"

        Me.Combo16.ColumnWidths = "0,1in,0,0,0"
        Me.Combo16.BoundColumn = 1

    Case "CustomerAddress"

        Me.Combo16.ColumnWidths = "0,0,1in,0,0"
        Me.Combo16.BoundColumn = 2

    Case "CustomerPhone"

        Me.Combo16.ColumnWidths = "0,0,0,1in,0"
        Me.Combo16.BoundColumn = 3

    Case "CustomerNotes"

        Me.Combo16.ColumnWidths = "0,0,0,0,1in"
        Me.Combo16.BoundColumn = 4

    End Select

End Sub
Thank you, I will try use the Case statements.
 

Users who are viewing this thread

Top Bottom