|
the Following bit of code doesn't work.
It seems to respond to the second, starting with 'add iif statement
for Good Practice', but not to the first, starting 'add iif statement
for archived'
Help me to sort this out, it has taken me almost a week to wade
through and it still won't work.
Select Case Forms("frmForce").OpenArgs
Case "Normal"
Forms("frmForce").Controls("lblInfoName").Caption =
Forms("frmForce").Controls("lstOptions").Column(1)
'add iif statement for archived
Set rst = CurrentDb.OpenRecordset("SELECT
Min(tblForceProjectAllocation.ProjectID) AS MinOfProjectID FROM
tblForceProjectAllocation INNER JOIN tblForceProjectMain ON
tblForceProjectAllocation.ProjectID = tblForceProjectMain.ProjectID
WHERE (((tblForceProjectMain.Archived)=" &
IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption], "Archive") > 0,
True, False) & ") And ((tblForceProjectAllocation.ForceID)=" &
Forms("frmForce").Controls("cboForce") & "));")
Me.cboProjectName.RowSource = "SELECT
tblForceProjectMain.ProjectID, tblForceProjectMain.ProjectName FROM
tblForceProjectMain INNER JOIN tblForceProjectAllocation ON
tblForceProjectMain.ProjectID = tblForceProjectAllocation.ProjectID
WHERE (((tblForceProjectMain.Archived)=IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
'Archive') > 0, True, False)) AND
((tblForceProjectAllocation.ForceID)=[Forms]![frmForce]![cboForce]))
ORDER BY tblForceProjectMain.ProjectName;"
'Check if there are any projects in the recordset
If Not IsNull(rst!MinOfProjectid) Then
Me.cboProjectName = rst!MinOfProjectid
Me.RecordSource = "SELECT tblForceProjectMain.*
FROM tblForceProjectMain WHERE tblForceProjectMain.ProjectID=" &
Me.cboProjectName.Value & ";"
Call sbRefreshLinks
rst.Close
Set rst = Nothing
Else
Me.cboProjectName.SetFocus
Me.tabMain.Visible = False
Me.cmdDelete.Enabled = False
Me.RecordSource = ""
End If
Forms("frmForce").Controls("lblInfoName").Caption =
Forms("frmForce").Controls("lstOptions").Column(1)
'add iif statement for Good Practice
Set rst = CurrentDb.OpenRecordset("SELECT
Min(tblForceProjectAllocation.ProjectID) AS MinOfProjectID FROM
tblForceProjectAllocation INNER JOIN tblForceProjectMain ON
tblForceProjectAllocation.ProjectID = tblForceProjectMain.ProjectID
WHERE (((tblForceProjectMain.GoodPractice)=" &
IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption], "Good Practice")[color=blue]
> 0, True, False) & ") And ((tblForceProjectAllocation.ForceID)=" &[/color]
Forms("frmForce").Controls("cboForce") & "));")
Me.cboProjectName.RowSource = "SELECT
tblForceProjectMain.ProjectID, tblForceProjectMain.ProjectName FROM
tblForceProjectMain INNER JOIN tblForceProjectAllocation ON
tblForceProjectMain.ProjectID = tblForceProjectAllocation.ProjectID
WHERE (((tblForceProjectMain.GoodPractice)=IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
'Good Practice') > 0, True, False)) AND
((tblForceProjectAllocation.ForceID)=[Forms]![frmForce]![cboForce]))
ORDER BY tblForceProjectMain.ProjectName;"
'Check if there are any projects in the recordset
If Not IsNull(rst!MinOfProjectid) Then
Me.cboProjectName = rst!MinOfProjectid
Me.RecordSource = "SELECT tblForceProjectMain.*
FROM tblForceProjectMain WHERE tblForceProjectMain.ProjectID=" &
Me.cboProjectName.Value & ";"
Call sbRefreshLinks
rst.Close
Set rst = Nothing
Else
Me.cboProjectName.SetFocus
Me.tabMain.Visible = False
Me.cmdDelete.Enabled = False
Me.RecordSource = ""
End If
Case "Searching"
'Me.cboProjectName.RowSource = "SELECT
First(tblForceProjectMain.ProjectID) AS FirstOfProjectID,
tblForceProjectMain.ProjectName FROM tblForceProjectMain INNER JOIN
tblForceProjectAllocation ON tblForceProjectMain.ProjectID =
tblForceProjectAllocation.ProjectID GROUP BY
tblForceProjectMain.ProjectName HAVING
(((tblForceProjectMain.ProjectName) Is Not Null)) ORDER BY
tblForceProjectMain.ProjectName;"
Me.cboProjectName.RowSource = "SELECT
First(tblForceProjectMain.ProjectID) AS FirstOfProjectID,
[tblForceProjectMain]![ProjectName] & ' - ' & [tblForce]![ForceName]
AS FullProjectName FROM tblForce INNER JOIN (tblForceProjectMain INNER
JOIN tblForceProjectAllocation ON tblForceProjectMain.ProjectID =
tblForceProjectAllocation.ProjectID) ON tblForce.ForceID =
tblForceProjectAllocation.ForceID " & _
"GROUP BY
tblForceProjectMain.ProjectName, [tblForceProjectMain]![ProjectName] &
' - ' & [tblForce]![ForceName] HAVING
(((tblForceProjectMain.ProjectName) Is Not Null)) ORDER BY
[tblForceProjectMain]![ProjectName] & ' - ' & [tblForce]![ForceName];"
Me.cboProjectName = Me.cboProjectName.ItemData(0)
Me.RecordSource = "SELECT tblForceProjectMain.* FROM
tblForceProjectMain WHERE tblForceProjectMain.ProjectID=" &
Me.cboProjectName.Value & ";"
Call sbRefreshLinks
End Select
| 
