SQL for list manipulation (a mess)

  • Thread starter Thread starter Robin
  • Start date Start date
R

Robin

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
 
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.
 
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
 
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
 
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 said:
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
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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

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
Click to expand...
Click to expand...
 
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 said:
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.
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,
Click to expand...
[quoted text clipped - 289 lines]
Me.lblEc1.FontBold = False
Me.lblEc2.FontBold = False
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top