1) Change Combo4 Properties to the following
Code:
Row Source "UL";"OL";""
Row Source Type Value List
Bound Column 1
Limit to List Yes
2) Replace the entire VBA code module of the form with the following:
Code:
Option Compare Database
Option Explicit
Private Sub Combo0_AfterUpdate()
Combo2.Value = ""
Combo2.RowSource = _
"Select DISTINCT POWERCONTROL1.CELL " & _
"FROM POWERCONTROL1 " & _
"WHERE POWERCONTROL1.EXCHID = '" & Combo0.Value & "' "
End Sub
Private Sub Combo2_AfterUpdate()
Dim ULCount As Integer
Dim OLCount As Integer
Dim strULCount As String
Dim strOLCount As String
' Set up query conditions
strOLCount = "( ([EXCHID] = '" & Combo0.Value & "') " & _
"AND ([CELL] = '" & Combo2.Value & "') " & _
"AND ([SCTYPE] = 'OL') " & _
")"
' uncomment this line to display SQL
'MsgBox "OLCount SQL is" & vbCrLf & strOLCOunt
strULCount = "( ([EXCHID] = '" & Combo0.Value & "') " & _
"AND ([CELL] = '" & Combo2.Value & "') " & _
"AND (([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))" & _
")"
' uncomment this line to display SQL
'MsgBox "ULCount SQL is" & vbCrLf & strULCOunt
' verify that ULs or OLs exist
ULCount = DCount("[CELL]", "[POWERCONTROL1]", strULCount)
OLCount = DCount("[CELL]", "[POWERCONTROL1]", strOLCount)
' uncomment these lines to display counts
'MsgBox "OLCount is" & OLCount & " and ULCount is " & ULCount
If ULCount > 0 Then
Combo4.Value = "UL"
Call LoadValues(strULCount)
Else
If OLCount > 0 Then
Combo4.Value = "OL"
Call LoadValues(strOLCount)
Else
Combo4.Value = ""
Call ClearValues
End If
End If
End Sub
Private Sub Combo4_AfterUpdate()
Dim ULCount As Integer
Dim OLCount As Integer
Dim strULCount As String
Dim strOLCount As String
' Set up query conditions
strOLCount = "( ([EXCHID] = '" & Combo0.Value & "') " & _
"AND ([CELL] = '" & Combo2.Value & "') " & _
"AND ([SCTYPE] = 'OL') " & _
")"
' uncomment this line to display SQL
'MsgBox "OLCount SQL is" & vbCrLf & strOLCOunt
strULCount = "( ([EXCHID] = '" & Combo0.Value & "') " & _
"AND ([CELL] = '" & Combo2.Value & "') " & _
"AND (([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))" & _
")"
' uncomment this line to display SQL
'MsgBox "ULCount SQL is" & vbCrLf & strULCOunt
' verify that ULs or OLs exist
ULCount = DCount("[CELL]", "[POWERCONTROL1]", strULCount)
OLCount = DCount("[CELL]", "[POWERCONTROL1]", strOLCount)
' uncomment this line to display counts
'MsgBox "OLCount is" & OLCount & " and ULCount is " & ULCount
' If the selected SCType exists, load it,
' otherwise pop message and clear
Select Case Combo4.Value
Case "UL"
If ULCount > 0 Then
Call LoadValues(strULCount)
Else
MsgBox "No UL records are present"
Call ClearValues
End If
Case "OL"
If OLCount > 0 Then
Call LoadValues(strOLCount)
Else
MsgBox "No OL records are present"
Call ClearValues
End If
Case Else
Call ClearValues
End Select
End Sub
Private Sub ClearValues()
Text6 = ""
Text8 = ""
Text10 = ""
Text12 = ""
Text14 = ""
Text16 = ""
Text18 = ""
Text20 = ""
Text22 = ""
Text24 = ""
End Sub
Private Sub LoadValues(strOLUL As String)
Dim strBasic As String
strBasic = "(([EXCHID] = '" & Combo0.Value & "') " & _
"AND ([CELL] = '" & Combo2.Value & "'))"
Text6 = DLookup("[SSDESUL]", "[POWERCONTROL1]", strOLUL)
Text8 = DLookup("[LCOMPUL]", "[POWERCONTROL1]", strOLUL)
Text10 = DLookup("[QDESULAFR]", "[POWERCONTROL2]", strBasic)
Text12 = DLookup("[MSTXPWR]", "[POWER]", strOLUL)
Text14 = DLookup("[CLSRAMP]", "[CLS]", strBasic)
Text16 = DLookup("[SCLD]", "[SUBCELL]", strBasic)
Text18 = DLookup("[DTXD]", "[SYSINFO]", strBasic)
Text20 = DLookup("[FBOFFSP]", "[LOCATING1]", strOLUL)
Text22 = DLookup("[PSSTA]", "[LOCATING2]", strBasic)
Text24 = DLookup("[IHO]", "[IHO]", strOLUL)
End Sub
I believe you will find the new behavior acceptable.
Lessons to learn from this code -
1) When possible, build your SQL in a string variable in such a way that it can be reused in different places.
2) Table name is not strictly necessary in the conditions parameter of a Dlookup... which means that you only have two distinct conditions to test for, the ones WITH SCTYPE (passed in variable strOLUL), and the ones without SCTYPE (built in strBasic).
3) This code tests for UL, Null, and empty string:
Code:
(([SCTYPE] = 'UL') OR (NZ([SCTYPE],'') = ''))
4) This whole issue can be corrected (and should be corrected, and would have avoided most of this code) if you were to run updates on all the tables to change blanks/nulls to UL, since you have specified that blanks/nulls are equivalent to UL.
5) When you're doing the same thing in multiple places, you can build a called routine so that the code isn't duplicated unnecessarily.