SQL for list manipulation (a mess)

Hello,

I have a set of queries which have taken on a life of their own!  I don't 
believe there's room here for all the SQLs (nor would I wish that on anyone) 
but there has to be a simplier way to do this.

I have a form (zfmMain) which has a listbox (lstEMainE), a toggle 
(tglECInactive) and subform (subfmMainE) with a listbox (lstECsubEc) with 8 
or 9 columns dependeng on the selection in lstEMainE (specific Engagement or 
"*" - All), three toggles (tglECOpen, tglECHold, tglECClosed) and 8/9 label 
column headings (lblEC1…lblEC9).

In VBA on the Click Event for lstEMainE, I declare variables: stSQLs 
(SELECT), stSQLw1 (WHERE part 1), stSQLw2 (WHERE part 2), stSQLo (ORDER BY) 
and stSQL to concatenate the parts.

When a selection in lstEMainE clicked, the program looks to see:
1)what combination of the 3 toggles on the subform are depressed (I assigned 
a value -2,-4,-8 to variables and multiplied by the boolean value of the 
toggles and added all, then used Select Case for Case 2-14);
2)what column is the lstECSubEc sorted on and whether ascending or decending 
(when a lblEC1 thru 9 are clicked, a hidden text box is assigned a value 1-9 
depending on the cloumn label and checks whether another hidden text box is 
"a" or"d" for the previous sort being ascending or decending then using 
Select Case for Case 1-9 with an If statement for ascending/decending in each 
Case clause.)
3)whether the tglECInactive is depressed on zfmMain (filter by a CEInactive 
field in the ClientEngagement table;.

AND THIS IS JUST FOR THE lstEMainE CLICK EVENT.  I also have programming 
triggered on changes to the combination of the 3 toggles on the subform which 
also has to check all the other "variables" in this cluster**** and for the 
click events of each label which, again has to check all the "variables".  
That's about 600 lines on the zfmMain and about 1500 lines on the subform.  
(Probably not that much to a pro but this is by far the biggest thing I've 
ever done in VBA!)

To top it off, my SELECT statement pulls from two tables LEFT JOINED but 
with a filter (WHERE part 1) on the right table which is negating the LEFT 
JOIN!!!!  Trying to fix this in all the places in the code stSQLw2 is set is 
a bear.

THIS WHOLE THING IS STRETCHING MY MEGER CAPABILITIES!

Is there some "standardized" way people (experts) do sorting/filtering of 
lists based on multiple "inputs"?  (I would think sorting on column labels 
would be pretty common). Or are there any other "tricks" of which I am 
ignorant?

Whew!

Thank you in advance for any information or for steering me to something 
that will help.
Robin


0
Utf
1/27/2010 11:45:01 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

10 Replies
461 Views

Similar Articles

[PageSpeed] 18

Hi Robin,

I suspect it will be very difficult for anyone to help you without seeing 
your actual SQL statements. You should go ahead and post them and let us take 
a look.


-- 
Lynn Trapp
MCP, MOS, MCAS
Lynn.TrappNoSpam@hotmail.com


"Robin" wrote:

> Hello,
> 
> I have a set of queries which have taken on a life of their own!  I don't 
> believe there's room here for all the SQLs (nor would I wish that on anyone) 
> but there has to be a simplier way to do this.
> 
> I have a form (zfmMain) which has a listbox (lstEMainE), a toggle 
> (tglECInactive) and subform (subfmMainE) with a listbox (lstECsubEc) with 8 
> or 9 columns dependeng on the selection in lstEMainE (specific Engagement or 
> "*" - All), three toggles (tglECOpen, tglECHold, tglECClosed) and 8/9 label 
> column headings (lblEC1…lblEC9).
> 
> In VBA on the Click Event for lstEMainE, I declare variables: stSQLs 
> (SELECT), stSQLw1 (WHERE part 1), stSQLw2 (WHERE part 2), stSQLo (ORDER BY) 
> and stSQL to concatenate the parts.
> 
> When a selection in lstEMainE clicked, the program looks to see:
> 1)what combination of the 3 toggles on the subform are depressed (I assigned 
> a value -2,-4,-8 to variables and multiplied by the boolean value of the 
> toggles and added all, then used Select Case for Case 2-14);
> 2)what column is the lstECSubEc sorted on and whether ascending or decending 
> (when a lblEC1 thru 9 are clicked, a hidden text box is assigned a value 1-9 
> depending on the cloumn label and checks whether another hidden text box is 
> "a" or"d" for the previous sort being ascending or decending then using 
> Select Case for Case 1-9 with an If statement for ascending/decending in each 
> Case clause.)
> 3)whether the tglECInactive is depressed on zfmMain (filter by a CEInactive 
> field in the ClientEngagement table;.
> 
> AND THIS IS JUST FOR THE lstEMainE CLICK EVENT.  I also have programming 
> triggered on changes to the combination of the 3 toggles on the subform which 
> also has to check all the other "variables" in this cluster**** and for the 
> click events of each label which, again has to check all the "variables".  
> That's about 600 lines on the zfmMain and about 1500 lines on the subform.  
> (Probably not that much to a pro but this is by far the biggest thing I've 
> ever done in VBA!)
> 
> To top it off, my SELECT statement pulls from two tables LEFT JOINED but 
> with a filter (WHERE part 1) on the right table which is negating the LEFT 
> JOIN!!!!  Trying to fix this in all the places in the code stSQLw2 is set is 
> a bear.
> 
> THIS WHOLE THING IS STRETCHING MY MEGER CAPABILITIES!
> 
> Is there some "standardized" way people (experts) do sorting/filtering of 
> lists based on multiple "inputs"?  (I would think sorting on column labels 
> would be pretty common). Or are there any other "tricks" of which I am 
> ignorant?
> 
> Whew!
> 
> Thank you in advance for any information or for steering me to something 
> that will help.
> Robin
> 
> 
0
Utf
1/28/2010 6:14:14 PM
Ok…don’t laugh.
(I don't expect a troubleshoot. Just...there has to be a better way!)

Well I just hit "Post" and got a message Limit=30000, Current=52352
________________________________________________________________
ON FORM zfmMain
________________________________________________________________
Private Sub lstEMainE_Click()
On Error GoTo Err_lstEMainE_Click
    Dim stSQLs As String
    Dim stSQLf As String
    Dim stSQLw1 As String
    Dim stSQLw2 As String
    Dim stSQLo As String
    Dim stSQL As String
    Select Case Form_subfmMainE.tabSubE
        Case 0
            Dim intTglECSum As Integer
            Dim intTglECOpen As Integer
            Dim intTglECHold As Integer
            Dim intTglECComplete As Integer

            intTglECOpen = -2
            intTglECHold = -4
            intTglECComplete = -8
intTglECSum = (Form_subfmMainE.tglECOpen * intTglECOpen) + 
(Form_subfmMainE.tglECHold * intTglECHold) + _
                (Form_subfmMainE.tglECComplete * intTglECComplete)
 stSQLf = "FROM (Client INNER JOIN ClientOL ON Client.ClientID = 
ClientOL.[User Field 1]) " & _
                "INNER JOIN ClientEngagement ON Client.ClientID = 
ClientEngagement.ClientID "
            Select Case Me.tglEInactive
                Case False
                    If Me.lstEMainE = "*" Then
                        stSQLw1 = ""
                        Else
stSQLw1 = "WHERE ((ClientEngagement.EngagementID) = 
[Forms]![zfmMain]![lstEMainE]) "
                    End If
                Case True
                    If Me.lstEMainE = "*" Then
                        stSQLw1 = "WHERE ((ClientEngagement.CEInactive) = 
True) "
                        Else
                            stSQLw1 = "WHERE 
((ClientEngagement.EngagementID) = [Forms]![zfmMain]![lstEMainE]) "
                        End If
            End Select
            
            Select Case Form_subfmMainE.txtEcECo
                Case 1
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.ClientID;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.ClientID 
DESC;"
                    End Select
                Case 2
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientOL.[File As];"
                        Case "d"
                            stSQLo = "ORDER BY ClientOL.[File As] DESC;"
                    End Select
                Case 3
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.EngagementID;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.EngagementID 
DESC;"
                    End Select
                Case 4
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.EngagementYr;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.EngagementYr 
DESC;"
                    End Select
                Case 6
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.CAStaffID;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.CAStaffID 
DESC;"
                    End Select
                Case 7
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.CADueDate;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.CADueDate 
DESC;"
                    End Select
                Case 8
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY ClientEngagement.CABudgetHrs;"
                        Case "d"
                            stSQLo = "ORDER BY ClientEngagement.CABudgetHrs 
DESC;"
                    End Select
                Case 9
                    Select Case Form_subfmMainE.txtEcECs
                        Case "a"
                            stSQLo = "ORDER BY 
IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -'));"
                        Case "d"
                            stSQLo = "ORDER BY 
IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) DESC;"
                    End Select
            End Select
            
            Select Case intTglECSum
                Case 0
                    stSQLs = ""
                    stSQLf = ""
                    stSQLw1 = ""
                    stSQLw2 = ""
                    stSQLo = ""
                Case 2
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE ((ClientEngagement.CEHold) 
= False) AND ((ClientEngagement.CEComplete) = False) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) = False) AND ((ClientEngagement.CEComplete) = 
False) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND ((ClientEngagement.CEHold) = 
False) AND ((ClientEngagement.CEComplete) = False) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) = False) AND ((ClientEngagement.CEComplete) = 
False) "
                            End If
                    End Select
                Case 4
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus,, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE ((ClientEngagement.CEHold) 
= True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) = True) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND ((ClientEngagement.CEHold) = 
True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) = True) "
                            End If
                    End Select
                        
                Case 6
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE (((ClientEngagement.CEHold) 
= True) OR ((ClientEngagement.CEComplete) = False)) "
                                Else
                                    stSQLw2 = "AND 
(((ClientEngagement.CEHold) = True) OR ((ClientEngagement.CEComplete) = 
False)) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = False)) "
                                Else
                                    stSQLw2 = "AND 
(((ClientEngagement.CEHold) = True) OR ((ClientEngagement.CEComplete) = 
False)) "
                            End If
                    End Select
                Case 8
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE 
((ClientEngagement.CEComplete) = True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEComplete) = True) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND 
((ClientEngagement.CEComplete) = True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEComplete) = True) "
                            End If
                    End Select
                Case 10
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE ((ClientEngagement.CEHold) 
<> True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) <> True) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND ((ClientEngagement.CEHold) <> 
True) "
                                Else
                                    stSQLw2 = "AND 
((ClientEngagement.CEHold) <> True) "
                            End If
                    End Select
                Case 12
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "WHERE (((ClientEngagement.CEHold) 
= True) OR ((ClientEngagement.CEComplete) = True)) "
                                Else
                                    stSQLw2 = "AND 
(((ClientEngagement.CEHold) = True) OR ((ClientEngagement.CEComplete) = 
True)) "
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = True)) "
                                Else
                                    stSQLw2 = "AND 
(((ClientEngagement.CEHold) = True) OR ((ClientEngagement.CEComplete) = 
True)) "
                            End If
                    End Select
                Case 14
                    If Me.lstEMainE = "*" Then
                        stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementID, 
ClientEngagement.EngagementYr, " & _
                            "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                            "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,' 
    Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus, 
ClientEngagement.CATrackBudget "
                        Form_subfmMainE.lblEc3.Visible = True
                        Form_subfmMainE.lblEc7.Caption = "DueDate"
                        Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                        Form_subfmMainE.lstECsubEc.ColumnCount = 9
                        Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                        Else
                            stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                                "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                                
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus, ClientEngagement.CATrackBudget "
                            Form_subfmMainE.lblEc3.Visible = False
                            Form_subfmMainE.lblEc7.Caption = "DueDate"
                            Form_subfmMainE.lblEc8.Caption = "BudgetHrs"
                            Form_subfmMainE.lstECsubEc.ColumnCount = 8
                            Form_subfmMainE.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
                    End If
                    Select Case Form_zfmMain.tglEInactive
                        Case False
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = ""
                                Else
                                    stSQLw2 = ""
                            End If
                        Case True
                            If Me.lstEMainE = "*" Then
                                stSQLw2 = ""
                                Else
                                    stSQLw2 = ""
                            End If
                    End Select
            End Select
            stSQL = stSQLs & " " & stSQLf & " " & stSQLw1 & " " & stSQLw2 & 
" " & stSQLo
            Form_subfmMainE.lstECsubEc.RowSource = stSQL
            Form_subfmMainE.Refresh
        Case 1
        Case 2
    End Select
            
Exit_lstEMainE_Click:
    Exit Sub

Err_lstEMainE_Click:
    MsgBox Err.Description
    Resume Exit_lstEMainE_Click

End Sub

0
Utf
1/29/2010 2:28:06 AM
OK, Only the code from zfm Main would fit in the first reply.  Here's the 
code from the subform subfmMainE.

Again, please don't laugh...I know not what I do!

ON SUBFORM subfmMainE

Private Sub lblEc1_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 1 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.ClientID DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
            Me.txtEcECo = 1
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = True
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc2_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 2 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientOL.[File As];"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientOL.[File As] DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientOL.[File As];"
            Me.txtEcECo = 2
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = True
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc3_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 3 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.EngagementID DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
            Me.txtEcECo = 3
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = True
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc4_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 4 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.EngagementYr DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
            Me.txtEcECo = 4
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = True
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc5_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 6 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.CEStaffID DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
            Me.txtEcECo = 6
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = True
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False
End Sub

Private Sub lblEc7_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 7 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.CEDueDateI DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
            Me.txtEcECo = 7
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = True
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc8_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 8 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
            Me.txtEcECo = 8
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = True
    Me.lblEc9.FontBold = False

End Sub

Private Sub lblEc9_Click()

    Dim stSQLsource As String
    Dim lngSQLpos As Long
    Dim stSQL1 As String
    Dim stSQL2 As String
    Dim stSQL As String
    
    stSQLsource = lstECsubEc.RowSource
    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
    
    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
    
    If Me.txtEcECo = 9 Then
        If Me.txtEcECs = "d" Then
            stSQL2 = "ORDER BY 
IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -'));"
            Me.txtEcECs = "a"
            ElseIf Me.txtEcECs = "a" Then
                stSQL2 = "ORDER BY 
IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -')) DESC;"
                Me.txtEcECs = "d"
        End If
        Else
            stSQL2 = "ORDER BY 
IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -'));"
            Me.txtEcECo = 9
            Me.txtEcECs = "a"
    End If
    
    stSQL = stSQL1 & stSQL2
    Me.lstECsubEc.RowSource = stSQL
    
    Me.lblEc1.FontBold = False
    Me.lblEc2.FontBold = False
    Me.lblEc3.FontBold = False
    Me.lblEc4.FontBold = False
    Me.lblEc5.FontBold = False
    Me.lblEc7.FontBold = False
    Me.lblEc8.FontBold = False
    Me.lblEc9.FontBold = True

End Sub

Private Sub tglECComplete_AfterUpdate()

    TglECFunction

End Sub

Private Sub tglECHold_AfterUpdate()

    TglECFunction

End Sub

Private Sub tglECOpen_AfterUpdate()

    TglECFunction

End Sub

Private Sub TglECFunction()
    
    Dim stSQLs As String
    Dim stSQLf As String
    Dim stSQLw1 As String
    Dim stSQLw2 As String
    Dim stSQLo As String
    Dim stSQL As String
    Dim intTglECSum As Integer
    Dim intTglECOpen As Integer
    Dim intTglECHold As Integer
    Dim intTglECComplete As Integer

    intTglECOpen = -2
    intTglECHold = -4
    intTglECComplete = -8
    intTglECSum = (Me.tglECOpen * intTglECOpen) + (Me.tglECHold * 
intTglECHold) + (Me.tglECComplete * intTglECComplete)
    
    stSQLf = "FROM (Client INNER JOIN ClientOL ON Client.ClientID = 
ClientOL.[User Field 1]) " & _
        "INNER JOIN ClientEngagement ON Client.ClientID = 
ClientEngagement.ClientID "
    
    Select Case Form_zfmMain.tglEInactive
        Case False
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLw1 = ""
                Else
                    stSQLw1 = "WHERE ((ClientEngagement.EngagementID) = 
[Forms]![zfmMain]![lstEMainE]) "
            End If
        Case True
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLw1 = "WHERE ((ClientEngagement.CEInactive) = True) "
                Else
                    stSQLw1 = "WHERE ((ClientEngagement.EngagementID) = 
[Forms]![zfmMain]![lstEMainE]) "
            End If
    End Select
    
    Select Case Me.txtEcECo
        Case 1
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.ClientID;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.ClientID DESC;"
            End Select
        Case 2
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientOL.[File As];"
                Case "d"
                    stSQLo = "ORDER BY ClientOL.[File As] DESC;"
            End Select
        Case 3
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.EngagementID;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.EngagementID DESC;"
            End Select
        Case 4
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.EngagementYr;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.EngagementYr DESC;"
            End Select
        Case 6
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.CEStaffID;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.CEStaffID DESC;"
            End Select
        Case 7
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.CEDueDateI;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.CEDueDateI DESC;"
            End Select
        Case 8
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY ClientEngagement.CEBudgetHrs;"
                Case "d"
                    stSQLo = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
            End Select
        Case 9
            Select Case Me.txtEcECs
                Case "a"
                    stSQLo = "ORDER BY 
IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -'));"
                Case "d"
                    stSQLo = "ORDER BY 
IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) DESC;"
            End Select
    End Select
    
    Select Case intTglECSum
        Case 0
            stSQLs = ""
            stSQLf = ""
            stSQLw1 = ""
            stSQLw2 = ""
            stSQLo = ""
        Case 2
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) = False) 
AND ((ClientEngagement.CEComplete) = False) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
False) AND ((ClientEngagement.CEComplete) = False) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND ((ClientEngagement.CEHold) = False) 
AND ((ClientEngagement.CEComplete) = False) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
False) AND ((ClientEngagement.CEComplete) = False) "
                    End If
            End Select
        Case 4
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) = True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
True) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND ((ClientEngagement.CEHold) = True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
True) "
                    End If
            End Select
                
        Case 6
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE (((ClientEngagement.CEHold) = True) 
OR ((ClientEngagement.CEComplete) = False)) "
                        Else
                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = False)) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND (((ClientEngagement.CEHold) = True) 
OR ((ClientEngagement.CEComplete) = False)) "
                        Else
                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = False)) "
                    End If
            End Select
        Case 8
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE ((ClientEngagement.CEComplete) = 
True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
True) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
True) "
                    End If
            End Select
        Case 10
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) <> True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) <> 
True) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND ((ClientEngagement.CEHold) <> True) "
                        Else
                            stSQLw2 = "AND ((ClientEngagement.CEHold) <> 
True) "
                    End If
            End Select
        Case 12
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "WHERE (((ClientEngagement.CEHold) = True) 
OR ((ClientEngagement.CEComplete) = True)) "
                        Else
                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = True)) "
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = "AND (((ClientEngagement.CEHold) = True) 
OR ((ClientEngagement.CEComplete) = True)) "
                        Else
                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
True) OR ((ClientEngagement.CEComplete) = True)) "
                    End If
            End Select
        Case 14
            If Form_zfmMain.lstEMainE = "*" Then
                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
                    "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, " & _
                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
                Me.lblEc3.Visible = True
                Me.lblEc7.Caption = "DueDate"
                Me.lblEc8.Caption = "BudgetHrs"
                Me.lstECsubEc.ColumnCount = 9
                Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
                Else
                    stSQLs = "SELECT ClientEngagement.ClientID, 
ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                        "ClientEngagement.CEStaffID, 
ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
                        
"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
calcCEStatus "
                    Me.lblEc3.Visible = False
                    Me.lblEc7.Caption = "DueDate"
                    Me.lblEc8.Caption = "BudgetHrs"
                    Me.lstECsubEc.ColumnCount = 8
                    Me.lstECsubEc.ColumnWidths = 
"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
            End If
            Select Case Form_zfmMain.tglEInactive
                Case False
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = ""
                        Else
                            stSQLw2 = ""
                    End If
                Case True
                    If Form_zfmMain.lstEMainE = "*" Then
                        stSQLw2 = ""
                        Else
                            stSQLw2 = ""
                    End If
            End Select
        Case 1
        Case 2
    End Select
    
    stSQL = stSQLs & stSQLf & stSQLw1 & stSQLw2 & stSQLo
    Me.lstECsubEc.RowSource = stSQL
    Me.Refresh
  
End Sub

0
Utf
1/29/2010 2:31:01 AM
Yup, that's a lot of stuff.  What you posted was a SQL string assembled in
VBA, which is fine, except that the code includes a lot of other stuff (a
majority of the code, I suspect) that doesn't affect the question at hand.
It would help if you could strip out the formatting code, and anything that
doesn't affect the SQL string.

Having said that, I managed to notice a few things.  Consider this:

   Select Case Me.txtEcECo
       Case 1
           Select Case Me.txtEcECs
               Case "a"
                   stSQLo = "ORDER BY ClientEngagement.ClientID;"
               Case "d"
                   stSQLo = "ORDER BY ClientEngagement.ClientID DESC;"
           End Select

In every case 1 through 9 you have an embedded Select Case for the express
purpose of selecting whether to sort ascending or descending.  You could
select the ORDER BY, then append DESC if needed.  It seems you place a number
in txtEcECo.  If so, how does that number relate to the ORDER BY?

Perhaps you could have a table with the first field being something the user
could readily identify as a sort order, and the second column could be the
sort field (ClientID, etc.).  Use the table (or a query based on the table)
as the Row Source for a combo box cboEcECo, with its Column Count set to 2
and its Column Widths something like 1.5",0".  strSQLo would then be
something like:

strSQLo = "ORDER BY ClientEngagement." & Me.cboEcECo.Column(1)

If Me.txtEcECs = "d" Then
  strSQLo = strSQLo & " DESC"
End If

There are other ways you could go about this.  The field could be the bound
column of the combo box so you don't need to reference the Column property,
or you could use constants in the code module.  The point is that the few
lines of code above can replace about 50 lines of code.  I don't know how
your Case 9 is supposed to work.  I don't see how it could work as an ORDER
BY, as it doesn't seem to reference a field.  In any case, the point remains
that you need to find ways to streamline the code.  If you find yourself
writing the same lines of code over and over, look for another way.  It's
almost always there.

The following link is about using a multi-select list box to filter a report,
but the principle can be put to use in other situations such as yours, where
you are getting your values from a variety of controls rather than a single
list box:
http://allenbrowne.com/ser-50.html


Robin wrote:
>OK, Only the code from zfm Main would fit in the first reply.  Here's the 
>code from the subform subfmMainE.
>
>Again, please don't laugh...I know not what I do!
>
>ON SUBFORM subfmMainE
>
>Private Sub lblEc1_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 1 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.ClientID DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
>            Me.txtEcECo = 1
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = True
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc2_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 2 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientOL.[File As];"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientOL.[File As] DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientOL.[File As];"
>            Me.txtEcECo = 2
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = True
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc3_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 3 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.EngagementID DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
>            Me.txtEcECo = 3
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = True
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc4_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 4 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.EngagementYr DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
>            Me.txtEcECo = 4
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = True
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc5_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 6 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.CEStaffID DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
>            Me.txtEcECo = 6
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = True
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>End Sub
>
>Private Sub lblEc7_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 7 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.CEDueDateI DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
>            Me.txtEcECo = 7
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = True
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc8_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 8 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
>            Me.txtEcECo = 8
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = True
>    Me.lblEc9.FontBold = False
>
>End Sub
>
>Private Sub lblEc9_Click()
>
>    Dim stSQLsource As String
>    Dim lngSQLpos As Long
>    Dim stSQL1 As String
>    Dim stSQL2 As String
>    Dim stSQL As String
>    
>    stSQLsource = lstECsubEc.RowSource
>    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>    
>    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>    
>    If Me.txtEcECo = 9 Then
>        If Me.txtEcECs = "d" Then
>            stSQL2 = "ORDER BY 
>IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -'));"
>            Me.txtEcECs = "a"
>            ElseIf Me.txtEcECs = "a" Then
>                stSQL2 = "ORDER BY 
>IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -')) DESC;"
>                Me.txtEcECs = "d"
>        End If
>        Else
>            stSQL2 = "ORDER BY 
>IIf([CAHold]=-1,'Hold',IIf([CAComplete]=-1,'Complete','       -'));"
>            Me.txtEcECo = 9
>            Me.txtEcECs = "a"
>    End If
>    
>    stSQL = stSQL1 & stSQL2
>    Me.lstECsubEc.RowSource = stSQL
>    
>    Me.lblEc1.FontBold = False
>    Me.lblEc2.FontBold = False
>    Me.lblEc3.FontBold = False
>    Me.lblEc4.FontBold = False
>    Me.lblEc5.FontBold = False
>    Me.lblEc7.FontBold = False
>    Me.lblEc8.FontBold = False
>    Me.lblEc9.FontBold = True
>
>End Sub
>
>Private Sub tglECComplete_AfterUpdate()
>
>    TglECFunction
>
>End Sub
>
>Private Sub tglECHold_AfterUpdate()
>
>    TglECFunction
>
>End Sub
>
>Private Sub tglECOpen_AfterUpdate()
>
>    TglECFunction
>
>End Sub
>
>Private Sub TglECFunction()
>    
>    Dim stSQLs As String
>    Dim stSQLf As String
>    Dim stSQLw1 As String
>    Dim stSQLw2 As String
>    Dim stSQLo As String
>    Dim stSQL As String
>    Dim intTglECSum As Integer
>    Dim intTglECOpen As Integer
>    Dim intTglECHold As Integer
>    Dim intTglECComplete As Integer
>
>    intTglECOpen = -2
>    intTglECHold = -4
>    intTglECComplete = -8
>    intTglECSum = (Me.tglECOpen * intTglECOpen) + (Me.tglECHold * 
>intTglECHold) + (Me.tglECComplete * intTglECComplete)
>    
>    stSQLf = "FROM (Client INNER JOIN ClientOL ON Client.ClientID = 
>ClientOL.[User Field 1]) " & _
>        "INNER JOIN ClientEngagement ON Client.ClientID = 
>ClientEngagement.ClientID "
>    
>    Select Case Form_zfmMain.tglEInactive
>        Case False
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLw1 = ""
>                Else
>                    stSQLw1 = "WHERE ((ClientEngagement.EngagementID) = 
>[Forms]![zfmMain]![lstEMainE]) "
>            End If
>        Case True
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLw1 = "WHERE ((ClientEngagement.CEInactive) = True) "
>                Else
>                    stSQLw1 = "WHERE ((ClientEngagement.EngagementID) = 
>[Forms]![zfmMain]![lstEMainE]) "
>            End If
>    End Select
>    
>    Select Case Me.txtEcECo
>        Case 1
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.ClientID;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.ClientID DESC;"
>            End Select
>        Case 2
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientOL.[File As];"
>                Case "d"
>                    stSQLo = "ORDER BY ClientOL.[File As] DESC;"
>            End Select
>        Case 3
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.EngagementID;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.EngagementID DESC;"
>            End Select
>        Case 4
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.EngagementYr;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.EngagementYr DESC;"
>            End Select
>        Case 6
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.CEStaffID;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.CEStaffID DESC;"
>            End Select
>        Case 7
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.CEDueDateI;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.CEDueDateI DESC;"
>            End Select
>        Case 8
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.CEBudgetHrs;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
>            End Select
>        Case 9
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY 
>IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -'));"
>                Case "d"
>                    stSQLo = "ORDER BY 
>IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) DESC;"
>            End Select
>    End Select
>    
>    Select Case intTglECSum
>        Case 0
>            stSQLs = ""
>            stSQLf = ""
>            stSQLw1 = ""
>            stSQLw2 = ""
>            stSQLo = ""
>        Case 2
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) = False) 
>AND ((ClientEngagement.CEComplete) = False) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
>False) AND ((ClientEngagement.CEComplete) = False) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND ((ClientEngagement.CEHold) = False) 
>AND ((ClientEngagement.CEComplete) = False) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
>False) AND ((ClientEngagement.CEComplete) = False) "
>                    End If
>            End Select
>        Case 4
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) = True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
>True) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND ((ClientEngagement.CEHold) = True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) = 
>True) "
>                    End If
>            End Select
>                
>        Case 6
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE (((ClientEngagement.CEHold) = True) 
>OR ((ClientEngagement.CEComplete) = False)) "
>                        Else
>                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
>True) OR ((ClientEngagement.CEComplete) = False)) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND (((ClientEngagement.CEHold) = True) 
>OR ((ClientEngagement.CEComplete) = False)) "
>                        Else
>                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
>True) OR ((ClientEngagement.CEComplete) = False)) "
>                    End If
>            End Select
>        Case 8
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE ((ClientEngagement.CEComplete) = 
>True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
>True) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
>True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEComplete) = 
>True) "
>                    End If
>            End Select
>        Case 10
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE ((ClientEngagement.CEHold) <> True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) <> 
>True) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND ((ClientEngagement.CEHold) <> True) "
>                        Else
>                            stSQLw2 = "AND ((ClientEngagement.CEHold) <> 
>True) "
>                    End If
>            End Select
>        Case 12
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "WHERE (((ClientEngagement.CEHold) = True) 
>OR ((ClientEngagement.CEComplete) = True)) "
>                        Else
>                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
>True) OR ((ClientEngagement.CEComplete) = True)) "
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = "AND (((ClientEngagement.CEHold) = True) 
>OR ((ClientEngagement.CEComplete) = True)) "
>                        Else
>                            stSQLw2 = "AND (((ClientEngagement.CEHold) = 
>True) OR ((ClientEngagement.CEComplete) = True)) "
>                    End If
>            End Select
>        Case 14
>            If Form_zfmMain.lstEMainE = "*" Then
>                stSQLs = "SELECT ClientEngagement.ClientID, ClientOL.[File 
>As], ClientEngagement.EngagementID, ClientEngagement.EngagementYr, " & _
>                    "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, " & _
>                    "ClientEngagement.CEBudgetHrs, IIf([CEHold]=-1,'     
>Hold',IIf([CEComplete]=-1,'Complete','       -')) AS calcCEStatus "
>                Me.lblEc3.Visible = True
>                Me.lblEc7.Caption = "DueDate"
>                Me.lblEc8.Caption = "BudgetHrs"
>                Me.lstECsubEc.ColumnCount = 9
>                Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.4in;0.65in;0.5in;0.5in;0.5in;0.65in"
>                Else
>                    stSQLs = "SELECT ClientEngagement.ClientID, 
>ClientOL.[File As], ClientEngagement.EngagementYr, " & _
>                        "ClientEngagement.CEStaffID, 
>ClientEngagement.CEDueDateI, ClientEngagement.CEBudgetHrs, " & _
>                        
>"IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','       -')) AS 
>calcCEStatus "
>                    Me.lblEc3.Visible = False
>                    Me.lblEc7.Caption = "DueDate"
>                    Me.lblEc8.Caption = "BudgetHrs"
>                    Me.lstECsubEc.ColumnCount = 8
>                    Me.lstECsubEc.ColumnWidths = 
>"0.65in;2.25in;0.65in;0.4in;0.65in;0.65in;0.65in;0.65in"
>            End If
>            Select Case Form_zfmMain.tglEInactive
>                Case False
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = ""
>                        Else
>                            stSQLw2 = ""
>                    End If
>                Case True
>                    If Form_zfmMain.lstEMainE = "*" Then
>                        stSQLw2 = ""
>                        Else
>                            stSQLw2 = ""
>                    End If
>            End Select
>        Case 1
>        Case 2
>    End Select
>    
>    stSQL = stSQLs & stSQLf & stSQLw1 & stSQLw2 & stSQLo
>    Me.lstECsubEc.RowSource = stSQL
>    Me.Refresh
>  
>End Sub

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201001/1

0
BruceM
1/29/2010 5:50:56 PM
Robin,

No laughing here, but that really does look like a big overkill of code to 
accomplish a relatively simple thing. The first thing I would do would be to 
create a group of saved queries, rather than trying to build the SQL 
statements on the fly and, under the appropriate circumstances, set the 
RowSource of your list to the correct saved query.

-- 
Lynn Trapp
MCP, MOS, MCAS


"Robin" wrote:

> OK, Only the code from zfm Main would fit in the first reply.  Here's the 
> code from the subform subfmMainE.
> 
> Again, please don't laugh...I know not what I do!
> 
> ON SUBFORM subfmMainE
> 
> Private Sub lblEc1_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 1 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.ClientID;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.ClientID DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.ClientID;"
>             Me.txtEcECo = 1
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = True
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc2_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 2 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientOL.[File As];"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientOL.[File As] DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientOL.[File As];"
>             Me.txtEcECo = 2
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = True
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc3_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 3 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.EngagementID DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
>             Me.txtEcECo = 3
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = True
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc4_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 4 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.EngagementYr DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
>             Me.txtEcECo = 4
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = True
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc5_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 6 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.CEStaffID DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
>             Me.txtEcECo = 6
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = True
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> End Sub
> 
> Private Sub lblEc7_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 7 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.CEDueDateI DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
>             Me.txtEcECo = 7
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = True
>     Me.lblEc8.FontBold = False
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc8_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
>     Dim stSQL As String
>     
>     stSQLsource = lstECsubEc.RowSource
>     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
>     
>     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
>     
>     If Me.txtEcECo = 8 Then
>         If Me.txtEcECs = "d" Then
>             stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
>             Me.txtEcECs = "a"
>             ElseIf Me.txtEcECs = "a" Then
>                 stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
>                 Me.txtEcECs = "d"
>         End If
>         Else
>             stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
>             Me.txtEcECo = 8
>             Me.txtEcECs = "a"
>     End If
>     
>     stSQL = stSQL1 & stSQL2
>     Me.lstECsubEc.RowSource = stSQL
>     
>     Me.lblEc1.FontBold = False
>     Me.lblEc2.FontBold = False
>     Me.lblEc3.FontBold = False
>     Me.lblEc4.FontBold = False
>     Me.lblEc5.FontBold = False
>     Me.lblEc7.FontBold = False
>     Me.lblEc8.FontBold = True
>     Me.lblEc9.FontBold = False
> 
> End Sub
> 
> Private Sub lblEc9_Click()
> 
>     Dim stSQLsource As String
>     Dim lngSQLpos As Long
>     Dim stSQL1 As String
>     Dim stSQL2 As String
0
Utf
1/29/2010 6:34:01 PM
Thank you both.

First, to address Bruce's comment - that IS only the code related to the 
possible views I want in the list box!  Keep in mind, it's not just a SQL 
string but a SQL string for a plethora of possible view combinations.

Lynn, I agree your way would be easier but I've always had trouble with 
saved queries when it comes to changing a condition in instances such as my 
Toggle Sum combination.  Do you think it is possible to write a saved query 
that will filter records based on which of the 7 possible combinations of 3 
toggles are pressed?  And use that result along with sorting on any one of 9 
particular columns. And filtering from another selection in a listbox on the 
master form?

This all grew from a single listbox to be viewed based only on the selection 
in the master form list box.  That I could handle pretty effeciently.  Having 
expanded in the peicemeal way that it happened I feel is the reason the is 
written so inefficiently.

This weekend I will take a copy and pretend I'm starting from scratch and 
see if I can use a combo of saved queries with some limited manipulation and 
create something more manageable.  At least I now know that what I've done is 
not "normal" for the filtering and sorting of a listbox!

Thank you,
Robin


"Lynn Trapp" wrote:

> Robin,
> 
> No laughing here, but that really does look like a big overkill of code to 
> accomplish a relatively simple thing. The first thing I would do would be to 
> create a group of saved queries, rather than trying to build the SQL 
> statements on the fly and, under the appropriate circumstances, set the 
> RowSource of your list to the correct saved query.
> 
> -- 
> Lynn Trapp
> MCP, MOS, MCAS
> 
> 
> "Robin" wrote:
> 
> > OK, Only the code from zfm Main would fit in the first reply.  Here's the 
> > code from the subform subfmMainE.
> > 
> > Again, please don't laugh...I know not what I do!
> > 
> > ON SUBFORM subfmMainE
> > 
> > Private Sub lblEc1_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 1 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.ClientID;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.ClientID DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.ClientID;"
> >             Me.txtEcECo = 1
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = True
> >     Me.lblEc2.FontBold = False
> >     Me.lblEc3.FontBold = False
> >     Me.lblEc4.FontBold = False
> >     Me.lblEc5.FontBold = False
> >     Me.lblEc7.FontBold = False
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > 
> > End Sub
> > 
> > Private Sub lblEc2_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 2 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientOL.[File As];"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientOL.[File As] DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientOL.[File As];"
> >             Me.txtEcECo = 2
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = True
> >     Me.lblEc3.FontBold = False
> >     Me.lblEc4.FontBold = False
> >     Me.lblEc5.FontBold = False
> >     Me.lblEc7.FontBold = False
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > 
> > End Sub
> > 
> > Private Sub lblEc3_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 3 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.EngagementID DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
> >             Me.txtEcECo = 3
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = False
> >     Me.lblEc3.FontBold = True
> >     Me.lblEc4.FontBold = False
> >     Me.lblEc5.FontBold = False
> >     Me.lblEc7.FontBold = False
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > 
> > End Sub
> > 
> > Private Sub lblEc4_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 4 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.EngagementYr DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
> >             Me.txtEcECo = 4
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = False
> >     Me.lblEc3.FontBold = False
> >     Me.lblEc4.FontBold = True
> >     Me.lblEc5.FontBold = False
> >     Me.lblEc7.FontBold = False
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > 
> > End Sub
> > 
> > Private Sub lblEc5_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 6 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.CEStaffID DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
> >             Me.txtEcECo = 6
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = False
> >     Me.lblEc3.FontBold = False
> >     Me.lblEc4.FontBold = False
> >     Me.lblEc5.FontBold = True
> >     Me.lblEc7.FontBold = False
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > End Sub
> > 
> > Private Sub lblEc7_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 7 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.CEDueDateI DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
> >             Me.txtEcECo = 7
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = False
> >     Me.lblEc3.FontBold = False
> >     Me.lblEc4.FontBold = False
> >     Me.lblEc5.FontBold = False
> >     Me.lblEc7.FontBold = True
> >     Me.lblEc8.FontBold = False
> >     Me.lblEc9.FontBold = False
> > 
> > End Sub
> > 
> > Private Sub lblEc8_Click()
> > 
> >     Dim stSQLsource As String
> >     Dim lngSQLpos As Long
> >     Dim stSQL1 As String
> >     Dim stSQL2 As String
> >     Dim stSQL As String
> >     
> >     stSQLsource = lstECsubEc.RowSource
> >     lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >     
> >     stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >     
> >     If Me.txtEcECo = 8 Then
> >         If Me.txtEcECs = "d" Then
> >             stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
> >             Me.txtEcECs = "a"
> >             ElseIf Me.txtEcECs = "a" Then
> >                 stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs DESC;"
> >                 Me.txtEcECs = "d"
> >         End If
> >         Else
> >             stSQL2 = "ORDER BY ClientEngagement.CEBudgetHrs;"
> >             Me.txtEcECo = 8
> >             Me.txtEcECs = "a"
> >     End If
> >     
> >     stSQL = stSQL1 & stSQL2
> >     Me.lstECsubEc.RowSource = stSQL
> >     
> >     Me.lblEc1.FontBold = False
> >     Me.lblEc2.FontBold = False
0
Utf
1/29/2010 7:05:01 PM
Bruce,

I just re-read your response and the artile by Allen.  If I'm writing the 
same code over and over I'm doing something wrong...agreed!  I believe the 
code you do not "see how it works" is a place where I set an order string 
then take the string that is currently the rowsource, find the ORDER part of 
the string and replace it with my new string.  Unbelievably it DOES all 
work...until someone wants to make a tweak to it...then it blows up. 
(Originally the list drew from one table and that's where it worked, albeit 
with great complexity.  Now someone wants to see a value from another table 
with each row in the list box.  (The LEFT JOIN I mentioned in my original 
post) That's where the complexity and inefficiency really struck me!

I'm going to try from the beginning this weekend and see what I can come up 
with doing it not in a piecemeal fashion

Thank you for your comments,
Robin

"BruceM via AccessMonster.com" wrote:

> Yup, that's a lot of stuff.  What you posted was a SQL string assembled in
> VBA, which is fine, except that the code includes a lot of other stuff (a
> majority of the code, I suspect) that doesn't affect the question at hand.
> It would help if you could strip out the formatting code, and anything that
> doesn't affect the SQL string.
> 
> Having said that, I managed to notice a few things.  Consider this:
> 
>    Select Case Me.txtEcECo
>        Case 1
>            Select Case Me.txtEcECs
>                Case "a"
>                    stSQLo = "ORDER BY ClientEngagement.ClientID;"
>                Case "d"
>                    stSQLo = "ORDER BY ClientEngagement.ClientID DESC;"
>            End Select
> 
> In every case 1 through 9 you have an embedded Select Case for the express
> purpose of selecting whether to sort ascending or descending.  You could
> select the ORDER BY, then append DESC if needed.  It seems you place a number
> in txtEcECo.  If so, how does that number relate to the ORDER BY?
> 
> Perhaps you could have a table with the first field being something the user
> could readily identify as a sort order, and the second column could be the
> sort field (ClientID, etc.).  Use the table (or a query based on the table)
> as the Row Source for a combo box cboEcECo, with its Column Count set to 2
> and its Column Widths something like 1.5",0".  strSQLo would then be
> something like:
> 
> strSQLo = "ORDER BY ClientEngagement." & Me.cboEcECo.Column(1)
> 
> If Me.txtEcECs = "d" Then
>   strSQLo = strSQLo & " DESC"
> End If
> 
> There are other ways you could go about this.  The field could be the bound
> column of the combo box so you don't need to reference the Column property,
> or you could use constants in the code module.  The point is that the few
> lines of code above can replace about 50 lines of code.  I don't know how
> your Case 9 is supposed to work.  I don't see how it could work as an ORDER
> BY, as it doesn't seem to reference a field.  In any case, the point remains
> that you need to find ways to streamline the code.  If you find yourself
> writing the same lines of code over and over, look for another way.  It's
> almost always there.
> 
> The following link is about using a multi-select list box to filter a report,
> but the principle can be put to use in other situations such as yours, where
> you are getting your values from a variety of controls rather than a single
> list box:
> http://allenbrowne.com/ser-50.html
> 
> 
> Robin wrote:
> >OK, Only the code from zfm Main would fit in the first reply.  Here's the 
> >code from the subform subfmMainE.
> >
> >Again, please don't laugh...I know not what I do!
> >
> >ON SUBFORM subfmMainE
> >
> >Private Sub lblEc1_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 1 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientEngagement.ClientID DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientEngagement.ClientID;"
> >            Me.txtEcECo = 1
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = True
> >    Me.lblEc2.FontBold = False
> >    Me.lblEc3.FontBold = False
> >    Me.lblEc4.FontBold = False
> >    Me.lblEc5.FontBold = False
> >    Me.lblEc7.FontBold = False
> >    Me.lblEc8.FontBold = False
> >    Me.lblEc9.FontBold = False
> >
> >End Sub
> >
> >Private Sub lblEc2_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 2 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientOL.[File As];"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientOL.[File As] DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientOL.[File As];"
> >            Me.txtEcECo = 2
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = False
> >    Me.lblEc2.FontBold = True
> >    Me.lblEc3.FontBold = False
> >    Me.lblEc4.FontBold = False
> >    Me.lblEc5.FontBold = False
> >    Me.lblEc7.FontBold = False
> >    Me.lblEc8.FontBold = False
> >    Me.lblEc9.FontBold = False
> >
> >End Sub
> >
> >Private Sub lblEc3_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 3 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientEngagement.EngagementID DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientEngagement.EngagementID;"
> >            Me.txtEcECo = 3
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = False
> >    Me.lblEc2.FontBold = False
> >    Me.lblEc3.FontBold = True
> >    Me.lblEc4.FontBold = False
> >    Me.lblEc5.FontBold = False
> >    Me.lblEc7.FontBold = False
> >    Me.lblEc8.FontBold = False
> >    Me.lblEc9.FontBold = False
> >
> >End Sub
> >
> >Private Sub lblEc4_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 4 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientEngagement.EngagementYr DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientEngagement.EngagementYr;"
> >            Me.txtEcECo = 4
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = False
> >    Me.lblEc2.FontBold = False
> >    Me.lblEc3.FontBold = False
> >    Me.lblEc4.FontBold = True
> >    Me.lblEc5.FontBold = False
> >    Me.lblEc7.FontBold = False
> >    Me.lblEc8.FontBold = False
> >    Me.lblEc9.FontBold = False
> >
> >End Sub
> >
> >Private Sub lblEc5_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 6 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientEngagement.CEStaffID DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientEngagement.CEStaffID;"
> >            Me.txtEcECo = 6
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = False
> >    Me.lblEc2.FontBold = False
> >    Me.lblEc3.FontBold = False
> >    Me.lblEc4.FontBold = False
> >    Me.lblEc5.FontBold = True
> >    Me.lblEc7.FontBold = False
> >    Me.lblEc8.FontBold = False
> >    Me.lblEc9.FontBold = False
> >End Sub
> >
> >Private Sub lblEc7_Click()
> >
> >    Dim stSQLsource As String
> >    Dim lngSQLpos As Long
> >    Dim stSQL1 As String
> >    Dim stSQL2 As String
> >    Dim stSQL As String
> >    
> >    stSQLsource = lstECsubEc.RowSource
> >    lngSQLpos = InStr(1, stSQLsource, "ORDER BY")
> >    
> >    stSQL1 = Left(stSQLsource, (lngSQLpos - 1))
> >    
> >    If Me.txtEcECo = 7 Then
> >        If Me.txtEcECs = "d" Then
> >            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
> >            Me.txtEcECs = "a"
> >            ElseIf Me.txtEcECs = "a" Then
> >                stSQL2 = "ORDER BY ClientEngagement.CEDueDateI DESC;"
> >                Me.txtEcECs = "d"
> >        End If
> >        Else
> >            stSQL2 = "ORDER BY ClientEngagement.CEDueDateI;"
> >            Me.txtEcECo = 7
> >            Me.txtEcECs = "a"
> >    End If
> >    
> >    stSQL = stSQL1 & stSQL2
> >    Me.lstECsubEc.RowSource = stSQL
> >    
> >    Me.lblEc1.FontBold = False
> >    Me.lblEc2.FontBold = False
> >    Me.lblEc3.FontBold = False
> >    Me.lblEc4.FontBold = False
> >    Me.lblEc5.FontBold = False
0
Utf
1/29/2010 7:25:01 PM
What I meant is that there is a lot of code to set the Visible property of
controls, captions for lablels, column widths of combo boxes, and so forth.
That is, a lot of code pertains to things that do not affect the SQL.

I cannot sort out how the toggles interact with each other.  Are any of them
mutually exclusive?  Does each one affect a different field?  In general,
what is the real-world situation represented by the toggles?  As I understand,
the toggles are yes/no, since you mention their boolean values.  If so, No is
0, so aren't you multiplying by 0 to get the intTglECSum value in some cases?

When I spoke of repeating code I was thinking about the code to set the font
to bold.  Maybe you could set the labels to Not Bold in the form's Current
event, then set a single label to bold, depending on the command button.
Something like that.  There are other repeated places, such as the one I
mentioned in my previous posting.  Set the ORDER BY string.  When that is
done, add "DESC" if needed.

Robin wrote:
>Thank you both.
>
>First, to address Bruce's comment - that IS only the code related to the 
>possible views I want in the list box!  Keep in mind, it's not just a SQL 
>string but a SQL string for a plethora of possible view combinations.
>
>Lynn, I agree your way would be easier but I've always had trouble with 
>saved queries when it comes to changing a condition in instances such as my 
>Toggle Sum combination.  Do you think it is possible to write a saved query 
>that will filter records based on which of the 7 possible combinations of 3 
>toggles are pressed?  And use that result along with sorting on any one of 9 
>particular columns. And filtering from another selection in a listbox on the 
>master form?
>
>This all grew from a single listbox to be viewed based only on the selection 
>in the master form list box.  That I could handle pretty effeciently.  Having 
>expanded in the peicemeal way that it happened I feel is the reason the is 
>written so inefficiently.
>
>This weekend I will take a copy and pretend I'm starting from scratch and 
>see if I can use a combo of saved queries with some limited manipulation and 
>create something more manageable.  At least I now know that what I've done is 
>not "normal" for the filtering and sorting of a listbox!
>
>Thank you,
>Robin
>
>> Robin,
>> 
>[quoted text clipped - 289 lines]
>> >     Me.lblEc1.FontBold = False
>> >     Me.lblEc2.FontBold = False

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201001/1

0
BruceM
1/29/2010 8:05:01 PM
Hello Bruce,

The "real world" use is as follows:
On the main form there is a list of 30 or so Engagements (jobs-prepare 
1040s, 1120s, audits, compilations, payroll taxes, bookkeeping, etc.) that we 
do for clients.  When you choose an Engagement, the subform list is populated 
with a list of all the clients we are currently engaged with. (ie: if you 
click on 1040, a list of about 400 clients will populate the subform list.)  
Those Client Engagements can be in 1 of 3 states: Open, OnHold, Complete.

I originally had the buttons only showing Open or OnHold or Complete but we 
found that if we could not see Open and OnHold simultaneously, things fell 
through the cracks. (But we did not want to always see the OnHold with the 
Opens, hence the "any combination of toggle buttons" method.)
The Toggle Buttons work like this:
intOpen =-2,intHold=-4,intComplete=-8
If only the Open button is depressed then tglSum =
intOpen(-2)*tglOpen(-1) = 2 +
intHold(-4)*tglHold(0)= 0 +
intComplete(-8)*tglComplete(0) = 0
So tglSum = 2 -> So do Case 2
If Open and Complete are depressed then tglSum =
intOpen(-2)*tglOpen(-1) = 2 +
intHold(-4)*tglHold(0)= 0 +
intComplete(-8)*tglComplete(-1) = 8
So tglSum = 10 -> so do Case 10
All the possible combinations are 2,4,6,8,10,12 and 14
(I have to say, I was kinda proud of this!)

The list displays 8 or nine columns of information (if you choose * (All) in 
the main form list box then you need a column in the subform listbox to 
identify what the Engagement is.  If you've only chosen the 1040 Engagement 
then you do not need a column of 400 1040s in the list. The other columns are 
ClientID, ClientName (from Outlook!), Engagement Year (sometimes we are doing 
a 2007 and 2008 tax return for people as 2 "Enagements and due to fiscal 
year-ends, the tax return years sometimes differ), Due Date, Budget Hours, 
StaffID (responsible for the Engagement)...  And it's nice to be able to sort 
by any of those items.

As long as I was doing all that I just went ahead and included programming 
that would sort ascending on the first click of the column label and sort 
descending if you clicked that column label again and go back to ascending if 
you clicked again, and so on...  (Hey, I had created a monster by this time 
anyway so why not!)

I hope that gives you a better understanding of what is happening.

The newest "tweak"is they want to see where in the process that engagement 
is currently. (Each engagement has 3-8 "Assignments - Prepare return, first 
review of return, final review of return, admin assembly and mailing of 
return...for example)
Because the assignments are in a different table linked by 
ClientID,EngagementID,EngagementYr then the LEFT JOIN would be necessary. 
(NOT ALL Engagements have assignments ie: answer an IRS notice) but I need to 
filter the client assignment to the level where it has not been completed.  
(A 1040 Engagement has had the Prep assignment completed and it is currently 
with the first reviewer.)  That filtering at the client assignment level is 
negating my LEFT JOIN!

But I think I need to CLEAN UP the former before I tackle the latter!

Thanks,
Robin


"BruceM via AccessMonster.com" wrote:

> What I meant is that there is a lot of code to set the Visible property of
> controls, captions for lablels, column widths of combo boxes, and so forth.
> That is, a lot of code pertains to things that do not affect the SQL.
> 
> I cannot sort out how the toggles interact with each other.  Are any of them
> mutually exclusive?  Does each one affect a different field?  In general,
> what is the real-world situation represented by the toggles?  As I understand,
> the toggles are yes/no, since you mention their boolean values.  If so, No is
> 0, so aren't you multiplying by 0 to get the intTglECSum value in some cases?
> 
> When I spoke of repeating code I was thinking about the code to set the font
> to bold.  Maybe you could set the labels to Not Bold in the form's Current
> event, then set a single label to bold, depending on the command button.
> Something like that.  There are other repeated places, such as the one I
> mentioned in my previous posting.  Set the ORDER BY string.  When that is
> done, add "DESC" if needed.
> 
> Robin wrote:
> >Thank you both.
> >
> >First, to address Bruce's comment - that IS only the code related to the 
> >possible views I want in the list box!  Keep in mind, it's not just a SQL 
> >string but a SQL string for a plethora of possible view combinations.
> >
> >Lynn, I agree your way would be easier but I've always had trouble with 
> >saved queries when it comes to changing a condition in instances such as my 
> >Toggle Sum combination.  Do you think it is possible to write a saved query 
> >that will filter records based on which of the 7 possible combinations of 3 
> >toggles are pressed?  And use that result along with sorting on any one of 9 
> >particular columns. And filtering from another selection in a listbox on the 
> >master form?
> >
> >This all grew from a single listbox to be viewed based only on the selection 
> >in the master form list box.  That I could handle pretty effeciently.  Having 
> >expanded in the peicemeal way that it happened I feel is the reason the is 
> >written so inefficiently.
> >
> >This weekend I will take a copy and pretend I'm starting from scratch and 
> >see if I can use a combo of saved queries with some limited manipulation and 
> >create something more manageable.  At least I now know that what I've done is 
> >not "normal" for the filtering and sorting of a listbox!
> >
> >Thank you,
> >Robin
> >
> >> Robin,
> >> 
> >[quoted text clipped - 289 lines]
> >> >     Me.lblEc1.FontBold = False
> >> >     Me.lblEc2.FontBold = False
> 
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201001/1
> 
> .
> 
0
Utf
1/30/2010 1:43:02 AM
OK, now I think I understand that you are assembling a recordset, not doing
actual data entry.  This is essentially a sort form, which explains why you
may want to select Open, OnHold, and Complete all at once.

You say the main form has a list, but does it have a record, or is the main
form an unbound search interface?

Here is your Case 10 code (Open and Complete toggles are set to True (-1).  I
have removed formatting code.  SQL is the language of queries.  VBA is the
code you have posted.  You can assemble SQL in VBA, but they are different
things.

       Case 10
           If Form_zfmMain.lstEMainE = "*" Then
               stSQLs = "SELECT ClientEngagement.ClientID, " & _
                 "ClientOL.[File As], ClientEngagement.EngagementID, " & _
                 "ClientEngagement.EngagementYr, " & _
                 "ClientEngagement.CEStaffID, ClientEngagement.CEDueDateI, "
& _
                 "ClientEngagement.CEBudgetHrs, " & _
                  "IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','-'))
" & _
                  "AS calcCEStatus "
               Else
                   stSQLs = "SELECT ClientEngagement.ClientID, " & _
                     ClientOL.[File As], ClientEngagement.EngagementYr, " & _
                       "ClientEngagement.CEStaffID, ClientEngagement.
CEDueDateI, " & _
                       "ClientEngagement.CEBudgetHrs, " & _

                       "IIf([CEHold]= -1,'Hold',IIf([CEComplete]= -1,
'Complete','-')) " & _
                       "AS calcCEStatus "
           End If

           Select Case Form_zfmMain.tglEInactive
               Case False
                   If Form_zfmMain.lstEMainE = "*" Then
                       stSQLw2 = "WHERE ((ClientEngagement.CEHold) <> True) "
                       Else
                           stSQLw2 = "AND ((ClientEngagement.CEHold) <> True)
"
                   End If
               Case True
                   If Form_zfmMain.lstEMainE = "*" Then
                       stSQLw2 = "AND ((ClientEngagement.CEHold) <> True) "
                       Else
                           stSQLw2 = "AND ((ClientEngagement.CEHold) <> True)
"
                   End If
           End Select 

I can't sort out all of what is going on, but it looks as if you repeat this
block of code for every Case 2 through 14:

 If Form_zfmMain.lstEMainE = "*" Then
   stSQLs = "SELECT ClientEngagement.ClientID, " & _
     "ClientOL.[File As], ClientEngagement.EngagementID, " & _
     "ClientEngagement.EngagementYr, " & _
     "ClientEngagement.CEStaffID, ClientEngagement.CEDueDateI, " & _
      "ClientEngagement.CEBudgetHrs, " & _
      "IIf([CEHold]=-1,'Hold',IIf([CEComplete]=-1,'Complete','-')) " & _
                  "AS calcCEStatus "

The Else seems to be the same code, except with EngagementID appearing just
once.  I doubt very much that you need to reset the column count and widths.
You can ignore the single unwanted column, I expect.

This is what I meant by writing the same code over and over.  If you ever
need to change the SQL you need to change it in many places in the code.  Use
a string or a constant for the unchanging parts of the code, then use the
Select Case and so forth to add just what is needed.

In the next part of the code you have this:

           Select Case Form_zfmMain.tglEInactive
               Case False
                   If Form_zfmMain.lstEMainE = "*" Then
                       stSQLw2 = "WHERE ((ClientEngagement.CEHold) <> True) "
                       Else
                           stSQLw2 = "AND ((ClientEngagement.CEHold) <> True)
"
                   End If
               Case True
                   If Form_zfmMain.lstEMainE = "*" Then
                       stSQLw2 = "AND ((ClientEngagement.CEHold) <> True) "
                       Else
                           stSQLw2 = "AND ((ClientEngagement.CEHold) <> True)
"
                   End If
           End Select 

If this is really what you meant to have, you could reduce it to:

           If Me.tglEInactive = False And Me.lstEMainE = "*" Then
              stSQLw2 = "WHERE ((ClientEngagement.CEHold) <> True) "
              Else
                  stSQLw2 = "AND ((ClientEngagement.CEHold) <> True) "
           End If

This saves nine lines of code.  If it is the same for every Case 2-14, do
this outside of the Select Case.  Same for the formatting.  It seems some of
the Visible properties and so forth are the same for every Case.

Robin wrote:
>Hello Bruce,
>
>The "real world" use is as follows:
>On the main form there is a list of 30 or so Engagements (jobs-prepare 
>1040s, 1120s, audits, compilations, payroll taxes, bookkeeping, etc.) that we 
>do for clients.  When you choose an Engagement, the subform list is populated 
>with a list of all the clients we are currently engaged with. (ie: if you 
>click on 1040, a list of about 400 clients will populate the subform list.)  
>Those Client Engagements can be in 1 of 3 states: Open, OnHold, Complete.
>
>I originally had the buttons only showing Open or OnHold or Complete but we 
>found that if we could not see Open and OnHold simultaneously, things fell 
>through the cracks. (But we did not want to always see the OnHold with the 
>Opens, hence the "any combination of toggle buttons" method.)
>The Toggle Buttons work like this:
>intOpen =-2,intHold=-4,intComplete=-8
>If only the Open button is depressed then tglSum =
>intOpen(-2)*tglOpen(-1) = 2 +
>intHold(-4)*tglHold(0)= 0 +
>intComplete(-8)*tglComplete(0) = 0
>So tglSum = 2 -> So do Case 2
>If Open and Complete are depressed then tglSum =
>intOpen(-2)*tglOpen(-1) = 2 +
>intHold(-4)*tglHold(0)= 0 +
>intComplete(-8)*tglComplete(-1) = 8
>So tglSum = 10 -> so do Case 10
>All the possible combinations are 2,4,6,8,10,12 and 14
>(I have to say, I was kinda proud of this!)
>
>The list displays 8 or nine columns of information (if you choose * (All) in 
>the main form list box then you need a column in the subform listbox to 
>identify what the Engagement is.  If you've only chosen the 1040 Engagement 
>then you do not need a column of 400 1040s in the list. The other columns are 
>ClientID, ClientName (from Outlook!), Engagement Year (sometimes we are doing 
>a 2007 and 2008 tax return for people as 2 "Enagements and due to fiscal 
>year-ends, the tax return years sometimes differ), Due Date, Budget Hours, 
>StaffID (responsible for the Engagement)...  And it's nice to be able to sort 
>by any of those items.
>
>As long as I was doing all that I just went ahead and included programming 
>that would sort ascending on the first click of the column label and sort 
>descending if you clicked that column label again and go back to ascending if 
>you clicked again, and so on...  (Hey, I had created a monster by this time 
>anyway so why not!)
>
>I hope that gives you a better understanding of what is happening.
>
>The newest "tweak"is they want to see where in the process that engagement 
>is currently. (Each engagement has 3-8 "Assignments - Prepare return, first 
>review of return, final review of return, admin assembly and mailing of 
>return...for example)
>Because the assignments are in a different table linked by 
>ClientID,EngagementID,EngagementYr then the LEFT JOIN would be necessary. 
>(NOT ALL Engagements have assignments ie: answer an IRS notice) but I need to 
>filter the client assignment to the level where it has not been completed.  
>(A 1040 Engagement has had the Prep assignment completed and it is currently 
>with the first reviewer.)  That filtering at the client assignment level is 
>negating my LEFT JOIN!
>
>But I think I need to CLEAN UP the former before I tackle the latter!
>
>Thanks,
>Robin
>
>> What I meant is that there is a lot of code to set the Visible property of
>> controls, captions for lablels, column widths of combo boxes, and so forth.
>[quoted text clipped - 45 lines]
>> >> >     Me.lblEc1.FontBold = False
>> >> >     Me.lblEc2.FontBold = False

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-modules/201002/1

0
BruceM
2/1/2010 1:05:11 PM
Reply:

Similar Artilces:

Populate a comboxbox and Listbox in a excel userform with an sql table data
Hi All, I have a excel userform which has some combo boxes and list boxes and I want to populate this boxes with the data which I have on my sql server database tables. Just to give an example. My first combo box is for region which needs to be populated from a sql table called Region_Mapping and my first list box should be linked to the same sql table but it should populate the countries on the basis of the region which user will select in the combo box. For eg : In my sql table I have following countries which are mapped against America 1) Argentina 2) Brazil 3) Mexico 4) Canada...

Not showing blank cells in user form list box
The following bit of code populates a UserForm. I would like that none of the blank rows in C5:D14 be shown in the uf. (The cells in column D will always be blank if the corresponding row in column C is blank.) Q1. Can anyone suggest how I might achieve this? Q2. Is it possible to also have another range, namely ("C21:D25") added to the list box so that the list box is populated by ("C21:D25") and ("C5:D14") without any blank rows showing? I have generally used PivotTables to sort the ranges and eliminate blanks, but I wonder if there is a better way. If Activ...

Access, Javascript,and SQL
I have a data access page in Access that has a few dropdown lists, buttons and textboxes on it. (pretty simple) When you view the access page and enter information into the fields, it stores the data in an SQL db. In the code of my data access page, I have a javascript password generator: <SCRIPT language=JavaScript> <!-- Begin function randomPassword() { chars = "abcdefghjkmnpqrstuvwxyzABCDEFGHJKLMNPQRSTUVWXYZ123456789"; pass = ""; for(x=0;x<10;x++) { i = Math.floor(Math.random() * 62); pass += chars.charAt(i); } return pass; } function f...

Coming up against SQL 8K byte limit
Hi, Any ideas what is the correct way to total up the size of the row in SQL? I used the SQL "Design Table" to get the length of each column and total up all the bytes and it came up to 4396 bytes. The number of columns for the table is 197. So if this is the case, why would I get a error adding more fields into the table thru the Schema Manager? I was able to add a integer field, but not a field with string of 20 length. Any ideas anyone? Thanks. Try running the following query in the MSCRM database. It will give you the amount of space used by each table. SELECT ob.n...

Advanced SQL Query and Advanced Find
I need to add contacts to a marketing list based on an advanced SQL query, Advanced find does not offer the proper fields and joins. Is there an easy way to run the query outside of CRM and then somehow get the contacts into the marketing list? Create a Query using the "FilteredContact" view in CRM, it offers all the fields you need. Furthermore, in order to create marketing list against your query i suggest you do it using the webservice API. If you need help working with the API feel free to contact me at anytime I can certainly help you. Oshri Cohen Web: http://mymscrm3.blogs...

use port number for the sql server? while installing CRM 3.0
when installing Microsoft CRM 3.0, can we use port number for the sql server? Responded to on the CRM Deployment forum. -- Rgds Michael MCDBA 2000 | MCITP DBA 2005 "Amit - Techtrends" wrote: > when installing Microsoft CRM 3.0, > can we use port number for the sql server? ...

Folder/Directory listings saved in Excel?
Is it possible to save (to a file) a listing of the contents of a folder or directory? This would be useful for comparing names of files in say two different folders. It would also be good to save other attributes for the files - not just the names. I know there are non-MS downloads available to do this, but I'd rather have a MS-approved way of doing it. Surely some VB guru can come up with a little program to do this? I have a folder of files (.html). My users were to provide me a file of jpgs - 1 jpg per html. I know I have jpgs without htmls, and htmls wiithout jpgs. I ...

CRM 3.0 SBE on SBS2003 and a separate SQL server
I have installed CRM 3.0 on my SBS2003 but wanted to use a separate server for the SQL databases. The install went fine, although I did have to take the custom install route so I could specify my separate DB server name. I have the two CRM databases and the two Reporting service databases on my db server. After the install I set up one user as instructed in the Implementation Guide and in the "verify installation" section, I was unable to get to CRM via http://localhost (that brought up my SBS2003 "Welcome to Windows Small Business Server 2003" site, the one with o...

Can I update the sender filtering list automatically
I have a list of senders, about 300 that I would like to filter from my exchange server. The sender filtering tab, only lets you add them one at a time. How can I have exchange reject the entire list of senders, without having to enter them one at a time. Hello, Sender filter is stored in AD in a field named: msExchTurfListNames of the organization. you can write a script that read data from your text file and populate this attribute Hope this info help you Thanks Fadi Nabil "steve tysinger" wrote: > I have a list of senders, about 300 that I would like to filter from...

Quick SQL Question
If "Select salary from Employee" returns the following: 120000 70000 95000 How can I get the numbers to line up RIGHT JUSTIFIED? You can't format the results of a query without exporting them and using some other application. File | Export... Tom -- Stop fishing for e-mail "Luminox" wrote: > If "Select salary from Employee" returns the following: > 120000 > 70000 > 95000 > > How can I get the numbers to line up RIGHT JUSTIFIED? Try this one select right(replicate(' ', 12) + salary,...

Lists and Sharepoint
Currently I have a workbook that contains 50 to 70 budget worksheets for various programs. The first worksheet is a summary sheet for management to quickly look at bottom line summaries. I want to publish this sheet as a list to our share point site without losing the lookups and references to the detail sheets. So that as detail sheets are updated and changed the effected bottom line summaries are still represented on the summary sheet and will also be represented on the share point site. I have been reading about data view web parts but can't see the connection to Excel. Thank yo...

Hide rows based on selection in drop down list....
Hello. I was windering if anyone can tell me how to automatically hide/show rows in a spreadsheet based on the "Yes" or "No" choice in a drop down list? Can anyone help me here? I am having trouble reading responses on this group, so if you could copy my email with any response it would be really appreciated. sean_goldsworthy@hotmail.com Thanks! Sean ...

Phone List in activities view
Hi I can't seem to work out how to show the phone number, company, and contact in the activities view when looking at phoen activities...surely this is possible! I'd be grateful if somebody tell me how please...in a non programming way? Thank you in advance al go to the activities screen then at the top click type and choose phone call. You should now see the phone numbers as well. ======================= John O'Donnell Microsoft CRM MVP http://www.crowechizek.com/microsoft "al davies" <al davies@discussions.microsoft.com> wrote in message news:6AEEBAE...

disable out-of-office for (non-exchange) mailing lists
> At 10:07 PM 7/29/2004, you wrote: >>> >>> >>>Please disable your Out of Office AutoReply feature for the perl-win32-users mailing list. >>> >>>Thank you, >>>Randy. > > Randy, > > I would be glad to do so, but my employer has recently switched from our Unix system with a .forward to a generic web MS Exchange-based interface (although I continue to use Eudora as my POP email client). > > Do you have any idea how to perform selective out-of-office responses in an Exchange-based email environment? It is...

Comprehensive listing of all natural accounts across multiple data
Hi, I need a report that gives me a list of all natural accounts used across multiple GP databases (32 companies in all) Is it doable through smartlist builder or FRX? Thanks, GG You could do a multi-company smartlist. You could also do a union across all of the company databases. Do you want the natural account to appear just once in your list? "GG" wrote: > Hi, > > I need a report that gives me a list of all natural accounts used across > multiple GP databases (32 companies in all) Is it doable through smartlist > builder or FRX? > ...

Business Alert vs SQL Trigger
Hello all, Scenario: If the Document Amount (DOCAMNT field in SOP10100) in the SOP Entry is changed, then i need to send a mail. For the above scenario, which is the best method? SQL Trigger or Business alert in GP. Thanks, Prakash Prakash, The answer is... it depends! Here are some reasons for using Business Alerts: 1) Friendly user interface 2) Isolates the user from SQL server complexities 3) Isolates the user from knowing physical table and field names Here are some reasons for using SQL Server Triggers: 1) Cascading database events (inserts, updates, deletes) affecting other t...

point tasks list in outlook today to a public folder task list
I want the Outlook Today page to show tasks located in a public folder not the personal folder task list. Is there a way to do this. It isn't an option in the outlook today customization options. ...

How to retrieve the font list ???
Hello, How can I get the font list in NT ? Eric -- ========================== If you know what you are doing, it is not called RESEARCH! ========================== In article <O3N3RbMMEHA.1340@TK2MSFTNGP12.phx.gbl>, echow@macaucabletv.com says... > Hello, > > How can I get the font list in NT ? EnumFontFamiliesEx is the most common way. Actually, the MOST common way is probably to just let the user select from the font selection common dialog, but if you're not going to do that... -- Later, Jerry. The universe is a figment of its own imagination. ...

Contact List Funkiness
A friend is using Outlook XP and is having a small problem with his contact list. If he right clicks a contact in his contact list and chooses New Message to Contact he gets an error basically stating the e-mail address is invalid. However, we've confirmed that the Email address field does have a valid e-mail address (the format is correct also). Now, also related to this. In the To: field in a new message, if he types the name of a contact and hits send, he gets the dialog box that basically wants to confirm who he is wanting to send the message to. However, when he chooses the c...

Where are the pop-up contact list saved #2
I have outlook 2003. When I start typing a name a pop-list shows the contacts that match my typing. When I go to contacts I cannot find this list. I need to update some of these addresses how can I find them ----When you start to type the address and the wrong address appears on th list, use your arrow keys to highlight that wrong address, then pres [DELETE]. That'll remove the bad ones. -- response from Ben M. Schorr, MVP-OneNote (search by author ------ Great. I will delete the duplicates but where are they stored. Why aren't these contacts listed under frequent contacts? I woul...

Need Macro to manipulate the selected object on double clik.
I need this bad but can't take the time to learn visual basic etc. I need to take a double-clicked object (or keyboard short cut) and: 1. Flip it vertically 2. Rotate 180 degrees 3. If I am now seeing the backside, then apply a cross-hatch without changing the color or other properties. 4. Otherwise I am seeing the back side, (which is cross hatched), so remove the cross hatch. The selected object may be a group. Thanks in advance. ...

Running SQL statements in VBA
I am constantly getting an error when I run the following sql statement in docmd runsql: dim sqlcheck as string Dim db As DAO.Database Dim rs As DAO.Recordset db = currentdb sqlcheck = "SELECT Processing.Retort_id, Processing.labor_code, Processing.process_name, Processing.employee_name " & _ "FROM Processing WHERE (((Processing.Retort_id)=" & Me.retortid.Value & ") AND ((Processing.labor_code)=" & Me!select_labor & ") AND ((Processing.process_name)=" & Me!select_process & ") " & _ "AND...

Installing CRM SQL Lock failure.
Hi all, During install I get the following error: Action Microsoft.Crm.Setup.Server.ImportDefaultDataAction failed. The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration. The CRM and SQL reporting are on the same server and the database is on another dedicated SQL server. The servers are brand new dual processors with 2 gig of memory in and the SQL server is on 0% cpu all the time so its not actually a resource problem. The install is using t...

Table/List Control
Hi, I have a dialog application that needs to present output in a similar format as an excel spreadsheet, i.e Tabular. I was wondering which MFC contol(s) would allow me to do this and also how to add a button that would print out the dialog that contained the control. Is it possible to manipulate a list control within a dialog to do this? Any suggesstions would be welcome, Regards Macca Depending on how functionality you need, It might be harder to do it with a listctrl. Take a look at the MSFlex grid control. Here is an exmple of how to insert it into your project http://www.codegu...

Blocked senders list 01-29-10
I belong to a group that has several members who post messages about the same topic over and over and in the past I have been able to add them to my blocked senders list and filter them out. Recently my blocked senders have been getting through even though I keep readding them to the list. My os is vista. The filter worked for almost a year and just suddenly stopped. Has anyone else experienced this problem and is there a fix for it? You can try a couple of things. One is to compact and repair the database (www.oehelp.com/WMUTil/) and see if that helps. The other might be...