Solved multiple combox

[ProgramRasta]

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]

if combo1 shows customer name,
why does combo2 show customer name too?

 

[ProgramRasta]

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]

so combo box1 has the column names and combox 2 will apply the filter

 

[ProgramRasta]

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]

DO a search for Cascading Comboboxes

 

[ProgramRasta]

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.

 

[theDBguy]

Hi. Did you see this one in your seach? Just curious...

Microsoft Access Form Cascading Combo Boxes

Creating cascading comboboxes on Microsoft Access forms so selecting one item filters the data in the next combo box's row source

www.fmsinc.com

 

[ProgramRasta]

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]

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]

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]

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]

Thank you!

 

[theDBguy]

Thank you!

See if this is what you mean...

 

[ProgramRasta]

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?

 

[ProgramRasta]

I figured it out, thank you for your help.

I hope you have a nice day.

 

[moke123]

Another option to DBG's solution is to set the properties of the combobox

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

 

[theDBguy]

I figured it out, thank you for your help.

I hope you have a nice day.

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[ProgramRasta]

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]

Another option to DBG's solution is to set the properties of the combobox

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.