Option box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use the below code to set list23's rowsource after the user
selects a option button from the frame.

Basically I want to amend the original SQL satement for the rowsource:
SELECT DISTINCTROW qryContractInfo.ContractID, qryContractInfo.ContractDate,
qryContractInfo.SupplierID
FROM qryContractInfo
WHERE (((qryContractInfo.SupplierID) Like
[Forms]![frmSupplierMenu]![Text18]));

The 3 options are Active(End Date should be greater than date()),
Expired(end date should be smaller than date()
and all where all contracts should be displayed.

Can anyone spot any errors with this?

Private Sub FraContractStatus_AfterUpdate()
Dim strSQL As String

If FraContractStatus.Value = 1 Then
strSQL = "SELECT DISTINCTROW qryContractInfo.ContractID,
qryContractInfo.StartDate, qryContractInfo.EndDate,
qryContractInfo.SupplierID FROM qryContractInfo WHERE
(((qryContractInfo.EndDate)>Date()) AND ((qryContractInfo.SupplierID) Like
[Forms]![frmSupplierMenu]![Text18]));"
List23.RowSource = strSQL
ElseIf FraContractStatus.Value = 2 Then
strSQL = "SELECT DISTINCTROW qryContractInfo.ContractID,
qryContractInfo.StartDate, qryContractInfo.EndDate,
qryContractInfo.SupplierID FROM qryContractInfo WHERE
(((qryContractInfo.EndDate)<Date()) AND ((qryContractInfo.SupplierID) Like
[Forms]![frmSupplierMenu]![Text18]));"
List23.RowSource = strSQL
Else
strSQL = "SELECT DISTINCTROW tblContracts.ContractID,
tblContracts.StartDate, tblContracts.EndDate FROM tblContracts WHERE
((qryContractInfo.SupplierID) Like [Forms]![frmSupplierMenu]![Text18]));"
List23.RowSource = strSQL
End If

End Sub
 
I have now fixed this with the below code:
rivate Sub FraContractStatus_AfterUpdate()
Dim strSQL As String
Dim strFilter As String

Select Case FraContractStatus.Value
Case 1
strFilter = "qryContractInfo.EndDate < Date() AND"
Case 2
strFilter = "qryContractInfo.EndDate > Date() AND"
Case 3
strFilter = ""
End Select

strSQL = "SELECT DISTINCTROW qryContractInfo.ContractID,
qryContractInfo.ContractDate, qryContractInfo.SupplierID " & _
"FROM qryContractInfo WHERE " & strFilter & "
qryContractInfo.SupplierID Like [Forms]![frmSupplierMenu]![Text18];"

List23.RowSource = strSQL

End Sub
 
Back
Top