Using Select Case to change ListBox RowSource

[stonward]

Hi Folks,

I have a listbox listing my products. The listbox rowsource changes depending upon the user's selection of exactly which products to list, and in addition there are two pricing structures, trade and retail. I have this working quite well using If Then Else, but there is surely a more elegant solution using Select Case. The following code always jumps to Case Else, although I know thru debug that each case is evaluated and that the correct rowsource query is found. But the source is not being altered. Can you see what is wrong?

Dim querySource as String
querySource = me.lstProds.RowSource

Select Case querySource
       Case "qry_SearchedStockR"
            Debug.Print querySource
            lstProds.RowSource = "qry_searchedStockT"
       Case "qry_AllSearchedR"
            lstProds.Rowsource = "qry_AllSearchedT"
       Case "qryNotTyreR"
            lstProds.RowSource = "qry_NotTyreT"
       Case Else
            Msgbox "Trade Price Not Available in This View."

End Select

I have missed out further case statements, debug lines and the turning on/off of labels for the sake of brevity.
Naturally I have tried a number of methods to get this working, but think I'm missing something, um, profound?

Thanks for your time.

Best Regards,

Stonward

[Seth Schrock]

In line 3, put

Debug.Print querySource

and tell us what it says. It seems strange that your If Then Else worked, but not your Select Case.

[stonward]

Hi Seth.

The immediate window shows the current query underlying the listbox ("qry_SearchedS tockR"), which is simply all products in stock and showing Retail price. And it's correct. If I change the listbox query, the Select Case debug statement is always correct. It just doesn't CHANGE the rowsource as it needs to.

[Seth Schrock]

Then I'm stumped as to why it goes to the Case Else instead of first Case.

[stonward]

Hi Seth,

Ha Ha! That's exactly what i told my user; "Stumped"! Here's the code I'm using at the moment. "Trade Note" is simply a label that says 'Trade Price Only' and I use it's Visible property to gauge which pricing I'm on...

If lblTradeNote.Visible = False Then
Me.lblTradeNote.Visible = True
Me.cmdPricing.Caption = "Retail Pricing"
If List55.rowSource = "qry_searchedstockR" Then
    List55.rowSource = "qry_searchedstockT"
ElseIf List55.rowSource = "qry_allsearchedR" Then
    List55.rowSource = "qry_allsearchedT"
ElseIf List55.rowSource = "qry_nottyreR" Then
    List55.rowSource = "qry_nottyreT"
Me.cmdPricing.Caption = "Retail Pricing"

End If

'if Trade is ON
ElseIf lblTradeNote.Visible = True Then
lblTradeNote.Visible = False
cmdPricing.Caption = "Trade Pricing"
If List55.rowSource = "qry_searchedstockT" Then
    List55.rowSource = "qry_searchedstockR"
ElseIf List55.rowSource = "qry_allsearchedT" Then
    List55.rowSource = "qry_allsearchedR"
ElseIf List55.rowSource = "qry_nottyreT" Then
    List55.rowSource = "qry_nottyreR"
cmdPricing.Caption = "Trade Pricing"


End If

End If

The above works, but does 'strange' things now and again. I got bigger issues at the moment tho...

Thanks for your time.

Regards,

Stonward

[zmbd]

What you didn't show us was the output from the debug.print statements.
Tad difficult to determine why the Select failed without that information.

[stonward]

Hi ZMBD,

Yeah, no apparent issue. The default query the listbox opens with
"qry_SearchedSt ockR" is what comes up with debug.print, including adding a debug command at line 3.

Stonward

[Seth Schrock]

Just an idea, does it literally say qry_SearchedSto ckR or does it give the SQL code that makes up the query?

[zmbd]

If you still have the select case code.
If you would please place the debug.print back in at line 3.
Please run the form so the code executes.
<Ctrl><G> to open the immediate window.
Select all of the text that is printed in the immediate window
<ctrl><C>
open a new post in this thread and <ctrl><P>
Something very suspicious going on here... decades of programming and I've never seen a select case fail without cause.

[ADezii]

If you generated the Row Source by using the Wizard with a Saved Query as the base, then modified it, the Row Source would be a SQL Statement and not the Name of the Query.