Combo Question: Switching Order By

[Meltdown]

Hi friends,

I've got a drop down combo with the following rowsource info...

SELECT [tblProducts].[ProductID], [tblProducts].[ProductName] FROM tblProducts ORDER BY [tblProducts].[ProductID];

...which is fine.

The problem is some of users prefer to search by Product ID and some by Product Name. Is there any way to allow the user to select how they want the combo list ordered?

I'd prefer not to have to add a second optional combo

Thanks for any help

 

[databasedonr]

You could add a radio button for the user to select the sort order, and then through code (If/Else or Case) launch the query with the appropriate sort order. More work for you, but transparent for the users.

 

[Meltdown]

I know how to build the If/Else to create two seperate SQL strings but how do I apply it to combo rowsource just using option button?

Thanks for the reply

 

[databasedonr]

It might go something like this (it's from VB, but should be pretty similar to VBA - forgive me, I'm learning both)

If Option1 = True Then
SELECT [tblProducts].[ProductID], [tblProducts].[ProductName] FROM tblProducts ORDER BY [tblProducts].[ProductID];

End If

ElseIf Option2 = True Then
SELECT [tblProducts].[ProductID], [tblProducts].[ProductName] FROM tblProducts ORDER BY [tblProducts].[ProductName];

End If

Run these from a Command button, so that the user selects the radio button and then the command button to launch the query.

Does that makes sense?

 

[Kevin_S]

something like this:

Dim strSQL1 as String
Dim strSQL2 as String

strSQL1 = "combo box SQL string one way"
strSQL2 = "combo box SQL string the other way"

select case me.youroptiongroupnamehere.value
Case is 1
Me.yourcomboboxname.rowsource = strSQL1
Case is 2
Me.yourcomboboxname.rowsource = strSQL2
end select
end sub

 

[Meltdown]

Many Thanks databasedonr and Kevin for taking the time to reply. I have it working now.

Cheers