Drop Down Box Group By in VBA

[BoyWonder]

Hi,

I'm hoping that someone can point me in the right direction, I've searched online and cant seem to find the solution to my problem. I think its because i'm searching for the wrong thing.

I have a combo box (Cmb_Price_Matrix) on my form that has two options, depending on what's has been selected in this combo box it instructs another combo box (Cmb_Height) to display results from different tables.

as shown below

Dim sPriceMatrixSource As String

If Me.Cmb_Price_Matrix.Value = "Price Matrix 1" Then
    sPriceMatrixSource = "SELECT [Tbl_Price_Matrix_1].[HeightValue], FROM Tbl_Price_Matrix_1"
    Me.Cmb_Height.RowSource = sPriceMatrixSource
    Me.Cmb_Height.Requery
Else
    sPriceMatrixSource = "SELECT [Tbl_Price_Matrix_2].[HeightValue] FROM Tbl_Price_Matrix_2"
    Me.Cmb_Height.RowSource = sPriceMatrixSource
    Me.Cmb_Height.Requery
End If

I Wish to group the Height value field in the second combo box but cant figure out where to put the GROUP BY part in the code.

my current results show:

1000
1000
1000
1000
1001
1001
1001
1002
1002
1002

and I want it to show:

1000
1001
1002

I can do this in a normal query but trying to do it from VBA code is something that's out of my depth.

Hope the above makes sense and someone can shed some light on this.

Thank you

 

[jdraw]

What are designs of:
Tbl_Price_Matrix_1
Tbl_Price_Matrix_2

Please post the records in these also.

Also can you post the SQL for your "normal query" ?

It seems you may have multiple records in each table.
Is there some criteria to ensure you get 1 specific record returned?

 

[Gasman]

Have you tried

"SELECT [Tbl_Price_Matrix_1].[HeightValue], FROM Tbl_Price_Matrix_1 GROUP BY [HeightValue]"

I believe if you change the rowsource, there is no need for a requery,?

I also think you should only have one table with identfier for 1 or 2?

HTH

 

[Deleted member 147267]

I can do this in a normal query but trying to do it from VBA code is something that's out of my depth.

Then create the query, copy the sql and paste it into your code. You're not using form control references so it should be a straight copy/paste. I could tell you what keyword to use, but that would take all the fun and learning out of it for you.

 

[pbaldy]

Post 2 was moderated, I'm posting to trigger email notifications.

 

[BoyWonder]

Thank you guys for replying back, you pointed me to the right direction!

cant believe it was so easy

 

[isladogs]

@BoyWonder
For the benefit of others finding the thread in future, please outline your solution ….and I agree its very easy

 

[BoyWonder]

@BoyWonder
For the benefit of others finding the thread in future, please outline your solution ….and I agree its very easy

This is how I resolved my issue …

 Dim sPriceMatrixSource As String

If Me.Cmb_Price_Matrix.Value = "Price Matrix 1" Then
    sPriceMatrixSource = "SELECT [Tbl_Price_Matrix_1].[HeightValue] FROM Tbl_Price_Matrix_1 GROUP BY [HeightValue]"
    Me.Cmb_Height.RowSource = sPriceMatrixSource
    Me.Cmb_Height.Requery
Else
    sPriceMatrixSource = "SELECT [Tbl_Price_Matrix_2].[HeightValue] FROM Tbl_Price_Matrix_2 GROUP BY [HeightValue]"
    Me.Cmb_Height.RowSource = sPriceMatrixSource
    Me.Cmb_Height.Requery
End If

Such a simple thing!

Thank you.

 

[Deleted member 147267]

And here was I thinking that it would end up being
"SELECT DISTINCT [Tbl_Price_Matrix_1].[HeightValue] FROM Tbl_Price_Matrix_1...

Often when I see repeated code that doesn't differ much I like to see how much it can be compressed. IF your combo had an additional column that returned the actual table name that would make it even easier, but let's assume

- that column just had text values like 1, 2 and was the 2nd column
- that you don't need square brackets (you don't in this case)
- that DISTINCT would work

Dim tbl As String

tbl = Me.Cmb_Price_Matrix.Column(1)  

Me.CmbHeight.RowSource =  "SELECT DISTINCT Tbl_Price_Matrix_" & tbl & ".HeightValue FROM " & tbl
Me.Cmb_Height.Requery

At the very least, you could move the .Rowsource and Requery lines outside of the IF block - no need to repeat them. Going forward, consider using combos to provide other values not seen that you can reference in code.