to use QueryDefs or not? Combine to one query 1

[Trevahaha]

I want a listbox to display all the available user types a user can be, i.e. display all user types that they haven't already been selected

I have two tables for this:

tlkpUsrType - lookup of all user types (usrTypeID;usrTypeDesc;usrTypeCat)
tblUsrType - links a usrID to a user type (usrID;usrTypeID)

I was trying to do it with SQL sorta like:

I found a workaround by creating a query on the fly of:

Dim strSQL as String
Dim db as DAO.Database

 'The rest of the project I use ADO, but don't know how to rewrite queries other than DAO

[/green]

Set db = CurrentDb

strSQL = "SELECT tblUsrType.usrID, tblUsrType.usrTypeID " & _
   "FROM tblUsrType " & _
   "WHERE (((tblUsrType.usrID)=" & Me.usrID & "));"

db.QueryDefs("dqryUsrType").SQL = strSQL

strSQL = "SELECT tlkpUsrType.usrTypeID, tlkpUsrType.usrTypeDesc, tlkpUsrType.usrTypeCat, dqryUsrType.usrID " & _
   "FROM dqryUsrType RIGHT JOIN tlkpUsrType ON dqryUsrType.usrTypeID = tlkpUsrType.usrTypeID " & _
   "WHERE (((dqryUsrType.usrID) Is Null));"

lstBox.RowSource = strSQL
...

Anyways, so this is working, however, I'd like a way to do it without re-writing a query.. is there a way to combine this into one query?

THANKS! Let me know if you need any clarification.

Trevor

 

[RickSpr]

Here's one way to do it, with a subquery and a NOT IN predicate:
strSQL = "SELECT tlkpUsrType.usrTypeID, " _
& "tlkpUsrType.usrTypeDesc, tlkpUsrType.usrTypeCat " _
& "FROM tlkpUsrType " _
& "WHERE tlkpUsrType.usrTypeID NOT IN " _
& " (SELECT tblUsrType.usrTypeID " _
& " FROM tblUsrType " _
& " WHERE tblUsrType.usrID = " & Me.usrID & &quot"


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Trevahaha]

NEAT! I've never done sub-queries.. Thanks for this great example!