Hi All
Think I have it working, full code as below if anyone wants to take a look:
Code:
Private Sub SetListRS()Dim strRS As String
Dim strWhere As String
Dim rs As String
Dim strOrder As String
strOrder = " ORDER BY Master_Data2.[Valid From], Master_Data2.[Valid To]"
strRS = "SELECT DISTINCT Master_Data2.ID, Master_Data2.[POL Name], Master_Data2.[POD Name], Master_Data2.Carrier, Master_Data2.[Contract Type], Master_Data2.Contract, Master_Data2.[20GP All In], Master_Data2.[40GP All In], Master_Data2.[40HC All In], Master_Data2.[Valid From], Master_Data2.[Valid To], Master_Data2.Transit FROM FRT_Table INNER JOIN Master_Data2 ON FRT_Table.ID = Master_Data2.ID"
strWhere = ""
If POLCombo.Value <> "" Then
strWhere = strWhere & " And Master_Data2.[POL Name] in(" & POLCombo.Value & ")"
End If
If PODCombo.Value <> "" Then
strWhere = strWhere & " And Master_Data2.[POD Name] in(" & PODCombo.Value & ")"
End If
If CarrierCombo.Value <> "" Then
strWhere = strWhere & " And Master_Data2.Carrier in(" & CarrierCombo.Value & ")"
End If
'remove the leading " And "
If Len(strWhere) <> 0 Then
strWhere = Right(strWhere, Len(strWhere) - 4)
Else
If ExpiredCheck.Value = True Then
List8.RowSource = "Form_ListBox_OldDates_Query"
Exit Sub
End If
If viewDate.Value <> "" Then
List8.RowSource = "Form_ListBox_SingleDate_Query"
Exit Sub
End If
List8.RowSource = "Form_ListBox_Query"
Exit Sub
End If
If ExpiredCheck.Value = True Then
Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
Exit Sub
Else
End If
If viewDate.Value <> "" Then
strWhere = strWhere & " And (Master_Data2.[Valid From])<=Forms![Test Form]!viewDate And (Master_Data2.[Valid To])>=Forms![Test Form]!viewDate"
Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
Exit Sub
Else
End If
strWhere = strWhere & " And ((Master_Data2.[Valid To])>=Date())"
'MsgBox strRS & " Where " & strWhere & strOrder
Me.List8.RowSource = strRS & " Where " & strWhere & strOrder
End Sub
Private Sub POLCombo_AfterUpdate()
'getLBX(Me.lstPOLNAME, , , """")
If IsNull(POLCombo.Value) = True Then
Else
POLCombo.Value = """" & POLCombo.Value & """"
Call ClearLBX(Me.lstPOLNAME, , , """")
End If
SetListRS
End Sub
Private Sub PODCombo_AfterUpdate()
'getLBX(Me.lstPODName, , , """")
If IsNull(PODCombo.Value) = True Then
Else
PODCombo.Value = """" & PODCombo.Value & """"
Call ClearLBX(Me.lstPODName, , , """")
End If
SetListRS
End Sub
Private Sub CarrierCombo_AfterUpdate()
'getLBX(Me.lstCarrier, , , """")
If IsNull(CarrierCombo.Value) = True Then
Else
CarrierCombo.Value = """" & CarrierCombo.Value & """"
Call ClearLBX(Me.lstCarrier, , , """")
End If
SetListRS
End Sub
Private Sub lstPOLNAME_AfterUpdate()
POLCombo.Value = getLBX(Me.lstPOLNAME, , , """")
SetListRS
End Sub
Private Sub lstPODName_AfterUpdate()
PODCombo.Value = getLBX(Me.lstPODName, , , """")
SetListRS
End Sub
Private Sub lstCarrier_AfterUpdate()
CarrierCombo.Value = getLBX(Me.lstCarrier, , , """")
SetListRS
End Sub
So far it seems to do what I want it to do, so touch wood another hurdle overcome, thanks heaps for your help.