combo box

  • Thread starter Thread starter Armin
  • Start date Start date
A

Armin

How do I exclude some values from combo box? What I'm
trying to do is to exclude programs to which donor has
already donated from all available programs.

My code so far:

Dim strProg As String, strsql As String

strProg = " SELECT tblcontrib.Program,
tblcontrib.DonorID " & _
" FROM tblcontrib " & _
" WHERE (((tblcontrib.DonorID)=[Forms]![frmdonors]!
[DonorID]));"

this would return 1 or more values like : Food, Clothing,
SH

TABLE WITH ALL PROGRAMS, WHICH HAS 20 PROGRAMS

strsql = " SELECT tblprog.program FROM tblprog WHERE
((tblprog.program)NOT LIKE ' " & VarProg & ")"

Me!CmbProg.RowSource = strsql


This code works fine if strProg returns one value but when
it returns 2 or more it doesn't.
How do I put OR between every value that strProg returns?
I'm also open to any other suggestions.

Thanks in advance

Armin
 
How do I exclude some values from combo box? What I'm
trying to do is to exclude programs to which donor has
already donated from all available programs.
Click to expand...

A "Frustrated Outer Join" query would help here: select all the
programs, and use an Outer Join query to exclude those that have
entries for this donor in tblContrib. e.g.

SELECT [Program].Program
FROM tblProg
LEFT JOIN
(SELECT Program FROM tblContrib
WHERE tblContrib.DonorID = [Forms]![frmdonors]![DonorID]) AS T
WHERE T.Program IS NULL;
 
Back
Top