Stupid bit of SQL? that won't work ! HELP

[Mark]

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")

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.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

[Salad]

Mark wrote:

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.

For complex statements...such as yours...I normally slash and burn all
parans () in the whereclause. It's a PITA to figure out what's going on
otherwise.

Next, I may dim a variable as strSQL and store the SQL statement to it
and then open the recordset with strSQL. I can then use a Debug.Print
strSQL if I want to check the value of it in the immediate window.

It is possible that your use of quotes around control form names in the
statement are throwing things off. I would prefer some readability if
case I debug so I might to something like this:

Dim strSQL As STring
Dim blnArchive As Boolean
Dim intForceiD As Integer 'assumes forceID is integer

Select Case Forms("frmForce").OpenArgs
Case "Normal"
'set the form caption
Forms!frmForce!LblbInfoName.Caption = _
Forms!frmForce!lstOptions.Column(1)

'set the flag to True if the caption contains the
'word Archive
blnArchive = (Instr(Forms!frmForce!LblbInfoName.Caption,_
"Archive") > 0)

intForceID = Forms!frmForce!cboForce

strSQL = "Select .... From ......" & _
"Where tblForceProjectMain.Archived = " & blnArchive & _
" And tblForceProjectAllocation.ForceID = " & _
intForceID & ";"

'remove ' to print SQL statement...you can cut/paste the
'result into a new query (Query/New/Design/Close/View/SQL/Paste)
'and run it and see what happens and modify to suit.
'Debug.Print strSQL
set rst = currentdb.openrecordset(strSQL,dbopendynaset)

....... more code of yours follows.

I just think this is a better method to use in debugging a complex
statement.

[Don Leverton]

Hi Mark,

Not to be rude or anything ... But DAMN that's some ugly code!
You said "a week to wade through" ... ya I can see that!

You're going to have to explain what the eventual purpose is here, too, I
think.
The way I follow it (and to be honest I didn't try very hard) is that you
are opening a form ("Form B" ... is it THIS form?) from a different form
(Form A") and are trying to set the Recordsource for "Form B" from "Form A"
?

Three suggestions...

Suggestion #1.)
My usual loathing of that damned "_" line-contnuation character ... which
idiot dreamed that up?
Use a string variable (I usually use "MySQL") and cocatenation instead...
something like this (and "this" has not been syntax checked.)
I did notice that your SQL statement included an extra ")" after the
semi-colon which is probably causing you some grief.
Another benefit, I think to writing your SQL like this is that you can add a
"Debug.Print MySQL" and view the resulting SQL statement in the Immediate
Window.

************************************************** *******
Dim MySQL As String

MySQL = "" 'clear the string variable
MySQL = MySQL & "SELECT Min(tblForceProjectAllocation.ProjectID) AS
MinOfProjectID "
MySQL = MySQL & "FROM tblForceProjectAllocation "
MySQL = MySQL & "INNER JOIN tblForceProjectMain "
MySQL = MySQL & "ON tblForceProjectAllocation.ProjectID =
tblForceProjectMain.ProjectID "
MySQL = MySQL & "WHERE (((tblForceProjectMain.Archived) = "
MySQL = MySQL & "IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
"Archive") > 0, True, False) & ") "
MySQL = MySQL & "And ((tblForceProjectAllocation.ForceID) = "
MySQL = MySQL & " Forms("frmForce").Controls("cboForce") & ")) "
MySQL = MySQL & ";"
'Debug.Print MySQL

Set rst = CurrentDb.OpenRecordset(MySQL)

MySQL = "" 'clear the string variable again
MySQL = MySQL & "SELECT tblForceProjectMain.ProjectID,
tblForceProjectMain.ProjectName "
MySQL = MySQL & "FROM tblForceProjectMain "
MySQL = MySQL & "INNER JOIN tblForceProjectAllocation "
MySQL = MySQL & "ON tblForceProjectMain.ProjectID =
tblForceProjectAllocation.ProjectID "
MySQL = MySQL & "WHERE (((tblForceProjectMain.Archived) = "
MySQL = MySQL & "IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
'Archive') > 0, True, False)) "
MySQL = MySQL & "AND
((tblForceProjectAllocation.ForceID)=[Forms]![frmForce]![cboForce])) "
MySQL = MySQL & "ORDER BY tblForceProjectMain.ProjectName
MySQL = MySQL & ";"
'Debug.Print MySQL

Me.cboProjectName.RowSource = MySQL

************************************************** *******
There now... isn't that better? :)

Suggestion #2)
As you can see, I have used the same "MySQL" string variable for both
"procedures"...
You may want to assign them to seperate variables, which might even be a
good idea.
What I *think* I see below is that these SQL statements all seem VERY
similar in that they all seem to select records from "tblForceProjectMain"
Would there be an advantage to building a string ("MainSQL") that could be
re-used throughout this mess of code?
Could you even assign the SQL generation to a seperate Sub or Function
Procedure if you had to run it several times for different purposes?

Suggestion #3)
Rather than opening FormB from FormA *at all* ... Put FormB on formA as a
subform, and play with the subform's Recordsource from code in FormA. I have
been posting similar suggestions for quite a while now, so have a look at
Google to see if this idea may appeal to you as well

http://groups.google.ca/groups?hl=en...ss&btnG=Search

Suggestion #4)
Ya, I know I said "Three" above... :)

I think you could use the .Count property of your recordset directly instead
of:
" 'Check if there are any projects in the recordset
If Not IsNull(rst!MinOfProjectid) Then "

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Mark" <ms******@cwcom.net> wrote in message
news:d6**************************@posting.google.c om...

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")

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.GoodPractice)=IIf(InStr([Forms]![frmForce]![lblInfoNa
me].[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

[Mark]

OK
Cheers guys for that, have completely re written the code, so now have
a list box.

only the SQL code I now have is missing an operator and so I am stuck
again.

SELECT (tblForceProjectAllocation.ProjectID) AS ProjectID FROM
tblForceProjectAllocation INNER JOIN tblForceProjectMain ON
tblForceProjectAllocation.ProjectID = tblForceProjectMain.ProjectID
WHERE ((tblForceProjectMain.StatusID) = " &
Forms("frmForce")("lblInfoName").Caption & ") AND
((tblForceProjectAllocation.ForceID)= " &
Forms("frmForce").Controls("cboForce") & ");

Why won't this work?!?!?!?!

Cheers for any further thoughts.

"Don Leverton" <le****************@telusplanet.net> wrote in message news:<UJSlc.13781$LA4.7210@edtnps84>...

Hi Mark,

Not to be rude or anything ... But DAMN that's some ugly code!
You said "a week to wade through" ... ya I can see that!

You're going to have to explain what the eventual purpose is here, too, I
think.
The way I follow it (and to be honest I didn't try very hard) is that you
are opening a form ("Form B" ... is it THIS form?) from a different form
(Form A") and are trying to set the Recordsource for "Form B" from "Form A"
?

Three suggestions...

Suggestion #1.)
My usual loathing of that damned "_" line-contnuation character ... which
idiot dreamed that up?
Use a string variable (I usually use "MySQL") and cocatenation instead...
something like this (and "this" has not been syntax checked.)
I did notice that your SQL statement included an extra ")" after the
semi-colon which is probably causing you some grief.
Another benefit, I think to writing your SQL like this is that you can add a
"Debug.Print MySQL" and view the resulting SQL statement in the Immediate
Window.

************************************************** *******
Dim MySQL As String

MySQL = "" 'clear the string variable
MySQL = MySQL & "SELECT Min(tblForceProjectAllocation.ProjectID) AS
MinOfProjectID "
MySQL = MySQL & "FROM tblForceProjectAllocation "
MySQL = MySQL & "INNER JOIN tblForceProjectMain "
MySQL = MySQL & "ON tblForceProjectAllocation.ProjectID =
tblForceProjectMain.ProjectID "
MySQL = MySQL & "WHERE (((tblForceProjectMain.Archived) = "
MySQL = MySQL & "IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
"Archive") > 0, True, False) & ") "
MySQL = MySQL & "And ((tblForceProjectAllocation.ForceID) = "
MySQL = MySQL & " Forms("frmForce").Controls("cboForce") & ")) "
MySQL = MySQL & ";"
'Debug.Print MySQL

Set rst = CurrentDb.OpenRecordset(MySQL)

MySQL = "" 'clear the string variable again
MySQL = MySQL & "SELECT tblForceProjectMain.ProjectID,
tblForceProjectMain.ProjectName "
MySQL = MySQL & "FROM tblForceProjectMain "
MySQL = MySQL & "INNER JOIN tblForceProjectAllocation "
MySQL = MySQL & "ON tblForceProjectMain.ProjectID =
tblForceProjectAllocation.ProjectID "
MySQL = MySQL & "WHERE (((tblForceProjectMain.Archived) = "
MySQL = MySQL & "IIf(InStr([Forms]![frmForce]![lblInfoName].[Caption],
'Archive') > 0, True, False)) "
MySQL = MySQL & "AND
((tblForceProjectAllocation.ForceID)=[Forms]![frmForce]![cboForce])) "
MySQL = MySQL & "ORDER BY tblForceProjectMain.ProjectName
MySQL = MySQL & ";"
'Debug.Print MySQL

Me.cboProjectName.RowSource = MySQL

************************************************** *******
There now... isn't that better? :)

Suggestion #2)
As you can see, I have used the same "MySQL" string variable for both
"procedures"...
You may want to assign them to seperate variables, which might even be a
good idea.
What I *think* I see below is that these SQL statements all seem VERY
similar in that they all seem to select records from "tblForceProjectMain"
Would there be an advantage to building a string ("MainSQL") that could be
re-used throughout this mess of code?
Could you even assign the SQL generation to a seperate Sub or Function
Procedure if you had to run it several times for different purposes?

Suggestion #3)
Rather than opening FormB from FormA *at all* ... Put FormB on formA as a
subform, and play with the subform's Recordsource from code in FormA. I have
been posting similar suggestions for quite a while now, so have a look at
Google to see if this idea may appeal to you as well

http://groups.google.ca/groups?hl=en...ss&btnG=Search

Suggestion #4)
Ya, I know I said "Three" above... :)

I think you could use the .Count property of your recordset directly instead
of:
" 'Check if there are any projects in the recordset
If Not IsNull(rst!MinOfProjectid) Then "

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Mark" <ms******@cwcom.net> wrote in message
news:d6**************************@posting.google.c om...

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")

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.GoodPractice)=IIf(InStr([Forms]![frmForce]![lblInfoNa
me].[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

[Salad]

Mark wrote:

OK
Cheers guys for that, have completely re written the code, so now have
a list box.

only the SQL code I now have is missing an operator and so I am stuck
again.

SELECT (tblForceProjectAllocation.ProjectID) AS ProjectID FROM
tblForceProjectAllocation INNER JOIN tblForceProjectMain ON
tblForceProjectAllocation.ProjectID = tblForceProjectMain.ProjectID
WHERE ((tblForceProjectMain.StatusID) = " &
Forms("frmForce")("lblInfoName").Caption & ") AND
((tblForceProjectAllocation.ForceID)= " &
Forms("frmForce").Controls("cboForce") & ");

Why won't this work?!?!?!?!

Cheers for any further thoughts.

If your status id is a string, surround it in single quotes. Strings
have quotes, dates use #, numbers use nothing.
StatusID = '" & var & "'"
where var is now surrounded by single quotes.

If you assigned that string to a variable, you could enter a line in
your code to
Debug.Print strSQL
and then you could copy that SQL string that is printed in the immediate
window and paste it into a new query, and then attempt to run and
determine where you messed up.