Option group and listbox

[Anne]

Hi all. Thanks in advance for any help.

I have a form with a lsitbox displaying studies. In the
form header, I have an option group (opt_FilterBy) whose
intention is to allow the user to filter the display in
the listbox to show
1 - All studies,
2 - Studies by support person (chosen via
cbo_SupportPerson) and
3 - Unsupported studies.

The listbox is unbound, row source type table/query. I am
having trouble with the row source. I'm using this
(minus, of course, the stuff between <>s).

SELECT tbl_Studies.StudyID, tbl_Studies.StudyName,
tbl_Studies.Supported_By
FROM tbl_Studies
WHERE (((tbl_Studies.Supported_By)=IIf([Forms]!
[frm_MainStudy]![opt_FilterBy]=1,<Here's where I want to
select all studies>,IIf([Forms]![frm_MainStudy]!
[opt_FilterBy]=2,[Forms]![frm_MainStudy]!
[cbo_SupportPerson],<Here's where I'm looking for null
values>))))
ORDER BY tbl_Studies.StudyName;

Anyone? Any ideas?

 

[Stewart Tanner]

Hi Anne,

In the afterupdate event of opt_FilterBy do something like the following
start aircode---------------------
dim mySQL as String
dim myCriteria as string

mySQL = " SELECT tbl_Studies.StudyID, tbl_Studies.StudyName,
tbl_Studies.Supported_By FROM tbl_Studies"

Select Case opt_FilterBy
case 1 ' All studies
myCriteria = ""
case 2 ' Studies by support person (chosen via cbo_SupportPerson)
myCriteria = " Where Supported_By =" & chr(34) &
ME.cbo_SupportPerson & CHR(34)
case 3 ' Unsupported studies.
myCriteria = " Where isnull(Supported_By)"
end select

mySQL = mySQL & " " & mycriteria
mySQL = mySQL & " ORDER BY tbl_Studies.StudyName;"

me.lstBox.rowsource = mysql

end aircode ------------------------------

You could pursue the IIF statement in the rowsource of the list box but you
are going to have to requery it in the event of opt_FilterBy anyway.

Stewart