Don't understand why this query crashes Access

Question

I have a query with a nested query in it like so:

select table1.name, table1.address 
       from table1 
       where table1.year=[forms]![form1]![year] 
             and table1.name not in 
             (select table2.name 
                     from table2 
                     where table2.year=[forms]![form1]![year])

This crashes Access every time. Am a doing something that isn't allowed?

Answer 1

I was able to get it to work by using VBA to build my query.

Essentially I wrote a function that sets rs=db.OpenRecordset("my subquery")

Then iterates through rs, and appends it to a string like so:

string=""
rs.MoveFirst
Do Until rs.EOF
string=string & "'" & rs.Fields(0) & "', "
rs.MoveNext
loop
if string <> "" then string=Left(string,len(string)-2)

Finally:

strSQL="select table1.name, table1.address from table1 where table1.year=forms]![form1]![year] and table1.name not in (" & string & ")"

Then during my afterupdate event

me![subform].form.Recordsource=myfunction()

Everything works now